Oracle性能：找出性能问题二

1、性能测量和剖析分析

要收集性能问题的信息，基本有如下两种方法。

性能测量（instrumentation）：在应用程序已经开发就绪后，要随着其他功能一起提供性能测量数据。关于性能测量的一个例子就是Oracle的SQL跟踪功能，默认情况下是不激活的，一旦激活，跟踪文件中将包含关于所执行SQL语句的详细信息。

剖析分析（profiling analysis）：所谓剖析工具（profiler）就是一种性能分析工具，针对运行中的应用程序，能够记录其执行的操作，运行耗去的时间以及使用的系统资源，如CPU，内存。有些剖析工具在调用层收集数据，有的在代码层进行收集。

性能测量和剖析分析的优缺点：

剖析工具提供在调用层或者代码层的信息；性能测量重点关注业务相关的操作或者组件层之间的交互，提供主要步骤的执行响应时间。

2、性能测量

简单的讲，执行性能测量代码是用来获取应用程序行为的。要识别性能问题，我们应该关注运行了哪些操作、运行顺序、处理的数据量、执行次数、执行的时间，特殊情况下还要知道使用了多少资源。

a、应用程序代码：在方法开始的时候得到一个时间戳，在方法结束的时候再得到一个时间戳。

b、数据库调用：数据库并不知道应用程序代码的哪一部分会通过某会话执行SQL语句；应用程序通过技术用户开辟的连接池连接到数据库而没有利用代理用户，最终用户的验证通常是通过应用程序自身来完成的，因此，数据库会忽略使用该会话的用户。处于这个原因，数据库引擎为每一个数据库会话动态关联了如下的属性：

客户端标记（client identifier）：用以标记客户端64字符的字符串，尽管并不明确。

客户端信息（client information）：用以描述客户端64位字符的字符串。

模块名称（module name）：描述当时使用该会话的模块的48字符的字符串。

操作名称（action name）：32字符的字符串，用以描述当前处理的操作。

这些值可以通过视图v$session获得。v$sql中也包含模块（module）与操作（action）名称的列。

3、剖析应用程序代码

性能剖析工具应该提供如下的核心功能：

支持分布式应用程序的能力。

具备在调用层和代码行级别进行剖析分析的能力。

具备针对部分应用程序代码或是特定请求来选择性地激活或者关闭剖析工具的能力。

4、跟踪数据库调用

跟踪数据库调用的基本步骤如下：

SQL跟踪：

SQL跟踪的目的有两方面：提供信息以在服务时间和等待时间之间分解响应时间；给出所用资源与同步点的详细信息。

Oracle数据库SQL跟踪的几种方法：

1. 使用 AUTOTRACE 查看执行计划

    set autotrace ON | ON EXPLAIN | ONSTATISTICS | TRACEONLY | TRACEONLY EXPLAIN

    set autotrace OFF

SET AUTOTRACE OFF ： 不生成AUTOTRACE报告，这是缺省模式

SET AUTOTRACE ON EXPLAIN ：AUTOTRACE只显示优化器执行路径报告

SET AUTOTRACE ON STATISTICS ： 只显示执行统计信息

SET AUTOTRACE ON ： 包含执行计划和统计信息

SET AUTOTRACE TRACEONLY ： 同setautotrace on，但是不显示查询输出

例如：SQL> set time on

16:49:12 SQL> set timing on

16:49:19 SQL> SET AUTOTRACE ON

16:49:33 SQL> select * frommicky where rownum<2;

。。。。

已用时间:  00: 00: 00.03

执行计划

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

Plan hash value: 4001097626

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

| Id  | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |   207 |    3  (34)| 00:00:01 |

|*  1 | COUNT STOPKEY     |       |      |       |            |          |

|   2 |  TABLE ACCESS FULL| MICKY | 76428 |   15M|     3  (34)| 00:00:01 |

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

Predicate Information (identifiedby operation id):

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

  1 - filter(ROWNUM<2)

Note

-----

  - dynamic sampling used for this statement (level=2)

