Re: Lot of row cache/library pin

On Nov 29, 9:03 am, "astalavista" <nob..._at_nowhere.com> wrote:
> Hi,
>
> I have a database 9.2.0.6, with the wait events below:
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~                                                     %
> Total
> Event                                               Waits    Time (s) Ela
> Time
> -------------------------------------------- ------------ ----------- --------
> row cache lock                                     37,587      87,406
> 63.12
> library cache pin                                   6,565      17,853
> 12.89
> enqueue                                             5,255      15,093
> 10.90
> CPU time                                                       11,337
> 8.19
> db file sequential read                         1,880,609       2,261
> 1.63
>
> It is a DSS database used for a trainning
> so a lot of same select are done at the same moment
> and we have a slow  response time.
>
> What can I do to improve response time ?
> Thanks in advance

Do you have a any related error messages in your alert log for the row cache lock event?

Even if not some things you can look for especially if this is a RAC environment:

Check for nocache sequences or heavily used sequences with small cache sizes

Check if heavy recompilation of objects is occurring (Look for invalid objects and dependencies)

Reference: Potential reasons for "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "
Oracle support document 278316.1 -- additional potential issues listed but these two seemed most likely

If you have support you can check the document out plus look at the following articles:

There are issues related to materialized views: Bug 5138741 which is not confirmed till 10.2 but may also affect lower versions prior to 11g.

Bug 2605240 Row cache lock contention updating table with MATERIALIZED VIEW LOG in RAC #2605240.8

There is an issue on 9.2 with an unpublished bug where sql trace being on for a session can cause row cache enqueue hangs. Probably not your issue since you just seem to have a lot of waits.

HTH -- Mark D Powell -- Received on Sat Nov 29 2008 - 16:06:01 CST