Re: Interesting problem

On 05/27/2005 04:39:13 AM, dbvision_at_iinet.net.au wrote:
> Quoting David Turner <dnt9000_at_yahoo.com>:

> Maybe v$session is the wrong view? v$sql, columns CPU_TIME=20
> and ELAPSED_TIME spring to mind as better chances at
> catching the offender? Followed by snapshot stats for
> that session?

Nuno, querying V$QL on anything else then hash & address will hit the library cache latch once per row. Doing that once a minute may cause a problem. Adding sort to the mix to get "the most expensive SQL" will further cause bottlenecks. That is what Quest Spotlight is doing and I've heard testimonials about Spotlight using 100% of CPU when active.

Think about it: you are using sql to see what sql statements have been rece= ntly=20
executed. To do your bidding, the instance has to enter the sql you are usi= ng=20
to query it into the very same library cache that you are querying. If it's=  already
there, it has to find it. There is no read consistency for V$ table, as Nia= ll has
masterfully proven to me with V$SEGMENT_STATISTICS, so the instance has to = protect
each row from being modified while being queried. Hence - library cache lat= ch.
Doing that once a minute, once per row is a recipe for disaster.=20

--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l