Re: Interesting problem

Quoting Mladen Gogala <gogala@xxxxxxxxxxxxx>:
> 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

The way I understood the OP is that he was trying to catch a 
particular length of SQL duration. Not the "most expensive SQL". 
No sort needed.  Just look out for CPU_TIME or ELAPSED 
between certain values and capture info on the sessions found 
under that umbrella. Ie, minimal intrusion into v$sql.

> Think about it: you are using sql to see what sql statements have been
> recently executed.

Is there another way??


> there, it has to find it. There is no read consistency for V$ table, as Niall
> 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
> latch.
> Doing that once a minute, once per row is a recipe for disaster. 

I think a bigger disaster is to let bad SQL go unchecked.
I'm quite happy with temporarily slowing down the proceedings 
to catch a bad offender: much more to be gained.

And if the objective is to catch something taking 10 minutes, then 
once a minute is too often: maybe once every 5 minutes if v$sql
is used. Again, don't see the problem as that critical: this is not 
an on-going OLTP process, this is a once off exercise in catching a 
bad SQL or combination thereof. Hopefully not to be repeated.
If I have to use a bit of CPU to achieve that, so what?


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

Other related posts: