Wednesday, June 1, 2011

Exadata Performance Monitoring | ASH Report

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.

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;

No comments:

Post a Comment