RE: v$sqlarea & v$session

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Wed, 12 Mar 2003 10:22:30 -0800
Message-ID: <F001.00567D37.20030312102230@fatcity.com>

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_at_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_at_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_at_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_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 - 12:22:30 CST