统计信息

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

          7 recursive calls

          0 db block gets

        128 consistent gets

          0 physical reads

          0 redo size

       1609 bytes sent via SQL*Net to client

        520 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

16:49:53 SQL>

2. 启用 sql_trace 跟踪当前session

开启会话跟踪：alter session set sql_trace=true;

关闭会话跟踪：alter session set sql_trace=false;

例如：SQL> show parameter iden

NAME                                 TYPE        VALUE

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

tracefile_identifier                 string

SQL> alter session settracefile_identifier='micky';

会话已更改。

SQL> alter session setsql_trace=true;

会话已更改。

SQL> select * from micky whererownum<2;

。。。。

SQL> alter session setsql_trace=false;

会话已更改。

生成的trace文件为：

D:\app\Micky\diag\rdbms\micky\micky\trace>tkprofmicky_ora_366860_micky.trc mick

y_ora_366860_micky.txtsort=exeela

TKPROF: Release 11.2.0.4.0 -Development on 星期日 8月 26 16:58:09 2018

Copyright (c) 1982, 2011, Oracleand/or its affiliates.  All rightsreserved.

生成了格式化文件：

具体内容为：

3. 启用 10046 事件跟踪当前 session

开启会话跟踪：alter session setevents '10046 trace name context forever, level 12';

关闭会话跟踪：alter session setevents '10046 trace name context off';

对跟踪文件加标识：alter session settracefile_identifier='micky';

其他和sql_trace差不多。

10046事件是扩展的SQL跟踪，提供以下级别：

0：禁止调试事件。

1：调试事件是激活的。针对每个被处理的数据库调用，给定如下信息：SQL语句、响应时间、服务时间、处理的行数、逻辑读数量、物理读和写的数量、执行计划以及一些额外信息。

4：如同级别1，包括绑定变量的额外信息。主要是数据类型、精度以及每次执行时所用的值。

8：如同级别1，加上关于等待时间的细节信息。为了处理过程中的每个等待，提供如下信息：等待事件的名称、持续时间，以及一些额外的参数，可标明所等待的资源。

12：同时启动4和8.

4. 使用 Oracle 系统包DBMS_SYSTEM.SET_EV 跟踪指定 session

参数说明：

SI-指定SESSION的SID;

SE-指定SESSION的SE;

EV-事件ID(如：10046);

LE-表示TRACE的级别;

NM-指定SESSION的username;

SQL> select userenv('sid') sidfrom dual;

       SID

----------

       129

SQL> select sid, serial#,username from v$session where sid=129;

       SID   SERIAL# USERNAME

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

       129        133 SYS

开启会话跟踪：SQL> exec dbms_system.set_ev(129, 133,10046,12,'');

获取跟踪文件：

select pr.value || '\' ||i.instance_name || '_ora_' || to_char(ps.spid)

|| '.trc' "trace filename" from v$session s, v$process ps, v$parameter pr, v$instance i

where s.paddr = ps.addr and s.sid= userenv('sid') and pr.name = 'user_dump_dest';

关闭会话跟踪：SQL> exec dbms_system.set_ev(129, 133,10046,0,'');

完整示例如下：

SQL> select userenv('sid') sidfrom dual;

       SID

----------

       129

SQL> select sid, serial#,username from v$session where sid=129;

       SID   SERIAL# USERNAME

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

       129        133 SYS

SQL> execdbms_system.set_ev(129, 133,10046,12,'');

PL/SQL 过程已成功完成。

SQL> select * from micky whererownum<2;

。。。。

SQL>  select pr.value || '\' || i.instance_name ||'_ora_' || to_char(ps.spid)

 2  || '.trc' "trace filename" from v$session s, v$process ps, v$parameter pr,

 v$instance i

 3  where s.paddr = ps.addr ands.sid = userenv('sid') and pr.name = 'user_dump

_dest';

trace file name

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

D:\app\Micky\diag\rdbms\micky\micky\trace\micky_ora_381624.trc

SQL> execdbms_system.set_ev(129, 133,10046,0,'');

