Snapshots (take snaps before & after test): – exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
AWR Report (@?/rdbms/admin/awrrpt.sql) – Workload Repository Report – Top events to start
ADDM report (@?/rdbms/admin/addmrpt.sql) – Oracle provided recommendations for further analysis
ASH Report (use particularly for concurrency issues) – @?/rdbms/admin/ashrpt.sql
SQR Report (With sqlid get the explain plan) – @?/rdbms/admin/awrsqrpt.sql
tune sql
@$ORACLE_HOME/rdbms/admin/sqltrpt.sql
set linesize 200
col BEGIN_INTERVAL_TIME format a70
select * from (select snap_id,begin_interval_time from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3;
---------------------------------------------
Set pages 1000
Set lines 75
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message : '||c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status='COMPLETED'
and a.description like '%4782%'
Order by b.impact, d.rank;
-----------------------------------------------------------
export ORACLE_SID=$1
export ORAENV_ASK=NO
. oraenv
sqlplus -S / as sysdba <<oogy
set echo off
set lines 100
set pages 200
set trimspool on
set termout off
set feedback off
column dcol new_value mydate noprint
select to_char(sysdate,'YYMMDD') dcol from dual;
spool /home/oraprd/scripts/dbreport_$1_&mydate..txt
ttitle 'Average Active Sessions in the last week: Instance $1'
column sample_hour format a16
select
to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour
, round(avg(sub1.on_cpu),1) as cpu_avg
, round(avg(sub1.waiting),1) as wait_avg
, round(avg(sub1.active_sessions),1) as act_avg
, round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
( -- sub1: one row per second, the resolution of SAMPLE_TIME
select
sample_id,
sample_time,
sum(decode(session_state, 'ON CPU', 1, 0)) as on_cpu,
sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
count(*) as active_sessions
from
dba_hist_active_sess_history
where
sample_time > sysdate - 7
group by
sample_id,
sample_time
) sub1
group by
round(sub1.sample_time, 'HH24')
order by
round(sub1.sample_time, 'HH24');
ttitle 'Most expensive queries in the last week: Instance $1'
-- gets most expensive queries
-- (by time spent, change "order by" to use another metric)
-- after a specific date
select
sub.sql_id,
sub.seconds_used,
sub.executions,
sub.gets
from
( -- sub to sort before rownum
select
sql_id,
round(sum(elapsed_time_delta)/1000000) as seconds_used,
sum(executions_delta) as executions,
sum(buffer_gets_delta) as gets
from
dba_hist_snapshot natural join dba_hist_sqlstat
where
begin_interval_time > sysdate - 7
group by
sql_id
order by
2 desc
) sub
where
rownum < 30
;
set long 32768
ttitle 'Text for most expensive SQL in the last week: Instance $1'
select sql_text
from dba_hist_sqltext
where sql_id =
(
select sub.sql_id
from
( -- sub to sort before rownum
select
sql_id,
round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date
from
dba_hist_snapshot natural join dba_hist_sqlstat
where
begin_interval_time > sysdate - 7
group by
sql_id
order by
2 desc
) sub
where
rownum = 1
);
spool off;
exit
oogy
