Script is to get Essence of AWR.
it takes last 24 Hour snapshot and shows statistics of Queries which has elapsed time more than 1 Hours. which query used offloading and bytes returned by Exadata cell. (which can be customize).
It also shows Top wait class & 40 Hot objects by Physical Reads.
it takes last 24 Hour snapshot and shows statistics of Queries which has elapsed time more than 1 Hours. which query used offloading and bytes returned by Exadata cell. (which can be customize).
It also shows Top wait class & 40 Hot objects by Physical Reads.
set linesize 250 set numwidth 20 set pagesize 555 set serveroutput off; SET ECHO OFF; set termout on; set verify off; set recsep off; col TEXT FOR A30 col ELAPSED_TIME_SEC for 99999999 col objects for a50 col INST for 9999 col ITL_WT FOR 9999999 COL PHY_WRIT FOR 9999999999 COL ROW_LCK_WT FOR 999999999 COL ELAPSED_TIME FOR 999999999999 COL CPU_TIME FOR 99999999 COL EXECUTIONS FOR 9999999999 COL PX_SERVERS FOR 99999999999 COL DISK_READ_BYTES FOR 999999999999999 COL DISK_WRITE_BYTES FOR 999999999999999 COL ROWS_PROCESSED FOR 9999999999999 --SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF; undefine MIN_SNAP undefine MAX_SNAP col MAX_SNAP new_value MAX_SNAP col MIN_SNAP new_value MIN_SNAP ---PROMPT "USE THIS WHEN SNAPSHOT TAKEN BY EVERY HALF HOUR" ---SELECT MAX(SNAP_ID) MAX_SNAP,MIN(SNAP_ID) MIN_SNAP FROM ---DBA_HIST_SNAPSHOT ---WHERE BEGIN_INTERVAL_TIME > (SYSDATE - 1) - 4/ 24 ---AND END_INTERVAL_TIME <= (SYSDATE) - 3/ 24 ORDER BY 1; PROMPT "USE THIS WHEN SNAPSHOT IS TAKEN EVERY 1 HOUR" SELECT MAX(SNAP_ID) MAX_SNAP,MIN(SNAP_ID) MIN_SNAP FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME > (sysdate -1) - 4/24 and BEGIN_INTERVAL_TIME <= (SYSDATE) - 3/24 ORDER BY 1; --SET VARIFY OFF; set verify off; SET TERMOUT ON; SPOOL /home/oracle/scripts/monitor/HM_repot.txt; prompt " snapshot between &&MIN_SNAP and &&MAX_SNAP" SELECT END_INTERVAL_TIME as START_TIME FROM DBA_HIST_SNAPSHOt WHERE SNAP_ID=&&MIN_SNAP AND ROWNUM= 1; SELECT END_INTERVAL_TIME as END_TIME FROM DBA_HIST_SNAPSHOt WHERE SNAP_ID=&&MAX_SNAP and rownum=1; PROMPT ***************************************************************** prompt *********SQLs with elapsed time more then 1 hour***************** PROMPT ***************************************************************** SELECT sql_id, text, elapsed_time, CPU_TIME, EXECUTIONS, PX_SERVERS, DISK_READ_BYTES, DISK_WRITE_BYTES, IO_INTERCONNECT_BYTES, OFFLOAD_ELIGIBLE_BYTES, CELL_SMART_SCAN_ONLY_BYTES, FLASH_CACHE_READS, ROWS_PROCESSED --AVG_PX_SERVER FROM (SELECT x.sql_id, SUBSTR ( dhst.sql_text, 1, 30) text, ROUND ( x.elapsed_time / 1000000,0) elapsed_time, ROUND ( x.cpu_time / 1000000,0) CPU_TIME, --ROUND ( x.elapsed_time / 1000000, 3) elapsed_time, --ROUND ( x.cpu_time / 1000000, 3) cpu_time_sec, x.executions_delta EXECUTIONS, ROUND (X.DISK_READ_BYTES/1048576,0) DISK_READ_BYTES, ROUND (X.DISK_WRITE_BYTES/1048576,0) DISK_WRITE_BYTES, ROUND (X.IO_INTERCONNECT_BYTES/1048576,0) IO_INTERCONNECT_BYTES, ROUND (X.OFFLOAD_ELIGIBLE_BYTES/1048576,0) OFFLOAD_ELIGIBLE_BYTES, X.FLASH_CACHE_READS FLASH_CACHE_READS, ROUND (X.cell_smart_scan_only_BYTES/1048576,0) CELL_SMART_SCAN_ONLY_BYTES, (x.ROWS_PROCESSED) ROWS_PROCESSED, (X.PX_SERVERS) PX_SERVERS, --ROUND(X.PX_SERVERS/X.executions_delta,0) AVG_PX_SERVER, row_number () OVER (PARTITION BY x.sql_id ORDER BY 0) rn FROM dba_hist_sqltext dhst, (SELECT dhss.sql_id sql_id, SUM (dhss.cpu_time_delta) cpu_time, SUM (dhss.elapsed_time_delta) elapsed_time, SUM (dhss.executions_delta) executions_delta, SUM (dhss.PHYSICAL_READ_BYTES_DELTA) DISK_READ_BYTES, SUM (dhss.PHYSICAL_WRITE_BYTES_DELTA) DISK_WRITE_BYTES, SUM (dhss.IO_INTERCONNECT_BYTES_DELTA) IO_INTERCONNECT_BYTES, SUM (dhss.IO_OFFLOAD_ELIG_BYTES_DELTA) OFFLOAD_ELIGIBLE_BYTES, SUM (dhss.OPTIMIZED_PHYSICAL_READS_DELTA) FLASH_CACHE_READS, SUM (dhss.IO_OFFLOAD_RETURN_BYTES_DELTA) cell_smart_scan_only_BYTES, SUM (dhss.ROWS_PROCESSED_DELTA) ROWS_PROCESSED, SUM (dhss.PX_SERVERS_EXECS_DELTA) PX_SERVERS FROM dba_hist_sqlstat dhss WHERE dhss.snap_id IN (SELECT snap_id FROM dba_hist_snapshot --WHERE SNAP_ID > 1865 AND SNAP_ID<= 1889) WHERE SNAP_ID > &&MIN_SNAP AND SNAP_ID<= &&MAX_SNAP) --comment BELOW line if want to include current executions. --AND dhss.executions_delta > 0 GROUP BY dhss.sql_id) x WHERE x.sql_id = dhst.sql_id AND ROUND ( x.elapsed_time / 1000000, 3) > 3600) WHERE rn = 1 ORDER BY ELAPSED_TIME DESC; PROMPT ***************************************************************** PROMPT ********WAIT_CLASS AND COUNTS / NOTE " NULL VALUE IS CPU"******** PROMPT ***************************************************************** select wait_class, count(*) cnt from dba_hist_active_sess_history WHERE SNAP_ID > &&MIN_SNAP AND SNAP_ID<= &&MAX_SNAP group by wait_class_id, wait_class order by 2; PROMPT ***************************************************************** prompt ********* Top 40 Objects by Physical Read ***************** PROMPT ***************************************************************** SELECT * FROM ( SELECT do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']' AS OBJECTS, DHSS.INSTANCE_NUMBER AS INST, SUM(DHSS.LOGICAL_READS_DELTA) LOGICAL_READ, SUM(DHSS.PHYSICAL_READS_DELTA) PHY_READ, SUM(DHSS.PHYSICAL_WRITES_DELTA) PHY_WRIT, SUM(DHSS.ITL_WAITS_DELTA) ITL_WT, SUM(DHSS.ROW_LOCK_WAITS_DELTA) ROW_LCK_WT from dba_hist_seg_stat DHSS, DBA_OBJECTS DO WHERE SNAP_ID > &&MIN_SNAP AND SNAP_ID<= &&MAX_SNAP --WHERE DHSS.SNAP_ID > 20135 AND DHSS.SNAP_ID<= 20183 AND DHSS.OBJ#=DO.OBJECT_ID group by do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']',DHSS.INSTANCE_NUMBER order BY PHY_READ DESC ) WHERE ROWNUM <=40; SPOOL off; EXIT;
