Oracle获取AWR和ASH

-- AWR 
    --相关的信息保存在sysaux表空间

--- 保存周期、设置保存和自动快照的设置
select * from dba_hist_wr_control;
exec dbms_workload_repository.create_snapshot();
exec dbms_workload_repository.modify_baseline_window_size(8);
exec dbms_workload_repository.modify_snapshot_settings(interval => 30, retention => 16*24*60, topnsql => 50);
exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 6770,high_snap_id => 6774,dbid => 7059638244);
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention => 15*1440, interval => 30, topnsql => 50);


begin 
    dbms_workload_repository.create_snapshot();
end;



SELECT * 
FROM (SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC)
WHERE ROWNUM < 200;

--awr report
SELECT * 
FROM TABLE(dbms_workload_repository.awr_report_html(
        (SELECT dbid FROM v$database)
       , 1       -- RAC节点
       , 26663   -- begin snap_id 
       , 26664   -- end snap_id 
       , 8       -- (0, 8) ADDM
     ));

--sql report
SELECT * 
FROM TABLE(dbms_workload_repository.awr_sql_report_html(
        (SELECT dbid FROM v$database)
       , 1       -- RAC节点
       , 1059   -- begin snap_id 
       , 1060   -- end snap_id 
       , '2k4f8h4rqfkhd'
       , l_options => 8
     ));
     
     
-- ASH
SELECT * 
FROM TABLE(dbms_workload_repository.ash_report_html(
       (SELECT dbid FROM v$database), 1,
       TO_DATE('2015-11-15 20:10:00', 'yyyy-mm-dd hh24:mi:ss'),   -- begin datetime
       TO_DATE('2015-11-15 20:30:00', 'yyyy-mm-dd hh24:mi:ss')    -- end
     ));


--ADDM报告
select * from dba_advisor_tasks t order by t.created desc;
 
set long 1000000 pagesize 0 longchunksize 1000 
 
select dbms_advisor.get_task_report('ADDM:3620614489_1_25298', owner_name => 'SYS') from dual;

---创建优化任务并执行(生成ADDM报告内容)：
DECLARE                                                              
    task_name VARCHAR2(30) := 'DEMO_ADDM02';                              
    task_desc VARCHAR2(30) := 'ADDM Feature Test';                        
    task_id NUMBER;                                                       
BEGIN                                                                     
    dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
    dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 1954);    
    dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 1955);      
    dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);            
    dbms_advisor.set_task_parameter(task_name, 'DB_ID', 1363777294);       
    dbms_advisor.execute_task(task_name);                                 
END;  


-- 切换联机日志
alter system switch logfile;

-- 查看日志切换频率
select * from v$log_history t order by t.SEQUENCE# desc;


/* 查看某个脚本的Redo日志量 */
-- 日志模式
select t.LOG_MODE, t.FORCE_LOGGING, t.* from v$database t;
select t.LOGGING, t.FORCE_LOGGING, t.* from dba_tablespaces t;
select t.LOGGING, t.* from dba_tables t;

-- Redo日志的产生量
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%redo size%'


select * from v$active_session_history t where ROWNUM < 20;

SELECT * 
FROM (
        SELECT * FROM  v$active_session_history t 
        WHERE   t.sql_id = 'xxxx' 
            AND t.sample_time BETWEEN
                    TO_DATE('2016-05-04 10:10:00', 'yyyy-mm-dd hh24:mi:ss') 
                AND TO_DATE('2016-05-04 10:20:00', 'yyyy-mm-dd hh24:mi:ss')
        ORDER BY t.SAMPLE_TIME
    )  t
WHERE ROWNUM < 20;



select * from dba_hist_active_sess_history t where rownum < 20;

SELECT * 
FROM (
        SELECT * FROM  dba_hist_active_sess_history t 
        WHERE t.sample_time BETWEEN 
                    TO_DATE('2016-05-04 10:10:00', 'yyyy-mm-dd hh24:mi:ss') 
                AND TO_DATE('2016-05-04 10:20:00', 'yyyy-mm-dd hh24:mi:ss')
        ORDER BY t.SAMPLE_TIME
    )  t
WHERE ROWNUM < 20;


/*
ASH(V$ACTIVE_SESSION_HISTORY) 在内存中保存，每秒从v$session_wait中采样一次(等待会话每秒的快照)
dba_hist_active_sess_history视图的数据来自于V$ACTIVE_SESSION_HISTORY。
dba_hist_active_sess_history视图的记录了内存中活动会话的历史信息，而动态性能视图V$ACTIVE_SESSION_HISTORY则记录了当前活动会话信息。
v$active_session_history 每秒钟将数据库Active Session采样一次，而dba_hist_active_sess_history则会将v$active_session_history里的数据每10秒采样一次并持久化保存。
通过dba_hist_active_sess_history视图关联v$sqlarea和DBA_HIST_SNAPSHOT可以跟踪到某个时间段内的sql。
当然能跟踪到的sql多少取决于v$sqlarea，毕竟只有还保留在v$sqlarea中的sql才能跟踪到。
另外，也可以根据以下历史视图，检索相应的数据。
dba_hist_sqlstat
dba_hist_sqltext                sys.wrh$_sqltext
dba_hist_sql_plan               sys.wrh$_sql_plan
dba_hist_active_sess_history    sys.wrh$_active_session_history
*/

