实践Oracle 性能调优-从诊断到解决

 

本文永久地址: https://www.askmac.cn/archives/实践oracle-性能调优-从诊断到解决.html

前言

  • 本资料中,包含oracle数据库的系统中产生性能问题时,将对调查原因到完成调优为止,进行指导。
    • 本资料面向对数据库具有基相关知识的人士。
    • 因此,发生性能问题时的对策,仅仅讲解确认数据库是否有性能问题的方法,最多讲解处理性能问题的1个方法,但这也不是绝对的
  • 请作为系统发生性能问题时的参考资料来灵活使用

发生了系统性能问题

发生了系统性能问题

 

解决的顺序

为了解决问题,需要处理与考虑方法

  1. 指定出现性能问题的地方
  2. 执行调优

性能问题解决步骤

确认DB性能问题

获得DB内部的统计信息,确认其是否发生了性能问题

DB内部的统计信息是指

性能统计信息(ex. 资源使用情况以及发生待机的信息)

执行各SQL时的详细信息

<方法>

Statspack(8i~)

AWR报告(10g~)

※Enterprise Edition中需要Diagnostic Pack选项

SQL 追踪(主要在测试环境中使用)

 

<参考>其他的推荐事先获得的信息

OS统计信息

  -CPU统计信息

-磁盘统计信息

-内存统计信息

-网络统计信息

-运行进程统计信息

OS中的统计信息因为含有包含oracle以外的部分的服务器整体的资源信息

所以即使原因可能出在DB中,也可以获得

性能参考体系

分析需要了解Statspack是什么

在使用DB内部的统计信息进行分析之前,先需要获得统计信息

理解各个方法的概要

<方法>

Statspack(8i~)

AWR报告(10g~)

※Enterprise Edition中需要Diagnostic Pack选项

SQL追踪(主要在测试环境中使用)

 

Statspack(Statistics Package)

Statspack 的意思是

从Oracle 8i 安装的oracle标准的性能分析工具

使用Statspack 的话

某段期间内,还可以确认oracle的运行情况

基本的Statspack使用流程

  1. 安装之后,可以定期获得统计信息
  2. 发生问题时,会将累积的统计信息生成报告
  3. 比较现有的报告(必要的话还可以同时比较过去的报告)进行分析

 

确认Statspack 的的信息,确认在执行SQL时,到底是什么原因导致了性能恶化

 

Statspack机制

Statspack是两个不同时点分别作为snapshot来记录,仅仅从差异中输出报告

statspack

灵活地输出报告

输出报告时,可以指定使用任何snapshot,可以灵活地变更输出范围

statspack1

参考>通过Statspack获得的信息(9iR2,10g,11g)

leve水平(获得的信息详细程度)设定参考基准

一般正常运行时默认为Level 5,如果感觉到“最近变得有点慢了”的时候,可以遵循oracle技术支持的意见调整为Level 6Level 7、或者Level 10(高负荷)

 

Snapshot level 收集数据
基本统计信息 建议信息 SQL统计信息 SQL详细信息 段信息 latch详细信息
Level 0
Level 5
Level 6
Level 7
Level 10

 

Statspack 使用顺序

Statspack的使用顺序如下所示

Step1 安装・设定

首次使用Statspack时,可以创建执行用的用户,同时可以设定密码与表空间

Step2  获得snapshot

根据情况不同自动获得snapshot level,或者调整snapshot level

Step3 创建报告

选择开始与终止的snapshot 输入文件名

使用了Statspack的脚本已经在导入了DB的环境中准备好了,只要连接到DB,就可以执行脚本

 

执行安装脚本

Statspack相关的操作全部都是通过执行用用户(PERFSTAT)来执行的,所以需要创建执行安装脚本的PERFSTAT用户

1.通过SQL*Plus,作为拥有SYSDBA权限的用户来连接DB

2.执行安装脚本(spcreate.sql)

# 以SYSDBA权限连接到SQL*Plus

SQL> connect sqlplus / as sysdba

# 执行安装・脚本

SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql

 

设定密码与表空间

设定PERFSTAT 用户的密码、在Statspack中所使用的默认表空间以及临时表空间

1.执行脚本,PERFSTAT用户的密码,默认表空间,临时表空间

# 设定执行用户的密码

