EM Query

  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