Database Maintenance 维护DB

you should be able to: Manage optimizer statistics Manage the Automatic Workload Repository (AWR) 自动负载知识库 Use the Automatic Database Diagnostic Monitor (ADDM) 自动数据库诊断监控 Describe and use the advisory framework Set alert thresholds 阈值 Use server-generated alerts Use automated tasks

AWR --（严重错误）--> ADR 自动诊断知识库

术语 Automatic Workload Repository (AWR): Infrastructure for data gathering, analysis, and solutions recommendations AWR Baseline: AWR基线 A set of AWR snapshots for performance comparison Metric: 度量值 Rate of change in a cumulative statistic Statistics: 统计信息Data collections providing database and object detail Optimizer statistics: 优化器统计信息Used by query optimizer Database statistics: 数据库统计信息Used for performance Threshold: 阈值A boundary value againstwhich metric values are compared

Oracle Optimizer Oracle 优化器 依赖于统计信息，找出最优执行计划： 1、评估表达式和条件 2、使用统计信息 3、决定如何访问数据 4、多表JOIN连接方式 5、找出最优的路径 The optimizer: Evaluates expressions and conditions Uses object and system statistics Decides how to access the data Decides how to join tables Determines the most efficient path

SQL> SELECT COUNT() FROM hr.employees; COUNT()

   214

SQL> SELECT num_rows FROM dba_tables 2 WHERE owner='HR' AND table_name = 'EMPLOYEES'; NUM_ROWS

   107

exec dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','13');

AWR： 默认每60分钟生成快照，默认保留8天，使用MMON进程

建议使用TYPICAL。

select * from DBA_OUTSTANDING_ALERTS; 告警消失后告警信息被放入DBA_ALERT_HISTORY; select * from DBA_ALERT_HISTORY;

有状态告警先进入DBA_OUTSTANDING_ALERT; 无状态告警直接进入DBA_ALERT_HISTORY;