Re: v$sqlarea & v$session

Charlie_Mengler_at_HomeDepot.com wrote:
>
> 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?
>

Charlie,

    I have noticed a number of (correct) replies about the (address, hash_value) join, however I am not sure that what those who answered and you have in mind is quite compatible. It looks like what you want is finding out who has issued this dreadful query, right ? The join with V$SESSION will only tell you who is still executing or has just executed the query. If somebody runs a cartesian join of death followed by a 'select null from dual', it's the latter you will join with. And no need to try to join on (PREV_SQL_ADDR, PREV_HASH_VALUE), it was buggy (meaning the same as the current pair) in all the versions I have checked. If you check FIRST_LOAD_TIME you will see that in most instances many statements stay for a long time in memory, so this terrible query may have been run a few hours ago.

   I think that for a delayed analysis the best you can use is PARSING_USER_ID in V$SQLAREA and join with USER_ID in DBA_USERS - only if PARSE_CALLS > 0 (otherwise you will have 0, NOT meaning SYS) - which then you can possibly relate to a current session, but this is a different problem. Not ideal if everybody connects under the same account, nor in an OLTP environment where zillion users are repeatedly executing the same statements. But it can be useful in a DSS environment where what you fear is the haphazard terrible query which is executed only once.  

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).