在perfstat_password中输入值:输入password

# 设定默认表空间

在default_tablespace中输入值:输入表空间名

# 设定临时表空间

在temporary_tablespace中输入值:输入表空间名

默认表空间推荐多设定300MB的冗余

 

获得snapshot

用PERFSTAT用户连接到DB,通过snapshot来获得统计信息

1.通过PERDSTAT用户执行statspack.snap

2.必要的话可以通过i_snap_level => 来设定获得信息的详细程度(level)(用户没有指定level时,以默认值的level5来获得)

#获得 snapshot

SQL> execute statspack.snap(I_snap_level => 6);

l记录重复snapshot,通过在某个时间点的snapshot,作为报告来输出,可以获得特定期间内的DB统计信息

 

报告的创建

1.通过PERDSTAT用户来执行spreport.sql

SQL> @?/rdbms/admin/spreport.sql  # 创建报告脚本を执行

    ~省略~                                    Snap

Instance   DB Name   Snap Id    Snap Started               Level   Comment

————  —————  ———–  ——————————  ———  —————

orcl1         ORCL                  1    17 10月 2008 12:17            5

                                            11    20 10月 2008 09:29             5

Specify the Begin and End Snapshot Ids

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

在begin_snap中输入值:

 

输入snapshot ID

指定开始、终止获得统计信息范围的时间点的snapshot ID,输出报告

1.参考Snap Id,输入起始snapshot以及终止snapshot ID

2.输入报告名

Specify the Begin and End Snapshot Ids

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

# 输入起始snapshot ID

请在begin_snap中输入值: 输入Snap Id 例:1

# 输入终止snapshot ID

请在end_snap中输入值: 输入Snap Id 例:11

#输入 报告名

请在report_name中输入值: 输入报告名>

删除snapshot

获得的性能统计信息只要不明确地删除,无论何时都会被保留,根据不同情况可能会挤压储存区域

因此,请删除不需要的统计信息

1.通过PERFSTAT用户来执行sppurge.sql

2.指定想删除的范围的起始于终止时点,指定snapshot ID

SQL> @?/rdbms/admin/sppurge.sql   #执行删除的脚本

Specify the Lo Snap Id and Hi Snap Id range to purge

 

Statspack 实际的报告输入例

 

 

STATSPACK report for

Database      DB Id    Instance  Inst Num         Startup Time      Release      RAC
~~~~~~~~  -------------- ------------ -------------   -----------------------    --------------
           1196126054    orcl1              1   17-10月-08 11:40    11.1.0.7.0    YES
Host         Name              Platform                CPUs   Cores   Sockets Memory (G)
~~~~ -------------------------  ---------------------- ----- -----  ---------  ------------ -----------------
     jpintl005.jp.ora   Linux IA (32-bit)          8          8             2          4.0
Snapshot       Snap Id     Snap Time                  Sessions    Curs/Sess Comment
~~~~~~~~    ---------- ---------------------------------- -------------   --------- ------------------
Begin Snap:         36    21-10月-08 19:46:15           38            1.0
   End Snap:         37    21-10月-08 19:47:51           38            1.0
      Elapsed:                1.60 (mins) Av Act Sess:       0.4 
・			
・			
・			


		・
		・
		・
SQL ordered by Elapsed time for DB: ORCL  Instance: orcl1  Snaps: 36 -37
-> Total DB Time (s):              42
-> Captured SQL accounts for  195.5% of Total DB Time
-> SQL reported below exceeded  1.0% of Total DB Time
  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
     40.69            1      40.69   95.9      40.67               0 3417014992
Module: SQL*Plus
declare   v_num number; begin   for i in 1..10000 loop     selec
t c1 into v_num from t1 where c1 = 100;   end loop; end;
     40.48       10,000       0.00   95.4      40.46               0 2968801179
Module: SQL*Plus

AWR报告是指什么

AWR自动负载库

从Oracle 10g开始会自动获得DB内部的统计信息(snapshot)

AWR是使得Statspack进化的功能

  • 与Statspack相同,可以经常观察到以低负荷来创建各种报告
  • 通过Enterprise Manager(EM),可以变更、查看设定内容
  • 可以通过ADDM来自动进行性能减少/诊断

oracle-awr-1

 

