Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: v$sqlarea & v$session

Re: v$sqlarea & v$session

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 12 Mar 2003 15:33:51 -0800
Message-ID: <F001.005685C4.20030312153351@fatcity.com>

Just as a passing note - when you
query v$sqlarea like this it thrashes
the heck out of the library cache latch.

(And v$sqlarea is also an aggregate
view of x$kglob - so for a large shared pool you could end up with a massive
sort and thrash of the temporary tablespace as the view is instantiated. You might be better off going for v$sql - it still thrashes the latch, but it may cost a lot less
CPU and TEMP to run).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>
> THANKS AK!
>
>
> 1 select oc.sid, ss.serial#, user_name, osuser, sa.sql_text
> 2 from v$open_cursor oc, v$session ss, v$sqlarea sa
> 3 where oc.sid = ss.sid
> 4 and oc.address = sa.address
> 5 and oc.hash_value = sa.hash_value
> 6* and buffer_gets > 100000000
> SQL> /
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Mar 12 2003 - 17:33:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US