select * from sys.wrh$_sqltext t where t.sql_id = '';


-- 执行计划，根据SQL_ID获取实际使用的执行计划
SELECT   t.plan_hash_value, t.child_number, t.id,
         --LPAD (' ', DEPTH*2) || operation AS operation, 
         --lpad('|--'||operation,length('|--' ||operation)+(DEPTH*5-5),' '),
         lpad(t.DEPTH || '|' || t.operation, length(t.DEPTH || '|' || t.operation) + (t.DEPTH*3), ' ') AS operation,
         t.options,
         --object_owner,
         t.object_name, t.optimizer, t.cost,
         t.access_predicates, t.filter_predicates,
         t.cardinality, t.cpu_cost, t.io_cost,
         round(t.bytes/1024/1024/1024) as data_gb,
         round(t.temp_space/1024/1024/1024) as TempSpace_gb, 
         t.PARTITION_START, t.PARTITION_STOP, t.*
    FROM v$sql_plan t
   WHERE t.sql_id = '34cd4y8mbqvsk'
ORDER BY t.plan_hash_value, t.child_number, t.id;


-- 执行计划，根据SQL_ID获取实际使用的执行计划
SELECT   t.plan_hash_value, t.id,
         --LPAD (' ', DEPTH*2) || operation AS operation, 
         --lpad('|--'||operation,length('|--' ||operation)+(DEPTH*5-5),' '),
         lpad(t.DEPTH || '|' || t.operation, length(t.DEPTH || '|' || t.operation) + (t.DEPTH*3), ' ') AS operation,
         t.options,
         --object_owner,
         t.object_name, t.optimizer, t.cost,
         t.access_predicates, t.filter_predicates,
         t.cardinality, t.cpu_cost, t.io_cost,
         round(t.bytes/1024/1024/1024) as data_gb,
         round(t.temp_space/1024/1024/1024) as TempSpace_gb, 
         t.PARTITION_START, t.PARTITION_STOP, t.*
    FROM dba_hist_sql_plan t
   WHERE t.sql_id = '34cd4y8mbqvsk'
ORDER BY t.plan_hash_value, t.id;





--按时间段查看Redo、逻辑读、物理读、SQL执行、解析及事务的统计数据 
select s.snap_date,
       decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
       to_char(round(s.seconds/60,2)) "elapse(min)",
       round(t.db_time / 1000000 / 60, 2) "DB time(min)",
       s.redosize redo,
       round(s.redosize / s.seconds, 2) "redo/s",
       s.logicalreads logical,
       round(s.logicalreads / s.seconds, 2) "logical/s",
       physicalreads physical,
       round(s.physicalreads / s.seconds, 2) "phy/s",
       s.executes execs,
       round(s.executes / s.seconds, 2) "execs/s",
       s.parse,
       round(s.parse / s.seconds, 2) "parse/s",
       s.hardparse,
       round(s.hardparse / s.seconds, 2) "hardparse/s",
       s.transactions trans,
       round(s.transactions / s.seconds, 2) "trans/s"
  from (select curr_redo - last_redo redosize,
               curr_logicalreads - last_logicalreads logicalreads,
               curr_physicalreads - last_physicalreads physicalreads,
               curr_executes - last_executes executes,
               curr_parse - last_parse parse,
               curr_hardparse - last_hardparse hardparse,
               curr_transactions - last_transactions transactions,
               round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
               to_char(currtime, 'yy/mm/dd') snap_date,
               to_char(currtime, 'hh24:mi') currtime,
               currsnap_id endsnap_id,
               to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
          from (select a.redo last_redo,
                       a.logicalreads last_logicalreads,
                       a.physicalreads last_physicalreads,
                       a.executes last_executes,
                       a.parse last_parse,
                       a.hardparse last_hardparse,
                       a.transactions last_transactions,
                       lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
                       lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
                       lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
                       lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
                       lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
                       lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
                       lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
                       b.end_interval_time lasttime,
                       lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
                       lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
                       b.startup_time
                  from (select snap_id,
                               dbid,
                               instance_number,
                               sum(decode(stat_name, 'redo size', value, 0)) redo,
                               sum(decode(stat_name, 'session logical reads', value, 0)) logicalreads,
                               sum(decode(stat_name, 'physical reads', value, 0)) physicalreads,
                               sum(decode(stat_name, 'execute count', value, 0)) executes,
                               sum(decode(stat_name, 'parse count (total)', value, 0)) parse,
                               sum(decode(stat_name, 'parse count (hard)', value, 0)) hardparse,
                               sum(decode(stat_name, 'user rollbacks', value, 'user commits', value, 0)) transactions
                          from dba_hist_sysstat
                         where stat_name in
                               ('redo size',
                                'session logical reads',
                                'physical reads',
                                'execute count',
                                'user rollbacks',
                                'user commits',
                                'parse count (hard)',
                                'parse count (total)')
                         group by snap_id, dbid, instance_number) a,
                       dba_hist_snapshot b
                 where a.snap_id = b.snap_id
                   and a.dbid = b.dbid
                   and a.instance_number = b.instance_number
                 order by end_interval_time)) s,
       (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
               lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
          from dba_hist_sys_time_model a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.dbid = b.dbid
           and a.instance_number = b.instance_number
           and a.stat_name = 'DB time') t
 where s.endsnap_id = t.endsnap_id
 order by  s.snap_date, time;