AWR在创建DB时,也会在SYSAUX表空间中创建                      -不需要安装

MMON(Memory Monitor)会定期获得SGA的信息

-默认1小时内保存1次snapshot(11g中是8天)

-想查看、变更设定内容的话,需要Oracle EM可以使用

执行自动删除旧数据

oracle-awr-2

比较StatspackAWR的报告

statspackvsawr

 

Statspack 报告的分析

分析点

  • 吞吐量与负荷(Load Profile)
  • 实例效率(Instance Efficiency)
  • 待机 项目(Top-5 Timed Events)
  • SQL的详细信息(SQL ordered by ~)

特别是在正式环境中使用时,根据业务内容与时间段不同,所收集到的统计信息也会不同,所以我们推荐多多比较多个报告

Load Profile 负荷状況的读取

伴随着磁盘访问,我们需要着眼于是否会有数据的读入、写入,在DB的处理中,找出容易成为瓶颈的物理I/O

awr-load-profile

Instance Efficiency 实例效率.

确认DB缓冲区高速缓存命中率,确认实例是否高效运行

越接近100%就越好
参考値大于80%-% Non-Parse CPU
90%以上-Buffer Hit%、Optimal W/A Exec%、Sort Parse%
95%以上-Library Hit%、Redo Nowait%、Buffer Nowait%
100%以上-Latch Hit%

awr-instance-efficiency

 

参数名 説明
Buffer Hit% 高速缓存中所拥有的需要的数据的比例
Library Hit % Library cache中所拥有的需要的数据的比例
Soft Parse % 所有解析中可以重复利用的比例
In-Memory Sort% Sort在内存中执行的比例
Latch Hit% 所有latch的命中率
Parse CPU to Parse Elapsed % 解析CPU时间/ 解析的总计时间
Execute to Parse% 因为执行SQL而没执行解析的比例
%non-parse CPU 解析之外所使用的CPU时间的比例
Buffer Nowait% 高速缓冲区中提出需求时,可以马上应用的比例
Redo Nowait% 对redo log提出需求时,可以马上应用的比例

 

Top-5 Timed Events 待机 项目

检查实例level中待机时间前五的项目,确认监视对象的实例性能是否降低

Waits :项目待机的总次数

Time(s) :项目总计待机时间以及总计CPU时间(秒)

Avg wait(ms) : 项目平均待机时间

% Total Call Time: time for each timed event / total call time                                                 Total call time total CPU time + total wait time for non-idle events

 

awr-top5-timed-event

等待事件是什么?

  • 进程没有使用CPU的时间
  • 理想待机 项目(SQL的需求等待)
    • 有瓶颈时,意味着原因并不是DB资源
  • 其他待机 项目(SQL执行中)
    • DB资源(缓冲区竞争、I/O竞争、latch竞争等等)相关的待机时间

 

通过对SQL执行中的待机 项目与CPU使用时间进行调优 可以加快响应时间

awr-wait-event

SQL ordered by ~ SQL的处理信息

确认各SQL读入的缓冲区数以及磁盘的访问次数,确认是否使用了资源使用量较高的SQL

 

awr-sql-order

 

SQL trace是什么?

使用SQL trace的话就可以获得比执行SQL执行时更详细的信息通过分析刚刚获得的信息,就可以指定出现问题的SQL

 

SQL trace的获得方法如下所示
1. 获得所有会话信息的方法
2. 获得指定的会话信息的方法

获得的信息中,各个SQL包含以下信息

  1. 语句的分析、执行,fetch的执行次数
  2. CPU时间、消耗时间
  3. 物理读入(Physical read)、理论读入(Logical read)
  4. 处理的行数

 

SQL trace使用顺序

获得所有会话信息时

1. 设定初始化参数SQL_TRACE为TRUE

2. 重启Oracle
重启之后需要重新设定为FALSE,在重启期间,每次开始会话就都会创建追踪文件,就会输出各个会话的所有信息

仅仅获得指定应用的追踪方法

对应用中想开始SQL trace的point追加以下SQL语句

ALTER SESSION SET SQL_TRACE=TRUE;

这时,获得 trace期间,可以设定终止应用或者将SQL_TRACE设定为 FALSE

 

通过TKPROF进行的格式化

