Re: V$ Views
From: <art_at_unsu.com>
Date: Wed, 27 May 2009 09:42:26 -0700 (PDT)
Message-ID: <9e24ef01-715f-4a0a-bcaa-8bf119568d7e_at_q14g2000vbn.googlegroups.com>
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
0k8522rmdzg4k 15044.48 13874.3492
grv576tam3wz6 7026.69076 6149.33877
2mj8narkuq7cz 5029.03737 4438.24 Received on Wed May 27 2009 - 11:42:26 CDT
Date: Wed, 27 May 2009 09:42:26 -0700 (PDT)
Message-ID: <9e24ef01-715f-4a0a-bcaa-8bf119568d7e_at_q14g2000vbn.googlegroups.com>
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 Received on Wed May 27 2009 - 11:42:26 CDT