PL/SQL 过程已成功完成。

其他可以参看sql_trace。

5. 使用 Oracle 系统包 dbms_monitor激活SQL跟踪

bms_monitor方法提供了4种开启/关闭trace文件的功能.

SQL> desc dbms_monitor;

Element                     Type     

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

ALL_MODULES                 CONSTANT 

ALL_ACTIONS                 CONSTANT 

CLIENT_ID_STAT_ENABLE       PROCEDURE

CLIENT_ID_STAT_DISABLE      PROCEDURE

SERV_MOD_ACT_STAT_ENABLE    PROCEDURE

SERV_MOD_ACT_STAT_DISABLE   PROCEDURE

CLIENT_ID_TRACE_ENABLE      PROCEDURE

CLIENT_ID_TRACE_DISABLE     PROCEDURE

SERV_MOD_ACT_TRACE_ENABLE   PROCEDURE

SERV_MOD_ACT_TRACE_DISABLE  PROCEDURE

SESSION_TRACE_ENABLE        PROCEDURE

SESSION_TRACE_DISABLE       PROCEDURE

DATABASE_TRACE_ENABLE       PROCEDURE

DATABASE_TRACE_DISABLE      PROCEDURE

4种方法开启/关闭trace文件：

1)SESSION_TRACE_ENABLE/SESSION_TRACE_DISABLE:

通过指定会话的SID和SERIAL#开启/关闭trace，实现异步跟踪；

语法：

dbms_monitor.session_trace_enable(sid,serial#,waits,binds);

示例：

SQL> exec dbms_monitor.session_trace_enable(129,133,TRUE,FALSE);

PL/SQL 过程已成功完成。

SQL> execdbms_monitor.SESSION_TRACE_DISABLE(129, 133);

PL/SQL 过程已成功完成。

根据sid获取trace文件路径：

SQL> SELECT p.tracefile

  2   FROM v$process p, v$session s

  3  WHERE p.addr = s.paddr

  4  AND s.sid = 129;

TRACEFILE

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

D:\APP\MICKY\diag\rdbms\micky\micky\trace\micky_ora_381624.trc

2)DATABASE_TRACE_ENABLE/DATABASE_TRACE_DISABLE过程:

设置数据库实例上所有会话的trace 开启/关闭，不建议这样做；

语法：

dbms_monitor.database_trace_enable(waits,binds,instance_name)

示例：

SQL> EXECdbms_monitor.database_trace_enable(waits => true,binds => true,instanc

e_name => NULL);

PL/SQL 过程已成功完成。

SQL> select instance_name,waits, binds

 2    from dba_enabled_traces

 3   where TRACE_type = 'DATABASE';

INSTANCE_NAME    WAITS BINDS

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

                 TRUE  TRUE

SQL> EXECdbms_monitor.database_trace_disable(instance_name => NULL);

PL/SQL 过程已成功完成。

获取trace文件路径：

SQL> select value from v$diag_infowhere name='Default Trace File';

VALUE

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

D:\APP\MICKY\diag\rdbms\micky\micky\trace\micky_ora_381624.trc

3)CLIENT_ID_TRACE_ENABLE/CLIENT_ID_TRACE_DISABLE:

通过客户端ID开启/关闭指定客户端的TRACE，如果会话客户端ID为空，可以通过程序设置：DBMS_SESSION.SET_IDENTIFIER('YYC_TEST');

语法：

dbms_monitor.client_id_trace_enable(client_id,waits,binds)

示例：

SQL> EXECDBMS_monitor.client_id_trace_enable(client_id => 'Micky_test',waits =>true,binds => false);

PL/SQL procedure successfullycompleted

SQL> select primary_id asclient_id, waits, binds

 2    from dba_enabled_traces

 3   where TRACE_type ='CLIENT_ID';

CLIENT_ID                                                       WAITS BINDS

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

Micky_test                                                      TRUE  FALSE

SQL> EXECDBMS_monitor.client_id_trace_disable(client_id => 'Micky_test');

PL/SQL procedure successfullycompleted

