dbms_workload_repository

admin

查询当前的设置

1. SQL> select SNAP_INTERVAL,RETENTION from dba_hist_wr_control;

复制代码

修改为20天，30分钟间隔

1. exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(28800,30);
   

复制代码

手动生成快照

1. exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

复制代码

手动删除当前库快照

1. exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(106,107);
   

复制代码

手动删除其他库快照

1. exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(715,730,1022869489);

复制代码

创建基线

1. DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
   
2.         start_snap_id IN NUMBER,
   
3.         end_snap_id   IN NUMBER,
   
4.         baseline_name IN VARCHAR2);

复制代码

1. exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (106,107,'sqlt_base_1');

复制代码

删除基线

1. select baseline_id,baseline_name,start_snap_id,end_snap_id,baseline_type,creation_time from dba_hist_baseline;

复制代码

1. exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE ('&base_name')

复制代码

添加特定sql捕获

1. exec dbms_workload_repository.add_colored_sql('&sql_id');

复制代码

删除特定sql捕获

1. exec dbms_workload_repository.drop_colored_sql('&sql_id');

复制代码

用select生成text awrrpt

1. set lines 180
   
2. SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(631726184,1,89,90));

复制代码

用select生成html awrrpt

1. SELECT dbms_workload_repository.awr_report_html(<snapshot_id>, <snapshot_id>, <report_type>) as report FROM dual;
   
2. 将 <snapshot_id> 替换为你想要生成报告的快照 ID，<report_type> 可以是 html、text 或 active。
   

复制代码

关闭快照

1. begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(0,0); end;

复制代码

永久保留快照

1. begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(0,30); end;

复制代码