Oracle故障和性能诊断流程V0.1_20120116

1编写目的
介绍在现场出现Oracle故障和性能问题时的诊断流程，让项目支撑人员在解决Oracle问题时有据可依。

2适用场景
当现场出现以下问题时，可依照本文档进行处理：
1）Oracle异常关闭、重启、Hang住等故障。

2）PM相关程序出现性能急剧下降的情况。

3）PM相关程序出现Hang住等故障。

3分析流程
3.1信息收集及初步诊断
本节介绍以下知识点：
1）出现问题时，需要收集什么样的信息？
2）这些信息的作用是什么？
3）如何进行收集？
4）如何对这些信息进行分析，从而得到初步诊断结论？
初步诊断结论是指引起问题的原因，但不是根因。

例如，初步诊断结论是内存问题、IO问题、等待事件的问题、执行计划的问题、统计信息的问题。

3.1.1PM运行情况（黄）
反映PM运行情况的数据主要包括运行日志以及存储运行数据的表（例如COUNTERLOADMESSAGE、SUM_SUMMARYTASKQUEUE表等）。

从以上内容中，可以分析出PM 在运行过程中是否有错误发生，以及是否存在性能问题。

以下内容均以C03版本为准，C02和V4.5可能有变化，需结合实际情况作相应调整。

1)如何收集：
a)运行日志：加载环境变量后，使用cd $PM4H_LOG进入日志目录
2)如何分析：
a)运行日志：
i.可以使用cat <logfilename>| grep ERROR的方式，搜索指定日志中是否存
在错误信息。

<logfilename>可以是具体的日志文件名，也可以是使用了通
配符的表达式。

例如搜索所有的汇总日志中是否存在错误，可使用如下命
令：
cat Summarize* | grep ERROR
ii.如果某个日志文件中存在ERROR信息，可以使用vi命令编辑该文件，依次输入/ERROR和回车，搜索ERROR信息，查看详细的错误信息。

（/是vi
编辑器中的搜索命令）
iii.对于性能分析，可以分为两种情况，一种是程序出现Hang住的情况，表现为日志长时间无内容输出；另一种是程序没有Hang住，但在单位周期
内无法处理完一周期的性能数据。

iv.出现Hang住的情况时，可以使用两种方式找到Hang住的任务。

一种是通过日志分析出当前正在执行的任务。

以汇总执行日志为例，可以分析
Hang住时那一轮的日志中，哪些任务只有start日志，没有end日志，这
些任务就是Hang住的任务；另一种是在DB服务器上使用top命令查看
当前正在运行的Oracle进程，找到进程运行时间长的进程，并找到进程
对应的SQL，进而分析出Hang住的任务。

v.出现整体性能下降时，可以通过其它方面的信息来查找问题。

b)运行数据表：
i.运行数据表的数据主要用于分析核心模块的运行效率以及工作量的变化。

ii.分析COUNTERLOADMESSAGE表中每天产生的MSG数量，可以得出入库程序的工作量和性能情况。

PILOADMESSAGE和COUNTERLOADMESSAGE类
似。

iii.分析SUM_SUMMARYTASKQUEUE表中每小时执行完成的TASK数量，可以得出汇总执行程序的工作量和性能情况。

SUM_BHTASKQUEUE和SUM_SUMMARYTASKQUEUE类似。

3.1.2操作系统资源情况（孙）
●CPU
◆信息收集
登录系统后，使用top、vmstat、iostat、mpstat等命令，都可以查看到操作系统的
表1-1 TOP命令中的CPU信息
表1-2 vmstat命令中的CPU信息
表1-2 iostat命令中的CPU信息
◆初步诊断
使用CPU利用率和负载来进行CPU的初步诊断。

1.CPU利用率的参考值：正常情况下<=70%
1）如果CPU的利用率长期大约70%，说明CPU的已经比较繁忙；
2）如果故障时刻，连续超过95%以上，说明可能是CPU存在瓶颈。

2.CPU负载的参考值范围：CPU核数*0 - CPU核数*3
1）CPU负载越小越好；如果负载=CPU核数，还可以接受；
2）如果负载>=CPU核数*3，则说明系统负载非常高。

◆更详细的资料，见附录一《TOP命令详解》、附录二《iostat命令详解》、附录三《vmstat
命令详解》。

●内存
◆信息收集
登录系统后，使用top、vmstat、free等命令，都可以查看到操作系统的CPU使用
●更详细的资料，见附录四《free命令详解》。

◆初步诊断
诊断内存是是否是瓶颈的标准：swap和+buffers/cache。

1）只要不用swap的交换空间，就不用担心自己的内存太少。

如果常常swap用很多，可能你就要考虑加物理内存了。

这也是linux看内存是否够用的标准。

2）如果是应用服务器的话，一般只看第二行，+buffers/cache，即对应用程序来说free的内存太少了，也是该考虑优化程序或加内存了。

●IO
◆信息收集
表1-4 iostat命令中的I/O信息
◆初步诊断
表1-4中是最主要的I/O参数，使用他们来进初步的诊断：
1）如果%util 接近100%，说明产生的I/O请求太多，I/O系统已经满负荷，该磁盘可能存在瓶颈。

