v$session.sql_hash_value = v$sqlarea.hash_value or v$session.sql_address = v$sqlarea.address
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, March 12, 2003 10:18 AM > > 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 > -------------------------------------------------------------------------- ------ > 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 > > > [EMAIL PROTECTED]> 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: [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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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).