Re: V$ Views
From: joel garry <joel-garry_at_home.com>
Date: Wed, 27 May 2009 09:04:29 -0700 (PDT)
Message-ID: <152020e4-cf49-4de3-a637-149b96af2f3b_at_y34g2000prb.googlegroups.com>
On May 27, 7: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......
Date: Wed, 27 May 2009 09:04:29 -0700 (PDT)
Message-ID: <152020e4-cf49-4de3-a637-149b96af2f3b_at_y34g2000prb.googlegroups.com>
On May 27, 7: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......
Sorry, all tapped out :-)
But seriously, much has been written about this. http://lmgtfy.com/?q=CPU+TIME+oracle+ELAPSED+TIME+jonathan+lewis (in particular, check out the Tanel Poder link).
jg
-- _at_home.com is bogus. The $200M data point. http://www3.signonsandiego.com/stories/2009/may/27/1b27facebook205025-facebook-sells-stake-russian-in/?uniontribReceived on Wed May 27 2009 - 11:04:29 CDT