Microsoft Word - 10.性能诊断与SQL优化.doc

Transcription

1 第 9 章 性能诊断与 SQL 优化 对于一个数据库系统, 从应用的角度来说, 通常我们最期望有良好的性能, 稳定的运行 所以怎样维持一个数据库高性能稳定运行就变得非常重要, 对于大多数数据库维护人员来说, 直接面对的问题就是在问题出现时, 需要快速发现并迅速解决数据库性能等问题, 提高系统持续高效运行 本章我们将通过一些实际生产中遇到的案例进行剖析讲解, 希望大家能够从中领会到诊断性能问题的思路和方法, 并对具体问题, 特别是 SQL 问题进行常规处理 9.1 使用 AutoTrace 功能辅助 SQL 优化 Oracle SQL*Plus 提供一个 autotrace 的功能, 可以用于跟踪 SQL 的执行计划, 收集统计信 息, 经常被作为 SQL 的优化工具之一被广泛使用 Autotrace 功能的启用 在 Oracle10g 之前, 缺省的 autotrace 功能并未打开, 需要通过以下步骤手工启用该功能 : 1. 创建基础表这可以通过运行 $ORACLE_HOME\rdbms\admin\utlxplan 脚本完成, 该脚本用于创建 plan_talbe 表 : SQL> connect / as sysdba 已连接 表已创建 为了使多个用户可以共享同一个 plan_table, 可以为它创建一个同义词, 并授权给 Public: SQL> create public synonym plan_table for plan_table; 同义词已创建 SQL> grant all on plan_table to public ; 授权成功 2. 创建 plustrace 角色这需要运行 $ORACLE_HOME\sqlplus\admin\plustrce.sql 脚本 : SQL> SQL> drop role plustrace; drop role plustrace

2 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 * ERROR 位于第 1 行 : ORA-01919: 角色 'PLUSTRACE' 不存在 SQL> create role plustrace; 角色已创建 SQL> grant select on v_$sesstat to plustrace; 授权成功 SQL> grant select on v_$statname to plustrace; 授权成功 SQL> grant select on v_$session to plustrace; 授权成功 SQL> grant plustrace to dba with admin option; 授权成功 SQL> set echo off 3. 一点增强 DBA 用户首先被授予了 plustrace 角色, 然后我们可以手工把 plustrace 授予 public, 这样所有用户都将拥有 plustrace 角色的权限, 所有数据库用户也就拥有了使用 autotrace 功能的权限 SQL> grant plustrace to public ; 授权成功 完成以上步骤我们就可以使用 AutoTrace 的功能了 Autotrace 有几个常用选项, 简单说明如下 : u SET AUTOTRACE OFF 不生成 AUTOTRACE 报告, 这是缺省模式 u SET AUTOTRACE ON EXPLAIN AUTOTRACE 只显示优化器执行路径报告 u SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息 u SET AUTOTRACE ON 包含执行计划和统计信息 u SET AUTOTRACE TRACEONLY 同 set autotrace on, 但是不显示查询输出在 SQL*Plus 中,autotrace 的基本输出大致类似 : SQL> set autotrace on SQL> select * from v$version where rownum <2; BANNER Oracle9i Enterprise Edition Release Production Execution Plan SELECT STATEMENT Optimizer=CHOOSE 1 0 COUNT (STOPKEY) 2 1 FIXED TABLE (FULL) OF 'X$VERSION' 2

3 第 1 章 章名章名章名章名章名 Statistics recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 433 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Oracle10g Autotrace 功能的增强 在 Oracle10g Release 2 中,Autotrace 的功能已经被极大加强和改变 让我们先来看一下什么地方发生了改变 : SQL> set autotrace on SQL> select * from v$version where rownum <2; BANNER Oracle Database 10g Enterprise Edition Release Prod Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 * 1 COUNT STOPKEY * 2 FIXED TABLE FULL X$VERSION (0) 00:00: Predicate Information (identified by operation id): filter(rownum<2) 2 - filter("inst_id"=userenv('instance')) Statistics

4 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 471 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 注意, 此时 autotrace 的输出被良好格式化, 并给出关于执行计划部分的简要注释 其实这里并没有带来新的技术, 从 Oracle9i 开始,Oracle 提供了一个新的工具 dbms_xplan 用以格式化和查看 SQL 的执行计划 其原理是通过对 plan_table 的查询和格式化提供更友好 的用户输出 dbms_xplan 的调用的语法类似 : select * from table(dbms_xplan.display(format=>'basic')) 具体用法可以参考 Oracle 官方文档 实际上从 Oracle9i 开始我们就经常使用如下方式调用 dbms_xplan: SQL> explain plan for select count(*) from dual; Explained. PLAN_TABLE_OUTPUT Id Operation Name Rows Bytes Cost SELECT STATEMENT 1 SORT AGGREGATE 2 TABLE ACCESS FULL DUAL Note: rule based optimization 10 rows selected. utlxplp.sql 脚本中正是调用了 dbms_xplan: SQL> get?/rdbms/admin/utlxplp; 40* select * from table(dbms_xplan.display()); 41 而在 Oracle10gR2 中,Oracle 帮我们简化了这个过程, 一个 autotrace 就完成了所有的输出, 这是易用性上的一个进步 在使用 Oracle 的过程中, 经常能够感受到 Oracle 针对用户需求或 4

5 第 1 章 章名章名章名章名章名 易用性的改进, 这也许是很多人喜爱 Oracle 的一个原因吧 如果足够细心大家可能还会注意到, 在 Oracle10g 中 PLAN_TABLE 不再需要创建,Oracle 缺省增加了一个字典表 PLAN_TABLE$, 然后基于 PLAN_TABLE$ 创建公用同义词供用户使用 使用 Autotrace 功能的另外一个好处就是, 可以很容易的发现各种视图的底层基础表, 这可以作为大家学习和研究 Oracle 的一个重要手段 : SQL> set autotrace trace explain SQL> select * from plan_table; Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 TABLE ACCESS FULL PLAN_TABLE$ (0) 00:00: Note dynamic sampling used for this statement Autotrace 功能的内部操作 当使用 Autotrace 功能时, 在数据库内部,Oracle 实际上是启动了 2 个会话 (session) 连接, 一个 Session 用于执行查询等操作, 另外一个 Session 用于记录执行计划和输出最终结果等操作 让我们一起来进一步深入了解一下 Autotrace 功能 在启用 Autotrace 之前, 注意当前只有一个用户 SESSION 连接 : SQL> select sid,serial#,username from v$session where username is not null; SID SERIAL# USERNAME SYS 在启用 autotrace 功能后, 此时, 另外一个 SESSION 被创建 : SQL> set autotrace on SQL> select sid,serial#,username from v$session where username is not null; SID SERIAL# USERNAME SYS 9 14 SYS Execution Plan 5

6 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 SELECT STATEMENT Optimizer=CHOOSE 1 0 FIXED TABLE (FULL) OF 'X$KSUSE' Statistics recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 而且注意, 这两个 SESSION 都是由一个进程 (Process) 衍生创建的 : SQL> select a.sid,a.serial#,a.username,b.pid,b.spid from v$session a,v$process b 2 where a.paddr = b.addr and a.username is not null; SID SERIAL# USERNAME PID SPID SYS SYS Execution Plan SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 FIXED TABLE (FULL) OF 'X$KSUPR' 3 1 SORT (JOIN) 4 3 FIXED TABLE (FULL) OF 'X$KSUSE' 而此处的 V$PROCESS.SPID 正是操作系统的进程号 : SQL>! ps -ef grep grep -v grep oracle :03? 00:00:00 oracleeygle (DESCRIPTION=(LOCAL=YES) (ADDRESS= (PROTOCOL=beq))) 这就是通常所说的, 一个进程在数据库中可能对应多个 SESSION 通过在全局启用 事件 ( 具体使用方法可以参考本章后面章节 ), 可以得到 Autotrace 的内部操作 设置 事件可以采用如下命令 ( 在 spfile 中设置, 需要重新启动数据库后方能生效, 注意应当仅在测 试环境才可在全局启用 ): alter system set event='10046 trace name context forever,level 12' scope=spfile; 通过 tkprof 格式化跟踪文件 : [oracle@jumper udump]$ tkprof eygle_ora_28653.trc auto.log aggregate=no TKPROF: Release Production on Fri May 12 11:17: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 6 [oracle@jumper udump]$ ll

