03 找出性能问题

本章提要
------------------------------------------------------
如何找出性能问题的办法, 分而治之
跟踪SQL
TKPROF工具
------------------------------------------------------
3.1 分而治之
    开发基于数据库(类似oracle)的应用程序, 当前的多层架构已经是既定事实, 一般用 MVC, 例如:部署一个web应用, 以下组件, 多数
    运行在多台机器上.
    客户端-反向代理-web服务器-应用服务器-数据库服务器-存储
    反向代理的作用: (连接客户端与web服务器)
    1) 缓存, 将服务器的响应缓存在自己的内存中, 减少服务器的压力
    2) 负载均衡, 将用户请求分配给多个服务器
    3) 访问控制,
    4) 安全策略, IPS-入侵防御系统, web防火墙等
    分治法: 将端到端的相应时间分解为几个主要组件, 再看看是哪个部分出了问题, 哪个部分最消耗时间.
   
    当然, 以上收集信息需要借助一些工具, 有时候很难收集到
    因为本课程主要是考虑数据库方面, 所以, 如果主要时间开销在数据库上, 就进一步跟踪数据库调用,
    首先, 目标是找到哪一类语句, 是SQL还是PL/SQL占用了主要的响应时间, 如果分析是SQL语句的问题, 先要看跟踪文件是否已经包含了细节分析
    的必要信息, 用以决定是SQL还是PL/SQL导致了问题.
    时间模型统计: 实验
   

WITH
 db_time AS (SELECT sid, value
 FROM v$sess_time_model
 WHERE sid = 141
 AND stat_name = 'DB time')
 SELECT stm.stat_name AS statistic,
 trunc(stm.value/1000000,3) AS seconds,
 trunc(stm.value/tot.value*100,1) AS "%"
 FROM v$sess_time_model stm, db_time tot
 WHERE stm.sid = tot.sid
 AND stm.stat_name <> 'DB time'
 AND stm.value > 0
 ORDER BY stm.value DESC;

-- 针对某一个session, 判断占用情况

3-1

3.2 性能测量与剖析分析
    性能测量, 是通过测试主要的业务操作, 查看数据库运行情况.
    剖析分析, 是通过剖析工具, 查看数据库整体情况, 比如占用CPU, 内存等, 但是由于需要剖析工具, 暂时就使用toad作为辅助.
    设定调用数据库的标记(可以使用pl/sql唯一不依赖接口连接到数据库, 还有OCI, JDBC 等, 用到时再说)
   

-- 设置
BEGIN
 dbms_session.set_identifier(client_id=>'leon');
 dbms_application_info.set_client_info(client_info=>'Linux x86_64');
 dbms_application_info.set_module(module_name=>'session_info.sql',
 action_name=>'test session information');
 END;
 /

-- 查询
SELECT sys_context('userenv','client_identifier') AS client_identifier,
 sys_context('userenv','client_info') AS client_info,
 sys_context('userenv','module') AS module_name,
 sys_context('userenv','action') AS action_name
 FROM dual;

-- 查询2
SELECT client_identifier,
 client_info,
 module AS module_name,
 action AS action_name
 FROM v$session
 WHERE sid = sys_context('userenv','sid');

3-2

3.3 跟踪数据库调用
    跟踪数据库调用的基本步骤
   
    SQL跟踪的工具(TKPROF)
    oracle 10g 以后提供了 dbms_monitor包来开启或关闭SQL跟踪, 使用这个表, 还可以根据会话属性(客户端标记, 服务名, 模块名以及操作名,
    开启和关闭SQL跟踪)这意味着如果应用程序有恰当的性能测量, 就能够单独针对用以执行数据库调用的会话开启和关闭SQL跟踪, 现在, 这尤其重要,
    因为在许多场合使用了连接池, 用户不依赖特定的会话. 只有DBA角色的用户才允许执行dbms_monitor包.
    会话级
    要开启或关闭针对某会话的SQL跟踪, dnms_monitor包提供了过程session_trace_enable与session_trace_disable, 如下PL/SQL调用标记为ID 127,
    序列号为29的会话开启第8级的SQL跟踪, waits参数默认为true, binds参数默认为false.
    dbms_monitor.session_trace_enable(session_id => 127,
                                        serial_num => 29,
                                        waits => TRUE,
                                        binds => FALSE)
    dbms_monitor.session_trace_disable(session_id => 127,
                                        serial_num => 29)
    客户端级
    dbms_monitor.client_id_trace_enable(client_id => 'leon',
                                        waits => TRUE,
                                        binds => FALSE)
    dbms_monitor.client_id_trace_disable(client_id => 'leon')
    组件级
    数据库级, 所有连接到数据库的会话
    dbms_monitor.database_trace_enable(waits => TRUE,
                                        binds => TRUE,
                                        instance_name => NULL)
    要避免数据库级的跟踪, 通常建议创建一个角色(下面的例子中称为sql_treace), 临时授权给测试用户.
   

