Oracle active session report ( ASH report ) ..
1 Hr Interval report SELECT TO_CHAR(SYSDATE-1/24, 'DD-MM-YYYY HH24:MI') bdate , TO_CHAR(SYSDATE , 'DD-MM-YYYY HH24:MI') edate FROM dual; BDATE EDATE ---------------- ---------------- 25-08-2017 09:34 25-08-2017 10:34 vi ash.sql ---======== VAR dbid NUMBER VAR inst_id NUMBER COL bdate NEW_VALUE def_bdate COL edate NEW_VALUE def_edate SET TERMOUT OFF SELECT TO_CHAR(SYSDATE-1/24, 'DD-MM-YYYY HH24:MI') bdate , TO_CHAR(SYSDATE , 'DD-MM-YYYY HH24:MI') edate FROM dual / SET TERMOUT ON ACCEPT bdate DATE FORMAT 'DD-MM-YYYY HH24:MI' DEFAULT '&def_bdate' PROMPT "Enter begin time [&def_bdate]: " ACCEPT edate DATE FORMAT 'DD-MM-YYYY HH24:MI' DEFAULT '&def_edate' PROMPT "Enter end time [&def_edate]: " BEGIN SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE'); END; / SPOOL ash_report.txt SET TERMOUT OFF PAGESIZE 0 HEADING OFF LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF --SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null )); SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null )); SPOOL OFF SET TERMOUT ON PAGESIZE 1000 HEADING ON PROMPT Done. ----========= End SYS@rac1> @ash Enter begin time [25-08-2017 09:28]: Enter end time [25-08-2017 10:28]: PL/SQL procedure successfully completed. SYS@rac1> SYS@icrac1> !ls -ltr total 284 -rw-r--r-- 1 oracle oinstall 15323 Aug 25 10:28 ash_report.txt !cat ash_report.txt cat: !cat: No such file or directory old 1: SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null )) new 1: SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('25-08-2017 09:28', 'DD-MM-YYYY HH24:MI'), TO_DATE('25-08-2017 10:28', 'DD-MM-YYYY HH24:MI'), null, null, null, null )) ASH Report For
==========
define report_type = 'text'
define begin_time = '-5'
define duration = ''
define report_name = 'ashrpt.text'
set echo off heading on underline on;
column inst_num heading "Inst Num" new_value inst_num format 99999
column inst_name heading "Instance" new_value inst_name format a12
column db_name heading "DB Name" new_value db_name format a12
column dbid heading "DB Id" new_value dbid format 9999999999 just c
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d, v$instance i
;
@?/rdbms/admin/ashrpt
undefine report_type
undefine begin_time
undefine duration
undefine report_name
====================================================================================VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/
define bdate='28:01:2022 19:00'
define edate='28:01:2022 20:10'
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));
================================================================================
--- rac
define slot_width = ''
define target_session_id = ''
define target_sql_id = ''
define target_wait_class = ''
define target_service_hash = ''
define target_module_name = ''
define target_action_name = ''
define target_client_id = ''
define target_plsql_entry = ''
define report_type = 'text'
define begin_time = '-5'
define duration = ''
define report_name = 'ashrpt.text'
set echo off heading on underline on;
column inst_num heading "Inst Num" new_value inst_num format 99999
column inst_name heading "Instance" new_value inst_name format a12
column db_name heading "DB Name" new_value db_name format a12
column dbid heading "DB Id" new_value dbid format 9999999999 just c
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d, v$instance i
;
@?/rdbms/admin/ashrpti
undefine report_type
undefine begin_time
undefine duration
undefine report_name
====
VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/
define FROM_TIMESTAMP='2023-06-01 14:00:00'
define TO_TIMESTAMP ='2023-06-02 14:00:00'
define sql_id='gqkr2um43ga39'
SELECT * from table(dbms_workload_repository.ash_global_report_text
( l_dbid => :dbid
, l_inst_num => :inst_id
, l_btime => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
, l_etime => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
, l_sid => NULL
, l_sql_id => '&sql_id'
, l_wait_class => NULL
, l_service_hash => NULL
, l_module => NULL
, l_action => NULL
, l_client_id => NULL
)
);
VAR dbid NUMBER VAR inst_id NUMBER BEGIN SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE'); END; / define bdate='15:07:2023 10:00' define edate='15:07:2023 11:10' SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null )); VAR dbid NUMBER VAR inst_id NUMBER BEGIN SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE'); END; / define FROM_TIMESTAMP='2023-07-15 10:00:00' define TO_TIMESTAMP ='2023-07-15 11:00:00' define sql_id='dnm7fqpf08p8w' SELECT * from table(dbms_workload_repository.ash_report_text ( l_dbid => :dbid , l_inst_num => :inst_id , l_btime => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS') , l_etime => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS') , l_sid => NULL , l_sql_id => '&sql_id' , l_wait_class => NULL , l_service_hash => NULL , l_module => NULL , l_action => NULL , l_client_id => NULL ) );
with out sql !!!VAR dbid NUMBER VAR inst_id NUMBER BEGIN SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE'); END; / define FROM_TIMESTAMP='2023-07-15 10:00:00' define TO_TIMESTAMP ='2023-07-15 11:00:00' define sql_id='dnm7fqpf08p8w' ----<<<<<< SELECT * from table(dbms_workload_repository.ash_report_text ( l_dbid => :dbid , l_inst_num => :inst_id , l_btime => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS') , l_etime => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS') , l_sid => NULL , l_sql_id => NULL , l_wait_class => NULL , l_service_hash => NULL , l_module => NULL , l_action => NULL , l_client_id => NULL ) ); ======
================
Awr report
set linesize 300 pagesize 300
col command for a150
SELECT
'spool awr_XYZ_inst_1_'
|| t.si
|| '_'
|| t.se
|| '.text '
|| CHR(10)
|| 'SELECT * FROM TABLE(dbms_workload_repository.awr_report_text('
|| t.dbid
|| ','
|| t.instance_number
|| ','
|| t.si
|| ','
|| t.se
|| '));'
|| CHR(10)
|| ' spool off;' command
FROM
(SELECT
dbid,
snap_id si,
snap_id + 1 se,
instance_number
FROM dba_hist_snapshot
WHERE 1=1
-- begin_interval_time > TO_DATE('27102019 18:00:00', 'ddmmyyyy hh24:mi:ss')
-- AND end_interval_time <= TO_DATE('27102019 19:30:00', 'ddmmyyyy hh24:mi:ss')
and begin_interval_time > sysdate -1
AND instance_number = 1
) t;
COMMAND
------------------------------------------------------------------------------------------------------------------------------------------------------
spool awr_XYZ_inst_1_33103_33104.text
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(1222414252,1,33103,33104));
spool off;
spool awr_XYZ_inst_1_33104_33105.text
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(1222414252,1,33104,33105));
spool off;
=============
--- global Report
set head off pages 0 lines 300 echo off feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );
=====
define sql_id='8cnh50qfgwg73' set linesize 300 pagesize 300 select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from v$instance), :BgnSnap,:EndSnap, '&sql_id'));
=====================
set linesize 300 pagesize 300 select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from gv$instance where instance_number=1), :BgnSnap,:EndSnap, '&sql_id')); set linesize 300 pagesize 300 select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from gv$instance where instance_number=2), :BgnSnap,:EndSnap, '&sql_id'));
===
Global report !!!
set linesize 300
VARIABLE DID NUMBER
exec select DBID into :DID from v$database;
SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, 1, SYSDATE-1/24, SYSDATE, l_wait_class=>'Other'));
SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, 1, SYSDATE-1/24, SYSDATE));
SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, '', SYSDATE-1/24, SYSDATE, l_wait_class=>'Other'));
set serveroutput on
declare
i_dbid number;
i_inst_id NUMBER;
begin
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
select dbid into i_dbid from v$database;
select instance_number into i_inst_id from v$instance;
for cursor1 in
(
select * from table( DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_text( l_dbid => i_dbid, l_inst_num => i_inst_id, l_btime => SYSDATE-1/24, l_etime => SYSDATE ))
)
LOOP
DBMS_OUTPUT.PUT_LINE( cursor1.output);
END LOOP;
end;
/
====
DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_TEXT(
l_dbid IN VARCHAR2(1023),
l_inst_num IN NUMBER,
l_btime IN DATE,
l_etime IN DATE,
l_options IN NUMBER DEFAULT 0, -- Not currently used by Oracle
l_slot_width IN NUMBER DEFAULT 0, -- Not currently used by Oracle
l_sid IN NUMBER DEFAULT NULL, -- v$session.sid
l_sql_id IN VARCHAR2 DEFAULT NULL, -- V$SQL.SQL_ID (Wildcard allowed)
l_wait_class IN VARCHAR2 DEFAULT NULL, -- v$event_name.wait_class (Wildcard allowed)
l_service_hash IN NUMBER DEFAULT NULL, -- v$active_services.name_hash
l_module IN VARCHAR2 DEFAULT NULL, -- v$session.module (Wildcard allowed)
l_action IN VARCHAR2 DEFAULT NULL, -- v$session.action (Wildcard allowed)
l_client_id IN VARCHAR2 DEFAULT NULL, -- v$session.client_identifier (Wildcard allowed)
l_plsql_entry IN VARCHAR2 DEFAULT NULL,
l_data_src IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
define FROM_TIMESTAMP='2020-04-28 15:00:00'
define TO_TIMESTAMP='2020-04-28 15:10:00'
SELECT * from table(dbms_workload_repository.ash_global_report_text
( l_dbid => (select dbid from v$database)
, l_inst_num => DECODE(upper('&INSTANCE_LIST'),'%',NULL,'&INSTANCE_LIST')
, l_btime => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
, l_etime => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
, l_sid => NULL
, l_sql_id => NULL
, l_wait_class => NULL
, l_service_hash => NULL
, l_module => NULL
, l_action => NULL
, l_client_id => NULL
)
);
/*
sysdate - interval '3' hour
sysdate - interval '5' MINUTE
*/
SELECT * from table(dbms_workload_repository.ash_global_report_text
( l_dbid => (select dbid from v$database)
, l_inst_num => ''
, l_btime => (SYSDATE - interval '3' hour)
, l_etime => (sysdate - interval '5' MINUTE)
, l_sid => NULL
, l_sql_id => NULL
, l_wait_class => NULL
, l_service_hash => NULL
, l_module => NULL
, l_action => NULL
, l_client_id => NULL
)
);
===
for sql !!
SELECT * from table(dbms_workload_repository.ash_global_report_text
( l_dbid => (select dbid from v$database)
, l_inst_num => ''
, l_btime => (SYSDATE - interval '3' hour)
, l_etime => (sysdate - interval '5' MINUTE)
, l_sid => NULL
, l_sql_id => '34cd4y8mbqvsk'
, l_wait_class => NULL
, l_service_hash => NULL
, l_module => NULL
, l_action => NULL
, l_client_id => NULL
)
);
====
Ash Top
set linesize 500 pagesize 300
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL event FOR A40 WORD_WRAP
SELECT * FROM (
SELECT /*+ LEADING(a) USE_HASH(u) */
COUNT(*) totalseconds
, ROUND(COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-1/24 AS DATE)) * 86400), 1) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, username,sql_id
, SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU"
, SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O"
, SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application"
, SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency"
, SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit"
, SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration"
, SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle"
, SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network"
, SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O"
, SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler"
, SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
, SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster"
, SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
, SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other"
-- , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
-- , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
-- , MAX(sql_exec_id) - MIN(sql_exec_id)
-- , COUNT(DISTINCT sql_exec_start||':'||sql_exec_id)
FROM
(SELECT
a.*
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
FROM gv$active_session_history a) a
, dba_users u
WHERE
a.user_id = u.user_id (+)
AND session_type='FOREGROUND'
AND sample_time BETWEEN sysdate-1/24 AND sysdate
GROUP BY username,sql_id
ORDER BY TotalSeconds DESC , username
)
WHERE ROWNUM <= 20
/
set linesize 300 pagesize 300
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen HEAD "Distinct|Execs Seen"
COL event FOR A42 WORD_WRAP
COL event2 FOR A46 WORD_WRAP
COL time_model_name FOR A50 WORD_WRAP
COL program2 FOR A40 TRUNCATE
COL username FOR A20 wrap
COL obj FOR A30
COL objt FOR A50
COL sql_opname FOR A20
COL top_level_call_name FOR A30
COL wait_class FOR A15
SELECT * FROM (
WITH bclass AS (SELECT class, ROWNUM r from v$waitstat)
SELECT /*+ LEADING(a) USE_HASH(u) */
10 * COUNT(*) "TotalSeconds"
, ROUND(10 * COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-2/24 AS DATE)) * 86400), 1) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, username,sql_id
-- , 10 * SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU"
-- , 10 * SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O"
-- , 10 * SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application"
-- , 10 * SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency"
-- , 10 * SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit"
-- , 10 * SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration"
-- , 10 * SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster"
-- , 10 * SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle"
-- , 10 * SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network"
-- , 10 * SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O"
-- , 10 * SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler"
-- , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
-- , 10 * SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
-- , 10 * SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other"
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
FROM
(SELECT
a.*
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, NVL(event, session_state)||
CASE
WHEN event like 'enq%' AND session_state = 'WAITING'
THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
THEN (SELECT class FROM bclass WHERE r = a.p3)
ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
END ||']'
ELSE null
END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
, CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2
, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
FROM dba_hist_active_sess_history a) a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND session_type='FOREGROUND'
AND a.sample_time BETWEEN sysdate-2/24 AND sysdate
AND a.dbid = (SELECT d.dbid FROM v$database d) -- for partition pruning
--AND a.snap_id IN (SELECT sn.snap_id FROM dba_hist_snapshot sn WHERE sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for partition pruning
AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN sysdate-2/24 AND sysdate) -- for2partition pruning
AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn
WHERE d.dbid = sn.dbid AND sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for patition pruning
GROUP BY
username,sql_id
ORDER BY
"TotalSeconds" DESC
, username,sql_id
)
WHERE
ROWNUM <= 20
/
with time !!!!
define FROM_TIMESTAMP='2022-04-28 15:00:00'
define TO_TIMESTAMP='2022-04-28 15:10:00'
set linesize 300 pagesize 300
COL "%This" FOR A7
--COL p1 FOR 99999999999999
--COL p2 FOR 99999999999999
--COL p3 FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex FOR A17
COL p2hex FOR A17
COL p3hex FOR A17
COL AAS FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen HEAD "Distinct|Execs Seen"
COL event FOR A42 WORD_WRAP
COL event2 FOR A46 WORD_WRAP
COL time_model_name FOR A50 WORD_WRAP
COL program2 FOR A40 TRUNCATE
COL username FOR A20 wrap
COL obj FOR A30
COL objt FOR A50
COL sql_opname FOR A20
COL top_level_call_name FOR A30
COL wait_class FOR A15
SELECT * FROM (
WITH bclass AS (SELECT class, ROWNUM r from v$waitstat)
SELECT /*+ LEADING(a) USE_HASH(u) */
10 * COUNT(*) "TotalSeconds"
, ROUND(10 * COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-2/24 AS DATE)) * 86400), 1) AAS
, LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, username,sql_id
-- , 10 * SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU"
-- , 10 * SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O"
-- , 10 * SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application"
-- , 10 * SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency"
-- , 10 * SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit"
-- , 10 * SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration"
-- , 10 * SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster"
-- , 10 * SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle"
-- , 10 * SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network"
-- , 10 * SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O"
-- , 10 * SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler"
-- , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
-- , 10 * SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
-- , 10 * SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other"
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
FROM
(SELECT
a.*
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, NVL(event, session_state)||
CASE
WHEN event like 'enq%' AND session_state = 'WAITING'
THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
THEN (SELECT class FROM bclass WHERE r = a.p3)
ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
END ||']'
ELSE null
END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
, CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2
, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
FROM dba_hist_active_sess_history a) a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND session_type='FOREGROUND'
AND a.sample_time BETWEEN sysdate-2/24 AND sysdate
AND a.dbid = (SELECT d.dbid FROM v$database d) -- for partition pruning
--AND a.snap_id IN (SELECT sn.snap_id FROM dba_hist_snapshot sn WHERE sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for partition pruning
AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN sysdate-2/24 AND sysdate) -- for2partition pruning
AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn
WHERE d.dbid = sn.dbid
-- AND sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for patition pruning
-- AND sn.begin_interval_time >=TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS') AND sn.end_interval_time <=TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
)
GROUP BY
username,sql_id
ORDER BY
"TotalSeconds" DESC
, username,sql_id
)
WHERE
ROWNUM <= 20
/
prompt ************************************
prompt **** ASH OVERALL WAIT PROFILE
prompt ************************************
prompt RR columns are ratio to report
define how_many_min_back='10' -----------!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Min
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
col MIN_ASH_AVAILABLE for a28
col MAX_ASH_AVAILABLE for a28
SELECT MIN(sample_time) min_ash_available,max(sample_time) max_ash_available,sysdate-MIN(sample_time) available_duration FROM v$active_session_history;
set linesize 300 pagesize 300
col EVENT for a40
select * from (
select NVL(event,'CPU') event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history
WHERE user_id<>0
AND sample_time<trunc(SYSDATE+1) AND sample_time>trunc(sysdate-1)
group by event
order by 2 desc
) where rownum<10;
prompt ************************************
prompt **** ASH I/O by SQL_ID
prompt ************************************
prompt RR columns are ratio to report
set linesize 300 pagesize 300
col EVENT for a30
COLUMN force_matching_signature FOR 999999999999999999999999999
select * from (
select
sql_id ,sql_plan_hash_value,force_matching_signature,NVL(event,'CPU') Event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history
where 1=1
AND wait_class LIKE '%I/O'
--AND event IS null
and user_id<>0
AND sql_id IS NOT NULL
group by sql_id,sql_plan_hash_value,event,force_matching_signature
order by 5 desc
) where rownum<30;
prompt ************************************
prompt **** ASH OVERALL WAIT PROFILE
prompt ************************************
prompt RR columns are ratio to report
SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available_duration FROM v$active_session_history;
select * from (
select NVL(event,'CPU') event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history
WHERE user_id<>0
AND SAMPLE_TIME > sysdate - ('&how_many_min_back'/(24*60))
group by event
order by 2 desc
) where rownum<10;
prompt ************************************
prompt **** ASH I/O by SQL_ID, Top 10
prompt ************************************
prompt RR columns are ratio to report
COLUMN force_matching_signature FOR 999999999999999999999999999
select * from (
select
sql_id ,sql_plan_hash_value,force_matching_signature,NVL(event,'CPU') Event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history
where 1=1
AND SAMPLE_TIME > sysdate - ('&how_many_min_back'/(24*60))
AND wait_class LIKE '%I/O'
--AND event IS null
and user_id<>0
AND sql_id IS NOT NULL
group by sql_id,sql_plan_hash_value,event,force_matching_signature
order by 5 desc
) where rownum<11;
prompt ************************************
prompt **** ASH DB_TIME by SQL_ID, Top 10
prompt ************************************
select * from (
select sql_id
, count(*) DBTime
, round(count(*)*100/sum(count(*))
over (), 2) pctload
from gv$active_session_history
where sample_time > sysdate - ('&how_many_min_back'/(24*60))
and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc
) where rownum < 11;
====
SQL REPORT ...
@?/rdbms/admin/awrsqrpt.sql
====
if running slow !!!!!
alter session set "_push_join_predicate" = FALSE ;
SELECT * from table(dbms_workload_repository.ash_global_report_text
( l_dbid => (select dbid from v$database)
, l_inst_num => ''
, l_btime => (SYSDATE - interval '1' hour)
, l_etime => (sysdate - interval '5' MINUTE)
, l_sid => NULL
, l_sql_id => NULL
, l_wait_class => NULL
, l_service_hash => NULL
, l_module => NULL
, l_action => NULL
, l_client_id => NULL
)
);
define sql_id='36psp2j8kf741'
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
set linesize 300 pagesize 300
select * from table(dbms_workload_repository.awr_sql_report_text((SELECT DBID FROM V$DATABASE), (SELECT INSTANCE_NUMBER FROM V$INSTANCE), :BgnSnap,:EndSnap, '&sql_id'));
alter session set "_push_join_predicate" = FALSE ;
define sql_id='ga5m3ggy97233'
SELECT * from table(dbms_workload_repository.ash_global_report_text
( l_dbid => (select dbid from v$database)
, l_inst_num => ''
, l_btime => (SYSDATE - interval '1' hour)
, l_etime => (sysdate - interval '5' MINUTE)
, l_sid => NULL
, l_sql_id => '&sql_id'
, l_wait_class => NULL
, l_service_hash => NULL
, l_module => NULL
, l_action => NULL
, l_client_id => NULL
)
);
alter session set "_push_join_predicate" = FALSE ;
set serveroutput on linesize 400
declare
i_dbid number;
i_inst_id NUMBER;
begin
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
select dbid into i_dbid from v$database;
select instance_number into i_inst_id from v$instance;
for cursor1 in
(
select * from table( DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_text( l_dbid => i_dbid, l_inst_num => i_inst_id, l_btime => SYSDATE - 10/24/60, l_etime => SYSDATE ))
)
LOOP
DBMS_OUTPUT.PUT_LINE( cursor1.output);
END LOOP;
end;
/
6 comments:
http://anuj-singh.blogspot.co.uk/2011/10/oracle-active-session-report-ash-report.html
select output from table(dbms_workload_repository.awr_report_text((select dbid from v$database),1,6557,6558));
select output from table(dbms_workload_repository.awr_report_html((select dbid from v$database),1,6557,6558));
select output from table(dbms_workload_repository.ash_report_html((select dbid from v$database),1,SYSDATE-30/1440, SYSDATE-1/1440));
select output from table(dbms_workload_repository.ash_report_text((select dbid from v$database),1,SYSDATE-30/1440, SYSDATE-1/1440));
select * from table(dbms_workload_repository.awr_sql_report_html((select dbid from v$database), 1, 6557,6558, 'fu738sx0a97j2'));
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), 1, 6557,6558, 'fu738sx0a97j2'));
-- last 24hr report
var BgnSnap number;
var EndSnap number;
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from v$instance), :BgnSnap,:EndSnap, '&sql_id'));
@?/rdbms/admin/ashrpt.sql
--Global Report
select * from table(dbms_workload_repository.awr_global_report_text((select dbid from v$database), '', (select max(snap_id) -1 from dba_hist_snapshot) ,(select max(snap_id) from dba_hist_snapshot), 0));
For IO
http://anuj-singh.blogspot.com/2023/ io
Post a Comment