Re: V$ Views
From: <art_at_unsu.com>
Date: Wed, 27 May 2009 09:49:58 -0700 (PDT)
Message-ID: <0ad2d473-20d7-4dd9-bc87-2894864038a3_at_g20g2000vba.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
Date: Wed, 27 May 2009 09:49:58 -0700 (PDT)
Message-ID: <0ad2d473-20d7-4dd9-bc87-2894864038a3_at_g20g2000vba.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
Actually Dave, let me add something........are these 'Active' queries running? It is possible for a query to be in the buffer for a long period of time? Received on Wed May 27 2009 - 11:49:58 CDT