2）如果%util接近100%，表明I/O请求太多，I/O系统已经满负荷，磁盘可能存在瓶颈，一般%util大于70%，I/O压力就比较大，读取速度有较多的wait.同时可
以结合vmstat查看查看b参数(等待资源的进程数)和wa参数(IO 等待所占用的
CPU时间的百分比，高过30%时IO压力高)。

3）await 的大小一般取决于服务时间(svctm) 以及I/O队列的长度和I/O请求的发出模式。

如果svctm 比较接近await，说明I/O几乎没有等待时间；如果await
远大于svctm，说明I/O队列太长，应用得到的响应时间变慢。

●持续收集资源信息
◆信息收集
需要在系统上部署NMON软件。

具体的部署方法见附录五《NMON的部署方法》。

◆初步诊断
见附录六《NMON的诊断方法》
3.1.3Alert日志和Trace文件（孙）
◆信息收集
收集方法：
第一步：ftp到alert日志的目录，将alert日志收集下来。

方法一：Alert日志的目录可以通过oracle的参数background_dump_dest找到。

如图1-1所示。

方法二：使用这个语句select value from v$diag_info where name ='Diag Trace'得到alert日志的位
置。

另外，RAC节点下的日志文件结构和普通的日志文件结构不一样。

请参考附件《附录五：Oracle
RAC集群环境下日志文件结构》。

第二步：找到故障发生时的时间段的log日志进行分析。

第三步：如果通过alert日志看不到具体的信息，可以通过alert日志的提示，转到
相应的trace文件，进行信息收集。

示例如下：
其中：
“/opt/oracle/db/diag/rdbms/vmos5200/vmos52001/trace/vmos52001_ora_8723.trc”
和
“/opt/oracle/db/diag/rdbms/vmos5200/vmos52001/incident/incdir_28441/vmos520
01_ora_8723_i28441.trc”就是trace文件。

第四步：也可以登录https://网站，查找具体的错误代码。

例如：
错误代码：kksfbc-wrong-kkscsflgs。

◆初步诊断
在alert日志中能够看到大部分严重错误、警告，以及一些重要的操作产生的日志。

从ORACLE的角度来看，alert日志的信息可以分为如下几类：
所有的内部错误（ORA-00600），块损坏错误（ORA-01578），死锁（ORA-00060）。

管理性的操作，如create，alter，drop语句和start，shutdown，archivelog语
句
MTS模式（多线程服务器。

区别于专用服务器）下共享服务器进程和调度进程的信息和错误
自动刷新物化视图时发生的错误
非默认的启动参数
各个后台进程运行产生的错误。

Oracle的后台进程见附录六《Oracle的后台进程》。

同时用户也可以使用Oracle的DBMS_SYSTEM包的KSDWRT过程向alert日志书写自定义信息
Job执行失败错误
从用户的角度，或者DBA的角度来看，alert日志的信息可以分为如下几类：
1）故障级错误：这种信息需要优先定位；故障类信息如：
Sat Nov 05 10:13:37 2011
Errors in file /opt/oracle/db/diag/rdbms/vmos5200/vmos52001/trace/vmos52001_ora_8723.trc (incident=28441):
ORA-00600: internal error code, arguments: [kksfbc-wrong-kkscsflgs], [10327733560], [45], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/db/diag/rdbms/vmos5200/vmos52001/incident/incdir_28441/vmos52001 _ora_8723_i28441.trc
2）警告级信息。

如：
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.129.7.98)(PORT=2243)) WARNING: inbound connection timed out (ORA-3136)
3）操作级信息：
Errors in file
/opt/oracle/db/diag/rdbms/vmos5200/vmos52001/trace/vmos52001_ora_8480.trc: ORA-00942: table or view does not exist
更详细的信息请参见：
1）附录七：《读懂alert日志》
2）附录八：《读懂trace文件》
3.1.4AWR报告（房）
3.1.5ADDM报告（张）
（1）出现问题时，需要收集什么样的信息？
ADDM(Automatic Database Diagnostic Monitor) 是植入Oracle数据库的一个自诊断引擎。

ADDM 通过检查和分析AWR获取的数据来判断Oracle数据库中可能的问题。

ADDM会找到数据库当前的瓶颈，找出等待时间或占用CPU处理时间较长的事件（比如不合理的SQLID 等），并可以给出相应的建议，达到减小系统吞吐量的目的。

（2）这些信息的作用是什么？
通过ADDM我们可以找到当前占用数据库资源的事件，并根据Oracle自动给出这些事件的建议来降低对系统资源的占用。

（3）如何进行收集？
收集方法：
①用sqlplus登陆Oracle。

②输入@?/rdbms/admin/addmrpt准备生成ADDM报告
SQL> @?/rdbms/admin/addmrpt
Oracle会把最近的snapshotID号及对应的时间段显示出来，并提示输入snapshotID。

③根据需要诊断的时间段，输入需要的snapshotID开始号和结束号。

Enter value for begin_snap: 191
Enter value for end_snap: 194
④输入ADDM报告的名字，没有的话Oracle会默认为addmrpt_1_（起始snapshot）_（终止snapshot）.txt；这里就用默认的，敲回车即可。

