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: <Charlie_Mengler_at_HomeDepot.com>
Date: Wed, 12 Mar 2003 11:19:54 -0800
Message-ID: <F001.00567EC9.20030312111954@fatcity.com>

>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



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"                                                                                                    
                      <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




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: 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

Original text of this message

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