Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: v$sqlarea & v$session

Re: v$sqlarea & v$session

From: AK <oramagic_at_hotmail.com>
Date: Wed, 12 Mar 2003 10:39:31 -0800
Message-ID: <F001.00567DFE.20030312103931@fatcity.com>


charlie I think sqlarea and session has no direct relationship . Same sql can belong to many sessions and columns like users_opening, users_executing suggests this. There should be one entry in sqlarea per hashaddress I guess .
may user v$open_cursors to join with addess and sid .

-ak

>
> 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
>
>
> 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).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: AK
  INET: oramagic_at_hotmail.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:39:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US