CREATE ROLE sql_trace;

CREATE OR REPLACE TRIGGER enable_sql_trace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQL_TRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/
-- 以上两个参数, timed_statistics 肯定要设置为ture
-- max_dump_file_size 表示限制跟踪文件的大小

3-3

    查找跟踪文件
    dedicated server进程创建的跟踪文件通过初始化参数 user_dump_dest 配置的目录
    后台进程创建的跟踪文件通过初始化参数 background_dump_dest配置的目录
    在oracle 11g上, 引入了自动诊断资料库, 初始化参数 user_dump_dest与background_dump_dest失效, 转而使用 v$diag_info中查看, 如下:
    SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
    诊断文件的格式: {instance name}_{process name}_{process id}.trc
    查看所有会话的诊断文件:
   

SELECT s.sid,
 s.server,
 lower(
 CASE
 WHEN s.server IN ('DEDICATED','SHARED') THEN
 i.instance_name || '_' ||
 nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' ||
 p.spid || '.trc'
 ELSE NULL
 END
 ) 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;

3-4

    查看当前会话的诊断文件:
    SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
    要轻易找到跟踪文件, 还可以使用 tracefile_identifier初始化参数, 用了它, 诊断文件的格式变为:(该方法只对dedicated server管用)
    {instance name}_{process name}_{process id}_{tracefile identifier}.trc
    SQL的跟踪文件本身,我们读它是很困难的, 所以我们需要借助工具来分析, TKPROF(唯一一个可以直接使用的剖析工具)
    说明基于如下的PL/SQL块执行生效后的跟踪文件:
   

DECLARE
l_channel_id sh.sales.channel_id%TYPE := 3;
BEGIN
FOR c IN (SELECT cust_id, extract(YEAR FROM time_id), sum(amount_sold)
FROM sh.sales
WHERE channel_id = l_channel_id
GROUP BY cust_id, extract(YEAR FROM time_id))
LOOP
NULL;
END LOOP;
END;

