Mark et. al,
Just a caveat based on a personal observation. For
pre-9i databases on some OS platforms(sorry it has
been a while so I can't remember the exact details),
the "CPU used by this session" metric had some
accounting issues and hence reflected much higher
values (by orders of magnitude) than its counterpart -
"CPU used when call started". Usually "CPU used by
this session" is higher than its counterpart, but not
by too much.
On those versions, "CPU used when call started"
provides a more accurate indication of CPU usage. This
was fixed in 9i. Having said that the preferred metric
to be used should be "CPU used by this session", as
this metric gets updated "more often" than the other.
"CPU used when call started" gets updated before "a
call is started" (parse, execute, fetch). Thought this
may be something for folks to keep in mind while using
them in queries.
Cheers,
Gaja
- Mark Leith <mark_at_cool-tools.co.uk> wrote:
> Prompt
> Prompt Show total CPU and statement CPU for current
> session (requires TIMED
> STATISTICS = TRUE)
> Prompt
>
> set verify off
> col UNAM format a20 word heading 'User'
> col STMT format a56 word heading 'Statement'
> col RUNT format a08 word heading 'CPU Time'
> col ltim format a20 word heading 'Logon Time'
> col etim format a20 word heading 'Connect Time'
> col PROG format a30 word heading 'Program|Client
> Terminal Details'
> col SID format a10 word heading 'SID/|Serial#'
> col DR format 999999999 heading 'Disk Reads'
> col BG format 999999999 heading 'Buffer Gets'
> col EX format 999999999 heading 'Executions'
> col rsecs format 999,999,999.00 heading "CPU
> time|(seconds)"
>
> Prompt Overall Top CPU for all connected session
> (used by this session)
> Prompt
> select nvl(username,'ORACLE PROC')||'
>
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','')
> UNAM,
>
> ss.program||'-'||ss.terminal||'('||ss.machine||') as
> '||ss.osuser
> PROG,
> ltrim(to_char(floor(se1.value*.01/3600),
> '09')) || ':'
> || ltrim(to_char(floor(mod(se1.value*.01,
> 360000)/60), '09')) || ':'
> || ltrim(to_char(mod(se1.value*.01, 60),
> '09')) RUNT,se1.value*.01
> rsecs
> from v$session ss, v$sesstat se1, v$statname sn1
> where se1.statistic# = sn1.statistic#
> and sn1.name like '%CPU used by this session%'
> and se1.sid = ss.sid
> -- and ss.username is not null
> and se1.value !=0
> ORDER BY 3 DESC,1
> /
> Prompt Overall Top CPU for all connected session
> (when call started)
> Prompt
> select nvl(username,'ORACLE PROC')||'
>
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','')
> UNAM,
>
> ss.program||'-'||ss.terminal||'('||ss.machine||') as
> '||ss.osuser
> PROG,
> ltrim(to_char(floor(se1.value*.01/3600),
> '09')) || ':'
> || ltrim(to_char(floor(mod(se1.value*.01,
> 3600)/60), '09')) || ':'
> || ltrim(to_char(mod(se1.value*.01, 60),
> '09')) RUNT,se1.value*.01
> rsecs
> from v$session ss, v$sesstat se1, v$statname sn1
> where se1.statistic# = sn1.statistic#
> and sn1.name like '%CPU used when call
> started%'
> and se1.sid = ss.sid
> -- and ss.username is not null
> and se1.value !=0
> ORDER BY 3 DESC,1
> /
>
> Prompt Top CPU for currently active statements
> Prompt
> select nvl(username,'ORACLE PROC')||'
>
('||ss.sid||')'||DECODE(SS.AUDSID,userenv('SESSIONID'),'**','')
> UNAM,
> --
> ss.program||'-'||ss.terminal||'('||ss.machine||') as
> '||ss.osuser
> PROG,
>
>
ltrim(to_char(floor((se1.value*.01-se2.value*.01)/3600),
> '09')) ||
> ':'
> ||
>
ltrim(to_char(floor(mod((se1.value*.01-se2.value*.01),
> 3600)/60),
> '09')) || ':'
> ||
> ltrim(to_char(mod((se1.value*.01-se2.value*.01),
> 60), '09'))
> RUNT,
> se1.value*.01-se2.value*.01 rsecs,
> NVL(SQL.SQL_TEXT,'***NO known SQL***')
> STMT
> from v$session ss, v$sesstat se1, v$sesstat se2,
> v$statname sn1, v$statname
> sn2
> , V$SQL SQL
> where se1.statistic# = sn1.statistic#
> and se2.statistic# = sn2.statistic#
> and sn1.name like '%CPU used by this session%'
> and sn2.name like '%CPU used when call
> started%'
> and se1.sid = ss.sid
> and se2.sid = ss.sid
> and ss.username is not null
> and se1.value !=0
> and se2.value !=0
> and SS.SQL_ADDRESS = SQL.ADDRESS --(+)
> and SS.SQL_HASH_VALUE = SQL.HASH_VALUE --(+)
> ORDER BY 3 DESC,1
> /
>
> clear columns
>
> ===================================================
>
> I'm not sure where this is from (so can't point to
> an author)
>
> Mark
>
> -----Original Message-----
> Bernaus Berraondo
> Sent: 27 March 2003 15:14
> To: Multiple recipients of list ORACLE-L
>
>
>
> I usually track our HP-UX 11.0 system with the
> 'top' command so I can
> notice when the system is under slow performance. If
> that happens, I use
> Toad to look for any active Oracle SQL query which
> may be heavy enough for
> degrading the performance.
>
> I think my question is simple, but since I am a
> newbie on this... how can I
> see who is executing an Oracle SQL that is taking
> all our CPU provided that
> I only see his PID with the TOP command? I only see
> the oracle process, but
> I don't know how to get the username and the SQL
> beside him.
>
> Thank you for your answers!
>
> Fermin.
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Fermin Bernaus Berraondo
> INET: fbernaus_at_sammic.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Mark Leith
> INET: mark_at_cool-tools.co.uk
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gaja Krishna Vaidyanatha
INET: oraperfman_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Mar 27 2003 - 12:14:38 CST