⑤Oracle会把ADDM报告输出到屏幕上并同时写入addmrpt_1_（起始snapshot）_（终止snapshot）.txt文件。

生成的文件默认在$ORACLE_HOME/rdbms/admin下。

这样ADDM报告就生成完毕了。

（4）如何对这些信息进行分析，从而得到初步诊断结论？
Oracle 11G的ADDM报告分为3部分：
①ADDM报告的生成环境概述：
Analysis Period
---------------
AWR snapshot range from 191 to 194.
Time period starts at 07-DEC-11 07.00.35 AM
Time period ends at 07-DEC-11 10.00.16 AM
Analysis Target
---------------
Database 'MOS5200' with DB ID 3304876993.
Database version 11.1.0.6.0.
ADDM performed an analysis of instance mos5200, numbered 1 and hosted at
creede.
Activity During the Analysis Period
-----------------------------------
Total database time was 437 seconds.
The average number of active sessions was .04.
Analysis Period说明ADDM生成的snapshot范围和时间段；
Analysis Target说明ADDM生成的Oracle实例、主机及版本；
Activity During the Analysis Period说明ADDM生成的数据库消耗时间，活动回话信息。

②问题列表（这是ADDM的主要部分）：
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
--------------------- ------------------- ---------------
1 Top SQL by DB Time .0
2 | 37.36 5
2 Hard Parse 0 | 10.74 0
3 "User I/O" wait Class 0 | 7.81 0
4 Soft Parse 0 | 4.82 2
5 Undersized SGA 0 | 4.81 1
6 Java Execution 0 | 4.3 1
7 CPU Usage 0 | 2.24 1
Summary of Findings是ADDM找出的Oracle存在的瓶颈总览。

下面Description每一行代表一个导致系统瓶颈的事件、对系统的影响比例，以及Oracle自己给出建议的条数。

