Re: v$access + latch free(library cache) BUG???

  • From: Steve Adams <steve.adams@xxxxxxxxxxxx>
  • To: sjaffarhussain@xxxxxxxxx
  • Date: Tue, 28 Feb 2006 11:37:29 +1100

Hello Syed,

Any access to V$ACCESS on an instance with 1000+ connections creates at least short term contention for the library cache latches. That is not a bug. It is a consequence of all the work that is needed to instantiate the view. With your system already suffering contention for another latch, it is not strange that any reference to V$ACCESS would induce severe library cache latch contention. In short, I don't think that this is a bug.

@   Regards,
@   Steve Adams
@         - For DBAs
@  - For all

The Human Fly wrote:
Hello list,

We have faced a very very strange problem when accessing or desc v$access table.

Database : Oracle 9i Rel.2 (9205)
OS : AIX 5.3 JFS2 Filesystem.
CPU = 30+
No. Connections = 1000+

We are facing problem with latch free (cache buffer chains), when the
problem occurs, cpu becomes 0% ideal and we kill some process from
which transaction are coming.

I just desc v$access from the sql prompt, and strangly, all the
session are started waiting on 'latch free' and this time its was
library cache and when I kill the session which was desc v$access,
then, everything comes normal.

It was not just an accident, I can still reproduce the same error.
Whatever action I do on v$access, this is causing. If this was a bug,
we have same OS server, same oracle version for another database,
where this doesn't happens. The only differene between these two
databases are just no. of cpus.

It is really drawing me crazy. Does anyone has experience the same?

Best Regards,
Syed Jaffar Hussain

Other related posts: