ORACLE Automatic Maintenance

check the status of auto maintenance job in ORACLE 11gR2

select client_name, task_name, status from dba_autotask_task;

分类：

1. 系统采样：

AWR(Automatic Workload Repository)：一个小时对整个数据库采样一次，保留7天的内容；

ASH(Automated Session History)：15秒对整个数据库的10%采样一次

2. ADDM(Automatic Database Diagnostic Monitor)：分析AWR报告，然后给出调整优化建议；

每次AWR收集snapshot后，ADDM自动运行一次；ADDM运行结果存放在AWR报告中；

手工调用ADDM：

a. Enterprise Manager GUI；

b. DBMS_Advisor package APIs；

c. $ORACLE_HOME/rdbms/admin/addmrpt.sql；

3. Auto task：定义自动任务；

4. Server alert：系统报警；

查看级别：show parameter level 数据均存放在sysaux表空间中

修改级别：

alter system set STATISTICS_LEVEL = 'BASIC' 

alter system set STATISTICS_LEVEL = 'ALL' 

alter system set STATISTICS_LEVEL = 'TYPICAL' 

不过如果开启自动内存管理改成BASIC会报错：

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled 

收集数据job：gather_stats_job

查看：

SELECT owner,

job_name,

state,

last_start_date,

last_run_duration,

failure_count

FROM dba_scheduler_jobs

WHERE job_name = 'GATHER_STATS_JOB'; 

关闭：

BEGIN 

  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

END;

/

开启：

BEGIN 

  DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');

END;

/

手动执行全库统计信息
exec dbms_stats.GATHER_DATABASE_STATS(99,CASCADE=>TRUE);

手工收集统计信息：
SQL> exec dbms_stats.gather_schema_stats('detail',options=>'gather stale',estimate_percent =>10);

--收集没有分析过的表的统计信息
begin
dbms_stats.gather_schema_stats(ownname=>scott,options=>'gather empty');
end;

--重新分析修改量超过10%的表（这些修改包括插入、更新和删除）
begin
dbms_stats.gather_schema_stats(ownname=>scott,options=>'gather stale');
end;

自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = 'SCOTT';

查看历史JOB执行情况
select LOG_ID,LOG_DATE,OWNER,JOB_NAME,STATUS,REQ_START_DATE,ACTUAL_START_DATE from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='BSLN_MAINTAIN_STATS_JOB'

手工收集数据花费时间：导入完成后90GB数据，分析花费4小时

ADDM分析SQL方式：

SQL Turning：给出添加Index的建议；重建sql语句结构，建立sql语句profile

SQL Access：前提需要做统计；给出添加Index，实体化视图的建议，可以用于模拟workload，分区表

MTTR Advice：给出Instance recovery时间

系统参数：optimizer_mode

尽快查询出的行数

ALL_ROWS：全部行（默认）

first_rows_[1 | 10 | 100 | 1000]：前1，10，100，1000行

first_rows：第一行

命令行方式做报表：

AWR：$ORACLE_HOME/rdbms/admin/awrrpt.sql

ADDM：$ORACLE_HOME/rdbms/admin/addmrpt.sql

ASH：$ORACLE_HOME/rdbms/admin/ashrpt.sql

解析sql脚本：

hard：第一次解析

soft：同样脚本再次运行前的解析

hard时间长于soft

尽量是oracle运行soft解析

方法：

1. 查看系统设置：show parameter cursor_sharing

2. 参数设定：SIMILAR / FALSE / EXACT（默认）

在EXACT模式下：使用取代变量，取代常量，带来的问题：ORACLE会选择使用上次使用的索引

在SIMILAR模式下：系统会默认使用变量来提高效率

在FALSE模式下：系统每次都会进行hard解析，除非完全一致

手工运行advice

1. 对表删除大量数据；

2. 手工手机统计信息：exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'CORRUP');

3. EM中使用segment advicer

4. 根据建议运行shrink

Check the statistics last update date

select table_name, last_analyzed, NUM_ROWS from dba_tables where owner='THOR' ORDER BY LAST_ANALYZED