Database Maintenance 维护DB

you should be able to:

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

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

术语

Automatic Workload Repository (AWR): Infrastructure for data gathering, analysis, and solutions recommendationsAWR Baseline: AWR基线 A set of AWR snapshots for performance comparisonMetric: 度量值 Rate of change in a cumulative statisticStatistics: 统计信息Data collections providing database and object detailOptimizer statistics: 优化器统计信息Used by query optimizerDatabase statistics: 数据库统计信息Used for performanceThreshold: 阈值A boundary value againstwhich metric values are compared

Oracle Optimizer Oracle 优化器

依赖于统计信息，找出最优执行计划：1、评估表达式和条件2、使用统计信息3、决定如何访问数据4、多表JOIN连接方式5、找出最优的路径The optimizer:Evaluates expressions and conditionsUses object and system statisticsDecides how to access the dataDecides how to join tablesDetermines 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;