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?