lSQL trace的输出结果通过使用TKPROF,可以以更加简明的形式来进行格式化

<UNIX/Linux环境中执行TKPROF的例>

TKPROF命令中orcl1_ora_12059.trc 这个trace文件,对         tkprof_1.prf进行格式化

$ tkprof orcl1_ora_12059.trc tkprof_1.prf

※ TKPROF的执行Module名由于环境不同而不同,具体请参考手册

 

TKPROF的输出例

tkprof

 

弄清楚是应用程序的问题还是网络问题

判断DB中的性能问题时需要调查到底是AP问题还是网络问题

Net 的 trace文件:确认通信(packet)的处理信息

 

 

Net trace文件是指什么?

每个网络组成(listener・客户端・服务器等等)都可以在信息交换是,生成 trace信息

获得网络通信时的 trace信息,指定获得的信息交换中可能产生性能问题的地方

※listener与服务器的 trace每次连接与启动时,都会创建得比客户端量要更多的

首先我们推荐检查客户端(可能的话请检查服务器)的 trace

 

net trace

 使用trace时需要考虑的问题

1.需要充分确保磁盘空间

trace以及日志可能使用大量磁盘空间

另外,通常使得设定无效。或者定期删除日志文件,尽量不对进程增加负荷

2.输出地址文件的位置 我们不推荐所有用户来写入的swap space

3.输出地址是否具有对目录的写入权限 连接用户、oracle软件拥有者、listener启动用户来写入

注意:  trace功能中,因为使用了大量磁盘区域,可能造成系统 性能大幅降低。所以只有在必要时才能使用trace

 

客户端中的 trace设定方法

<设定方法>

l通过客户端系统的sqlnet.ora文件来设定以下参数

TRACE_LEVEL_CLIENT

TRACE_DIRECTORY_CLIENT

TRACE_UNIQUE_CLIENT

8.0.6 以后会记录以下参数

TRACE_TIMESTAMP_CLIENT

通过上述设定之后,客户端每次新建连接时都会在指定目录中生成trace文件

XXX.trc 这个文件,只要在 trace子目录中输出了的话就成功了

 

 

获得客户端的 trace相关的注意事项

<获得方法>

通过Oracle Net Services 11g (11.1.0) 将默认的 trace输出地址设定为Automatic Diagnostic Repository (ADR)

为了在通过TRACE_DIRECTORY_CLIENT指定的目录中输出,可以指定以下参数

默认为$ORACLE_HOME/log/diag

 

sqlnet.ora

DIAG_ADR_ENABLED=OFF

<参考>服务器的 trace也与客户端的情况相同                       listener的 trace的情况如下所示

listener.ora

DIAG_ADR_ENABLED_<listener名>=OFF

 

tracelevel

设定通过TRACE_LEVEL_CLIENT获得的信息

