Oracle Performance Tuning —— 我的学习理解过程
1. Gathering Database Statistics Using the Automatic Workload Repository

Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. Setting the STATISTICS_LEVEL parameter to BASIC disables many Oracle Database features, including the AWR, and is not recommended.

-----所以这里对于statistics_level参数需要理解；

statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个:basic,typical,all.支持alter session,alter system 动态修改.如果要用statspack或者AWR收集系统性能统计数据.那么这个参数的值必须为typical或all.通常all是一个全面收集,包括 OS以及sql执行路径方面的一些统计信息,除非遇见非常严重的性能问题或在一些特殊的性能挣断方面才会用到statistics_level=all, 平常statistics_level=typeical已经足够挣断99%的性能问题了.
example: seting statistics_level
 
alter system set statistics_level=basic;
alter system set statistics_level=typical;
alter system set statistics_level=all;
or
alter session set statistics_level=basic;
alter session set statistics_level=typical;
alter session set statistics_level=all;
 
oracle 还提供了v$statistics_level视图用来记录各个参数下oracle可以收集的统计信息的类别:
 
SQL> ALTER SYSTEM SET statistics_level=basic;
 
System altered.
 
SQL> SELECT statistics_name,
  2         session_status,
  3         system_status,
  4         activation_level,
  5         session_settable
  6  FROM   v$statistics_level
  7  ORDER BY statistics_name;
 
                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
—————————— ———- ———- ———- ———-
Buffer Cache Advice            DISABLED   DISABLED   TYPICAL    NO
MTTR Advice                    DISABLED   DISABLED   TYPICAL    NO
PGA Advice                     DISABLED   DISABLED   TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       DISABLED   DISABLED   TYPICAL    NO
Shared Pool Advice             DISABLED   DISABLED   TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               DISABLED   DISABLED   TYPICAL    YES
 
8 rows selected.
 
statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了;
 
SQL> ALTER SYSTEM SET statistics_level=typical;
 
System altered.
 
SQL> SELECT statistics_name,
  2         session_status,
  3         system_status,
  4         activation_level,
  5         session_settable
  6  FROM   v$statistics_level
  7  ORDER BY statistics_name;
 
                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
—————————— ———- ———- ———- ———-
Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO
MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO
PGA Advice                     ENABLED    ENABLED    TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO
Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES
 
8 rows selected.
 
statistics_level=typical的时候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集这个两项,必须设置statistics_level=all;
 

SQL> ALTER SYSTEM SET statistics_level=all;
 
System altered.
 
SQL> SELECT statistics_name,
  2         session_status,
  3         system_status,
  4         activation_level,
  5         session_settable
  6  FROM   v$statistics_level
  7  ORDER BY statistics_name; 
  

Statistics_Name  Session_Status   System_Status  Activation _Level   Session_Settable
—————————— ———- ———- ———- ———-
Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO
MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO
PGA Advice                     ENABLED    ENABLED    TYPICAL    NO
Plan Execution Statistics      ENABLED    ENABLED    ALL        YES
Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO
Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            ENABLED    ENABLED    ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES
 
如果当statistics_level=ALL,系统收集所有的统计信息.

======================================================================================

10g中表监控与statistics_level

在10g之前的版本,必须用dbms_stats的ALTER_DATABASE/SCHEMA_TAB_MONITORING过程或者create/alter table ... monitoring打开表的监控,在10g中,MONITORING与NOMONITORING关键字已经过时,如果你在create/alter table中指定表的监控或取消监控,该关键字将忽略,表监控特征被statistics_level控制,当statistics_level等于basic时,将禁止表的监控,如果等于typical或all时,表监控是激活的.


 

statistics_level默认是typical,在10g中表监控是激活的,强烈建议在10g中此参数的值是typical.如果STATISTICS_LEVEL设置为basic,不仅不能监控表,而且将禁掉如下一些10g的新功能:
ASH(Active Session History)
ASSM(Automatic Shared Memory Management)
AWR(Automatic Workload Repository)
ADDM(Automatic Database Diagnostic Monitor)

监控结果是自上次统计收集以来大概的INSERT/UPDATE/DELETE的记录数,当然这也与sga中维护的记录数有关,每15分钟,smon会刷新统计结果到数据字典中,可以通过如下DBA/ALL/USER_TAB_MODIFICATIONS数据字典查看监控结果,如果想立即看到监控结果,可以用exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO()来刷新数据到数据字典中,oracle使用这些表的数据去判断表的统计数据是否过期,如果当表的数据改变超过10%,oracle就认为该表的统计数据已经过期.

Example:
--------
Let us take an example:

Step 1:
-------

Create table EMP. Its description is as follows

SQL> desc emp
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> select * from user_tab_modifications;

no rows selected

Initially there are 14 rows in EMP.

Step 2:
-------

Set parameter STATISTICS_LEVEL='TYPICAL'

SQL> alter system set STATISTICS_LEVEL='TYPICAL';

System altered.

Step 3:
-------

Insert additional 14 rows. This will increase the data in EMP by 50% and
therefore the statistics in EMP will be regarded as stale by Oracle.

SQL> insert into emp select * from emp;

14 rows created.
SQL>commit;

Step 4:
-------

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ---------- ---------- ---------- --------- ---
EMP
14 0 0 16-OCT-03 NO

Step 5:
-------

If a monitored table has been modified more than 10%, then these
statistics are considered stale

Analyze the tables whose statistics have become stale using the following command:

execute DBMS_STATS.GATHER_SCHEMA_STATS ('RAJIV',
NULL,
FALSE,
'FOR ALL COLUMNS SIZE 1',
NULL,
'DEFAULT',
TRUE,
NULL,
NULL,
'GATHER STALE',
'LIST' );

Step 6:
-------

Query dba_tab_modifications to check whether the table has been analyzed
or not?

SQL> select * from user_tab_modifications;

no rows selected

No rows in dba_tab_modifications indicates that the table is analyzed.
 

-----------------------------------------------------------------------------------------------

Qusetions:

如果设置监控级别为typical，则对于数据库的许多的中间表，我不需要进行监控，能采用什么方法跳过吗？

中间表可以用global temporary table啊,这样肯定不监控.