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;
----------------------------------------------------------- cat
get_addm_report.sql which gets each task from the last snapshot from dba_advisor_tasks
set long 10000000
set pagesize 50000
column get_clob format a80
select dbms_advisor.get_task_report (task_name) as ADDM_report
from dba_advisor_tasks
where task_id = (
select max(t. task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name = 'ADDM'
and l.status = 'COMPLETED');
---------------------------------------------------------------
export ORACLE_SID=$1
export ORAENV_ASK=NO
. oraenv
sqlplus -S / as sysdba <
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
Posts
