Oracle-L: v$sqlarea & v$session

From: <Charlie_Mengler_at_HomeDepot.com>
Date: Wed, 12 Mar 2003 09:18:05 -0800
Message-ID: <F001.00567BF2.20030312091805@fatcity.com>

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

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.paddr
SQL> / no rows selected

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.sql_address SQL> / no rows selected

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.sql_address SQL> / SQL_TEXT

mwh_at_MWH.ARES.MWH.COM> select * from v$sess_io where consistent_gets > 10000000;

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- -------------

173 115422 78096265 30158 74924 954

308 29739 12804854 186511 3614 2961

827 8 18906753 120904 4 1075

1034 152 37413409 195408 40 2913

1067 150 18915634 129775 20 976

So exactly how do I join V$SQLAREA to V$SESSION?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Charlie_Mengler_at_HomeDepot.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 - 11:18:05 CST