CPU Top Sql's

 

clear breaks

Set lines 240 feed 0 verify off num 15

column parses format 9999999999

column sql_id format a14

column execs format 9999999999

column PLAN_HASH_VALUE format 9999999999 heading plan_hash

prompt

prompt

Select * from (

select  sql_id, plan_hash_value,CPU_TIME,DELTA_CPU_TIME DCPU_TIME,

PARSE_CALLS parses,DELTA_PARSE_CALLS Dparses,EXECUTIONS execs,DELTA_EXECUTION_COUNT dexecs, BUFFER_GETS, DELTA_BUFFER_GETS DBGETS,DISK_READS, DELTA_DISK_READS DDREADS,

ROWS_PROCESSED ROWSPR,DELTA_ROWS_PROCESSED DROWSPR

from v$sqlstats where LAST_ACTIVE_TIME >(sysdate -      180/1440)  order by DELTA_CPU_TIME desc ) where rownum < 25;

undef minutes_sinse

undef orderby

undef orderbydesc

========================

sysdate - 8/24

===================

Select 

   ss.username,

   se.SID,

   VALUE/100 cpu_usage_seconds

from

   v$session ss, 

   v$sesstat se, 

   v$statname sn

where

   se.STATISTIC# = sn.STATISTIC#

and

   NAME like '%CPU used by this session%'

and

   se.SID = ss.SID

and 

   ss.status='ACTIVE'

and 

   ss.username is not null

order by VALUE desc;  

=============================================

ttitle "TOP CPU SESSIONS:"

set lines 150

set pages 50

select rownum as rank, a.*

from (

    SELECT v.sid,sess.osuser,sess.username,program, v.value / (100 * 60) CPUMins

    FROM v$statname s , v$sesstat v, v$session sess

   WHERE s.name = 'CPU used by this session'

     and sess.sid = v.sid

     and v.statistic#=s.statistic#

     and v.value>0

   ORDER BY v.value DESC) a

where rownum < &1;

=====================================================

col USERNAME for a15

col osuser for a10

col machine for a15

col EVENT for a30

select sid, serial#, username, osuser,machine, sql_id, PREV_SQL_ID,event, status, last_call_et,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') logon_time from v$session where sid in

(SELECT v.sid

    FROM v$statname s , v$sesstat v, v$session sess

   WHERE s.name = 'CPU used by this session'

     and sess.sid = v.sid

     and v.statistic#=s.statistic#

     and v.value>0

   ORDER BY v.value DESC

where rownum < &1);

==============================================

col name format a70

select statistic#,name from v$statname where upper(name) like '%CPU%';

STATISTIC# NAME

---------- ----------------------------------------------------------------------

         0 OS CPU Qt wait time

        10 recursive cpu usage

        16 CPU used when call started

        17 CPU used by this session

        61 IPC CPU used by this session

        64 global enqueue CPU used by this session

       229 gc CPU used by this session

       248 cell physical IO bytes sent directly to DB node to balance CPU

       581 parse time cpu

============================================================

alter session set nls_date_format='Dd-MON-YY HH24:MI:SS';

set lines 250

set pages 2000

col name format a26

col username format a15

col program format a40

col SESS_CPU_SECS wra format 999,999,999.99

col LAST_CPU_SECS wra format 999,999,999.99

col logon_secs  wra format 999,999,999

col Percent  wra format 999.99

select sess_cpu.sid, NVL(sess_cpu.username, 'Oracle Process') username, sess_cpu.status, sess_cpu.logon_time,  round((sysdate - sess_cpu.logon_time)*1440*60) logon_SECS, sess_cpu.value/100 SESS_CPU_SECS, (sess_cpu.value - call_cpu.value)/100 LAST_CPU_SECS, round ((sess_cpu.value/100)/round((sysdate - sess_cpu.logon_time)*1440*60)*100,2) Percent, sess_cpu.sql_id          

from

(select se.sql_id,ss.statistic#,se.sid, se.username, se.status, se.program, se.logon_time, sn.name, ss.value from v$session se, v$sesstat ss,

v$statname sn

where se.sid=ss.sid

and sn.statistic#=ss.statistic#

and sn.name in ('CPU used by this session') ) sess_cpu,

(select ss.statistic#,se.sid, ss.value, value/100 seconds from v$session se, v$sesstat ss, v$statname sn

where se.sid=ss.sid

and sn.statistic#=ss.statistic#

and sn.name in ('CPU used when call started') ) call_cpu

where sess_cpu.sid=call_cpu.sid

order by SESS_CPU_SECS ;