Re: Interesting problem

Quoting Mladen Gogala <gogala_at_sbcglobal.net>:
> 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