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 15:04:52 -0800
Message-ID: <F001.0056857C.20030312150452@fatcity.com>

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"                                                                                                       
                      <oramagic_at_hotmail        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>             
                      .com>                    cc:                                                                               
                      Sent by:                 Subject:  Re: v$sqlarea & v$session                                               
                      root_at_fatcity.com                                                                                           
                                                                                                                                 
                                                                                                                                 
                      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" <ORACLE-L_at_fatcity.com> 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
>
>
> 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?
>
>
>
> --
> 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).
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: AK
  INET: oramagic_at_hotmail.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).






-- 
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 - 17:04:52 CST

This message: [ Message body ]
Next message: Jonathan Lewis: "Re: v$sqlarea & v$session"
Previous message: Jared.Still_at_radisys.com: "RE: Import data only into existing schema"
Maybe in reply to: Charlie_Mengler_at_HomeDepot.com: "v$sqlarea & v$session"
Next in thread: Jonathan Lewis: "Re: v$sqlarea & v$session"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message

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