3-5

    TKPROF(操作系统命令)
    是一个命令行工具, 主要作用是把原始跟踪文件作为输入, 输出格式化的文本文件. 是操作系统命令, 所以输入文件和输出文件要注意路径.
    tkprof dbhz01_ora_7339.trc dbhz01_ora_7339.txt (前面输入, 后面输出)
    不带任何参数的tkprof只对分析很小的跟踪文件有帮助, 每个参数的功能如下:
    print, 用于限制输出文件生成的SQL语句的数量, 默认是无限制的, 只有在和参数sort一起使用时才有意义, 例如, 只得到10个SQL语句, 参数
        必须制定为 print=10.
    aggregate, 制定是否单独处理同样内容的SQL, 默认是不需要, 不过你有特殊要求是, 可以制定为 aggregate=no, 这样就能单独处理每个SQL.
    insert, 告诉tkprof生成sql脚本, sql脚本名字本身指定, 比如 insert=aaa.sql
    sys, 制定sys用户运行SQL
    sort, 写入输出文件里的SQL语句的顺序, 默认是跟踪文件发现的SQL顺序, 基本上, 通过制定想要的选项, 可以根据资源使用(比如, 调用次数,
        CPU时间, 物理读的数量等)或响应时间进行排序, 例如, prsela是对一个游标解析耗费的时间, exeela针对游标执行时间, fchela针对
        游标获取数据行所花费时间, 比如我们制定 sort=prsela,exeela,fchela(指定逗号分隔, 依次排序)
    综上, 大师经常使用的 TKPROF参数设置为:
        tkprof {input trace file} {output file} sys=no sort=prsela,exeela,fchela
    解释TKPROF输出
    头部有解释信息, 之后, 针对每个SQL语句提供如下信息: SQL语句文本, 执行统计, 解析信息, 执行计划, 等待事件.     
    count: 表示执行的数据库调用数量
    cpu: 表示处理数据调用花去的CPUS时间, 单位秒
    elapsed: 处理数据库调用花费的总时间, 以秒为单位, 如果这个值比CPU时间搞, 等待事件会提供等待的资源或同步点
    disk: 物理读的数据块数量, 这不是物理I/O操作数量, 如果这个值大于逻辑读的数量(disk > query+current), 这意味着数据块填充进了临时表
        空间.
    query: 一致性模式下从高速缓存逻辑读取的块数量, 通常, 这类型的逻辑读用作查询.
    current: 代表在当前模式下从高速缓存逻辑读取的块数量, 通常,这类逻辑读被INSERT, DELETE, MERGE, UPDATE语句使用.
    rows: 代表处理的数据行数量, 对于查询来说, 这就是获取行数量, 对于insert,delete,merge,update等语句来说, 这是所影响的行数量.
    例如:(执行统计)
    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.04 0.19 0 0 0 0
    Fetch 164 1.09 4.50 2590 1720 0 16348
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 166 1.13 4.70 2590 1720 0 16348
    
    下面几行总结了关于解析基本信息(解析信息)
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 33 (OPS$CHA) (recursive depth: 1)
    开始两行(misses in library cache)提供了发生在解析和执行调用阶段的硬解析数量, 如果在执行调用时没有硬解析发生, 执行的这一行将不存在.
    接下来是优化器模式以及用于解析SQL的用户, 最后括号中的是递归深度, 只针对递归SQL语句提供,深度为n(本例为1)仅仅表示另一个深度为n-1(本例
    为0)的SQL语句执行了这条语句.
    
    执行计划(执行计划)
    rows 列提供执行计划中每个操作返回的行数(不是处理的-要注意, 是返回的)
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          6  SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us cost=2 size=2128 card=1)
          6   COUNT  (cr=0 pr=0 pw=0 time=5 us)
          6    HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=1 size=2128 card=1)
         44     FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1075 us cost=0 size=94 card=1)
       2399     FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=3912 us cost=0 size=203400 card=100)
    对于每个操作源:
    cr 是一致性模式下逻辑读出的数据块数
    pr 是从磁盘物理读出的数据块数
    pw 是物理写入磁盘的数据块数
    time 是以微妙表示的总的消逝时间, 不是很精确
    cost 是操作的评估开销, 11g后才有
    size 是操作返回的预估数据量(字节数), 11g后提供
    card 是操作返回的预估行数, 11g后提供
    注意这些值, 除了card, 其他的都是累计的.
    
    等待事件(等待事件)    
    Times Waited 是等待事件占用时间
    Max. Wait 是单个等待事件最大等待时间, 单位 秒
    Total Waited 是针对一个等待事件总的等待秒数, 理想情况下, 所有等待事件的等待时间总量应该等于执行统计提供的流逝时间与CPU时间的差值,该
        差值如果存在, 叫做未说明的时间(unaccounted for time)
    要分析等待事件, 关键是要知道哪些操作是相关的, 幸运的是, 尽管有数百个等待事件类型, 重复发生的大多数是常见的几种,可以在oracle database
    reference手册的俘虏中找到关于多数常见等待事件的简短描述. SQL*NET message to client 表明网络层在发送数据给客户端(注意, 实际通过网络
    发送数据的时间没有包括在内), 并且, 在等待客户端数据的时候,数据库在等待SQL*Net message from client的消息, 因而, 对于SQL*Net层带来每
    一个往返, 应该能看到一对如此的等待事件.    

    另外, 还有一个工具, TVD$XTAT, 是这本书作者自己开发的? 总之, 先记下下载地址, 这个软件是免费的, 如果以后有需要,再补充
    下载地址: http://top.antognini.ch下载TVD$XTAT(免费软件)
    解压文件到所选的目录中, 调整 java_home与tvdxtat_home 等等.
3.4 剖析PL/SQL代码
    以上是剖析SQL, 针对于PL/SQL, 数据库引擎通过 dbms_profiler包提供了一个API(toad中也有对应的可视化的剖析工具)
    个人还是推荐使用 toad 的剖析工具SQL Navigator(Quest Software)