Oracle Database Discussions

Snapper shows (Oracle 11.2.0.2 on Linux SLES)

@snapper ash 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.52 by Tanel Poder @ E2SN ( http://tech.e2sn.com )


-----------------------------------------------------------------------
Active% | SQL_ID          | EVENT                     | WAIT_CLASS
-----------------------------------------------------------------------
   849% | 6k5p0akvc7b42   | library cache lock        | Concurrency
   100% | 6k5p0akvc7b42   | ON CPU                    | ON CPU
    93% | 2byjh4h8u2xz0   | ON CPU                    | ON CPU
    73% | 2qn7pc3c09cyr   | ON CPU                    | ON CPU
    24% | 9k3y572frxzys   | db file scattered read    | User I/O
    17% | b698xf4hs173p   | ON CPU                    | ON CPU
     7% |                 | ON CPU                    | ON CPU
     7% | 6k5p0akvc7b42   | cursor: pin S wait on X   | Concurrency
     5% | 6k5p0akvc7b42   | kksfbc child completion   | Other
     5% | bxud62qj6yjx1   | db file scattered read    | User I/O

--  End of ASH snap 1, end=2012-03-07 09:40:58, seconds=5, samples_taken=41

Currently many statements (sql_id 6k5p0akvc7b42) run in in several sessions in parallel
INSERT INTO xxx (...)
(SELECT ...
FROM yyy)


Now I wonder how to reduce the library cache locks

Might be increasing session_cached_cursors (currentyl 50) an approach?