下面就是对每个事件的具体分析，拿Top SQL by DB Time这个首要问题为例：
Findings and Recommendations
----------------------------
Finding 1: Top SQL by DB Time
Impact is .02 active sessions, 37.36% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found.
Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 23.39% of total activity.
-------------------------------------------------------------------
Action
Investigate the SQL statement with SQL_ID "a9bacd1uu35ga" for possible performance improvements.
Related Object
SQL statement with SQL_ID a9bacd1uu35ga and PLAN_HASH 2060173346.
SELECT /*+NESTED_TABLE_GET_REFS+*/ "PM4H_AD"."SYS_INTERGRATION".* FROM "PM4H_AD"."SYS_INTERGRATION"
Rationale
SQL statement with SQL_ID "a9bacd1uu35ga" was executed 1 times and had
an average elapsed time of 79 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, 9.29% of total activity.
----------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6gvch1xu9ca3g.
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF
broken THEN :b := 1; ELSE :b := 0; END IF; END;
Rationale
SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 179 times and had an average elapsed time of 0.3 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is 0 active sessions, 5.2% of total activity.
---------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "fqmpmkfr6pqyk".
Related Object
SQL statement with SQL_ID fqmpmkfr6pqyk and PLAN_HASH 2805031759.
select s.synonym_name object_name, o.object_type
from sys.all_synonyms s,
sys.all_objects o
where s.owner in ('PUBLIC', user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE',
'FUNCTION', 'SEQUENCE')
Action
Investigate the SQL statement with SQL_ID "fqmpmkfr6pqyk" for possible performance improvements.
Related Object
SQL statement with SQL_ID fqmpmkfr6pqyk and PLAN_HASH 2805031759.
select s.synonym_name object_name, o.object_type
from sys.all_synonyms s,
sys.all_objects o
where s.owner in ('PUBLIC', user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE',
'FUNCTION', 'SEQUENCE')
Rationale
SQL statement with SQL_ID "fqmpmkfr6pqyk" was executed 4 times and had an average elapsed time of 3.4 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, 3.35% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1rswbxwhbpmr7".
Related Object
SQL statement with SQL_ID 1rswbxwhbpmr7 and PLAN_HASH 2506064221.
select decode(bitand(a.flags, 16384), 0, a.next_run_date,
st_enabled_time), a.obj#, decode(bitand(a.flags, 16384),
0, 0, 1), a.sch_job from (select p.obj# obj#, p.flags flags,
p.next_run_date next_run_date, p.job_status job_status,
p.class_oid class_oid, st_enabled_time last_enabled_time,
p.instance_id instance_id, 1 sch_job from sys.scheduler$_job p
UNION ALL select q.obj#, q.flags, q.next_run_date, q.job_status,
q.class_oid, st_enabled_time, q.instance_id, 1 from
sys.scheduler$_lightweight_job q UNION ALL select j.job, 0,
cast(j.next_date as timestamp with time zone), 1, NULL,
cast(j.next_date as timestamp with time zone), NULL, 0 from
sys.job$ j where (:1 = 1) and (j.field1 is null or j.field1 =
0) and j.job not in (select v.id2 from v$lock v where v.type =
'JQ')) a where bitand(a.job_status, 3) = 1 and ((bitand(a.flags,
134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <>
0)) and bitand(a.flags, 4096) = 0 and a.instance_id is NULL
and (a.class_oid is null or (a.class_oid is not null and
a.class_oid in (select
b.obj# from sys.scheduler$_class b
where b.affinity is null))) and decode(bitand(a.flags, 16384), 0,
a.next_run_date, st_enabled_time) = (select
min(decode(bitand(c.flags, 16384), 0, c.next_run_date,
st_enabled_time)) from (select r.flags flags, r.next_run_date
next_run_date, r.job_status job_status, r.class_oid class_oid,
st_enabled_time last_enabled_time, r.instance_id instance_id
from sys.scheduler$_job r UNION ALL select s.flags,
s.next_run_date, s.job_status, s.class_oid, st_enabled_time, s.instance_id from sys.scheduler$_lightweight_job s UNION ALL
select 0, cast(k.next_date as timestamp with time zone), 1, NULL,
cast(k.next_date as timestamp with time zone), NULL from sys.job$
k where (:2 = 1) and (k.field1 is null or k.field1 = 0)
and k.job not in (select w.id2 from v$lock w where w.type = 'JQ')) c
where bitand(c.job_status, 3) = 1 and ((bitand(c.flags, 134217728
+ 268435456) = 0) or (bitand(c.job_status, 1024) <> 0))
and bitand(c.flags, 4096) = 0 and c.instance_id is NULL and
(c.class_oid is null or (c.class_oid is not null and
c.class_oid in (select
d.obj# from sys.scheduler$_class d
where d.affinity is null))))
Action
Investigate the SQL statement with SQL_ID "1rswbxwhbpmr7" for possible performance improvements.
Related Object
SQL statement with SQL_ID 1rswbxwhbpmr7 and PLAN_HASH 2506064221.
select decode(bitand(a.flags, 16384), 0, a.next_run_date,
st_enabled_time), a.obj#, decode(bitand(a.flags, 16384),
0, 0, 1), a.sch_job from (select p.obj# obj#, p.flags flags,
p.next_run_date next_run_date, p.job_status job_status,
p.class_oid class_oid, st_enabled_time last_enabled_time,
p.instance_id instance_id, 1 sch_job from sys.scheduler$_job p
UNION ALL select q.obj#, q.flags, q.next_run_date, q.job_status,
q.class_oid, st_enabled_time, q.instance_id, 1 from
sys.scheduler$_lightweight_job q UNION ALL select j.job, 0,
cast(j.next_date as timestamp with time zone), 1, NULL,
cast(j.next_date as timestamp with time zone), NULL, 0 from
sys.job$ j where (:1 = 1) and (j.field1 is null or j.field1 =
0) and j.job not in (select v.id2 from v$lock v where v.type =
'JQ')) a where bitand(a.job_status, 3) = 1 and ((bitand(a.flags,
134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <>
0)) and bitand(a.flags, 4096) = 0 and a.instance_id is NULL
and (a.class_oid is null or (a.class_oid is not null and
a.class_oid in (select
b.obj# from sys.scheduler$_class b
where b.affinity is null))) and decode(bitand(a.flags, 16384), 0,
a.next_run_date, st_enabled_time) = (select
min(decode(bitand(c.flags, 16384), 0, c.next_run_date,
st_enabled_time)) from (select r.flags flags, r.next_run_date
next_run_date, r.job_status job_status, r.class_oid class_oid,
st_enabled_time last_enabled_time, r.instance_id instance_id
from sys.scheduler$_job r UNION ALL select s.flags,
s.next_run_date, s.job_status, s.class_oid, st_enabled_time,
s.instance_id from sys.scheduler$_lightweight_job s UNION ALL
select 0, cast(k.next_date as timestamp with time zone), 1, NULL,
cast(k.next_date as timestamp with time zone), NULL from sys.job$
k where (:2 = 1) and (k.field1 is null or k.field1 = 0)
and k.job not in (select w.id2 from v$lock w where w.type = 'JQ')) c
where bitand(c.job_status, 3) = 1 and ((bitand(c.flags, 134217728
+ 268435456) = 0) or (bitand(c.job_status, 1024) <> 0))
and bitand(c.flags, 4096) = 0 and c.instance_id is NULL and
(c.class_oid is null or (c.class_oid is not null and
c.class_oid in (select
d.obj# from sys.scheduler$_class d
where d.affinity is null))))
Rationale
SQL statement with SQL_ID "1rswbxwhbpmr7" was executed 209 times and had an average elapsed time of 0.07 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is 0 active sessions, 3.34% of total activity.
----------------------------------------------------------------
Action
Investigate the SQL statement with SQL_ID "3am9cfkvx7gq1" for possible
performance improvements.
Related Object
SQL statement with SQL_ID 3am9cfkvx7gq1.
CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:target_guid,:metric_guid,
:metric_values)
Rationale
SQL statement with SQL_ID "3am9cfkvx7gq1" was executed 167 times and had
an average elapsed time of 0.087 seconds.
Impact is .02 active sessions, 37.36% of total activity.
说明这个事件在快照范围内影响2%的活动回话，对数据库影响为37.36%。

SQL statements consuming significant database time were found.
说明找到了占用数据库资源较大的SQL。

Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 23.39% of total activity.
Recommendation就是Oracle给出的建议，这里建议使用SQL Tuning来解决SQL性能问题，下面预计能够解决1%的会话的问题，有效性为23.39%。