获取trace文件路径：

SQL> select value fromv$diag_info where name='Default Trace File';

VALUE

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

D:\APP\MICKY\diag\rdbms\micky\micky\trace\micky_ora_381624.trc

4)SERV_MOD_ACT_TRACE_ENABLE/SERV_MOD_ACT_TRACE_DISABLE:

通过SERVICE_NAME, MODULE_NAME和ACTION_NAME开启/关闭trace；

语法：

dbms_monitor.client_id_trace_enable(client_id,waits,binds)

示例：

SQL> execdbms_monitor.serv_mod_act_trace_enable(service_name => 'micky',module_name=> 'mymodule',action_name => 'myaction',waits => true,binds =>false,instance_name => NULL);

PL/SQL procedure successfullycompleted

SQL> select et.primary_id asservice_name,et.qualifier_id1 as module_name,et.qualifier_id2 asaction_name,et.waits,et.binds

 2    from dba_enabled_traces et

 3   where TRACE_type ='SERVICE_MODULE_ACTION';

SERVICE_NAME  MODULE_NAME ACTION_NAME   WAITS BINDS

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

micky         mymodule     myaction      TRUE FALSE

SQL> execdbms_monitor.serv_mod_act_trace_disable(service_name => 'micky',module_name=> 'mymodule',action_name => 'myaction',instance_name => NULL);

PL/SQL procedure successfullycompleted

获取trace文件路径：

SQL> select value fromv$diag_info where name='Default Trace File';

VALUE

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

D:\APP\MICKY\diag\rdbms\micky\micky\trace\micky_ora_381624.trc

2种方法开启/关闭会话的统计信息功能：

1)SERV_MOD_ACT_STAT_ENABLE/SERV_MOD_ACT_STAT_DISABLE:

和trace方法4一样，都是通过SERVICE_NAME,MODULE_NAME和ACTION_NAME开启/关闭对应会话的统计信息，对应统计视图：V$SERV_MOD_ACT_STATS；

2) CLIENT_ID_STAT_ENABLE/CLIENT_ID_STAT_DISABLE:

和trace方法3一样，通过通过客户端ID开启/关闭指定客户端的统计信息，对应统计视图：V$CLIENT_STATS;

6、查找跟踪文件

Oracle11之前可以用如下方式：

select pr.value || '\' ||i.instance_name || '_ora_' || to_char(ps.spid) ||

       '.trc' "trace file name"

 from v$session s, v$process ps, v$parameter pr, v$instance i

 where s.paddr = ps.addr

  and s.sid = userenv('sid')

  and pr.name = 'user_dump_dest';

Oracle 11g开始跟踪文件变为：

{instance_name}_{process_name}_{process_id}.trc

或

{instance_name}_{process_name}_{process_id}_{tracefile_identifier}.trc

{tracefile_identifier}为可自定义选项

上述的查找方式也可以用，对于本session的trace文件，可以这样查询：

select value from v$diag_infowhere name='Default Trace File';

如果想一次查询多个trace文件，可以如下：

SELECT s.sid,

       s.server,

       CASE

         WHEN s.server IN ('DEDICATED','SHARED') THEN

          decode(substr(version, 1, 2),

                 '11',

                 i.instance_name,

                 lower(i.instance_name)) || '_'||

          nvl(lower(pp.server_name),nvl(lower(ss.name), 'ora')) || '_' ||

          p.spid

         ELSE

          NULL

       END || CASE

         WHEN p.traceid IS NOT NULL THEN

          '_' || p.traceid

         ELSE

          ''

       END || '.trc' AS trace_file_name

 FROM v$instance      i,

       v$session       s,

       v$process      p,

       v$px_process    pp,

       v$shared_server ss

 WHERE s.paddr = p.addr

  AND s.sid = pp.sid(+)

  AND s.paddr = ss.paddr(+)

  AND s.type = 'USER'

 ORDER BY s.sid;

7. 关于tkprof

在操作系统命令行中输入tkprof，会得到如下信息：

