DBA命令速查4：AWR报告相关命令

SQL> set linesize 200
SQL> col SRC_DBNAME format a20
SQL> select * from dba_hist_wr_control;


      DBID        SNAP_INTERVAL                      RETENTION                                 TOPNSQL                  CON_ID   SRC_DBID SRC_DBNAME
---------- ----------------------------- --------------------------------- -------------------- ---------- ---------- --------------------
1605927728 +00000 01:00:00.0            +00008 00:00:00.0                     DEFAULT                       0 1605927728 orcl

--每隔30分钟收集一次，保存1天execute 
dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>14000);

--关闭AWR自动收集
SQL>exec dbms_workload_repository.modify_snapshot_settings (interval=>0,retention=>24*60);

SQL> exec dbms_workload_repository.create_snapshot();

--本实例AWR
@?/rdbms/admin/awrrpt; 

--RAC中选择实例号
@?/rdbms/admin/awrrpti; 

--AWR对 比报告
@?/rdbms/admin/awrddrpt; 

--RAC全局AWR报告
@?/RDBMS/admin/awrgrpt;

--<查看SQL 信息>
SQL> select sql_id,sql_text from DBA_HIST_SQLTEXT where upper(sql_text) like '%EMP E%' and command_type=3;


SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
9ba377xqpau28
select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

SQL> conn / as sysdba
SQL> alter system set "_AWR_RESTRICT_MODE"=TRUE scope=spfile;
《重启数据库》
SQL> exec dbms_swrf_internal.cleanup_database(cleanup_local => true);
SQL> alter system set "_AWR_RESTRICT_MODE"=false scope=spfile;