正常运行时,一般为0或者OFF(默认,参考下列表,根据需求变更level

 trace level 収集数据
0或者OFF  没有trace的输出
4或者USER 用户・ trace信息
10或者ADMIN 管理 trace信息
16或者SUPPORT Cluster 支持 trace信息

 

客户端的 trace文件获得例

XXX.trc 这个文件是在trace子目录中输出了的话就成功

[21-JUN-2006 10:58:29:081] nscon: doing connect handshake…

[21-JUN-2006 10:58:29:081] nscon: sending NSPTCN packet

[21-JUN-2006 10:58:29:081] nspsend: entry

[21-JUN-2006 10:58:29:081] nspsend: plen=242, type=1

[21-JUN-2006 10:58:29:081] nttwr: entry

[21-JUN-2006 10:58:29:081] nttwr: socket 288 had bytes written=242

[21-JUN-2006 10:58:29:081] nttwr: exit

[21-JUN-2006 10:58:29:081] nspsend: 242 bytes to transport

[21-JUN-2006 10:58:29:081] nspsend: packet dump

[21-JUN-2006 10:58:30:081] nsprecv: 00 CD 00 00 06 00 00 00 |……..|

[21-JUN-2006 10:58:30:081] nsprecv: 00 00 08 00 01 00 00 00 |……..|

[21-JUN-2006 10:58:30:082] nsprecv: 0C 0C 41 55 54 48 5F 53 |..AUTH_S|

[21-JUN-2006 10:58:30:082] nsprecv: 45 53 53 4B 45 59 00 00 |ESSKEY..|

[21-JUN-2006 10:58:30:082] nsprecv: 32 37 46 32 45 38 39 36 |27F2E896|

 

分析的基本思路

[21-JUN-2006 10:58:30:081] nsprecv: 00 CD 00 00 06 00 00 00 |……..|

分析数据・packet的收发信息所花费的时间

packet-sqlnet

 

性能问题的区分①应用程序的问题例

客户端中的nsprecv(收信息)到nspsend(发信息)所花费的时间

sqlnet-performance-issue

 

性能问题的区分DB中的问题例

客户端中的nspsend(发信息)到nsprecv(收信息)所花费的时间

sqlnet-performance-issue2

 

性能问题的区分例③N/W的问题例

客户端中的nspsend(发信息)到nsprecv(收信息)为止,似乎没有等待时机,考虑到处理内容,收发信息的时间也花费太多了。

sqlnet-performance-issue3

 

数据库调优

参考分析中使用过的信息,执行合适的调优

  • SQL调优
    • 索引
    • 提示HINT
  • 使用ADDM的调优
  • 使用各个建议的调优
  • 确认效果
    • 确认使用了AUTOTRACE的调优效果

 

索引调优 例

Statspack的SQL ordered by Gets的确认

sql-index-tuning

索引的创建与删除

索引的创建

SQL> create index c1_index on t1(c1);

索引的删除

SQL> drop index c1_index;

即使建索引了,也会在下列情况中进行全表扫描,请大家注意

  • 搜索NULL值
  • 计算索引列
  • 隐秘地变更数据类型
  • 使用LIKE语句的中间一致、后方一致搜索
  • 使用!=和<>

索引的效果

sql-index-tuning1

 

提示HINT的使用

  • 通过使用提示HINT,就会用到指定的访问路径,可以在优化中进行指定

      <例>

  • 指定使用合适的索引(Index)
  • 指定合适的表结合方法以及结合顺序
  • 指定CBO的目标(重视吞吐量或者面向 OLTP等) etc.
  • 提示HINT的使用例 ( 在/*+ 与 */之间指定提示HINT、直接嵌入到SQL中 )

(例)就会使用表t1的c1列中附加的c1_indx这个Index

SQL>select /*+INDEX(t1 c1_index) */ * from where c1=100;

ADDM 自动数据库检测监视器
(Automatic Database Diagnostics Monitor)

  • 基于AWR中所示收集的统计信息,定期监视、诊断性能的,面向DB管理者(DBA)的功能

addm1

帮助功能

addm2

SQL调优・帮助

  • 诊断因为高负荷而发生问题的SQL语句以及执行计划
  • 以诊断为基础给予建议

sql-tuning-2

 

调优的流程

 

tuning-oracle

确认调优效果

随着SQL语句变得复杂,调优方法也越来越多,可能为了达到目标效果需要

反复调优,如果使用SQL*Plus的AUTOTRACE功能的话就能当即确认效果了

l要使用AUTOTRACE的话需要确认一下几条项目

  • lSQL*Plus中执行的SQL语句
  • 执行计划的结果
  • 统计信息

-物理读入  -論理读入

-REDO尺寸  -内存sort 尺寸

-处理件数  -磁盘sort 尺寸

 

AUTOTRACE功能的设定顺序

lAUTOTRACE的设定顺序如下所示

Step1 创建PLUSTRACE 角色(仅限首次)

SQL> connect sqlplus / as sysdba

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

 

Step2  对使用用户赋予已创建的PLUSTRACE角色

SQL> grant plustrace to 用户名>

Step3 通过使用用户创建AUTOTRACE功能将要使用的工作用的表

SQL> @$ORACLE_HOME/sqlplus/admin/utlxplan.sql

每个想使用AUTOTRACE功能的用户都需要重复Step2与Step3

 

AUTOTRACE的获得

1.通过使用用户执行SET AUTOTRACE命令

SQL> set autotrace on

2.SQL执行

SQL> select * from t1 where c1 = 100;

※想终止AUTOTRACE功能时

SQL> set autotrace off

 

AUTOTRACE的获得的例子

 

oracle-autotrace

 

总结

 

tuning-summary

 

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号