D:\app\Micky\diag\rdbms\micky\micky\trace>tkprof

Usage: tkprof tracefileoutputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

      输入文件 格式化后的输出文件

 table=schema.tablename   Use'schema.tablename' with 'explain=' option.

 用于指定在将执行规划写进输出文件之前，TKPROF用于临时存放执行规划所用表的架构和名称

  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.

 print=integer    List only thefirst 'integer' SQL statements.

 只列出输出文件中的第一个INTEGER的SQL语句，若忽略，则TKPROF将列出所有跟踪的SQL语句

  aggregate=yes|no

  若用户指定AGGREGATE=NO，TKPROF将不会对相同SQL文本的多个用户进行汇总

  insert=filename  List SQL statements and data inside INSERTstatements.

  SQL脚本的一种，用于将跟踪文件的动机信息存储到数据库中

 sys=no           TKPROF does notlist SQL statements run as user SYS.

 用于启动或禁止将用户SYS所发布的SQL语句列表到输出文件之中，也包括递归SQL(为执行用户的SQL语句，ORACLE还必须执行一些附加语句)语句在内。默认为YES

  record=filename  Record non-recursive statements found in thetrace file.

  对于跟踪文件中的所用非递归SQL语句，TKPROF 将以指定的名称来创建某个SQL脚本。用于对跟踪文件中的用户时间进行重放

  waits=yes|no     Record summary for any wait events foundin the trace file.

 跟踪文件中发现的任何等待事件的记录摘要。

 sort=option      Set of zero ormore of the following sort options:

  在将被跟踪的SQL语句列表输出到跟踪文件之前，先将其按照指定排序选项的降序关系对其进行排序；若指定了多种排序选项，那么根据排序选项所指定值的和的降序关系对其进行排序；若忽略此参数，那么TKPROF将按照使用次序把语句列表到输出文件中

    prscnt number of times parse was called

    语句解析的数目

    prscpu cpu time parsing

    语句解析所占用的CPU时间

    prsela elapsed time parsing

    语句解析所占用的时间（总是大于或等于CPU时间）

    prsdsk number of disk reads during parse

    语句解析期间，从磁盘进行物理读取的数目

    prsqry  number of buffers for consistent read duringparse

    语句解析期间，一致模式块读取（CONSISTENT MODE BLOCK READ）的数目

    prscu  number of buffers for current read during parse

     语句解析期间，当前模式读取（CURRENTMODE BLOCK READ）的数目

    prsmis number of misses in library cache during parse

    语句解析期间，库缓存失败的数目

    execnt number of execute was called

    语句执行的数目

    execpu cpu time spent executing

    语句执行所占用的CPU时间

    exeela elapsed time executing

    语句执行所占用的时间（总是大于或等于CPU时间）

    exedsk  number of disk reads during execute

    语句执行期间，从磁盘进行物理读取的数目

    exeqry  number of buffers for consistent read duringexecute

    语句执行期间，一致模式块读取（CONSISTENT MODE BLOCK READ）的数目

    execu  number of buffers for current read during execute

    语句执行期间，当前模式读取（CURRENTMODE BLOCK READ）的数目

    exerow number of rows processed during execute

    语句执行期间，所处理的语句行数

    exemis  number of library cache misses during execute

    语句执行期间，库缓存失败的数目

    fchcnt  number of times fetch was called

    取数据的数目

    fchcpu cpu time spent fetching

    取数据所占用的CPU时间

    fchela elapsed time fetching

    取数据所占用的时间（总是大于或等于CPU时间）

    fchdsk  number of disk reads during fetch

    取数据期间，从磁盘进行物理读取的数目

    fchqry  number of buffers for consistent read duringfetch

    取数据期间，一致模式块读取（CONSISTENT MODE BLOCK READ）的数目

    fchcu  number of buffers for current read during fetch

    取数据期间，当前模式读取（CURRENTMODE BLOCK READ）的数目

    fchrow number of rows fetched

    所获取的行数

    userid userid of user that parsed the cursor

    解析游标的用户的用户ID