Message-ID: <F001.00567D37.20030312102230@fatcity.com>
Date: Wed, 12 Mar 2003 10:22:30 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Jesse, Rich" <Rich.Jesse@qtiworld.com>
Subject: RE: v$sqlarea & v$session
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Perhaps this is what you're looking for?

select sql_text ,sid, username, osuser, logon_time
from v$sqlarea sa, v$session ss
where sa.buffer_gets > 100000000	-- that's a lot of gets!
and sa.hash_value = ss.sql_hash_value;

HTH!  GL!  :)


Rich

Rich Jesse                        System/Database Administrator
rich.jesse@qtiworld.com           Quad/Tech International, Sussex, WI USA

p.s.  West Bend, WI welcomes the new Home Depot to be built this summer!!
If this SQL helps you, can I get a discount??  ;)


-----Original Message-----
[mailto:Charlie_Mengler@HomeDepot.com]
Sent: Wednesday, March 12, 2003 11:18 AM
To: Multiple recipients of list ORACLE-L


I'm suffering from a senior moment.
The question is at the every bottom.


SQL> select sql_text from v$sqlarea sa where buffer_gets > 100000000

SQL_TEXT
----------------------------------------------------------------------------
----
SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID
= :p1 AN
D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART)
= 'Y'
AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND
TRUNC(NV
L(P.EFFDT,SYSDATE+1)) <= TRUNC(SYSDATE) ORDER BY 1


  1  select sql_text
  2                  ,sid, username, osuser, logon_time
  3  from v$sqlarea sa
  4                                       , v$session ss
  5  where buffer_gets > 100000000
  6*  and sa.address    = ss.saddr
SQL> /

no rows selected

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse@qtiworld.com

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@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).