本讲内容包括：
使用优化器统计信息
管理自动工作负载资料档案库
使用数据库自动性能诊断监控(ADDM)
术语
Automatic Workload Repository (AWR):Infrastructure for data gathering, analysis, and solutions recommendations
Baseline: Data gathered of a “normal running database” for performance comparison
Metric: Rate of change in a cumulative statistic
Statistics: Data collections used for optimizing internal operations, such as execution of a SQL statement
Threshold: A boundary value against which metric values are compared
一：优化器统计信息
特点：
非实时(默认的时间窗口是周一至周五晚上10点到第二天凌晨6天，周末全天)能跨越数据库实例重启，自动收集，优化器统计信息包括表，列索引和系统统计信息，储存在数据字典中
统计信息收集主要包括：
Size of the table or index in database blocks
Number of rows
Average row size and chain count (tables only)
Height and number of deleted leaf rows (indexes only)
[oracle@rhel6 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 22 16:34:00 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> create table hr.t04212_big as select * from dba_source;
Table created.
SQL> conn hr/hr
Connected.
SQL> set autot on
SQL> select count(*) from t04212_big;
COUNT(*)
----------
323065
Execution Plan
----------------------------------------------------------
Plan hash value: 1778284531
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1463 (1)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T04212_BIG | 294K| 1463 (1)| 00:00:18 |
-------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
//可以看到，在优化器统计信息未收集之前，进行了动态采样
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
6599 consistent gets
6520 physical reads
0 redo size
517 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select num_rows from dba_tables t where t.owner='HR' and t.table_name=upper('t04212_big');
NUM_ROWS
----------
SQL> exec dbms_stats.gather_table_stats('HR','T04212_BIG');
PL/SQL procedure successfully completed.
SQL> select num_rows from dba_tables t where t.owner='HR' and t.table_name=upper('t04212_big');
NUM_ROWS
----------
323445
----------
SQL> set autot on
SQL> select count(*) from hr.t04212_big;
COUNT(*)
----------
323065
Execution Plan
----------------------------------------------------------
Plan hash value: 1778284531
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1463 (1)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T04212_BIG | 323K| 1463 (1)| 00:00:18 |
------------------------