Action
Investigate the SQL statement with SQL_ID "a9bacd1uu35ga" for possible
performance improvements.
Related Object
SQL statement with SQL_ID a9bacd1uu35ga and PLAN_HASH 2060173346.
SELECT /*+NESTED_TABLE_GET_REFS+*/ "PM4H_AD"."SYS_INTERGRATION".*
FROM "PM4H_AD"."SYS_INTERGRATION"
Rationale
SQL statement with SQL_ID "a9bacd1uu35ga" was executed 1 times and had
an average elapsed time of 79 seconds.
Action是基于ADDM给出的建议如何去处理问题，通过Action可以看出SQL ID为a9bacd1uu35ga有性能优化的空间。

并给出了大致的SQL语句。

可以通过这个SQL ID找到历史SQL并进行处理。

Rationale是ADDM给出的这个SQL的信息，可以看到这个SQL执行了1次，但是花了79秒。

针对Top SQL by DB Time这个事件ADDM总共给出了5条建议，都是不同的SQL，基于上面的方法就可以找出SQL并进行优化。

可以通过select*from v$sqltext t where t.SQL_ID=''来根据SQLID查询SQL语句。

③附加信息
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Miscellaneous Information是ADDM给出的一些Oracle其他不是瓶颈的信息，就是说明这些事件没有过多占用数据库的资源。

3.2进阶诊断
本节主要介绍如何根据之前判断出的初步结论，进一步收集信息，进行进一步分析，从而找到问题的根因。

3.2.1内存分析（孙）
●通过AWR报告中的Cache Sizes、Advisory Statistics等章节分析内存设置是否合适
◆信息收集：
第一步：用oracle用户登录系统，并登录sqlplus；
第二步：运行命令@$ORACLE_HOME/rdbms/admin/awrrpt.sql;，进行awr报告的收
集。

第三步：下载awr报告，并分析。

◆分析：
因为Oracle 11g是自动内存管理，如果进行内存分析的话，主要看一下：SGA的调
整建议、PGA的调整建议，以及SGA的主要组成部分（buffer pool和share pool）
的调整建议即可。

第一步：在awr报告中搜索“Advisory Statistics”；
第二步：查看“SGA Target Advisory”部分给出的修改建议。

如下图所示，建议调
整为5G。

调整为5G以后，物理读不会再降低。

第三步：查看“PGA Target Advisory”部分给出的修改建议。

从下图可以看到，PGA
无需调整。

●通过ADDM报告查看对内存设置的建议
第一步：运行命令@$ORACLE_HOME/rdbms/admin/addmrpt.sql;，进行addm报告的收集。

第二步：下载，并分析。

从addm报告中可以看到对SGA的大小的建议，也是调整为5G。

●通过AWR报告中的SQL ordered by Sharable Memory章节找到最消耗共享内存的SQL，
对这些SQL进行重点分析。

第一步：在awr报告中搜索“SQL ordered by Sharable Memory”：占用library cache （语句缓冲区）最多的sql语句。

第二步：对sql语句进行分析。

如何分析？
3.2.2IO分析（郑、房）
●分析磁阵的划分是否存在问题
●分析数据文件的分布是否合理，是否因分布不均导致在某些磁盘上产生IO瓶颈
●分析Redo日志的大小和位置是否合理
●分析表的Block信息是否正常，占用空间是否正常
●通过AWR报告中的SQL ordered by Reads找到物理读最大的SQL，对这些SQL进行
重点分析
●通过AWR报告中的IO Stats分析各表空间和文件的IO是否存在异常
3.2.3CPU分析（黄）
1)查看AWR报告中的Top 5 Timed Foreground Events，查看DB CPU占DB Time的
百分比，如果DB CPU占比较低，则需要重点分析其它的等待事件。

等待事件主要分
为以下几类：
a)IO类：如db file sequential read等，是由于等待IO读写导致的，可能原因是SQL执行计划不对，
导致大量全表扫描或快速索引全扫描，也有可能是Buffer_cache大小不合适，导致大量的物理读。

b)Redo类：如log file sync等，是由于Redo日志的相关读写等待导致的，可能原
因是Redo日志写入量过大，写入不及时，也有可能是Redo日志个数及大小不
足，导致Redo日志无法切换等。

c) Cluster类：如gc cr multi block request等，是由于两台RAC机器在争用共享资
源产生的等待，可能原因是程序设计问题。

d)其它类：编译对象、truncate、自动段管理等Oracle内部操作也会产生一些等待
事件，消耗CPU资源。

2) 如果DB CPU占比较高，但CPU整体负载仍然很高，则需分析CPU消耗在哪一部分。

首先需要观察的是硬解析，可以通过AWR报告中的Load Profile观察每秒硬解析的数
量，也可以通过AWR报告中的Time Model Statistics，查看hard parse elapsed time
的时间。

如果硬解析较高，可能的原因有两个：一是存在大量没有使用绑定变量的SQL
导致重复硬解析；二是Shared_pool大小不足，导致SQL被快速置换出。

3) 如果硬解析没有占用大量的CPU时间，则需要去分析消耗大量CPU的SQL，查找
SQL的方式是查看AWR报告中SQL ordered by CPU Time列表中的内容。

3.2.4SQL分析（张）
通过SQL的执行计划分析SQL中存在的问题
SQL执行计划是基于CBO模式下，对SQL进行优化的工具，在执行计划中，能看到Oracle对SQ条件的处理方式。

要生成正确的执行计划，必须对查询的表进行统计信息的收集，否则执行计划将不准确。

下面以汇总模块网元间汇总的示例：
mergeinto INDICATOR_11547_1 tt
using(select t1.*, t2.INSTANCE_NUMBER
from(SELECT NVL(summarypath.outputmo, 'UnknowMOEntityid') moentityid,
input.startday,
input.starttime,
SUM(INDICATOR_11538_1_006) INDICATOR_11538_1_006,
SUM(INDICATOR_11538_1_004) INDICATOR_11538_1_004,
SUM(INDICATOR_11538_1_005) INDICATOR_11538_1_005,
SUM(INDICATOR_11538_1_003) INDICATOR_11538_1_003,
SUM(INDICATOR_11538_1_008) INDICATOR_11538_1_008,
SUM(INDICATOR_11538_1_001) INDICATOR_11538_1_001,
SUM(INDICATOR_11538_1_002) INDICATOR_11538_1_002,
SUM(INDICATOR_11538_1_007) INDICATOR_11538_1_007,
max(state) state
FROM(SELECT t1.moentityid,
t0.moentitycode,
t1.startday,
starttime,
1 state,
INDICATOR_11538_1_006,
INDICATOR_11538_1_001,
INDICATOR_11538_1_003,
INDICATOR_11538_1_007,
INDICATOR_11538_1_005,
INDICATOR_11538_1_008,
INDICATOR_11538_1_002,
INDICATOR_11538_1_004
FROM(select moentityid, moentitycode
from pm4h_db.qtm_modata_p) t0,
pm4h_db.indicator_11538_1 t1
WHERE t1.moentityid = t0.moentityid
AND t1.startday = 20110910
AND t1.starttime = 210000
) input,
(select moentityid, outputmo
from pm4h_db.qtm_summarypath) summarypath
WHERE input.moentityid = summarypath.moentityid(+)
GROUPBY summarypath.outputmo,
input.startday,
input.starttime) t1,
v$instance t2
where t1.state > 0) st
on(st.moentityid = tt.moentityid and st.startday = tt.startday and st.starttime = tt.starttime) whenmatchedthen
update
set tt.period = '3000',
tt.INDICATOR_11547_1_006 = st.INDICATOR_11538_1_006,
tt.INDICATOR_11547_1_001 = st.INDICATOR_11538_1_001,
tt.INDICATOR_11547_1_003 = st.INDICATOR_11538_1_003,
tt.INDICATOR_11547_1_007 = st.INDICATOR_11538_1_007,
tt.INDICATOR_11547_1_005 = st.INDICATOR_11538_1_005,
tt.INDICATOR_11547_1_008 = st.INDICATOR_11538_1_008,
tt.INDICATOR_11547_1_002 = st.INDICATOR_11538_1_002,
tt.INDICATOR_11547_1_004 = st.INDICATOR_11538_1_004 whennotmatchedthen
insert
(moentityid,
startday,
starttime,
period,
instanceid,
tt.INDICATOR_11547_1_006,
tt.INDICATOR_11547_1_001,
tt.INDICATOR_11547_1_003,
tt.INDICATOR_11547_1_007,
tt.INDICATOR_11547_1_005,
tt.INDICATOR_11547_1_008,
tt.INDICATOR_11547_1_002,
tt.INDICATOR_11547_1_004)
values
(st.moentityid,
st.startday,
st.starttime,
3000,
st.INSTANCE_NUMBER,
st.INDICATOR_11538_1_006,
st.INDICATOR_11538_1_001,
st.INDICATOR_11538_1_003,
st.INDICATOR_11538_1_007,
st.INDICATOR_11538_1_005,
st.INDICATOR_11538_1_008,
st.INDICATOR_11538_1_002,
st.INDICATOR_11538_1_004)
生成的执行计划如下：
执行计划共有6列：
Description：描述Oracle在处理SQL时的方式；
Object owner：对象的用户；
Object name：SQL处理过程中用到的对象（包含表、视图、索引等）；
Cost：处理的成本（这是调优的关键）；
Cardinality：处理返回的基数；
Bytes：处理返回结果的大小。

执行计划第一行显示Oracle处理SQL所采用的优化器，11G里默认是ALL_ROWS。

Cost显示的是处理整个SQL所花费的成本。

下面是整个SQL执行的过程，建议从里向外看，即是说整个执行计划是一个树形结构，建议从树的叶子开始看。

（1）这里叶子是QTM_SUMMARYPATH和INDICATOR_11538_1的关联，采用的是HASH JOIN连接；但是两表均采用TABLE ACCESS FULL全表扫描，没有走该表的索引，效率是非常低的，这从后面的COST里也能够看出，对于INDICATOR_11538_1这张大表，全表扫描COST为8042。

QTM_SUMMARYPATH虽然也是全表扫描，但是COST只有2，且该表是临时表，可忽略不计。

整个SQL直接计划最终COST为8049，在INDICATOR_11538_1表上进行全表扫描COST就为8042，可以看出整个SQL瓶颈就在INDICATOR_11538_1的查询上。

（2）向外一层是QTM_SUMMARYPATH和INDICATOR_11538_1关联的结果集与QTM_MODATA的关联查询，这里查询方式是NESTED LOOPS，且QTM_MODATA引用索引QTM_MODATA_P_IDX1。

采用INDEX UNIQUE SCAN是最快的扫描方式，可以看到COST 为0，几乎没有成本，所以这个查询没有问题。

（3）在向外一层是和INDICATOR_11547_1的关联，对应于SQL中的ON条件，这里也引用了INDICATOR_11547_1_G_IDX1这个索引，且采用INDEX UNIQUE SCAN扫描，成本也很低，所以这个查询没有问题。

（4）外面是一些和X$表的关联，是Oracle系统内部表的关连查询，这些就不用管了，也管不了。

解决第一个查询COST很大的问题，尝试建立索引
createindex INDICATOR_11538_1_D_IDX4 on INDICATOR_11538_1(STARTDAY,STARTTIME)
之后看执行计划：
总的COST只有157，比之前降了近80倍。

总结：
（1）对于执行计划，对于比较大的表应该尽量避免TABLE ACCESS FULL和MERGE SORT，MERGE SORT及BUFFER SORT都是在内存中完成的，如果数据量太大会占用临时大量表空间，小表可以考虑采用。

（2）大表尽量采用NESTED LOOPS，这样可以有效利用索引。

但是NESTED LOOPS也要区分，尽量采用INDEX UNIQUE SCAN（最快）和INDEX RANGE SCAN；避免INDEX FULL SCAN 和INDEX SKIP SCAN。

（3）等值连接尽量采用HASH JOIN，理论上最快。

●使用SQL Tuning工具分析SQL需要优化的内容
●使用10046事件追踪SQL执行的具体过程
Oracle 的事件很多。

10046 事件主要用来跟踪SQL语句，它并不是ORACLE 官方提供
给用户的命令，在官方文档上也找不到事件的说明信息。

但是用的却比较多，因为10046事件获取SQL的信息比SQL_TRACE 更多。

更有利于我们对SQL的判断。

10046 事件按照收集信息内容，可以分成4个级别：
Level 1：等同于SQL_TRACE 的功能
Level 4：在Level 1的基础上增加收集绑定变量的信息
Level 8：在Level 1 的基础上增加等待事件的信息
Level 12：等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。

为了使信息显示的最全，一般用以下命令：
alter session set events '10046 trace name context forever, level 8';
这条命令等于打开本回话的trace，执行完这条命令后在本回话内执行的SQL都会被记录在trace文件中。

在本回话执行完SQL后，如果想看本回话的trace，执行下面这条SQL可以找到trace文件位置。

selectdistinct t3.TRACEFILE
from v$mystat t1, v$session t2, v$process t3
where t1.SID = t2.SID
and t2.PADDR = t3.ADDR
由于trace文件不易阅读，所以找到trace后，可以用tkprof命令将trace转换一下格式。

但是trace 文件的信息量更大，如果想要深层了解Oracle对SQL的执行过程，可以看trace。

用下面命令转换trace文件
tkprof /opt/oracle/app/oracle/diag/rdbms/mos5200/mos5200/trace/mos5200_ora_5073.trc（trace文件）5073.txt （这个名字是导出文件名字，可以自己取）[sys=no]
sys=no是个可选参数，默认转换是yes但是同时会导出很多Oracle系统内部处理的一些信息，比如递归SQL等，选no就不会导出这类信息。

还有很多其他参数，如果需要可以添加。

这样就获取到了10046事件文件。

