Re: Interesting problem

On 05/27/2005 04:39:13 AM, dbvision@xxxxxxxxxxxx wrote:
> Quoting David Turner <dnt9000@xxxxxxxxx>:

> 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

Other related posts: