Oracle Top sql  spent more on cpu/wait/io ?

ASH top sql

define 1='30'  ---- minute

SET LINESIZE 200

SET PAGESIZE 200

COLUMN sample_time     FORMAT a11

COLUMN metric_name     FORMAT a11

COLUMN active_sessions FORMAT 999,999.9999

WITH

cpu AS

(

  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time

       , DECODE(metric_name, 'CPU Usage Per Sec', 'CPU') metric_name

       , ROUND(value/1e2, 4) active_sessions

  FROM v$sysmetric_history

  WHERE metric_name = 'CPU Usage Per Sec'

  AND   group_id = 2

  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE

  ORDER BY sample_time

),

user_io AS

(

  SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time

       , swc.wait_class metric_name

       , ROUND(wcmh.time_waited/wcmh.intsize_csec, 4) active_sessions

  FROM v$waitclassmetric_history wcmh

     , v$system_wait_class swc

  WHERE wcmh.wait_class_id = swc.wait_class_id

  AND   swc.wait_class = 'User I/O'

  AND   wcmh.end_time >= SYSDATE - INTERVAL '&1' MINUTE

  ORDER BY sample_time

),

wait AS

(

  SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time

       , 'Wait' metric_name

       , SUM(ROUND(wcmh.time_waited/wcmh.intsize_csec, 4)) active_sessions

  FROM v$waitclassmetric_history wcmh

     , v$system_wait_class swc

  WHERE wcmh.wait_class_id = swc.wait_class_id

  AND   (swc.wait_class NOT IN ('Idle', 'User I/O'))

  AND   wcmh.end_time >= SYSDATE - INTERVAL '&1' MINUTE

  GROUP BY TO_CHAR(wcmh.end_time, 'hh24:mi:ss')

  ORDER BY sample_time

)

SELECT * FROM cpu

UNION ALL

SELECT * FROM user_io

UNION ALL

SELECT * FROM wait

;

SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS

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

08:17:54    CPU                  1.6762

08:18:54    CPU                  1.7645

08:19:54    CPU                  1.6364

08:20:55    CPU                  1.5903

set linesiz 300 pagesize 300

col sql for a50 

col SAMPLE_TIME for a10

col EVENT for a30

col kill for a17

select 

'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''' KILL,

ash.con_id,

    TO_CHAR(ash.SAMPLE_TIME,'HH24:MI') SAMPLE_TIME,

    ash.sql_id,

TOP_LEVEL_SQL_ID,

    SUBSTR(sa.sql_text,1,50) as SQL,

    ash.EVENT,

    ROUND(AVG(ash.TIME_WAITED),2) as AVG_TIME_WAIT,  

    COUNT(*) as INSTANCES 

  from 

    GV$ACTIVE_SESSION_HISTORY ash

    left join v$sqlarea sa on sa.sql_id = ash.sql_id 

  where 1=1

  and ash.sample_time>sysdate - interval '1' minute

--      AND ash.sample_time BETWEEN (SYSDATE-5/1440) AND SYSDATE

  --    AND ash.sample_time BETWEEN '24-NOV-22 01.00PM' AND '24-NOV-22 01.30PM'

      AND ash.SESSION_STATE = 'WAITING'

  group by

  '''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id,

  ash.con_id,

  TO_CHAR(ash.SAMPLE_TIME,'HH24:MI'),

    ash.sql_id,

TOP_LEVEL_SQL_ID,

    SUBSTR(sa.sql_text,1,50),

    ash.EVENT

  order by

    6 DESC;

set linesize 200 pagesize 200
select
ash.inst_id,
ash.sql_id,
ash.session_id, 
ash.session_serial#, 
ash.user_id, 
ash.program, 
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU", 
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" , 
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" , 
sum(decode(session_state,'ON CPU',1,1)) "TOTAL" 
from gv$active_session_history ash, v$event_name en 
where en.event# = ash.event# 
and SQL_ID is not NULL
and ash.user_id in (select USER_ID from dba_users where username not in ( 'SYS','SYSTEM','DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM','SYSDG','SYSBACKUP','GSMCATUSER','SYSKM' ,'OJVMSYS','DVSYS') )
-- and ash.session_type = 'BACKGROUND'
-- and ash.session_type = 'FOREGROUND'
-- and TO_CHAR(ash.sample_time) > '01-AUG-15 08.36.09.094 AM'
and user_id!=0
--and ash.session_state = 'WAITING'
group by ash.inst_id,sql_id,session_id,user_id,session_serial#,program 
order by sum(decode(session_state,'ON CPU',1,1)) desc 
/

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

set linesize 500
select
   stat.sql_id  sql_id ,
   stat.con_id,
  -- sql_id,
   replace (cast (dbms_lob.substr (text.sql_text, 100) as varchar (100)), chr (10), '')   sql_text,
   --
   executions                                                                             executions,
   --
   round(elapsed_time                                / 1000000  , 3)                      seconds_elapsed,
   round(cpu_time                                    / 1000000  , 3)                      seconds_cpu_elapsed,
   --
   round(elapsed_time / executions                   / 1000000  , 3)                      seconds_elapsed_per_exec,
   round(cpu_time     / executions                   / 1000000  , 3)                      seconds_cpu_elapsed_per_exec,
   round(iowait_time  / executions                   / 1000000  , 3)                      seconds_iowait_ela_per_exec,
   --
   disk_reads                                                                             disk_reads,
   buffer_gets                                                                            buffer_gets,
   writes                                                                                 writes,
   parses                                                                                 parses,
   sorts                                                                                  sorts
from
    ( select  --- {
               stat.sql_id sql_id,stat.con_id ,                    
          sum (stat.executions_delta    )      executions,
          sum (stat.elapsed_time_delta  )      elapsed_time,
          sum (stat.cpu_time_delta      )      cpu_time,
          sum (stat.iowait_delta        )      iowait_time,
          sum (stat.disk_reads_delta    )      disk_reads,
          sum (stat.buffer_gets_delta   )      buffer_gets,
          sum (stat.direct_writes_delta )      writes,
          sum (stat.parse_calls_delta   )      parses,
          sum (stat.sorts_delta         )      sorts
      from
          dba_hist_sqlstat   stat where snap_id in (
                                        ------------- See script find_snap_ids.sql
                                           select snap_id from dba_hist_ash_snapshot
                                           where 1=1
										   and    end_interval_time   > sysdate - 3/24 and    -- first snap
                                             begin_interval_time < sysdate 
											 -- last snap
                                        -------------
                                  )
      group by
          stat.sql_id,stat.con_id
    )                       stat  --- }
    join dba_hist_sqltext   text on stat.sql_id = text.sql_id
where
    executions > 0
order by
    seconds_elapsed desc;
	

====

-- without con_id

set linesize 700
col SQL_TEXT for a50 wrap
select
   stat.sql_id  sql_id ,
  -- stat.con_id,
  -- sql_id,
   replace (cast (dbms_lob.substr (text.sql_text, 100) as varchar (100)), chr (10), '')   sql_text,
   --
   executions                                                                             executions,
   --
   round(elapsed_time                                / 1000000  , 3)                      seconds_elapsed,
   round(cpu_time                                    / 1000000  , 3)                      seconds_cpu_elapsed,
   --
   round(elapsed_time / executions                   / 1000000  , 3)                      seconds_elapsed_per_exec,
   round(cpu_time     / executions                   / 1000000  , 3)                      seconds_cpu_elapsed_per_exec,
   round(iowait_time  / executions                   / 1000000  , 3)                      seconds_iowait_ela_per_exec,
   --
   disk_reads                                                                             disk_reads,
   buffer_gets                                                                            buffer_gets,
   writes                                                                                 writes,
   parses                                                                                 parses,
   sorts                                                                                  sorts
from
    ( select  --- {
               stat.sql_id sql_id,
--stat.con_id ,                    
          sum (stat.executions_delta    )      executions,
          sum (stat.elapsed_time_delta  )      elapsed_time,
          sum (stat.cpu_time_delta      )      cpu_time,
          sum (stat.iowait_delta        )      iowait_time,
          sum (stat.disk_reads_delta    )      disk_reads,
          sum (stat.buffer_gets_delta   )      buffer_gets,
          sum (stat.direct_writes_delta )      writes,
          sum (stat.parse_calls_delta   )      parses,
          sum (stat.sorts_delta         )      sorts
      from
          dba_hist_sqlstat   stat where snap_id in (
                                        ------------- See script find_snap_ids.sql
                                           select snap_id from dba_hist_ash_snapshot
                                           where 1=1
										   and    end_interval_time   > sysdate - 3/24 and    -- first snap
                                             begin_interval_time < sysdate 
											 -- last snap
                                        -------------
                                  )
      group by
          stat.sql_id
--,stat.con_id
    )                       stat  --- }
    join dba_hist_sqltext   text on stat.sql_id = text.sql_id
where
    executions > 0
order by
    seconds_elapsed desc;
	

====

http://anuj-singh.blogspot.com/2021/05/sql-history.html

gv$sqlarea to get the sql_text for a sql_id.

SELECT sql_text FROM gv$sqlarea WHERE sql_id = '&sql_id';


-- Shared Pool

alter session set "_rowsource_execution_statistics" = TRUE;
define sql_id='8payvrbu7460q'
col PLAN_TABLE_OUTPUT for a200
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));


-- AWR

set pagesize 80
define sql_id='8payvrbu7460q'
select * from table(dbms_xplan.display_awr(sql_id=>'&sql_id', format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive'));