Monday, June 6, 2011

Generate tablespace usage trend / growth.

Links to this post
Hi

The script mention below generates tablespace usage trend/growth.

This is useful to understand when we deal with TEMP / UNDO tablespace peak usage.

It creates a spool file as excel sheet format (.xls), which helps to create graph for reporting purpose as a business standpoint.
=========================================================

SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
set linesize 125
set numwidth 20
set pagesize 50
COL NAME FOR A30
col SNAP_ID for 9999999
set serveroutput off;
SPOOL TBS_TREND.xls;
set verify off;
set echo off;

SELECT
distinct DHSS.SNAP_ID,VTS.NAME,
TO_CHAR(DHSS.END_INTERVAL_TIME, 'DD-MM HH:MI') AS SNAP_Time,
ROUND((DHTS.TABLESPACE_USEDSIZE*8192)/1024/1024)/&&max_instance_num AS USED_MB,
ROUND((DHTS.TABLESPACE_SIZE*8192)/1024/1024)/&&max_instance_num AS SIZE_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE DHTS,V$TABLESPACE VTS,DBA_HIST_SNAPSHOT DHSS
WHERE VTS.TS#=DHTS.TABLESPACE_ID
AND DHTS.SNAP_ID=DHSS.SNAP_ID
AND DHSS.INSTANCE_NUMBER=1
AND TABLESPACE_ID=&id
ORDER BY 1;
SPOOL OFF;
EXIT;

Wednesday, June 1, 2011

Exadata Performance Monitoring | ASH Report

Links to this post
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;