--SQLs with elapsed time more then 1 hour
SELECT *
FROM dba_hist_snapshot where end_interval_time>=to_date('03/08/2017 00:00:00','dd/mm/yyyy hh24:mi:ss')
and end_interval_time <=to_date('04/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
SELECT min(snap_id), max(snap_id)
FROM dba_hist_snapshot where end_interval_time>=to_date('03/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
and end_interval_time <=to_date('04/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
order by end_interval_time;
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, 4000) 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 distinct snap_id
FROM dba_hist_snapshot
WHERE SNAP_ID > 90796 AND SNAP_ID<= 90820)
--comment BELOW line if want to include current executions.
--AND dhss.executions_delta > 0
and dhss.instance_number=1
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;
--WAIT_CLASS AND COUNTS / NOTE " NULL VALUE IS CPU"
select wait_class, count(*) cnt from dba_hist_active_sess_history
WHERE SNAP_ID > 90796 AND SNAP_ID<= 90820 and instance_number=1
group by wait_class_id, wait_class
order by 2 desc;
-- Top 40 Objects by Physical Read
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 DHSS.SNAP_ID > 90797 AND DHSS.SNAP_ID<= 90820
AND DHSS.OBJ#=DO.OBJECT_ID
and DHSS.INSTANCE_NUMBER=1
group by do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']',DHSS.INSTANCE_NUMBER
order BY PHY_READ DESC
) WHERE ROWNUM <=40;
end;
-- Check IO
SELECT host_name,
db_name,
instance_name,
ROUND (SUM (last_15_mins) / 1024 / 1024) IO_MB_LAST_15_MINS,
SYSDATE
FROM ( SELECT inst.host_name,
db.name AS db_name,
inst.instance_name,
sm.metric_name,
ROUND (AVG (sm.VALUE), 0) last_15_mins
FROM GV$SYSMETRIC_HISTORY sm,
gv$instance inst,
(SELECT name FROM v$database) db
WHERE sm.inst_id = inst.inst_id
AND sm.metric_name IN ('Physical Read Total Bytes Per Sec',
'Physical Write Bytes Per Sec',
'Redo Generated Per Sec')
AND sm.begin_time >= SYSDATE - 15 / (24 * 60)
GROUP BY inst.host_name,
db.name,
inst.instance_name,
sm.inst_id,
sm.metric_name)
GROUP BY host_name, db_name, instance_name
ORDER BY 1;
select sql_id,sql_fulltext from gv$sql where sql_id in ('67bm8d2ah3xhk');
-- check order by elaped time
select * from
(SELECT parsing_schema_name "USER",X.sql_id,dbms_lob.substr (sql_text,100,1)||' ...' "SQL_TEXT" --dbms_lob.substr(SQL_TEXT,4000,1) "SQL_TEXT"
,ROUND(X.ELAPSED_TIME/1000000,0) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,0) CPU_TIME_SEC
,ROUND(X.BUFFER_GETS /1000000,0) BUFFER_GETS_NUMBER
, EXECUTIONS_DELTA
, ROUND(X.ELAPSED_TIME/1000000/EXECUTIONS_DELTA,2) AVG_ELAPSED_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT dhss.parsing_schema_name,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME,
SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME, SUM (DHSS.BUFFER_GETS_DELTA) BUFFER_GETS
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID in (select snap_id from dba_hist_snapshot where begin_interval_time >= sysdate - 2/24 and begin_interval_time <= sysdate) and EXECUTIONS_DELTA>0
--and DHSS.parsing_schema_name like '%SUP%'
GROUP BY dhss.parsing_schema_name,DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY ELAPSED_TIME_SEC DESC
)
where rownum < 10;
--- check cpu time --
select * from
(SELECT parsing_schema_name "USER",X.sql_id,dbms_lob.substr (sql_text,100,1)||' ...' "SQL_TEXT" --dbms_lob.substr(SQL_TEXT,4000,1) "SQL_TEXT"
,ROUND(X.ELAPSED_TIME/1000000,0) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,0) CPU_TIME_SEC
,ROUND(X.BUFFER_GETS /1000000,0) BUFFER_GETS_NUMBER
, EXECUTIONS_DELTA
, ROUND(X.CPU_TIME/1000000/EXECUTIONS_DELTA,2) AVG_ELAPSED_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT dhss.parsing_schema_name,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME,
SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME, SUM (DHSS.BUFFER_GETS_DELTA) BUFFER_GETS
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID in (select snap_id from dba_hist_snapshot where begin_interval_time >= sysdate - 2/24 and begin_interval_time <= sysdate) and EXECUTIONS_DELTA>0
--and DHSS.parsing_schema_name like '%SUP%'
GROUP BY dhss.parsing_schema_name,DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY CPU_TIME_SEC DESC
)
where rownum < 10;
