RE: Interesting problem

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Fri, 27 May 2005 14:22:13 -0700
Message-ID: <960A9B249C99994AA3C7FCA016A0DA8902AA09C7@ussccem08.corp.hds.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala
Sent: Friday, May 27, 2005 5:38 AM
To: dbvision_at_iinet.net.au
Cc: oracle-l_at_freelists.org
Subject: 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
--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 27 2005 - 17:27:04 CDT