Re: V$ Views

From: ddf <oratune_at_msn.com>
Date: Wed, 27 May 2009 10:13:20 -0700 (PDT)
Message-ID: <8a88f6f9-cd02-49ad-81fe-97353d850f47_at_u10g2000vbd.googlegroups.com>



On May 27, 11:42 am, a..._at_unsu.com wrote:
> On May 27, 11:20 am, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On May 27, 9:04 am, a..._at_unsu.com wrote:
>
> > > On May 26, 3:42 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > > > On May 26, 12:58 pm, a..._at_unsu.com wrote:
>
> > > > > Hi,
>
> > > > > I'm looking at some Oracle documentation and they are not really clear
> > > > > on that the following items are in V$SESSION & V$PROCESS:
>
> > > > > V$SESSION - process
> > > > > V$PROCESS - pid & spid
>
> > > > > They cannot all be server process ID's.......
>
> > > > It becomes a little more clear when you actually check these things on
> > > > a real system.  Remember, Oracle has its process id, while the OS has
> > > > its own process id, which on some systems may not be a process at all.
>
> > > > Note the PADDR in V$SESSION is an address, which can be used to find
> > > > the process in V$PROCESS.  In there, there is the Oracle process, PID,
> > > > and the OS process, SPID.  The PROCESS column in V$SESSION is the
> > > > client process, so you may see the "process" for a completely
> > > > different OS.
>
> > > >   1  select sid, process from v$session
> > > >   2* where rownum < 4
> > > > SYS_at_XXXX > /
>
> > > >        SID PROCESS
> > > > ---------- ------------
> > > >        231 10616
> > > >        234 10616
> > > >        235 5684:4476
>
> > > > So here we see there are two Oracle session identifiers with the same
> > > > unix process, so obviously at least one of them is expired.  The one
> > > > with a colon in it is a client on Windows.
>
> > > > SYS_at_TPRD> select a.sid, a.process, b.pid, b.spid from v$session a, v
> > > > $process b where a.paddr=b.addr and a.sid in (231,234,235);
>
> > > >        SID PROCESS             PID SPID
> > > > ---------- ------------ ---------- ------------
> > > >        235 5684:4476           206 10436
> > > >        234 10616               208 10618
> > > >        231 10616               209 10620
>
> > > > If you were to do a ps -ef (or whatever your local equivalent is)
> > > > grepping for those processes, you might see children owned by the init
> > > > process, while others might show that they are local or remote
> > > > connections.
>
> > > > jg
> > > > --
> > > > _at_home.com is bogus.http://www3.signonsandiego.com/stories/2009/may/26/1n26texting235813-...
>
> > > Actually Joel, I'd like to tap your brain one more time.......
>
> > > Oracle refers to a CPU TIME & ELAPSED TIME.  What are those?  Is CPU
> > > time the actual time the process has been running?
>
> > > Also, from within Oracle, is there a good way to get CPU Usage in a
> > > number that makes sense?  Like a percentage?  And, lastly, can I find
> > > out how long a query has been 'running'?    not how long it will take,
> > > but how long it has been executing?   not using 'timing on', as this
> > > will run within a block.....
>
> > > Thanks for your time......- Hide quoted text -
>
> > > - Show quoted text -
>
> > No, ELAPSED TIME is the time the process has been running.  CPU TIME
> > is the actual time the cpu was used for the process, and it oftentimes
> > is less than the elapsed time since the cpu is not continuously used
> > by a process.  For example a process consumes 38 seconds from start to
> > finish (the elapsed time) but only used 3 seconds of processor time
> > (cpu time).
>
> > The CPU statistic is reporting the actual CPU time to the nearest
> > 1/100 of a second.  Any CPU time less than 10 milliseconds is reported
> > as 0; any time 1/100 of a second or longer is reported to the nearest
> > 1/100 of a second.
>
> > V$SESSION_LONGOPS can report on such activity provided you're
> > executing a long-running operation in that query/session.  Normally
> > exceptionally long-running queries don't put records in V
> > $SESSION_LONGOPS, so that's not likely the best way to time them.
> > There's always the ELAPSED_TIME column (reported in microseconds) in V
> > $SQL:
>
> >   1  select *
> >   2  from
> >   3  (select sql_id, elapsed_time/1000000 elapsed_sec, cpu_time/
> > 1000000 cpu_sec
> >   4  from v$sql
> >   5  order by 2 desc)
> >   6* where rownum <20
> > SQL> /
>
> > SQL_ID        ELAPSED_SEC    CPU_SEC
> > ------------- ----------- ----------
> > 0f04wnwfz1bym   93.070916   1.578125
> > 6gvch1xu9ca3g   88.197334   33.03125
> > 4b7087n60nt4u   33.680734    .109375
> > 4g4k54qrsawn6   33.180485    .171875
> > cfnrd41661bgr   27.783444    4.71875
> > duv4bca2kyw10   23.519094    4.40625
> > cvn54b7yz0s8u   21.597706      .4375
> > 3am9cfkvx7gq1   17.308811   1.453125
> > db78fxqxwxt7r   10.594218    .109375
> > 04xtrk7uyhknh    9.903296    .140625
> > gjm43un5cy843    9.458824    .359375
>
> > SQL_ID        ELAPSED_SEC    CPU_SEC
> > ------------- ----------- ----------
> > c2p32r5mzv8hb    9.221781    .390625
> > 3ktacv9r56b51    8.422042         .5
> > db78fxqxwxt7r    8.399519      .3125
> > 9bhvms9my13tg    7.630945      .1875
> > 04xtrk7uyhknh     7.17565    .296875
> > 1rswbxwhbpmr7    7.035718   7.046875
> > f6cz4n8y72xdc     6.65765   2.046875
> > 02577v815yp77    5.464186     .40625
>
> > 19 rows selected.
>
> > SQL>
>
> > This view should be updated on a regular basis while a query is
> > running.
>
> > David Fitzjarrell
>
> David,
>
> Thank you for your explanation.  It helps a lot.  but, running your
> query seems to give some strange results to me, unless I am absent
> minded an I'm missing something.  These numbers seem a bit out of
> range.....
>
> SQL_ID        ELAPSED_SEC    CPU_SEC
> ------------- ----------- ----------
> grv576tam3wz6  25028.2336 22062.3498
> grv576tam3wz6  23809.5651 21193.4261
> dg2vykyks141h  22688.4501 17100.6989
> 6snq0whxbkmqr  18853.7533 18708.7543
> 0k8522rmdzg4k    15044.48 13874.3492
> ap0j1njyb3vn6  14348.6968 13520.1365
> 0h6b2sajwb74n  12746.8809  11565.297
> grv576tam3wz6  12541.5917 10041.2976
> grv576tam3wz6  11228.9609 9536.30756
> 3swtwfrkp2ycb  10635.0792  7236.3313
> 8c1a1wkydnyka   10564.061 8038.75488
> azvbyb2qyvuba  9236.01433 8977.61836
> 02cmjb189v50v  9166.69735 8931.69056
> cxasp1p5dzar9  8039.53314 5617.07135
> grv576tam3wz6  7853.67191 6745.95971
> 76cmq4n6gpf4m  7121.56018 6951.01764
> 3t1sr60f4k08t  7082.10863 6924.18775
> grv576tam3wz6  7026.69076 6149.33877
> 2mj8narkuq7cz  5029.03737    4438.24- Hide quoted text -
>
> - Show quoted text -

