Here are some performance tuning scripts, available specially for 10g and 11g.
1. Read Errors from Alert.log : 11g (last 20 days)
select substr(MESSAGE_TEXT, 1, 150) message_text,to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD') err_timestamp, count(*) cntfrom X$DBGALERTEXT
where (upper(MESSAGE_TEXT) like '%ORA-%' or upper(MESSAGE_TEXT) like '%ERROR%')and cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - 20
group by substr(MESSAGE_TEXT, 1, 150), to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD')
order by to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD');
2. SQL statistics (Top 10, last 5 days):
SQL Ordered by Elapsed Time:
select * from (
select distinct
round((sum(sql_hist.ELAPSED_TIME_delta)/1000000) ,3) c10,
sql_hist.sql_id c2,
sum(sql_hist.executions_delta) c3,
sum(sql_hist.buffer_gets_delta) c4,
sum(sql_hist.disk_reads_delta ) c5,
round( (sum(sql_hist.CPU_TIME_DELTA)/1000000) ,3) c6,
decode(sum(sql_hist.executions_delta), 0,
null, round((sum(sql_hist.ELAPSED_TIME_delta)/1000000)/sum(sql_hist.executions_delta),3)
) c7,
sql_hist.module c8 ,
to_char( substr(text.sql_text,1,600)) c9,
decode (sum(sql_hist.ELAPSED_TIME_delta), 0,null,
round((sum(sql_hist.CPU_TIME_DELTA)/sum(sql_hist.ELAPSED_TIME_delta))*100 ,3)
) c11
from
dba_hist_sqlstat sql_hist,
dba_hist_snapshot s,
dba_hist_sqltext text
where
s.snap_id = sql_hist.snap_id and
s.begin_interval_time >= sysdate -5 and
text.sql_id =sql_hist.sql_id and
text.dbid=sql_hist.dbid
group by sql_hist.sql_id , sql_hist.module , to_char( substr
(text.sql_text,1,600))
order by c10 desc--, c1 desc
) where rownum <= 10;
select distinct
round((sum(sql_hist.ELAPSED_TIME_delta)/1000000) ,3) c10,
sql_hist.sql_id c2,
sum(sql_hist.executions_delta) c3,
sum(sql_hist.buffer_gets_delta) c4,
sum(sql_hist.disk_reads_delta ) c5,
round( (sum(sql_hist.CPU_TIME_DELTA)/1000000) ,3) c6,
decode(sum(sql_hist.executions_delta), 0,
null, round((sum(sql_hist.ELAPSED_TIME_delta)/1000000)/sum(sql_hist.executions_delta),3)
) c7,
sql_hist.module c8 ,
to_char( substr(text.sql_text,1,600)) c9,
decode (sum(sql_hist.ELAPSED_TIME_delta), 0,null,
round((sum(sql_hist.CPU_TIME_DELTA)/sum(sql_hist.ELAPSED_TIME_delta))*100 ,3)
) c11
from
dba_hist_sqlstat sql_hist,
dba_hist_snapshot s,
dba_hist_sqltext text
where
s.snap_id = sql_hist.snap_id and
s.begin_interval_time >= sysdate -5 and
text.sql_id =sql_hist.sql_id and
text.dbid=sql_hist.dbid
group by sql_hist.sql_id , sql_hist.module , to_char( substr
(text.sql_text,1,600))
order by c10 desc--, c1 desc
) where rownum <= 10;
SQL ordered by CPU Time
select * from (
select distinct
sql_hist.sql_id c2,
sum(sql_hist.executions_delta ) c3,
sum(sql_hist.buffer_gets_delta ) c4,
sum(sql_hist.disk_reads_delta ) c5,
round((sum(sql_hist.CPU_TIME_DELTA)/1000000),3) c6,
round((sum(sql_hist.ELAPSED_TIME_delta)/1000000),3) c7,
sql_hist.module c8 ,
to_char( substr(text.sql_text,1,600)) c9,
decode(sum(sql_hist.executions_delta),0,null, round((sum(sql_hist.CPU_TIME_DELTA)/1000000)/sum(sql_hist.executions_delta),3)) c10 ,
decode (sum(sql_hist.ELAPSED_TIME_delta), 0,null,
round((sum(sql_hist.CPU_TIME_DELTA)/sum(sql_hist.ELAPSED_TIME_delta))*100 ,3)
) c11
from
dba_hist_sqlstat sql_hist,
dba_hist_snapshot s,
dba_hist_sqltext text
where
s.snap_id = sql_hist.snap_id and
s.begin_interval_time >= sysdate -5 and
text.sql_id =sql_hist.sql_id and
text.dbid=sql_hist.dbid
group by sql_hist.sql_id , sql_hist.module , to_char( substr(text.sql_text,1,600))
order by c6 desc--, c1 desc
) where rownum <= 10;
SQL Ordered by Gets
select * from (
select distinct
sql_hist.sql_id c2,
sum(sql_hist.executions_delta) c3,
sum(sql_hist.buffer_gets_delta ) c4,
sum(sql_hist.disk_reads_delta ) c5,
round((sum(sql_hist.CPU_TIME_DELTA)/1000000),3) c6,
round((sum(sql_hist.ELAPSED_TIME_delta)/1000000),3) c7,
sql_hist.module c8 ,
to_char( substr(text.sql_text,1,600)) c9,
decode(sum(sql_hist.executions_delta),0,null, round((sum(sql_hist.buffer_gets_delta))/sum(sql_hist.executions_delta),3)) c10 ,
decode (sum(sql_hist.ELAPSED_TIME_delta), 0,null,
round((sum(sql_hist.CPU_TIME_DELTA)/sum(sql_hist.ELAPSED_TIME_delta))*100 ,3)
) c11
from
dba_hist_sqlstat sql_hist,
dba_hist_snapshot s,
dba_hist_sqltext text
where
s.snap_id = sql_hist.snap_id and
s.begin_interval_time >= sysdate -5 and
text.sql_id =sql_hist.sql_id and
text.dbid=sql_hist.dbid
group by sql_hist.sql_id , sql_hist.module , to_char( substr(text.sql_text,1,600))
order by c4 desc--, c1 desc
) where rownum <= 10;
3. Optimization:
Current Execution Plan (last execution) for the top query (last 5 days)
var l_sql_id varchar2(13);
begin
select sql_id--, sql_text
into :l_sql_id
from
(
select distinct
round((sum(sql_hist.ELAPSED_TIME_delta)/1000000) ,3) c10,
sql_hist.sql_id sql_id
from
dba_hist_sqlstat sql_hist,
dba_hist_snapshot s,
dba_hist_sqltext text
where
s.snap_id = sql_hist.snap_id and
s.begin_interval_time >= sysdate -5 and
text.sql_id =sql_hist.sql_id and
text.dbid=sql_hist.dbid group by sql_hist.sql_id , sql_hist.module , to_char( substr(text.sql_text,1,600))
order by c10 desc--, c1 desc
) where rownum <= 1;
end;
/
SELECT tf.*
FROM dba_hist_sqltext ht,
TABLE(dbms_xplan.display_awr(ht.sql_id,NULL,NULL, 'ALL')) tf
WHERE ht.sql_id = :l_sql_id ;
begin
select sql_id--, sql_text
into :l_sql_id
from
(
select distinct
round((sum(sql_hist.ELAPSED_TIME_delta)/1000000) ,3) c10,
sql_hist.sql_id sql_id
from
dba_hist_sqlstat sql_hist,
dba_hist_snapshot s,
dba_hist_sqltext text
where
s.snap_id = sql_hist.snap_id and
s.begin_interval_time >= sysdate -5 and
text.sql_id =sql_hist.sql_id and
text.dbid=sql_hist.dbid group by sql_hist.sql_id , sql_hist.module , to_char( substr(text.sql_text,1,600))
order by c10 desc--, c1 desc
) where rownum <= 1;
end;
/
SELECT tf.*
FROM dba_hist_sqltext ht,
TABLE(dbms_xplan.display_awr(ht.sql_id,NULL,NULL, 'ALL')) tf
WHERE ht.sql_id = :l_sql_id ;
Multiple plan hash values for the top queries ? (Top 10, Last 5 days)
select * from (
with vue as ( select sql_id from
(
select distinct
-- s.snap_id, to_char(s.begin_interval_time,'mm-dd-yyyy hh24:mi:ss') c1,
sql_hist.sql_id ,
sql_hist.buffer_gets_delta c4
from
dba_hist_sqlstat sql_hist,
-- dba_hist_snapshot s,
dba_hist_sqltext text,
( select max(sql_hist.buffer_gets_delta) buffer_gets_delta, sql_hist.sql_id
from dba_hist_sqlstat sql_hist, dba_hist_snapshot s
where s.snap_id = sql_hist.snap_id and
s.begin_interval_time >= sysdate -5
group by sql_hist.sql_id
)max_condition
where
sql_hist.sql_id = max_condition.sql_id and
max_condition.buffer_gets_delta = sql_hist.buffer_gets_delta and
-- s.begin_interval_time >= sysdate -5 and
text.sql_id =sql_hist.sql_id
--order by c4 desc--, c1 desc
) where rownum <= 10
)
select
vue.SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT , VUE
where DBA_HIST_SQLSTAT .SQL_ID = vue.sql_id
group by vue.SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS
) ;
4. Events (Last 5 days):
Top 5 Timed Foreground Events:
SELECT * FROM ( SELECT event, waits, TIME, round(100*pct,2) pct , waitclass
FROM (SELECT e.event_name event,
e.total_waits - NVL (b.total_waits, 0) waits,
(e.time_waited_micro - NVL (b.time_waited_micro, 0)
)
/ 1000000 TIME,
(e.time_waited_micro - NVL (b.time_waited_micro, 0)
)
/ (SELECT SUM ( e1.time_waited_micro
- NVL (b1.time_waited_micro, 0)
)
FROM dba_hist_system_event b1,
dba_hist_system_event e1
WHERE b1.snap_id(+) = b.snap_id
AND e1.snap_id = e.snap_id
AND b1.dbid(+) = b.dbid
AND e1.dbid = e.dbid
AND b1.instance_number(+) = b.instance_number
AND e1.instance_number = e.instance_number
AND b1.event_id(+) = e1.event_id
AND e1.total_waits > NVL (b1.total_waits, 0)
AND e1.wait_class <> 'Idle') pct,
e.wait_class waitclass
FROM dba_hist_system_event b, dba_hist_system_event e, (select max(snap_id) max_snap_id, min (snap_id) min_snap_id
from dba_hist_snapshot
where begin_interval_time >= sysdate -5) s
WHERE b.snap_id = s.min_snap_id
AND e.snap_id = s.max_snap_id
AND b.event_id(+) = e.event_id
AND e.total_waits > NVL (b.total_waits, 0)
AND e.wait_class <> 'Idle'
ORDER BY waits DESC
)
WHERE ROWNUM < 6;
FROM (SELECT e.event_name event,
e.total_waits - NVL (b.total_waits, 0) waits,
(e.time_waited_micro - NVL (b.time_waited_micro, 0)
)
/ 1000000 TIME,
(e.time_waited_micro - NVL (b.time_waited_micro, 0)
)
/ (SELECT SUM ( e1.time_waited_micro
- NVL (b1.time_waited_micro, 0)
)
FROM dba_hist_system_event b1,
dba_hist_system_event e1
WHERE b1.snap_id(+) = b.snap_id
AND e1.snap_id = e.snap_id
AND b1.dbid(+) = b.dbid
AND e1.dbid = e.dbid
AND b1.instance_number(+) = b.instance_number
AND e1.instance_number = e.instance_number
AND b1.event_id(+) = e1.event_id
AND e1.total_waits > NVL (b1.total_waits, 0)
AND e1.wait_class <> 'Idle') pct,
e.wait_class waitclass
FROM dba_hist_system_event b, dba_hist_system_event e, (select max(snap_id) max_snap_id, min (snap_id) min_snap_id
from dba_hist_snapshot
where begin_interval_time >= sysdate -5) s
WHERE b.snap_id = s.min_snap_id
AND e.snap_id = s.max_snap_id
AND b.event_id(+) = e.event_id
AND e.total_waits > NVL (b.total_waits, 0)
AND e.wait_class <> 'Idle'
ORDER BY waits DESC
)
WHERE ROWNUM < 6;
Five first Database Objects Experienced the Most Number of Waits:
select * FROM ( WITH ORDERED AS
(
SELECT
dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
, ROW_NUMBER() OVER ( ORDER BY sum(active_session_history.wait_time +
active_session_history.time_waited) desc
) AS rn
FROM
v$active_session_history active_session_history,
dba_objects
where
active_session_history.sample_time between sysdate - 5 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by ttl_wait_time desc
)
SELECT
object_name,
object_type,
event,
ttl_wait_time
FROM
ORDERED
WHERE
rn <=6
);
(
SELECT
dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
, ROW_NUMBER() OVER ( ORDER BY sum(active_session_history.wait_time +
active_session_history.time_waited) desc
) AS rn
FROM
v$active_session_history active_session_history,
dba_objects
where
active_session_history.sample_time between sysdate - 5 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by ttl_wait_time desc
)
SELECT
object_name,
object_type,
event,
ttl_wait_time
FROM
ORDERED
WHERE
rn <=6
);
5. Sessions Activities (Top 10, Last 5 days):
select * from
(
select username, module, session_id, sample_time, session_state, event, wait_time, dba_hist_sqltext.sql_id, SQL_TEXT
from v$active_session_history, dba_users, dba_hist_sqltext
where dba_users.user_id = v$active_session_history.user_id
and username not in ('SYS','SYSTEM','CTXSYS','DBSNMP','OUTLN','SYSAUX', 'ORDSYS','ORDPLUGINS','MDSYS','DMSYS','APPQOSSYS', 'WMSYS','WKSYS','OLAPSYS','SYSMAN','XDB','EXFSYS','TSMSYS','MGMT_VIEW','ORACLE_OCM','DIP','SI_INFORMTN_SCHEMA','ANONYMOUS')
and sample_time between sysdate -5 and sysdate
and dba_hist_sqltext.sql_id = v$active_session_history.sql_id
and dba_hist_sqltext.sql_id is not null
order by wait_time desc ) where rownum <= 10 ;
(
select username, module, session_id, sample_time, session_state, event, wait_time, dba_hist_sqltext.sql_id, SQL_TEXT
from v$active_session_history, dba_users, dba_hist_sqltext
where dba_users.user_id = v$active_session_history.user_id
and username not in ('SYS','SYSTEM','CTXSYS','DBSNMP','OUTLN','SYSAUX', 'ORDSYS','ORDPLUGINS','MDSYS','DMSYS','APPQOSSYS', 'WMSYS','WKSYS','OLAPSYS','SYSMAN','XDB','EXFSYS','TSMSYS','MGMT_VIEW','ORACLE_OCM','DIP','SI_INFORMTN_SCHEMA','ANONYMOUS')
and sample_time between sysdate -5 and sysdate
and dba_hist_sqltext.sql_id = v$active_session_history.sql_id
and dba_hist_sqltext.sql_id is not null
order by wait_time desc ) where rownum <= 10 ;
6. Disk I/O
Segments ordered by Physical Reads (Top 10):
select * from
(
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc
)
) where rownum <= 10 ;
(
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc
)
) where rownum <= 10 ;
SQL with the highest I/O (Top 10, Last 5 days):
select * from
(
WITH ORDERED AS
(
SELECT
h.sql_id
, SUM(10) ash_secs
,ROW_NUMBER() OVER ( ORDER BY SUM(10) desc
) AS rn
FROM dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE x.begin_interval_time between sysdate -5 and sysdate
AND h.SNAP_id = X.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
and sql_id is not null
GROUP BY h.sql_id
ORDER BY ash_secs desc
)
SELECT
ORDERED.rn,
dba_hist_sqltext.sql_id,
to_char( substr(dba_hist_sqltext.sql_text,1,600)) text
FROM
ORDERED, dba_hist_sqltext
WHERE
ORDERED.sql_id= dba_hist_sqltext.sql_id
and rn <=10
ORDER BY rn
) ;
(
WITH ORDERED AS
(
SELECT
h.sql_id
, SUM(10) ash_secs
,ROW_NUMBER() OVER ( ORDER BY SUM(10) desc
) AS rn
FROM dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE x.begin_interval_time between sysdate -5 and sysdate
AND h.SNAP_id = X.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
and sql_id is not null
GROUP BY h.sql_id
ORDER BY ash_secs desc
)
SELECT
ORDERED.rn,
dba_hist_sqltext.sql_id,
to_char( substr(dba_hist_sqltext.sql_text,1,600)) text
FROM
ORDERED, dba_hist_sqltext
WHERE
ORDERED.sql_id= dba_hist_sqltext.sql_id
and rn <=10
ORDER BY rn
) ;