下面是截取的10046一段内容：
********************************************************************************
merge into INDICATOR_11547_1 tt
using (select t1.*, t2.INSTANCE_NUMBER
from (SELECT NVL(summarypath.outputmo, 'UnknowMOEntityid') moentityid,
input.startday,
input.starttime,
SUM(INDICATOR_11538_1_006) INDICATOR_11538_1_006,
SUM(INDICATOR_11538_1_004) INDICATOR_11538_1_004,
SUM(INDICATOR_11538_1_005) INDICATOR_11538_1_005,
SUM(INDICATOR_11538_1_003) INDICATOR_11538_1_003,
SUM(INDICATOR_11538_1_008) INDICATOR_11538_1_008,
SUM(INDICATOR_11538_1_001) INDICATOR_11538_1_001,
SUM(INDICATOR_11538_1_002) INDICATOR_11538_1_002,
SUM(INDICATOR_11538_1_007) INDICATOR_11538_1_007,
max(state) state
FROM (SELECT t1.moentityid,
t0.moentitycode,
t1.startday,
starttime,
1 state,
INDICATOR_11538_1_006,
INDICATOR_11538_1_001,
INDICATOR_11538_1_003,
INDICATOR_11538_1_007,
INDICATOR_11538_1_005,
INDICATOR_11538_1_008,
INDICATOR_11538_1_002,
INDICATOR_11538_1_004
FROM (select moentityid, moentitycode
from pm4h_db.qtm_modata) t0,
pm4h_db.indicator_11538_1 t1
WHERE t0.moentityid = t1.moentityid
AND t1.startday >= 20110910
AND t1.startday <= 20110910
AND t1.starttime >= 210000
AND t1.starttime < 212959) input,
(select moentityid, outputmo
from pm4h_db.qtm_summarypath) summarypath
WHERE input.moentityid = summarypath.moentityid(+)
GROUP BY summarypath.outputmo,
input.startday,
input.starttime) t1,
v$instance t2
where t1.state > 0) st
on (st.moentityid = tt.moentityid and st.startday = tt.startday and st.starttime = tt.starttime) when matched then
update
set tt.period = '3000',
tt.INDICATOR_11547_1_006 = st.INDICATOR_11538_1_006,
tt.INDICATOR_11547_1_001 = st.INDICATOR_11538_1_001,
tt.INDICATOR_11547_1_003 = st.INDICATOR_11538_1_003,
tt.INDICATOR_11547_1_007 = st.INDICATOR_11538_1_007,
tt.INDICATOR_11547_1_005 = st.INDICATOR_11538_1_005,
tt.INDICATOR_11547_1_008 = st.INDICATOR_11538_1_008,
tt.INDICATOR_11547_1_002 = st.INDICATOR_11538_1_002,
tt.INDICATOR_11547_1_004 = st.INDICATOR_11538_1_004
when not matched then
insert
(moentityid,
startday,
starttime,
period,
instanceid,
tt.INDICATOR_11547_1_006,
tt.INDICATOR_11547_1_001,
tt.INDICATOR_11547_1_003,
tt.INDICATOR_11547_1_007,
tt.INDICATOR_11547_1_005,
tt.INDICATOR_11547_1_008,
tt.INDICATOR_11547_1_002,
tt.INDICATOR_11547_1_004)
values
(st.moentityid,
st.startday,
st.starttime,
3000,
st.INSTANCE_NUMBER,
st.INDICATOR_11538_1_006,
st.INDICATOR_11538_1_001,
st.INDICATOR_11538_1_003,
st.INDICATOR_11538_1_007,
st.INDICATOR_11538_1_005,
st.INDICATOR_11538_1_008,
st.INDICATOR_11538_1_002,
st.INDICATOR_11538_1_004)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.48 1.24 1 12 4 0 Execute 1 9.98 45.11 29224 30167 6 4 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 10.47 46.35 29225 30179 10 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83
Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE INDICATOR_11547_1 (cr=30167 pr=29224 pw=29224 time=6 us)
4 VIEW (cr=30167 pr=29224 pw=29224 time=142 us)
4 MERGE JOIN CARTESIAN (cr=30167 pr=29224 pw=29224 time=139 us cost=8057 size=292700 card=100)
4 NESTED LOOPS OUTER (cr=30167 pr=29224 pw=29224 time=120 us cost=8057 size=2927 card=1)
4 MERGE JOIN CARTESIAN (cr=30157 pr=29221 pw=29221 time=8 us cost=8056 size=24
5 card=1)
1 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=0 size=60 card=1)
1 FIXED TABLE FULL X$KSUXSINST (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=34 card=1)
1 FIXED TABLE FULL X$KVIT (cr=0 pr=0 pw=0 time=0 us cost=0 size=34 card=1)
4 BUFFER SORT (cr=30157 pr=29221 pw=29221 time=4 us cost=8056 size=18
5 card=1)
4 VIEW (cr=30157 pr=29221 pw=29221 time=7 us cost=8056 size=18
5 card=1)
4 FILTER (cr=30157 pr=29221 pw=29221 time=4 us)
4 SORT GROUP BY (cr=30157 pr=29221 pw=29221 time=3 us cost=8056 size=22
5 card=1)
9526 NESTED LOOPS (cr=30157 pr=29221 pw=29221 time=4588 us cost=8055 size=2451375 card=10895)
9526 HASH JOIN OUTER (cr=29457 pr=29221 pw=29221 time=2090 us cost=8053 size=1884662 card=10894)
9526 TABLE ACCESS FULL INDICATOR_11538_1 (cr=29238 pr=29221 pw=29221 time=161 us cost=8044 size=751686 card=10894)
10172 INDEX FAST FULL SCAN QTM_SUMMARYPATH_IDX1 (cr=219 pr=0 pw=0 time=99 us cost=9 size=1022840 card=9835)(object id 70095)
9526 INDEX UNIQUE SCAN QTM_MODATA_IDX1 (cr=700 pr=0 pw=0 time=0 us cost=0 size=52 card=1)(object id 70090)
4 TABLE ACCESS BY INDEX ROWID INDICATOR_11547_1 (cr=10 pr=3 pw=3 time=0 us cost=1 size=2682 card=1)
4 INDEX UNIQUE SCAN INDICATOR_11547_1_G_IDX1 (cr=6 pr=2 pw=2 time=0 us cost=0 size=0 card=1)(object id 75290)
4 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=8056 size=0 card=100)
1 FIXED TABLE FULL X$QUIESCE (cr=0 pr=0 pw=0 time=0 us cost=0 size=0。