Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

High library cache locks

TimWong765Mar 7 2012 — edited Mar 8 2012
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?
This post has been answered by 912595 on Mar 7 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 5 2012
Added on Mar 7 2012
26 comments
5,735 views