Re: V$ Views

From: ddf <oratune_at_msn.com>
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

Original text of this message