Re: V$ Views
Date: Wed, 27 May 2009 09:20:10 -0700 (PDT)
Message-ID: <7e18560e-2b73-4e3e-bc9f-50c0fe168452_at_e24g2000vbe.googlegroups.com>
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 Received on Wed May 27 2009 - 11:20:10 CDT