7 第 1 章 章名章名章名章名章名 total 168 -rw-r--r-- 1 oracle dba May 12 11:17 auto.log -rw-r oracle dba May 12 11:04 eygle_ora_28653.trc 检查跟踪文件或格式化后的日志, 我们可以发现以上两个 SESSION( 在日志中显示位 8.5 和 9.14) 的内部操作 : *** SESSION ID:(8.5) :03: *** SESSION ID:(9.14) :04: 主要的步骤有 : 1. 执行计划的输出通过以下 SQL 完成信息记录 : insert into plan_table (statement_id, timestamp, operation, options, object_node, object_owner, object_name, object_instance, object_type, search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates ) values (:1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19, :20,:21,:22,:23,:24,:25,:26,:27) 通过以下 SQL 完成执行计划查询输出 : SELECT ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,LPAD(' ',2*(LEVEL-1)) OPERATION DECODE(OTHER_TAG,NULL,'','*') DECODE(OPTIONS,NULL,'',' (' OPTIONS ')') DECODE(OBJECT_NAME,NULL,'',' OF ''' OBJECT_NAME '''') DECODE(OBJECT_TYPE,NULL,'',' (' OBJECT_TYPE ')') DECODE(ID,0, DECODE(OPTIMIZER,NULL,'',' Optimizer=' OPTIMIZER)) DECODE(COST,NULL,'',' (Cost=' COST DECODE(CARDINALITY,NULL,'',' Card=' CARDINALITY) DECODE(BYTES,NULL,'',' Bytes=' BYTES) ')') PLAN_PLUS_EXP,OBJECT_NODE OBJECT_NODE_PLUS_EXP FROM PLAN_TABLE START WITH ID=0 AND STATEMENT_ID=:1 CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID=:1 ORDER BY ID,POSITION 2. 统计信息输出主要通过以下 SQL 获取统计信息名称 编号等信息 : SELECT STATISTIC# S, NAME FROM SYS.V_$STATNAME WHERE NAME IN ('recursive calls','db block gets','consistent gets','physical reads','redo size','bytes sent via SQL*Net to client', 'bytes received via SQL*Net from client','sql*net roundtrips to/from client','sorts (memory)','sorts (disk)') ORDER BY S 7

8 书名书名书名书名书名书名书名书名书名书名书名书名书名书名通过以下查询获得输出值 : SELECT PT.VALUE FROM SYS.V_$SESSTAT PT WHERE PT.SID=:1 AND PT.STATISTIC# IN (7,40,41,42,115,236, 237,238,242,243) ORDER BY PT.STATISTIC# 了解了这些内部操作, 更有利于我们学习和理解 Oracle 的运行机制 本书中介绍的很多方法都可以辅助大家进行进一步的深入研究, 希望大家在阅读中更多注意一下方法 使用 Autotrace 功能辅助 SQL 优化 曾经遇到这样一个案例, 有朋友在论坛中提出帮助请求, 问以下这样一条 SQL 是否可以优化 : SELECT * FROM sys_user WHERE user_code = 'zhangyong' OR user_code IN (SELECT grp_code FROM sys_grp WHERE sys_grp.user_code = 'zhangyong') 首先可以通过 SQL*Plus 的 Autotrace 功能查看该 SQL 的执行计划 : Execution Plan SELECT STATEMENT Optimizer=RULE 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'SYS_USER' 3 1 INDEX (UNIQUE SCAN) OF 'PK_SYS_GRP' (UNIQUE) Statistics recursive calls 4 db block gets consistent gets 0 physical reads 0 sorts (memory) 0 sorts (disk) 3 rows processed 注意到该 SQL 的逻辑读高达 30590, 优化该 SQL 在根本上需要降低逻辑读 而相关数据表的记录情况如下 : SQL> select count(distinct user_code) from sys_grp; COUNT(DISTINCTUSER_CODE) SQL> select count(distinct grp_code) from sys_grp; COUNT(DISTINCTGRP_CODE) 8

9 第 1 章 章名章名章名章名章名 SQL> select count(distinct user_code) from sys_user; COUNT(DISTINCTUSER_CODE) 通过执行计划可以知道, 该 SQL 通过全表扫描访问记录数为 的 SYS_USER 表, 通 过索引唯一键扫描访问 PK_SYS_GRP, 两者过滤 (FILTER) 返回结果集, 全表扫描及 FILTER 操作导致了大量的逻辑读 可以尝试通过 OR 展开 索引访问避免全表扫描和 FILTER 操作, 改写后的 SQL 如下所 示 : SELECT * FROM sys_user WHERE user_code = 'zhangyong' UNION ALL SELECT * FROM sys_user WHERE user_code <> 'zhangyong' AND user_code IN (SELECT grp_code FROM sys_grp WHERE sys_grp.user_code = 'zhangyong') 通过 UNION ALL 将 SQL 展开, 从而避免了 FILTER 操作, 表联合部分通过 NESTED LOOPS 实现 改写后的 SQL 执行计划如下所示 : Statistics recursive calls 0 db block gets 130 consistent gets 0 physical reads 1 sorts (memory) 0 sorts (disk) 3 rows processed Execution Plan SELECT STATEMENT Optimizer=RULE 1 0 UNION-ALL 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_USER' 3 2 INDEX (UNIQUE SCAN) OF 'PK_SYS_USER' (UNIQUE) 4 1 NESTED LOOPS 5 4 VIEW OF 'VW_NSO_1' 6 5 SORT (UNIQUE) 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_GRP' 8 7 INDEX (RANGE SCAN) OF 'FK_SYS_USER_CODE' (NON-UNIQUE) 9 4 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_USER' 9

10 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 10 9 INDEX (UNIQUE SCAN) OF 'PK_SYS_USER' (UNIQUE) 通过统计信息输出可以注意到,SQL 的逻辑读从原来的 降低到 130, 性能得到了极 大提高 同时改写后的 SQL 引入了一个排序, 排序来自于这一步 : 6 5 SORT (UNIQUE) 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_GRP' 8 7 INDEX (RANGE SCAN) OF 'FK_SYS_USER_CODE' (NON-UNIQUE) 在 SYS_GRP 表中,user_code 是非唯一键值, 在 in 值判断里, 要做 sort unique 排序, 去 除重复值, 这里的 union all 是不需要排序的 9.2 获取 SQL 执行计划的方法 在进行 SQL 诊断和优化时, 通常都需要获取 SQL 的执行计划, 通过执行计划来判断 SQL 的执行是否合理, 那么如何来获取 SQL 的执行计划就显得非常重要了 上一节介绍的 AutoTrace 功能是获得 SQL 执行计划的方法之一, 在这一节, 继续讨论 SQL 执行计划获取方法及相关诊断应用 通过 V$SQL_PLAN 获得执行计划 从 Oracle9i 开始,Oracle 开始通过 V$SQL_PLAN 等视图进行 SQL 执行计划的记录, 通过这个视图, 可以获取正在执行中或者仍然缓存着的 SQL 执行计划, 从而可以帮助我们进行实时准确的数据库诊断 在 Oracle9i 中, 可以通过自定义编写的一些脚本来获取 SQL 的执行计划, 如通过 HASH_VALUE( 可以通过 V$SESSION 或者 V$SQL V$SQL_PLAN 视图获得 SQL 的 HASH_VALUE) 输入来获取 SQL 及其执行计划 : oracle@/opt/oracle/tools$./getplan_by_hashvalue.sh SQL_TEXT select count(uspl.numusplguid) from hy_usersubplan_log uspl,hy_serviceplan sp,hy_serviceinfo s,hy_spinfo pvd,hy_platform pf where uspl.numsplanguid + 0 = sp.numplanguid and sp.numsvrguid = s.numsvrguid and s.numspguid + 0 = pvd.numspguid and pvd.numptguid+ 0 = pf.numptguid and pf.vc2platformid = :1 and pvd.vc2spcode = :2 and s.vc2service_id = :3 and s.vc2ispack = :4 and uspl.vc2enabledflag = 'Y' and uspl.datstart <= sysdate and nvl(uspl.datend, sysdate + 1) >= sysdate and uspl.vc2userid = :5 HASH_VALUE EXECUTIONS PER_GETS MODULE JDBC Thin Client Operation PHV/Object Name Rows Bytes Cost

11 第 1 章 章名章名章名章名章名 SELECT STATEMENT SORT AGGREGATE NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS TABLE ACCESS BY INDEX ROWID HY_PLATFORM INDEX UNIQUE SCAN HYUIDX_PLATFORMID 97 1 TABLE ACCESS FULL HY_SERVICEINFO TABLE ACCESS BY INDEX ROWID HY_SPINFO INDEX UNIQUE SCAN HYPK_SPINFO 97 TABLE ACCESS BY INDEX ROWID HY_SERVICEPLAN INDEX RANGE SCAN HYUIDX_SERVICEPLAN 2 1 PARTITION LIST ALL TABLE ACCESS BY LOCAL INDEX R HY_USERSUBPLAN_LOG INDEX RANGE SCAN HYIDX_USPL_USERID 该脚本的编码内容如下 : oracle@/opt/oracle/tools$cat getplan_by_hashvalue.sh #!/bin/ksh $ORACLE_HOME/bin/sqlplus -s "/ as sysdba"<<eof set lines 121 set pages 999 col sql_text format a80 col module format a36 col per_gets format select sql_text from v\$sqltext_with_newlines where hash_value=$1 order by piece; select hash_value,executions,buffer_gets/decode(executions,0,1,executions) as per_gets,a.module from v\$sqlarea a where a.hash_value=$1; set heading off select ' ' from dual union all select ' Operation PHV/Object Name Rows Bytes Cost ' as "Optimizer Plan:" from dual union all select ' ' from dual 11

12 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 union all select * from (select rpad(' ' substr(lpad(' ',1*(depth-1)) operation decode(options, null,'',' ' options), 1, 32), 33, ' ') ' ' rpad(decode(id, 0, '----- ' to_char(hash_value) ' -----', substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name) ' ',1, 20)), 21, ' ') ' ' lpad(decode(cardinality,null,' ', decode(sign(cardinality-1000), -1, cardinality ' ', decode(sign(cardinality ), -1, trunc(cardinality/1000) 'K', decode(sign(cardinality ), -1, trunc(cardinality/ ) 'M', trunc(cardinality/ ) 'G')))), 7, ' ') ' ' lpad(decode(bytes,null,' ', decode(sign(bytes-1024), -1, bytes ' ', decode(sign(bytes ), -1, trunc(bytes/1024) 'K', decode(sign(bytes ), -1, trunc(bytes/ ) 'M', trunc(bytes/ ) 'G')))), 6, ' ') ' ' lpad(decode(cost,null,' ', decode(sign(cost ), -1, cost ' ', decode(sign(cost ), -1, trunc(cost/ ) 'M', trunc(cost/ ) 'G'))), 8, ' ') ' ' as "Explain plan" from v\$sql_plan where hash_value = $1 and child_number = (select max(child_number) from v\$sql_plan where hash_value = $1)) union all select ' ' from dual; exit EOF 以下介绍一个实际的诊断案例供参考 曾经遇到过这样一次性能问题, 开发人员编写的一个存储过程, 其中包含了一系列的事务处理, 大约有 10 个左右的 DML 事务执行, 每一个 SQL 在 SQL*Plus 中执行都很迅速, 但是一旦放在过程中执行, 通过参数传入一个 sysdate, 整个过程的执行就变得非常缓慢, 无法成功完成 数据库环境为 Oralce9iR2: SQL> select * from v$version where rownum <2; BANNER Oracle9i Enterprise Edition Release bit Production 收到这个问题首先需要确定哪个 SQL 是真正引起性能问题的罪魁祸首 首先对 Procedure 12

13 第 1 章 章名章名章名章名章名 进行一点修改, 在每个 DML 事务执行之前在一张临时创建的测试表中插入一个时间, 前后两个时间相减可以得到每个 SQL 独立执行的时间 通过这个办法, 发现在执行到第 8 个 SQL 时, 响应失去 也就是说, 这个 SQL 是导致性能缓慢的根本原因 找到这个 SQL 接下来的事情就变得简单一些, 修改这个过程, 在 Procedure 之前增加一个跟踪 : create or replace procedure cmop_servdetail_d_eygle (m_datstat date) Authid Current_User is begin execute immediate 'alter session set sql_trace=true'; 那么当再次执行这个过程时,SQL 的执过程被记录到一个 Trace 文件中, 但是注意, 由于 SQL 可能暂时无法完成, 所以执行计划等信息并不会输出, 但是由于 SQL 的 HASH 过程首先完成, 在 Trace 文件的输出中, 首先打印出了 SQL 的 HASH VALUE 值, 这里是 :hv= APPNAME mod='sql*plus' mh= act='' ah= ===================== PARSING IN CURSOR #2 len=32 dep=1 uid=28 oct=42 lid=28 tim= hv= ad='20e289d8' alter session set sql_trace=true END OF STMT EXEC #2:c=0,e=7735,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim= ===================== PARSING IN CURSOR #2 len=4721 dep=1 uid=28 oct=2 lid=28 tim= hv= ad='1270b8c0' INSERT into cmo_servdetail_d (vc2dayguid, and a.vc2bt = c.vc2cmbt and a.vc2cid = c.vc2cmcid and c.numsvrguid = b.numsvrguid group by b.numsvrguid, b.vc2service_id, substr(a.vc2mid, 0, 4), a.vc2ua, c.vc2urltype END OF STMT 得到这个 HASH VALUE 值之后就可以通过 v$sql_plan 来获得这个 SQL 的执行计划, 另外一个需要说明的是, 我在诊断某个 SQL 问题时通常习惯将这个 SQL 创建到一张临时表中, 以避免可能对 v$sql_plan 的反复查询带来的消耗 对于这个案例我只需要发出如下语句 : create table t as select * from v$sql_plan where hash_value=

14 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 通过查询可以获得这条问题 SQL 的执行计划 : Operation PHV/Object Name Rows Bytes Cost INSERT STATEMENT SORT GROUP BY FILTER HASH JOIN TABLE ACCESS BY LOCAL INDEX R CM_URLLOG_JUMP NESTED LOOPS TABLE ACCESS FULL HY_SVR_REF_URL_CMCID PARTITION RANGE ITERATOR BITMAP CONVERSION TO ROWID BITMAP AND BITMAP CONVERSION FROM R SORT ORDER BY INDEX RANGE SCAN CMIDX_URLLOG_JUMP_DA 6 1 BITMAP CONVERSION FROM R INDEX RANGE SCAN CMIDX_URLLOG_JUMP_CI 6 19 TABLE ACCESS FULL HYO_SERVICEPLAN 10K 694K 这就是这个 SQL 的执行计划, 来对比一下 SQL*Plus 中执行这条 SQL 的执行计划 : Execution Plan INSERT STATEMENT Optimizer=CHOOSE (Cost=35 Card=12 Bytes=3192) 1 0 SORT (GROUP BY) (Cost=35 Card=12 Bytes=3192) 2 1 FILTER 3 2 HASH JOIN (Cost=26 Card=12 Bytes=3192) 4 3 HASH JOIN (Cost=11 Card=1 Bytes=201) 5 4 TABLE ACCESS (FULL) OF 'HY_SVR_REF_URL_CMCID' (Cost=3 Card=11 Bytes=814) 6 4 PARTITION RANGE (ITERATOR) 7 6 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CM_URLLOG_JUMP' (Cost=6 Card= Bytes= ) 8 7 INDEX (RANGE SCAN) OF 'CMIDX_URLLOG_JUMP_DAT'(NON-UNIQUE) (Cost=2 Card=229897) 9 3 TABLE ACCESS (FULL) OF 'HYO_SERVICEPLAN' (Cost=14 Card=10946 Bytes=711490) 注意到这两个执行计划完全不同, 速度快的执行计划对于 PARTITION RANGE 访问是通 过一个索引来完成的 ; 而对于速度慢的执行计划, 这里却使用了 2 个索引进行位图转换 : PARTITION RANGE ITERATOR 14

15 第 1 章 章名章名章名章名章名 BITMAP CONVERSION TO ROWIDS BITMAP AND BITMAP CONVERSION FROM ROWIDS SORT ORDER BY INDEX RANGE SCAN CMIDX_URLLOG_JUMP_DAT BITMAP CONVERSION FROM ROWIDS INDEX RANGE SCAN CMIDX_URLLOG_JUMP_CID 正是这个转换使得性能大为缩减 显然这个错误的选择是由于 CMIDX_URLLOG_JUMP_CID 索引的存在, 再加上绑定变量 的影响,CBO 最终选择了错误的执行计划, 为了快速的解决问题, 在确认之后, 我们直接 Drop 掉了这个索引 此时再次运行过程, 发现很快完成, 此时的执行计划通过同样的方法可以获得 : Operation PHV/Object Name Rows Bytes Cost INSERT STATEMENT SORT GROUP BY 12 3K 35 FILTER HASH JOIN 12 3K 26 HASH JOIN TABLE ACCESS FULL HY_SVR_REF_URL_CMCID PARTITION RANGE ITERATOR TABLE ACCESS BY LOCAL INDEX CM_URLLOG_JUMP 128K 15M 6 INDEX RANGE SCAN CMIDX_URLLOG_JUMP_DA 230K 2 TABLE ACCESS FULL HYO_SERVICEPLAN 10K 694K 现在的执行计划恢复了正常 注意到错误的执行计划选择了 bitmap convert 的执行计划, 而两个索引都是 B*Tree 索引 这种转换是 Oracle9i 引入的, 同时一个隐含参数被用来控制这 种转换 : SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y 3 WHERE x.inst_id = USERENV ('Instance') 4 AND y.inst_id = USERENV ('Instance') 5 AND x.indx = y.indx 6 AND x.ksppinm LIKE '%&par%' 7 / Enter value for par: _b_tree_bitmap_plans 15

16 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 old 6: AND x.ksppinm LIKE '%&par%' new 6: AND x.ksppinm LIKE '%_b_tree_bitmap_plans%' NAME VALUE DESCRIB _b_tree_bitmap_plans TRUE enable the use of bitmap plans for tables w. only B-tree indexes 如果这种性能衰减的转换经常发生, 可以将这个隐含参数设置为 FALSE EXPLAIN PLAN FOR 与 DBMS_XPLAN 在前面已经简单提到过,Explain Plan For 和 DBMS_XPLAN 包结合可以用于获取 SQL 的执行计划 本节我们将对这两者的结合使用进行进一步的介绍 EXPLAIN PLAN 命令可以在后台对 SQL 进行解析, 并将 SQL 执行计划加载到执行计划表中 ( 默认名称为 PLAN_TABLE), 这是 EXPLAIN PLAN 的作用, 其通常的使用方法是在 SQL*PLUS 中输入类似如下命令 : Explain plan <set statement_id = text > <into your plan table>for statement 其中通过 set statement_id = text 可以为 SQL 进行名称标记, into your plan table 默认的是 plan_table 表, 通常使用格式如下 : EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno=7788; 执行计划生成之后, 剩下的就是展现问题,DBMS_XPLAN 包就是用来实现这一功能的, 该 PACKAGE 自 Oracle9iR2 引入, 初始的只具有一个函数 : SQL> desc dbms_xplan FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name Type In/Out Default? TABLE_NAME VARCHAR2 IN DEFAULT STATEMENT_ID VARCHAR2 IN DEFAULT FORMAT VARCHAR2 IN DEFAULT 而在 Oracle10g 中该 Package 的功能得到了极大的增强 DBMS_XPLAN.DISPLAY 可以被调用来返回执行计划, 调用过程类似如下 : SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 值得注意的是,DBMS_XPLAN 还能从存储 SGA 内的指针显示 实时 执行计划, 通过查看 V$SESSION 视图, 可以找到会话执行 SQL 的 SQL ID, 拥有了这个 SQL ID 之后可以通过 DBMS_XPLAN.DISPLAY_CURSOR 来获得 Cursor 所使用的执行计划 此外 DBMS_XPLAN.DISPLAY_AWR 函数还可用来查询 Oracle 10g 的自动工作负载库 (Automatic Workload Repository, AWR) 获得的历史 SQL 语句, 并显示它的执行计划 下面我们通过 Oracle10g 中的测试应用来展示一下这个 Package 对于 SQL 执行计划的获取与展现 首先通过 EXPLAIN PLAN 来生成执行计划 : 16

17 第 1 章 章名章名章名章名章名 SQL> EXPLAIN PLAN set statement_id = 'NO' FOR 2 SELECT * FROM emp WHERE empno=7788; Explained. 然后通过查询展现以上生成的执行计划 : SQL> SELECT plan_table_output 2 FROM TABLE( DBMS_XPLAN.DISPLAY('PLAN_TABLE','NO','ALL') ); PLAN_TABLE_OUTPUT Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID EMP (0) 00:00:01 * 2 INDEX UNIQUE SCAN PK_EMP 1 1 (0) 00:00: Query Block Name / Object Alias (identified by operation id): SEL$1 / EMP@SEL$1 2 - SEL$1 / EMP@SEL$1 Predicate Information (identified by operation id): access("empno"=7788) Column Projection Information (identified by operation id): "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22] 2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22] 28 rows selected. 以上功能可以用于显示已知 SQL 的执行计划, 但是很多时候我们需要直接从数据库中获得其他应用 SQL 的执行计划 从 Oracle9i 开始,V$SQL_PLAN_STATISTICS V$SQL_PLAN 视图被引入用于记录 SQL 的执行统计信息以及执行计划, 但是在 Oracle9i 中, 从以上视图获取执行计划通常需要自己手工编写脚本, 实现起来较为复杂, 在 Oracle10g 中新的增强被引入, DBMS_XPLAN.DISPLAY_CURSOR 可以很容易的帮助我们实现以上需求, 执行该功能需要对 V$SESSION V$SQL V$SQL_PLAN V$SQL_PLAN_STATISTICS_ALL 就有访问权限 此时使用 scott 用户进行, 需要首先对 SCOTT 用户授权 : grant select on v_$session to scott; grant select on v_$sql_plan to scott; 17

18 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 grant select on v_$sql to scott; DISPLAY_CURSOR 的参数需要如下 : FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name Type In/Out Default? SQL_ID VARCHAR2 IN DEFAULT CURSOR_CHILD_NO NUMBER(38) IN DEFAULT FORMAT VARCHAR2 IN DEFAULT 现在看看 DISPLAY_CURSOR 的输出 : SQL> SELECT plan_table_output 2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); PLAN_TABLE_OUTPUT SQL_ID 1m225m1612xvg, child number SELECT d.dname, SUM(e.sal) AS sum_sal FROM dept d,emp e WHERE d.deptno = e.deptno GROUP BY d.dname Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 8 (100) 1 HASH GROUP BY (25) 00:00:01 * 2 HASH JOIN (15) 00:00:01 3 TABLE ACCESS FULL DEPT (0) 00:00:01 4 TABLE ACCESS FULL EMP (0) 00:00: Predicate Information (identified by operation id): access("d"."deptno"="e"."deptno") Note dynamic sampling used for this statement 在 Oracle10g 中, 可以通过 V$SESSION 或 V$SQL 等视图来获取不同会话的 SQL_ID 以 及 SQL_CHILD_NUMBER 来获得其 SQL 执行计划 : SQL> select sid,username,sql_id,sql_child_number 2 from v$session where sql_id is not null; SID USERNAME SQL_ID SQL_CHILD_NUMBER

19 第 1 章 章名章名章名章名章名 360 SMSNP bgg2p17bx7y1v SYS 6zn53xvm7zth SMSNP 1ug8zqhw906tb SMSNP fx3kcsrcm7hcb SMSNP 4t47xyz571hrh SMSNP 1ug8zqhw906tb SYS 65vuzhm491wk SMSNP 1bkv8cyq1c9f gd6b1r53yt88 0 注意, 某些 SQL 的执行计划可能老化无法获取, 以下是通过 v$session 信息获得的一个 SQL 执行计划 : SQL> SELECT plan_table_output 2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bgg2p17bx7y1v',2,'ALL')); PLAN_TABLE_OUTPUT SQL_ID bgg2p17bx7y1v, child number SELECT COUNT(*) FROM "NP_IO_MT" "A1" WHERE "A1"."MSGDATE">SYSDATE@! AND "A1"."HYSERVICECODE"='QWOZQX' AND EXISTS (SELECT 0 FROM "NP_IO_MO" "A2" WHERE "A2"."PHONE"="A1"."PHONE" AND "A2"."MSGDATE">SYSDATE@! AND "A2"."HYSERVICECODE"='HZXDBM') Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop SELECT STATEMENT 9 (100) 1 SORT AGGREGATE NESTED LOOPS SEMI (0) 00:00:01 3 PARTITION RANGE ITERATOR (0) 00:00:01 KEY 19 * 4 TABLE ACCESS BY LOCAL INDEX ROWID NP_IO_MT (0) 00:00:01 KEY 19 * 5 INDEX RANGE SCAN IND_IO_MT_MSGDATE 6 3 (0) 00:00:01 KEY 19 6 PARTITION RANGE ITERATOR (0) 00:00:01 KEY 19 * 7 TABLE ACCESS BY LOCAL INDEX ROWID NP_IO_MO (0) 00:00:01 KEY 19 * 8 INDEX RANGE SCAN IND_IO_MO_MSGDATE 9 2 (0) 00:00:01 KEY Query Block Name / Object Alias (identified by operation id):

20 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 1 - SEL$5DA710D3 4 - SEL$5DA710D3 / A1@SEL$1 5 - SEL$5DA710D3 / A1@SEL$1 7 - SEL$5DA710D3 / A2@SEL$2 8 - SEL$5DA710D3 / A2@SEL$2 Predicate Information (identified by operation id): filter("a1"."hyservicecode"='qwozqx') 5 - access("a1"."msgdate">sysdate@! ) 7 - filter(("a2"."hyservicecode"='hzxdbm' AND "A2"."PHONE"="A1"."PHONE")) 8 - access("a2"."msgdate">sysdate@! ) Column Projection Information (identified by operation id): (#keys=0) COUNT(*)[22] 3 - "A1"."PHONE"[VARCHAR2,40] 4 - "A1"."PHONE"[VARCHAR2,40] 5 - "A1".ROWID[ROWID,10] 8 - "A2".ROWID[ROWID,10] 通过 AWR 获取 SQL 执行计划 前面介绍的 DBMS_XPLAN 包还有另外一个功能, 通过 dbms_xplan.display_awr 函数来获 取 AWR 中的 SQL 执行计划 这个函数的主要参数需要是 SQL_ID: FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE 参数名称 类型 输入 / 输出默认值? SQL_ID VARCHAR2 IN PLAN_HASH_VALUE NUMBER(38) IN DEFAULT DB_ID NUMBER(38) IN DEFAULT FORMAT VARCHAR2 IN DEFAULT 通过 AWR 生成的报告关于 SQL 部分都会包含 SQL_ID 一项内容, 通过这个 SQL_ID 就 可以查询 AWR 中的 SQL 执行计划, 以下是 Oracle10g AWR 报告的一个输出片段 : Elapsed CPU Elap per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id , dbqwa8xts1g3 insert into HY_SIMULATESRC (DATTIMESTAMP, VC2SRCDESC, VC2MOBILE, VC2IMSI, VC2MOB ILEIP, VC2MEMO, VC2PLANKEY, VC2SRCTYPE, VC2UA, VC2PROVID, NUMGUID) values (:1, : 2, :3, :4, :5, :6, :7, :8, :9, :10, :11) 20

21 第 1 章 章名章名章名章名章名 获得 SQL 的 SQL_ID, 就可以通过 DBMS_XPLAN 来输出执行计划, 以下输出的 SQL 具有多个子指针, 执行计划各不相同, 在使用绑定变量的情况下,Oracle 数据库也会通过绑定变量 Peeking 来获得更为准确的执行 : SQL> select * from table(dbms_xplan.display_awr('072t81cu41xfj')); PLAN_TABLE_OUTPUT SQL_ID 072t81cu41xfj SELECT DECODE(COUNT(*), 0, 1, 0) FROM MGMT_SEVERITY WHERE TARGET_GUID = :B3 AND METRIC_GUID = :B2 AND KEY_VALUE = :B1 Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 1 (100) 1 SORT AGGREGATE INDEX RANGE SCAN SEVERITY_PRIMARY_KEY (0) 00:00: SQL_ID 072t81cu41xfj SELECT DECODE(COUNT(*), 0, 1, 0) FROM MGMT_SEVERITY WHERE TARGET_GUID = :B3 AND METRIC_GUID = :B2 AND KEY_VALUE = :B1 Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 24 (100) 1 SORT AGGREGATE INDEX FAST FULL SCAN SEVERITY_PRIMARY_KEY (0) 00:00: 已选择 30 行 通过 dbms_xplan.display_awr 函数获取的 SQL 执行计划来自 dba_hist_sql_plan 视图, 通过历史数据记录, 甚至一些被老化的 SQL 执行计划仍然可以被查询到 21

22 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 9.3 捕获问题 SQL 解决过度 CPU 消耗问题 在生产环境中, 我们可能会经常遇到 CPU 过度使用而影响系统性能或正常运行的问题 大多数情况下, 系统的性能问题都是由不良 SQL 代码引起的, 那么作为 DBA, 怎样发现和解 决这些 SQL 问题就显得尤为重要 本案例的系统环境为 : 操作系统 : Solaris8 数据库版本 : 断 问题描述 : 业务及开发人员报告系统运行缓慢, 已经影响业务系统正常使用 请求协助诊 使用 vmstat 检查系统当前情况 首先登陆数据库主机, 检查当前系统状况 使用 vmstat 检查, 发现 CPU 资源已经耗尽, 大量任务位于运行队列 : bash-2.03$ vmstat 3 procs memory page disk faults cpu r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id 对于 vmstat 的用法及输出, 简要说明一下 :vmstat 是 Unix 上一个常用的工具, 可以帮助 我们查看系统内存及 CPU 使用情况 Vmstat 最常用的两个参数是 t [n]: t n 表示采样间隔 表示采样次数 例如 :vmstat 5 5, 表示在 T(5) 秒时间内进行 N(5) 次采样 对于前三列 procs 输出, 分别代表以下含义 : 22

23 第 1 章 章名章名章名章名章名 r--> 指运行队列中的进程数, 如果这个参数经常超过 CPU 数量可能说明 CPU 存在瓶颈 b--> 因为 IO 被 Block 的进程数 w-->idle 的被 swap 的进程数 最后一项 CPU 标识系统 CPU 资源的分配和使用情况, 最后一列 Idle 值通常被用来衡量系统 CPU 的空闲情况 本案例当时, 系统 CPU 资源已经耗尽,Idle 为 0, 并且运行队列大量进程排队等待 使用 Top 工具辅助诊断 通过 Top 工具, 可以查看进程 CPU 耗用情况, 如果存在进程异常, 可以通过 Top 定位, 为进一步诊断提供依据 对于本案例, 观察进程 CPU 耗用, 发现没有明显过高 CPU 使用的进程 $ top last pid: 28313; load averages: 99.90, , :28: processes: 186 sleeping, 99 running, 2 zombie, 9 on cpu CPU states: 0.0% idle, 96.5% user, 3.5% kernel, 0.0% iowait, 0.0% swap Memory: 4096M real, 1404M free, 2185M swap in use, 5114M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND oracle8i M 1309M run 0: % oracle oracle8i M 1306M sleep 0: % oracle oracle8i M 1304M run 0: % oracle oracle8i M 1305M run 0: % oracle oracle8i M 1309M run 0: % oracle oracle8i M 1310M run 0: % oracle oracle8i M 1306M cpu/1 0: % oracle oracle8i M 1306M run 1: % oracle oracle8i M 1309M run 0: % oracle 从 Top 的输出中我们发现有大量进程处于 running 的运行状态,CPU 消耗很平均, 单进程 消耗大约在 1% 左右, 基本可以排除个别进程异常导致 CPU 问题的可能 ( 关于单进程异常 CPU 消耗问题可以参考第四章中的解决方法 ) 检查进程数量 对于一个生产数据库系统, 稳定运行的进程数量通常是可知的 23

24 书名书名书名书名书名书名书名书名书名书名书名书名书名书名提示 : 对于稳定运行的生产系统, 数据库的运行状况通常是稳定的, 如果你绘制出性能曲线, 你会发现每个星期的曲线几乎是可以重合的, 对数据库系统的运行状况及性能指标具有充分认识和了解是必须的 看一下当前系统的进程数量, 从而进行比较判断 : bash-2.03$ ps -ef grep ora wc -l 258 bash-2.03$ ps -ef grep ora wc -l 275 bash-2.03$ ps -ef grep ora wc -l 274 bash-2.03$ ps -ef grep ora wc -l 278 bash-2.03$ ps -ef grep ora wc -l 277 bash-2.03$ ps -ef grep ora wc -l 366 发现此时系统存在大量 Oracle 进程, 大约在 300 左右, 大量进程消耗了几乎所有 CPU 资源, 而正常情况下 Oracle 连接数应该在 100 左右 由此, 可以做出基本判断, 是数据库或应用出现问题, 导致进程任务无法完成, 不断累积, 从而出现大量队列等待 这些等待在数据库中应该有具体的体现, 接下来需要登陆数据库进行检查了 登陆数据库 我们判断数据库可能经历了等待, 那么 Oracle 数据库提供了相关视图供我们查询和发现 问题,v$session_wait 是首先值得我们关注的 查询 v$session_wait 获取各进程等待事件 : SQL> select sid,event,p1,p1text from v$session_wait; SID EVENT P1 P1TEXT latch free E+10 address 1 pmon timer 300 duration 2 rdbms ipc message 300 timeout 140 buffer busy waits 17 file# 66 buffer busy waits 17 file# 10 db file sequential read 17 file# 18 db file sequential read 17 file# 54 db file sequential read 17 file# 49 db file sequential read 17 file# 48 db file sequential read 17 file# 24

25 第 1 章 章名章名章名章名章名 46 db file sequential read 17 file# 45 db file sequential read 17 file# 234 db file sequential read 17 file# 233 db file sequential read 17 file# 230 db file sequential read 17 file# 333 db file sequential read 17 file# 330 db file scattered read 17 file# 310 db file scattered read 17 file# 244 rows selected. 对于本案例, 我们发现存在大量 db file scattered read 及 db file sequential read 等待 显然 全表扫描等操作成为系统最严重的性能影响因素 捕获相关 SQL 确定这些进程因为数据访问产生了等待, 我们考虑捕获这些 SQL 以发现问题 这里用到了我的以下脚本 getsqlbysid.sql, 该脚本通过已知 session 的 sid, 联合 v$session v$sqltext 视图, 获得相关 session 正在执行的完整的 SQL 语句 SELECT sql_text FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.sid = '&sid') ORDER BY piece ASC / 使用该脚本, 通过从 v$session_wait 中获得的等待全表或索引扫描的进程 SID, 可以捕获可能存在问题的 sql 语句 : Enter value for sid: 18 old 5: where b.sid='&sid' new 5: where b.sid='18' SQL_TEXT select i.vc2title,i.numinfoguid from hs_info i where i.intenab ledflag = 1 and i.intpublishstate = 1 and i.datpublishdate <= sysdate and i.numcatalogguid = 2047 order by i.datpublishdate d 25

26 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 esc, i.numorder desc 对几个进程进行跟踪, 分别得到以上 SQL 语句, 这些 SQL 可能就是问题产生的根源 ( 以上语句如果良好编码应该使用绑定变量, 但是目前这个不是我们关心的 ) 使用该应用用户连接, 通过 autotrace 功能检查以上 SQL 的执行计划 : SQL> set autotrace trace explain SQL> select i.vc2title,i.numinfoguid 2 from hs_info i where i.intenabledflag = 1 3 and i.intpublishstate = 1 and i.datpublishdate <=sysdate 4 and i.numcatalogguid = order by i.datpublishdate desc, i.numorder desc ; Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=228 Card=1 Bytes=106) 1 0 SORT (ORDER BY) (Cost=228 Card=1 Bytes=106) 2 1 TABLE ACCESS (FULL) OF 'HS_INFO' (Cost=218 Card=1 Bytes=106) SQL> select count(*) from hs_info; COUNT(*) 通过执行计划, 我们看到以上查询使用了全表扫描, 而该表这里有 22 万记录, 全表扫描已经不再适合 通常对于小表,Oracle 建议通过全表扫描进行数据访问, 对于大表则应该通过索引以加快数据查询, 当然如果查询要求返回表中大部分或者全部数据, 那么全表扫描可能仍然是最好的选择 从 V$SYSSTAT 视图中, 我们可以查询得到关于全表扫描的系统统计信息 : SQL> col name for a30 SQL> select name,value from v$sysstat 2 where name in ('table scans (short tables)','table scans (long tables)'); NAME VALUE table scans (short tables) 828 table scans (long tables) 101 其中 table scans (short tables) 指对于小表的全表扫描的此时 ;table scans (long tables) 指对于 大表的全表扫描的次数 从 Statspack 的报告中, 我们也可以找到这部分信息 : Instance Activity Stats for DB: CELLSTAR Instance: ora8i Snaps: 20 - Statistic Total per Second per Trans table scan blocks gotten 38,228,

27 第 1 章 章名章名章名章名章名 table scan rows gotten 546,452, table scans (direct read) 5, table scans (long tables) 5, table scans (rowid ranges) 5, table scans (short tables) 1,185, 通常, 如果一个数据库的 table scans (long tables) 过多, 那么 db file scattered read 等待事件 可能同样非常显著, 和以上数据来自同一个 report 的 Top5 等待事件就是如此 : Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time log file parallel write 1,436,993 1,102, log buffer space 16, , log file sync 1,413, , control file parallel write 329, , db file scattered read 425, , 数据库内部, 很多信息和现象都是紧密相关的, 只要我们加深对于数据库的了解, 在优 化和诊断数据库问题时就能够得心应手 Oracle 通过一个内部参数 _small_table_threshold 来定义大表和小表的界限 缺省的该参数 等于 2% 的 Buffer 数量, 如果表的大小小于该参数定义,Oracle 认为该表为小表, 否则 Oracle 认为该表为大表 我们看一下 Oracle9iR2 中的情况 : Enter value for par: small old 6: AND x.ksppinm LIKE '%&par%' new 6: AND x.ksppinm LIKE '%small%' NAME VALUE DESCRIB _small_table_threshold 200 threshold level of table size for direct reads 以上数据库中,200 正好约为 Buffer 数量的 2%: SQL> show parameter db_cache_size NAME TYPE VALUE db_cache_size big integer SQL> select ( /8192)*2/100 from dual; ( /8192)*2/ 所以要区分大小表 (Long/Short) 是因为全表扫描可能引起 Buffer Cache 的抖动, 缺省的 大表的全表扫描会被置于 LRU 的末端, 以期尽快老化, 减少 Buffer 的占用 从 Oracle8i 开始, 27

28 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 Oracle 的多缓冲池管理技术 (Default/Keep/Recycle 池 ) 给了我们另外一个选择, 对于不同大小 不同使用频率的数据表, 从建表之初就可以指定其存储 Buffer, 以使得内存使用更加有效 让我们继续以上的案例, 在实际处理中, 我们检查全表扫描的数据表, 发现存在以下索引 : SQL> select index_name,index_type from user_indexes where table_name='hs_info'; INDEX_NAME INDEX_TYPE HSIDX_INFO1 FUNCTION-BASED NORMAL HSIDX_INFO_SEARCHKEY DOMAIN PK_HS_INFO NORMAL 检查索引键值 : SQL> select index_name,column_name 2 from user_ind_columns where table_name ='HS_INFO'; INDEX_NAME COLUMN_NAME HSIDX_INFO1 NUMORDER HSIDX_INFO1 SYS_NC00024$ HSIDX_INFO_SEARCHKEY VC2INDEXWORDS PK_HS_INFO NUMINFOGUID SQL> desc hs_info Name Null? Type NUMINFOGUID NOT NULL NUMBER(15) NUMCATALOGGUID NOT NULL NUMBER(15) INTTEXTTYPE NOT NULL NUMBER(38) VC2TITLE NOT NULL VARCHAR2(60) VC2AUTHOR VARCHAR2(100) NUMPREVINFOGUID NUMBER(15) NUMNEXTINFOGUID NUMBER(15) NUMORDER NOT NULL NUMBER(15) 创建新的索引以消除全表扫描 检查发现在 numcatalogguid 字段上并没有索引, 该字段具有很好的区分度, 考虑在该字段创建索引以消除全表扫描 SQL> create index hs_info_numcatalogguid on hs_info(numcatalogguid); Index created. 28

29 第 1 章 章名章名章名章名章名 SQL> set autotrace trace explain SQL> select i.vc2title,i.numinfoguid 2 from hs_info i where i.intenabledflag = 1 3 and i.intpublishstate = 1 and i.datpublishdate <=sysdate 4 and i.numcatalogguid = order by i.datpublishdate desc, i.numorder desc ; Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=106) 1 0 SORT (ORDER BY) (Cost=12 Card=1 Bytes=106) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'HS_INFO' (Cost=2 Card=1 Bytes=106) 3 2 INDEX (RANGE SCAN) OF 'HS_INFO_NUMCATALOGGUID' (NON-UNIQUE) (Cost=1 Card=1) 观察系统状况 原大量等待消失 SQL> select sid,event,p1,p1text from v$session_wait where event not like 'SQL%'; SID EVENT P1 P1TEXT pmon timer 300 duration 2 rdbms ipc message 300 timeout 3 rdbms ipc message 300 timeout 6 rdbms ipc message timeout 59 rdbms ipc message 6000 timeout 118 rdbms ipc message 6000 timeout 275 rdbms ipc message timeout 147 rdbms ipc message 6000 timeout 62 rdbms ipc message 6000 timeout 11 rdbms ipc message timeout 4 rdbms ipc message 300 timeout 305 db file sequential read 17 file# 356 db file sequential read 17 file# 19 db file scattered read 17 file# 5 smon timer 300 sleep time 15 rows selected. 29

30 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 在另外的 session 里, 持续观察的 CPU 使用情况 : bash-2.03$ vmstat 3 procs memory page disk faults cpu r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id 以上为创建索引之前部分 ---- 以下为创建索引之后部分,CPU 使用率恢复正常 procs memory page disk faults cpu r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id 至此, 此问题得以解决 性能何以提高 回答这个问题似乎是多余的, 我只想重申一点 : 有效的降低 SQL 的逻辑读是 SQL 优化的基本原则之一 我们来比较一下前后两种执行方式的逻辑读取及性能差异 a. 全表扫描的性能 SQL> select i.vc2title,i.numinfoguid 2 from hs_info i where i.intenabledflag = 1 3 and i.intpublishstate = 1 and i.datpublishdate <=sysdate 4 and i.numcatalogguid = order by i.datpublishdate desc, i.numorder desc ; 352 rows selected. Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=541 Card=1 Bytes=106) 30

31 第 1 章 章名章名章名章名章名 1 0 SORT (ORDER BY) (Cost=541 Card=1 Bytes=106) 2 1 TABLE ACCESS (FULL) OF 'HS_INFO' (Cost=531 Card=1 Bytes=106) Statistics recursive calls 25 db block gets 3499 consistent gets 258 physical reads 0 redo size.. 2 sorts (memory) 0 sorts (disk) 352 rows processed b. 使用索引的性能 SQL> select i.vc2title,i.numinfoguid 2 from hs_info i where i.intenabledflag = 1 3 and i.intpublishstate = 1 and i.datpublishdate <=sysdate 4 and i.numcatalogguid = order by i.datpublishdate desc, i.numorder desc; 352 rows selected. Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=106) 1 0 SORT (ORDER BY) (Cost=12 Card=1 Bytes=106) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'HS_INFO' (Cost=2 Card=1 Bytes=106) 3 2 INDEX (RANGE SCAN) OF 'HS_INFO_NUMCATALOGGUID' (NON-UNIQUE) (Cost=1 Card=1) Statistics recursive calls 0 db block gets 89 consistent gets 0 physical reads 0 redo size. 1 sorts (memory) 0 sorts (disk) 352 rows processed 31

32 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 consistent gets 从 3499 到 89, 我们看到性能得到了巨大的提高. 结语 : 通常, 开发人员很少注意 SQL 代码的效率, 他们更着眼于功能的实现 至于性能问题通常被认为是次要的, 而且在应用系统开发初期, 由于数据库数据量较少, 对于查询 SQL 语句等, 不容易体会出各种 SQL 句法的性能差异 但是一旦这些应用作为生产系统上线运行, 随着数据库中数据量的增加, 大量并发访问, 系统的响应速度可能就会成为系统需要解决的最主要的问题之一 在少量用户下性能可以接受的 SQL, 可能在大量用户并发的条件下就会成为性能瓶颈 在我这个案例中, 开发人员很难相信仅只一条 SQL 语句就导致了整个数据库的性能下降 然而事实就是如此, 一条低效的 SQL 语句就可能毁掉你的数据库, 所以在系统设计及开发过程中, 你必须考虑到诸多细节, 严格的测试也是提早发现问题的有效方法 如果不幸以上环节都被忽略, 那么,DBA( 也许就是你 ) 就是最后的一环, 你必须能够快速的诊断并解决各种复杂问题 9.3 使用 SQL_TRACE/10046 事件进行数据库诊断 SQL_TRACE/10046 事件是 Oracle 提供的用于进行 SQL 跟踪的手段, 是强有力的辅助诊断工具. 在日常的数据库问题诊断和解决中,SQL_TRACE 是非常常用的方法 当在数据库中启用 SQL_TRACE 或者设置 事件之后,Oracle 将会启动内核跟踪程序, 持续记录会话的相关信息, 并写入到相应 trace 文件中 跟踪记录的内容包括 SQL 的解析过程 SQL 的执行计划 绑定变量的使用 会话中发生的等待事件等 在本章之前, 我们多次提到和使用过 sql_trace/10046 功能, 本节就 SQL_TRACE/10046 事件的使用作简单探讨, 并通过具体案例对 sql_trace 的使用进行说明 SQL_TRACE 及 事件的基础介绍 首先我们先对 SQL_TRACE 及 事件进行一些基本介绍, 以使大家能对这个工具有所了解, 并熟悉其使用方法 SQL_TRACE 说明 我们先来关注一下 Oracle 官方文档 (Oracle9iR2 文档 ) 对 SQL_TRACE 的说明 SQL_TRACE: 参数类型布尔型缺省值 false 参数类别静态 32

33 第 1 章 章名章名章名章名章名 取值范围 true false SQL_TRACE 的取值可以启用或禁用 SQL trace 工具 设置 sql_trace 为 true 可以收集信息 用于性能优化 DBMS_SYSTEM 包也可以用于实现同样的功能 警告 : 设置初始化参数 SQL_TRACE 为 true 会对整个实例产生严重的性能影响, 所以在产品环境中如非必要, 确保不要设置这个参数 如果只是对特定的 session 启用跟踪, 可以使用 ALTER SESSION 或 DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION 来设置 如果必须在数据库级启用 SQL_TRACE, 你需要保证以下条件以最小化性能影响 : 1. 至少保证有 25% 的 CPU idle 2. 为 USER_DUMP_DEST 分配足够的空间 3. 条带化磁盘以减轻 IO 负担 注意 : 如果你使用 ALTER SESSION SET SQL_TRACE 来修改 session 级设置, 这个设置并不会在 v$parameter 动态性能视图中体现出来, 所以, 这个参数仍然被认为是静态参数 在使用 SQL_TRACE 之前, 几个注意事项需要简单说明一下 : 1. 初始化参数 TIMED_STATISTICS 参数 TIMED_STATISTICS 最好设置为 True, 否则一些重要信息不会被收集 2. 设置 MAX_DUMP_FILE_SIZE 该参数设置跟踪文件的大小限制, 可以以操作系统块为单位设置 ; 也可以以 K M 为单位设置 ; 如果跟踪的信息较多, 可以干脆设置为 UNLIMITED 从 9i 开始, 该参数缺省值为 UNLIMITED 在 Session 级可以设置如下 : SQL> alter session set MAX_DUMP_FILE_SIZE=unlimited; Session altered. 记住前面的警告, 你需要足够的空间保存 trace 文件, 跟踪过程产生的 Trace 文件可能远远大于你的想象 SQL_TRACE 可以作为初始化参数在全局启用, 也可以通过命令行方式在具体 session 启用 1. 在全局启用 SQL_TRACE 在参数文件 (pfile/spfile) 中指定 : sql_trace = true 在全局启用 SQL_TRACE 会导致所有进程的活动被跟踪, 包括后台进程及所有用户进程, 这通常会导致比较严重的性能问题, 所以在生产环境中要谨慎使用. 33

34 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 提示 : 通过在全局启用 sql_trace, 我们可以跟踪到所有后台进程的活动, 很多在文档中的抽象说 明, 通过跟踪文件的实时变化, 我们可以清晰的看到各个进程之间的紧密协调. 2. 在当前 session 级设置大多数时候我们使用 sql_trace 跟踪当前进程. 通过跟踪当前进程可以发现当前操作的后台数据库递归活动 ( 这在研究数据库新特性时尤其有效 ), 研究 SQL 执行, 发现后台错误等. 在 session 级启用和停止 sql_trace 方式如下 : 启用当前 session 的跟踪 : SQL> alter session set sql_trace=true; Session altered. 此时的 SQL 操作将被跟踪 : SQL> select count(*) from dba_users; COUNT(*) 结束跟踪 : SQL> alter session set sql_trace=false; Session altered. 3. 跟踪其他用户进程在很多时候我们需要跟踪其他用户的进程, 而不是当前用户, 这可以通过 Oracle 提供的系统包 DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION 来完成 SET_SQL_TRACE_IN_SESSION 过程序要提供三个参数 : SQL> desc dbms_system PROCEDURE SET_SQL_TRACE_IN_SESSION Argument Name Type In/Out Default? SID NUMBER IN SERIAL# NUMBER IN SQL_TRACE BOOLEAN IN 通过查询 v$session 我们可以获得 sid serial# 等信息 获得进程信息, 选择需要跟踪的进程, 设置跟踪 : SQL> select sid,serial#,username from v$session where username is not null; SID SERIAL# USERNAME SYS EYGLE 设置跟踪 : 34

35 第 1 章 章名章名章名章名章名 SQL> exec dbms_system.set_sql_trace_in_session(9,437,true) PL/SQL procedure successfully completed.. 可以等候片刻, 跟踪 session 执行任务, 捕获 sql 操作 如果确定某个功能或模块存在问题, 可以在此期间有意识的调用 以确保可以捕获问题代码. 停止跟踪 : SQL> exec dbms_system.set_sql_trace_in_session(9,437,false) PL/SQL procedure successfully completed. 如果要对其他用户的参数进行设置, 我们可能需要用到 DBMS_SYSTEM 包中的另外一个 过程 : SQL> desc dbms_system... PROCEDURE SET_INT_PARAM_IN_SESSION Argument Name Type In/Out Default? SID NUMBER IN SERIAL# NUMBER IN PARNAM VARCHAR2 IN INTVAL BINARY_INTEGER IN... 比如设置 MAX_DUMP_FILE_SIZE 等参数, 可以参考如下 : SQL> select sid,serial#,username from v$session where username is not null; SID SERIAL# USERNAME EYGLE SQL> begin 2 sys.dbms_system.set_bool_param_in_session(18, 1605, 'timed_statistics', true); 3 sys.dbms_system.set_int_param_in_session(18, 1605, 'max_dump_file_size', ); 4 sys.dbms_system.set_sql_trace_in_session(18, 1605, true); 5 end; 6 / PL/SQL procedure successfully completed. DBMS_SYSTEM 包功能强大, 值得仔细研究 35

36 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 事件说明 事件是 Oracle 提供的内部事件, 是对 SQL_TRACE 的增强 事件可以设置以下四个级别 : 1 - 启用标准的 SQL_TRACE 功能, 等价于 sql_trace 4 - Level 1 加上绑定值 (bind values) 8 - Level 1 + 等待事件跟踪 12 - Level 1 + Level 4 + Level 8 类似 sql_trace,10046 事件可以在全局设置, 也可以在 session 级设置 1. 在全局设置在参数文件中增加 : event="10046 trace name context forever,level 12" 此设置对所有用户的所有进程生效 包括后台进程. 2. 对当前 session 设置通过 alter session 的方式修改, 需要 alter session 的系统权限 : SQL> alter session set events '10046 trace name context forever, level 8'; Session altered. SQL> alter session set events '10046 trace name context off'; Session altered. 3. 对其他用户 session 设置通过 DBMS_SYSTEM.SET_EV 系统包来实现 SQL> desc dbms_system... PROCEDURE SET_EV Argument Name Type In/Out Default? SI BINARY_INTEGER IN SE BINARY_INTEGER IN EV BINARY_INTEGER IN LE BINARY_INTEGER IN NM VARCHAR2 IN 其中的参数 SI SE 来自 v$session 视图, 查询获得需要跟踪的 session 信息 : SQL> select sid,serial#,username from v$session where username is not null; SID SERIAL# USERNAME SYS EYGLE 执行跟踪 : 36