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 ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 12 March 2003 23:04 > > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