Which release of Oracle are you using? My example was run on 11.1.0.6.0; running on 10.2.0.3.0 produces similar results:

SQL_ID ELAPSED_SEC CPU_SEC
------------- ----------- ----------

0ay748ut6y71y   105.12124   8.612823
9babjv8yq8ru3   50.005961  48.368822
2fn772jvq65x8   22.846689  18.603207
04xtrk7uyhknh   19.659478   1.092427
6ssrk2dqj7jbx   11.581409  11.434821
db78fxqxwxt7r    9.367865   2.450075
15zytu14qzw6p    9.125182   5.992813
d84j21mk6yg4t    8.968155   8.265884
7ng34ruy5awxq    8.777385   4.068298
4g058urgafqba    8.446415   7.893553
83taa7kaw59c1    8.069311   5.503922

SQL_ID        ELAPSED_SEC    CPU_SEC

------------- ----------- ----------
3uhv17xx0rdaw    7.597785   7.269046
96g93hntrzjtr     7.24189   2.090622
cfz686a6qp0kg    6.641422    .806988
cqgv56fmuj63x    6.396006    .831888
g2wr3u7s1gtf3    6.355499   6.245516
db78fxqxwxt7r    6.036362   1.154494

f2rwh1dn46zqr 5.939855 5.63228
6769wyy3yf66f 5.905408 2.183099

19 rows selected.

with 'reasonable' times. ELAPSED_TIME is the overall time of execution for the query; it doesn't accrue time just by sitting idle in the cache waiting for someone else to run it. I'm not certain if each new execution of that same cursor adds time to the existing value (the documentation doesn't mention that), but it might, and in that case you'd need to divide by EXECUTIONS to get a per-execution time:

  1 select *
  2 from
  3 (select sql_id, executions, (elapsed_time/1000000)/executions elapsed_sec, (cpu_time/1000000)/executions cpu_sec   4 from v$sql
  5 where executions > 0
  6 order by 2 desc)
  7* where rownum <20
SQL> / SQL_ID EXECUTIONS ELAPSED_SEC CPU_SEC ------------- ---------- ----------- ----------

9babjv8yq8ru3      75952  .000659001 .000637446
bsa0wjtftg3uw      42702  .000134341 .000130793
g2wr3u7s1gtf3      26212  .000243062 .000238866
6ssrk2dqj7jbx      24630  .000471257 .000465305
d84j21mk6yg4t      23281  .000385213 .000355048
4g058urgafqba      15064    .0005609 .000524199
96g93hntrzjtr      14293  .000506774 .000146369
6769wyy3yf66f      12548  .000470647 .000174002
3dd7j7ax4b8q3      11433   .00049602 .000456913
6kqp8mxqdpmjm      10084   .00031818 .000314584
9gg39pv272g1s       9279  .000482514 .000439094

SQL_ID        EXECUTIONS ELAPSED_SEC    CPU_SEC
------------- ---------- ----------- ----------
83taa7kaw59c1       7872  .001025113 .000699226
53saa2zkr6wc3       5466  .000548442 .000160457
2ym6hhaq30r73       4754  .000213815 .000149964
db78fxqxwxt7r       3995  .002344897 .000613285
04xtrk7uyhknh       2597  .007578848 .000420977
7ng34ruy5awxq       2597  .003380357 .001567077
3c1kubcdjnppq       2553  .000497127 .000365373
3uhv17xx0rdaw       2392  .003178662 .003041229

19 rows selected.

SQL> David Fitzjarrell Received on Wed May 27 2009 - 12:13:20 CDT

Original text of this message