THANKS AK!
1 select oc.sid, ss.serial#, user_name, osuser, sa.sql_text
2 from v$open_cursor oc, v$session ss, v$sqlarea sa
3 where oc.sid = ss.sid
4 and oc.address = sa.address
5 and oc.hash_value = sa.hash_value
6* and buffer_gets > 100000000
SQL> /
SID SERIAL# USER_NAME OSUSER
---------- ---------- ------------------------------ ---------------
SQL_TEXT
--------------------------------------------------------------------------------
173 65 OPS$MISOPS misops
select codes.dsc, codes.parm2, codes.cdlng, count(distinct batchpl.cdhid),
count
(batchpl.pkgid), dept.id, dept.depnm, to_char(sysdate -
(dept.caseplfreq/24/60),
'HH24:MI:SS'), dept.caseplsz, dept.casefinalprttm, dept.caseplsort,
to_char(new_
time(sysdate,'PST',dept.tmzn),'HH24:MI:SS') from codes,dept,batchpl where
batch
pl.shploc = dept.id and batchpl.status in('WPL','BD') and batchpl.batchid
is nul
l and dept.id = codes.cd and codes.id = 'BATCHPLPRT' and dept.caseplflg
= 'Y' gr
oup by
dept.id,dept.depnm,dept.caseplfreq,dept.caseplsz,dept.casefinalprttm, dep
t.caseplsort,codes.dsc,codes.parm2,codes.cdlng,dept.tmzn order by
dept.id,dept.d
epnm,dept.caseplfreq,dept.caseplsz,dept.casefinalprttm,
dept.caseplsort,codes.ds
c,codes.parm2,codes.cdlng,dept.tmzn
341 40 DELS dels
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
479 931 BTAYLOR btaylor
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
535 230 JOSEAC joseac
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
563 852 MICHAELK michaelk
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
600 736 SBAKER sbaker
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
605 289 TYT tyt
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
649 149 RICKM rickm
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
727 159 DREWH drewh
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
783 327 BTAYLOR btaylor
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
934 155 JCURTIS jcurtis
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
978 233 SBAKER sbaker
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1056 196 MATTLA mattla
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1181 1303 MICHAELL michaell
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1237 246 RITAK ritak
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1269 1903 MICHELLY michelly
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1337 67 TOMS toms
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
17 rows selected.
"AK"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
.com> cc:
Sent by: Subject: Re: v$sqlarea & v$session
[EMAIL PROTECTED]
03/12/2003 10:39
AM
Please respond to
ORACLE-L
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
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, March 12, 2003 9: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: AK
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:
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).
