| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: v$sqlarea & v$session
>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
bcm_at_MWH.ARES.MWH.COM> /
ADDRESS HASH_VALUE BUFFER_GETS
-------- ---------- -----------
SQL_TEXT
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"
<Rich.Jesse_at_qtiwo To: "'Charlie_Mengler_at_HomeDepot.com'" <Charlie_Mengler_at_homedepot.com>
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:Charlie_Mengler_at_HomeDepot.com]
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
bcm_at_MWH.ARES.MWH.COM> /
no rows selected
bcm_at_MWH.ARES.MWH.COM>
NOTHING seems to allow me to join V$SESSION to V$SQLAREA. :-(
"Jesse, Rich"
<Rich.Jesse_at_qtiwo To:
"'ORACLE-L_at_fatcity.com'" <ORACLE-L_at_fatcity.com>
rld.com> cc:
"'Charlie_Mengler_at_HomeDepot.com'" <Charlie_Mengler_at_homedepot.com>
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 rich.jesse_at_qtiworld.com 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:Charlie_Mengler_at_HomeDepot.com]
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
1 select sql_text
2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss5 where buffer_gets > 100000000
-- 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).Received on Wed Mar 12 2003 - 13:19:54 CST
 |
 |