>From Window A
SQL> /
SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
CONSISTENT_CHANGES
---------- ---------- --------------- -------------- -------------
------------------
169 8385265 12700725 22864 266596
18526
173 182492 110868621 87457 118643
1836
225 29464 12818444 267665 3624
1303
308 29858 12805686 186559 3756
2982
671 28347 12803899 130510 2910
1027
827 8 18906753 120904 4
1075
934 14075 68243870 10341 12866
1501
1034 252 55919580 359701 255
8129
1067 150 18915634 129775 20
976
1157 8832 10268240 157532 1310
5328
1331 8 18925581 130679 98
7890
1334 47 66877945 56560 183
8407
12 rows selected.
>From Window B
1 SELECT address, hash_value, buffer_gets, sql_text
2 --,sid, username, osuser, logon_time
3 FROM v$sqlarea sa
4 --, v$session s
5 where buffer_gets > 100000000
6 --sa.address = s.sql_address
7 --and sa.hash_value = s.sql_hash_value
8* --and s.sid = 173
[EMAIL PROTECTED]> /
ADDRESS HASH_VALUE BUFFER_GETS
-------- ---------- -----------
SQL_TEXT
----------------------------------------------------------------------------------------------------
AA975444 3625081536 111745500
SELECT COUNT(*) FROM V_SB_PART V WHERE V.ID = :b1 AND V.VENDID = :b2
A41AB584 36165120 725314770
SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID
= :p1 AND 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(NVL(P.EFFDT,SYSDATE+1)) <=
TRUNC(SYSDATE) ORDER BY 1
AA64EF0C 1943687711 105332438
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
A2114824 14023929 106680698
select codes.dsc, codes.parm2, codes.cdlng, count(distinct batchpl.cdhid),
count(batchpl.pkgid), dep
t.id, dept.depnm, to_char(sysdate - (dept.caseplfreq/24/60),'HH24:MI:SS'),
dept.caseplsz, dept.casef
inalprttm, dept.caseplsort,
to_char(new_time(sysdate,'PST',dept.tmzn),'HH24:MI:SS') from codes,dept
,batchpl where batchpl.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' group by dept.id,dept.
depnm,dept.caseplfreq,dept.caseplsz,dept.casefinalprttm,
dept.caseplsort,codes.dsc,codes.parm2,codes
.cdlng,dept.tmzn order by
dept.id,dept.depnm,dept.caseplfreq,dept.caseplsz,dept.casefinalprttm, dept
.caseplsort,codes.dsc,codes.parm2,codes.cdlng,dept.tmzn
Back in Window A after query from B returns
QL> /
SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
CONSISTENT_CHANGES
---------- ---------- --------------- -------------- -------------
------------------
169 8409519 12841564 23381 267296
18808
173 183514 111385238 87477 119354
1854
225 29464 12818444 267665 3624
1303
308 29868 12805797 186611 3764
2982
671 28361 12803955 130513 2922
1027
827 8 18906753 120904 4
1075
934 14075 68243870 10341 12866
1501
1034 252 55919580 359701 255
8129
1067 150 18915634 129775 20
976
1157 8834 11017366 169782 1391
5745
1331 8 18925581 130679 98
7890
1334 47 72279674 60858 199
9414
12 rows selected.
A couple of the SID's show ever increasing CONSISTENT_GETS; such as
SID=173, 1334, etc.
I've NEVER, EVER gotten any rows returned when I attempt to join V$SESSION
& V$SQLAREA.
FWIW - The is V7.3.4.5 on Solaris V2.6
"Jesse, Rich"
<[EMAIL PROTECTED] To: "'[EMAIL PROTECTED]'"
<[EMAIL PROTECTED]>
rld.com> cc:
Subject: RE: v$sqlarea & v$session
03/12/2003 11:06
AM
Bummer! Looking back at the query, the user's probably not connected or
the
statement's not current... :(
Hopefully the sql_address-to-address join that others have suggested will
help more.
GL! :)
Rich
-----Original Message-----
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 12, 2003 12:25 PM
To: Jesse, Rich
I KNOW that is a lot of GETS;
which is exactly why I'm searching for the culprit.
1 select sql_text ,sid, username, osuser, logon_time
2 from v$sqlarea sa, v$session ss
3 where sa.buffer_gets > 100000000 -- that's a lot of gets!
4* and sa.hash_value = ss.sql_hash_value
[EMAIL PROTECTED]> /
no rows selected
[EMAIL PROTECTED]>
NOTHING seems to allow me to join V$SESSION to V$SQLAREA. :-(
"Jesse, Rich"
<[EMAIL PROTECTED] To:
"'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
rld.com> cc:
"'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
Subject: RE: v$sqlarea &
v$session
03/12/2003 10:18
AM
Perhaps this is what you're looking for?
select sql_text ,sid, username, osuser, logon_time
from v$sqlarea sa, v$session ss
where sa.buffer_gets > 100000000 -- that's a lot of gets!
and sa.hash_value = ss.sql_hash_value;
HTH! GL! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
p.s. West Bend, WI welcomes the new Home Depot to be built this summer!!
If this SQL helps you, can I get a discount?? ;)
-----Original Message-----
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 12, 2003 11:18 AM
To: Multiple recipients of list ORACLE-L
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
--
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).
