RE: Interesting problem

Mladen,

I think you are confusing V$SQL and V$SQLAREA - only the latter requires a
SORT since it is a GROUP BY on V$SQL. It is true though that the lib cache
latch needs to be taken for access to either. If Quest Spotlight goes
against V$SQLAREA, then they really don't know what they are doing. I also
believe that read consistency if provided for _some_ V$ views and not for
some others. 

The query below goes against V$SQLAREA, but may be worth trying:

select n.piece, n.sql_text, s.disk_reads, s.buffer_gets, s.rows_processed,
s.sorts, s.version_count, s.loaded_versions, s.open_versions,
s.users_opening, s.executions,
s.users_executing, s.first_load_time, s.parse_calls
from v$sqltext n, v$sqlarea s
where s.address = n.address
and s.hash_value = n.hash_value
and (n.address, n.hash_value) in
 ( select address, hash_value from (select s1.address, s1.hash_value
from v$sql s1 order by disk_reads desc)
 where rownum < 10)
order by n.address, n.hash_value, n.piece;

Hth,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
 
http://tahiti.oracle.com   - Manuals for DBAs (English only)
http://www.bibleserver.com - Manual for Life (in English, Deutsch, French,
Italian, Spanish, Portugese, Turkish,...)


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mladen Gogala
Sent: Friday, May 27, 2005 5:38 AM
To: dbvision@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: