ORACLE的statistics_level的三种状态以及oracle自动诊断工具ADDM

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;

statistics_level=basic的情况下,oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了;

statistics_level=typical的时
候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集这个两项,必须设置statistics_level=all;

如果当statistics_level=ALL,系统收集所有的统计信息.

ADDM介绍

ADDM（Automatic Database Diagnostic Monitor），就是通过诊断和分析awr得到的数据来推断数据库可能存在的问题，addm给出的建议是依照减少db_time为依据。每次收集完awr报告后，都会产生一个addm分析，分析结果在shared_pool里，mmon进程定期将其写入磁盘，AWR每产生一次快照，MMON进程就通知ADDM把AWR最近两次快照之间的差值进行分析。因此，在默认情况下，每当有AWR的快照产生，ADDM将自动运行。通过em可以查看。所以说白了，addm应该是帮助我们发现问题的一个很好的工具。addm可以定位出很多问题，或许你想不到的，它都能帮你想到。
设置开启ADDM(默认是开启的)，要使用这个工具必须设置参数STATISTICS_LEVEL为TYPICAL（默认）或者ALL。
SQL> show parameter control_management

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING--如果为none表示关闭

SQL> show parameter STATISTICS_LEVEL

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL---默认，如果改为none就关闭了很多自动的功能，比如统计信息的收集等，typical已经包含99%的功能，所以一般情况下不用all

SQL> SELECT statistics_name,session_status,system_status,activation_level,session_settable FROM v$statistics_level ORDER BY statistics_name;

STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
---------------------------------------------------------------- -------- -------- ------- ---
Active Session History ENABLED ENABLED TYPICAL NO
Adaptive Thresholds Enabled ENABLED ENABLED TYPICAL NO
Automated Maintenance Tasks ENABLED ENABLED TYPICAL NO
Bind Data Capture ENABLED ENABLED TYPICAL NO
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
Global Cache Statistics ENABLED ENABLED TYPICAL NO
Longops Statistics ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
Modification Monitoring ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Sampling ENABLED ENABLED TYPICAL YES

STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
---------------------------------------------------------------- -------- -------- ------- ---
Plan Execution Statistics DISABLED DISABLED ALL YES---需要在all下才收集
SQL Monitoring ENABLED ENABLED TYPICAL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Streams Pool Advice ENABLED ENABLED TYPICAL NO
Threshold-based Alerts ENABLED ENABLED TYPICAL NO
Time Model Events ENABLED ENABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES---需要在all下才收集
Timed Statistics ENABLED ENABLED TYPICAL YES
Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL NO

STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
---------------------------------------------------------------- -------- -------- ------- ---
V$IOSTAT_* statistics ENABLED ENABLED TYPICAL NO

23 rows selected.

ADDM能发现定位的问题包括：
?操作系统内存页入页出问题
?由于Oracle负载和非Oracle负载导致的CPU瓶颈问题
?导致不同资源负载的Top SQL语句和对象——CPU消耗、IO带宽占用、潜在IO问题、RAC内部通讯繁忙
?按照PLSQL和JAVA执行时间排的Top SQL语句.
?过多地连接 (login/logoff).
?过多硬解析问题——由于shared pool过小、书写问题、绑定大小不适应、解析失败原因引起的。
?过多软解析问题
?索引查询过多导致资源争用.
?由于用户锁导致的过多的等待时间 (通过包dbms_lock加的锁)
?由于DML锁导致的过多等待时间(例如锁住表了)
?由于管道输出导致的过多等待时间(如通过包dbms_pipe.put进行管道输出)
?由于并发更新同一个记录导致的过多等待时间(行级锁等待)
?由于ITL不够导致的过多等待时间（大量的事务操作同一个数据块）
?系统中过多的commit和rollback(logfile sync事件).
?由于磁盘带宽太小和其他潜在问题（如由于logfile太小导致过多的checkpoint，MTTR设置问题，过多的undo操作等等）导致的IO性能问题I
?对于DBWR进程写数据块，磁盘IO吞吐量不足
?由于归档进程无法跟上redo日至产生的速度，导致系统变慢
?redo数据文件太小导致的问题
?由于扩展磁盘分配导致的争用
?由于移动一个对象的高水位导致的争用问题
?内存太小问题——SGA Target, PGA, Buffer Cache, Shared Pool
?在一个实例或者一个机群环境中存在频繁读写争用的热块
?在一个实例或者一个机群环境中存在频繁读写争用的热对象
?RAC环境中内部通讯问题
?LMS进程无法跟上导致锁请求阻塞
?在RAC环境中由于阻塞和争用导致的实例倾斜
?RMAN导致的IO和CPU问题
?Streams和AQ问题
?资源管理等待事件
以上一些项目，其实在addm中，它会一find 的方式将这些项目逐一列出。

以上为摘录。

2 脚本收集ADDM报告
下面为我的实验：
ADDM和AWR一样可以跨任意两个时间快照，可以执行$ORACLE_HOME/rdbms/admin/addmrpt.sql生成addm报告，如果是rac那就执行addmrpti.sql。addm脚本收集方法和awr基本差不多。
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2120886780 TEST 1 TEST

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2120886780 1 TEST TEST MAOMAO

Using 2120886780 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressingwithout
specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
TEST TEST 2024 22 Dec 2013 00:00 1
2025 22 Dec 2013 01:00 1
2026 22 Dec 2013 02:00 1
2027 22 Dec 2013 03:00 1
2028 22 Dec 2013 04:00 1
2029 22 Dec 2013 05:00 1
2030 22 Dec 2013 06:00 1
2031 22 Dec 2013 07:00 1
2032 22 Dec 2013 08:00 1
2033 22 Dec 2013 09:00 1
2034 22 Dec 2013 10:00 1
2035 22 Dec 2013 11:00 1
2036 22 Dec 2013 12:00 1
2037 22 Dec 2013 13:00 1
2038 22 Dec 2013 14:00 1
2039 22 Dec 2013 15:00 1
2040 22 Dec 2013 16:00 1
2041 22 Dec 2013 17:00 1
2042 22 Dec 2013 18:00 1
2043 22 Dec 2013 19:00 1
2044 22 Dec 2013 20:00 1
2045 22 Dec 2013 21:00 1
2046 22 Dec 2013 22:00 1
2047 22 Dec 2013 23:00 1
2048 23 Dec 2013 00:00 1
2049 23 Dec 2013 01:00 1
2050 23 Dec 2013 02:00 1
2051 23 Dec 2013 03:00 1
2052 23 Dec 2013 04:00 1
2053 23 Dec 2013 05:00 1
2054 23 Dec 2013 06:00 1
2055 23 Dec 2013 07:00 1
2056 23 Dec 2013 08:00 1
2057 23 Dec 2013 09:00 1
2058 23 Dec 2013 10:00 1
2059 23 Dec 2013 11:00 1
2060 23 Dec 2013 12:00 1
2061 23 Dec 2013 13:00 1
2062 23 Dec 2013 14:00 1
2063 23 Dec 2013 15:00 1
2064 23 Dec 2013 16:00 1
2065 23 Dec 2013 17:00 1
2066 23 Dec 2013 18:00 1
2067 23 Dec 2013 19:00 1
2068 23 Dec 2013 20:00 1
2069 23 Dec 2013 21:00 1
2070 23 Dec 2013 22:00 1
2071 23 Dec 2013 23:00 1
2072 24 Dec 2013 00:00 1
2073 24 Dec 2013 01:00 1
2074 24 Dec 2013 02:00 1
2075 24 Dec 2013 03:00 1
2076 24 Dec 2013 04:00 1
2077 24 Dec 2013 05:00 1
2078 24 Dec 2013 06:00 1
2079 24 Dec 2013 07:00 1

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
TEST TEST 2080 24 Dec 2013 08:00 1
2081 24 Dec 2013 09:00 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2078
Begin Snapshot Id specified: 2078

Enter value for end_snap: 2079
End Snapshot Id specified: 2079

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_2078_2079.txt. To use this name,
pressto continue, otherwise enter an alternative.

Enter value for report_name: addm_1224_06_07.txt
Using the report name addm_1224_06_07.txt

Running the ADDM analysis on the specified pair of snapshots ...

Generating the ADDM report for this analysis ...

ADDM Report for Task 'TASK_2143'
--------------------------------

Analysis Period
---------------
AWR snapshot range from 2078 to 2079.
Time period starts at 24-DEC-13 06.00.50 AM
Time period ends at 24-DEC-13 07.00.07 AM

Analysis Target
---------------
Database 'TEST' with DB ID 2120886780.
Database version 11.2.0.3.0.
ADDM performed an analysis of instance TEST, numbered 1 and hosted at
MAOMAO.

Activity During the Analysis Period
-----------------------------------
Total database time was 14 seconds.
The average number of active sessions was 0.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are no findings to report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information
----------------------

Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.

End of Report
Report written to addm_1224_06_07.txt
SQL>

3 使用em来收集addm信息

查看详细信息可以点击查看报告：