Re: V$ Views
From: <art_at_unsu.com>
Date: Wed, 27 May 2009 10:34:12 -0700 (PDT)
Message-ID: <dda96823-cfbf-40ca-9254-86accb279b25_at_s16g2000vbp.googlegroups.com>
On May 27, 12:13 pm, ddf <orat..._at_msn.com> wrote:
> 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
Date: Wed, 27 May 2009 10:34:12 -0700 (PDT)
Message-ID: <dda96823-cfbf-40ca-9254-86accb279b25_at_s16g2000vbp.googlegroups.com>
On May 27, 12:13 pm, ddf <orat..._at_msn.com> wrote:
> 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
David,
We are running 10.2.0.1.0.
Your last example seems more in line with what I have, which would mean that I need to divide by executions....
SQL_ID EXECUTIONS ELAPSED_SEC CPU_SEC ------------- ---------- ----------- ----------
0h6b2sajwb74n 32254492 .000398411 .000361647 6yzdq8g95c8v1 18357941 .00022399 .000156537 0k8522rmdzg4k 15751217 .000961001 .000886548 2skwhauh2cwky 10006004 .000193552 .000128388 1cjdhywpf9bg9 7551700 .000177851 .000142995 grv576tam3wz6 7188628 .003312115 .002948188 d9jqr1btu2v65 7066942 .000218718 .000174304 grv576tam3wz6 6460058 .00400273 .003484914 d9jqr1btu2v65 5736842 .000207206 .000186247 d9jqr1btu2v65 3529968 .000287203 .000251645 d9jqr1btu2v65 3345433 .000262384 .000214567 grv576tam3wz6 2559235 .004387624 .003726234 az232kvbv7nsg 2542745 .000177958 .000145767 grv576tam3wz6 2363772 .005305754 .004247998d9jqr1btu2v65 2323516 .000291011 .00025257 grv576tam3wz6 1986615 .003537017 .003095385 4m7m0t6fjcs5x 1899141 .00130906 .0010982 3swtwfrkp2ycb 1863842 .005732104 .003898867 g781rgvstnwj5 1748359 .002057874 .00154739
Though that is saying that they all took only a fraction of a second....unless I need to divide by some further number. Received on Wed May 27 2009 - 12:34:12 CDT