Oracle整体性能获取

PROCEDURE modify_snapshot_settings(retention  IN NUMBER DEFAULT NULL,
                                     interval   IN NUMBER DEFAULT NULL,
                                     topnsql    IN NUMBER DEFAULT NULL,
                                     dbid       IN NUMBER DEFAULT NULL
                                     );

DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS
--
PROCEDURE control_restricted_snapshot(allow IN BOOLEAN);
-- ************************************************************************************
-- awr_set_report_thresholds  (PROCEDURE)
--  Allows configuring of specified report thresholds. Allows control of
--  number of rows in the report.
--
--  Parameters
--   top_n_events  	    - number of most significant wait events to be included
--   top_n_files   	    - number of most active files to be included
--   top_n_segments         - number of most active segments to be included
--   top_n_services         - number of most active services to be included
--   top_n_sql              - number of most significant SQL statements to be included
--   top_n_sql_max          - number of SQL statements to be included if their
--                            activity is greater than that specified by
--                            top_sql_pct.
--   top_sql_pct            - significance threshold for SQL statements between
--                            top_n_sql and top_n_max_sql
--   shmem_threshold        - shared memory low threshold
--   versions_threshold     - plan version count low threshold
--
--  Note: effect of each setting depends on the type of report being
--        generated as well as on the underlying AWR data. Not all
--        settings are meaningful for each report type.
--        Invalid settings (such as negative numbers, etc,) are ignored.
-- ************************************************************************************

PROCEDURE awr_set_report_thresholds(
top_n_events      IN NUMBER DEFAULT NULL,
top_n_files       IN NUMBER DEFAULT NULL,
top_n_segments    IN NUMBER DEFAULT NULL,
top_n_services    IN NUMBER DEFAULT NULL,
top_n_sql         IN NUMBER DEFAULT NULL,
top_n_sql_max     IN NUMBER DEFAULT NULL,
top_sql_pct       IN NUMBER DEFAULT NULL,
shmem_threshold   IN NUMBER DEFAULT NULL,
versions_threshold IN NUMBER DEFAULT NULL
);

如把awr设置为10分钟收集一次、每次收集50条耗费资源的sql，并保留2天的收集数据，可以用如下方式修改
SQL> exec dbms_workload_repository.modify_snapshot_settings(2*24*60,10,50);

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(topnsql=>50);

exec DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS(top_n_sql=>50, top_n_events=>10);

col SNAP_INTERVAL format a30
col RETENTION format a30
set linesize 150
select * from dba_hist_wr_control;

1.awr 关注数据库的整体性能报告 类似于体检报告
@?/rdbms/admin/awrrpt
select output from table(dbms_workload_repository.awr_report_html(v_dbid,v_instance_number,v_min_snap_id,v_max_snap_id));
关注的要点：load_profile、efficiency percentages、top 5 events、SQL Statistics、segment_statistics

2.ash ash关注数据库中的等待事件与哪些sql具体对应 类似于胃镜
@?/rdbms/admin/ashrpt
select output from table(dbms_workload_repository.ash_report_html(dbid,inst_num,l_btime,l_etime));
关注的要点：等待事件与sql的具体结合

3.addm Oracle给出的一些建议 类似于病历卡记录
@?/rdbms/admin/addmrpt
select dbms_advisor.get_task_report('ADDM_02','TEXT','ALL') from dual;
关注的要点：整体性的建议，局部sql建议

4.awrdd 针对不同时段的性能的一个比对报告 医生分析前后两次体检报告的动作
@?/rdbms/admin/awrddrpt
关注的要点：不同时期的load_profile、不同时期等待事件、不同时期TOP SQL

5.awrsq 具体某个sql的执行计划，可以保存多个执行计划 类似于活检
@?/rdbms/admin/awrsqrpt
关注的要点：Plan Statistics、Execution Plan