Oracle笔记-优化策略与工具

　　 

　　第 10 章 优化策略与工具

　　10.1 标识问题

　　10.2 我的方法

　　10.3 绑定变量与分析（再次）

　　不使用绑定变量将增加语句分析，除了消耗CPU时间外，还会增加字典高速缓存上的闩锁。

　　显示会话等待的事件：V$SESSION_EVENT.具体事件名和含义可以参考Oracle Reference Manual的附录Oracle Wait Events.

　　CURSOR_SHARING

　　CURSOR_SHARING参数缺省为EXACT，若指定为FORCE，则优化器可能将语句中所有的常数转换为绑定变量，虽然减少了语句分析，但是也会带来如下副作用：

　　优化器可供利用的信息可能减少，从而改变执行路径，例如条件中对于某个特定值索引有较好的选择性，改为绑定变量时优化器并不会发现这一点。

　　查询输出格式发生变化。虽然返回的数据长度不变，但列的长度可能改变。例如对于SELECT id， ‘tom’ name from emp； name应该为VARCHAR2（3），但是由于‘tom’被改为绑定变量，则可能name的显示长度变为32.

　　查询计划更难评估。由于语句的改变，EXPLAIN PLAN看到的查询与数据库看到的可能不一致，从而使AUTOTRACE等的输出与实际执行路径不一致。

　　因此，完善的应用系统不应当依靠CURSOR_SHARING来提高效率，仅能作为权宜之计。

　　10.4 SQL_TRACE， TIMED_STATISTICS与TKPROF

　　TIMED_STATISTICS并不会对系统产生过大负担，因此建议设置为TRUE.

　　启动跟踪

　　SQL_TRACE可在系统或会话级激活。激活后跟踪文件将产生至init.ora参数USER_DUMP_DEST（专用服务器）或 BACKGROUND_DUMP_DEST（MTS）指定的目录。而文件大小通过MAX_DUMP_FILE_SIZE控制，其设置有如下三种方法：

　　仅数值：以OS块为单位；

　　数值+K/M：指定文件绝对大小；

　　UNLIMITED：无上限。

　　一般只需要设置50-100M就足够了。

　　激活SQL_TRACE的几种常用方式如下：

　　ALTER SESSION SET SQL_TRACE=TRUE|FALSE；

　　SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION 这里我们需要指定SID和SERIAL#（参考V$SESSION）；

　　ALTER SESSION SET EVENTS. 可获得更详细的信息。

　　此外也可通过DBMS_SUPPORT包，相当于EVENTS跟踪的一个界面，但此包需要Oracle人员支持，非标配。

　　随着WEB服务方式的普及，往往一个数据库会话很短，难以单独跟踪，对此，我们可以根据用户，在数据库级建立触发器：

CREATE OR REPLACE TRIGGER logon_trigger

AFTER LOGON ON DATABASE

BEGIN

  IF ( USER= ‘TKYTE’ ) THEN

    EXECUTE IMMEDIATE ‘ALTER SESSION SET EVENTS ‘ ’10046 TRACE NAME CONTEXT FOREVER, LEVEL 4’ ‘ ’;

END IF;

END;/
　　使用并解析TKPROF输出

　　1.        激活SQL_TRACE后，通过如下查询检查SPID：

　　SELECT a.spid  FROM v$process a， v$session b

　　WHERE a.addr = b.paddr

　　AND b.audsid = userenv（‘sessionid’）；

　　此SPID就包含在跟踪文件的文件名中。

　　UNIX系统中，若你不在Oracle的管理组中，则生成的跟踪文件所在目录可能无法访问，此时需要设定init.ora参数_trace_files_public = true .

　　2.        TKPROF语法：  TKPROF *.trc *.txt

　　其他用法可以直接运行TKPROF查看。一般常用选项就是-sort，可以根据某些参数值排序。

　　3.        对跟踪文件输出的一些解释：

　　i. 行：

　　PARSE阶段：包括了软分析（在SHARED_POOL中找到语句）和硬分析；

　　EXECUTE阶段：对SELECT几乎为空，对UPDATE则几乎是全部工作的体现；

　　FETCH阶段：对SELECT是几乎所有的工作，对UPDATE则为空。

　　ii. 列：

　　COUNT：事件发生的次数；

　　CPU：消耗的CPU时间（CPU秒）；

　　ELAPSED：总体运行时间；

　　DISK：磁盘物理I/O；

　　QUERY：一致读模式访问的块数，也包括了从回滚段读取的块数；

　　CURRENT：访问的当前信息数据块（而不是一致读模式），例如SELECT时读取数据字典内容，修改时也需要访问数据字典内容以写。

　　ROWS：所涉及的行数。

　　4.        需要注意的现象：

　　i. 高的PARSE COUNT/EXECUTE COUNT（接近100%），且EXECUTE COUNT大于1

　　即执行语句时分析的次数，如果过高，可能是软分析也过多了，对一个会话，应该是分析一次反复执行。

　　ii. 对几乎所有SQL，EXECUTE COUNT都是1

　　可能没有使用绑定变量。在一个真实应用中，应该很少看到不同的SQL，同一个SQL应执行多次。

　　iii. CPU和ELAPSED时间相差较大

　　说明花了很长时间等待一个事件，例如磁盘I/O、锁等。

　　iv. （FETCH COUNT）/（ROWS FETCHED）比例高

　　没有很好的使用批量提取。批量提取数据的方法是和语言/API相关的，例如Pro* C中需要使用prefetch=NN预编译，Java/JDBC下可以调用SETROWPREFETCH方法，PL/SQL可以在SELECT INTO中直接使用BULK COLLECT.而SQL* PLUS缺省为每次取15行。

　　v. 极大的DISK COUNT

　　较难推断，但若DISK COUNT = QUERY + CURRENT MODE BLOCK COUNT，则说明几乎所有数据都来自磁盘。此时需要考虑SGA大小和此查询效率。

　　vi. 极大的QUERY COUNT或CURRENT COUNT

　　SQL工作量很大，需要注意。

　　5.        EXPLAIN PLAN问题

　　跟踪文件中显示的是真正执行的路径。TKPROF也支持EXPLAIN=XXX/XXX选项，不建议使用，其输出是转换跟踪文件当时优化器选择的执行路径，并是利用数据库的EXPLAIN工具，与真实路径时不完全一致的。

　　使用与解析原始跟踪文件

　　1.        EVENTS跟踪

　　ALTER SESSION SET EVENTS ‘10046 trace name context forever， level N’；

　　N=1 同标准SQL_TRACE；

　　N=4 增加获得绑定变量值；

　　N=8 增加获得查询级的等待事件；

　　N=12 增加获得绑定变量值和查询级的等待事件。

　　2.        原始跟踪文件分段解析

　　文件头含有时间、数据库版本、OS版本、实例名等。

　　APPNAME mod=‘%s’ mh=%lu act=‘%s’ ah=%lu

　　mod

　　传入DBMS_APPLICATION_INFO的模块名

　　mh

　　模块哈希值

　　act

　　传入DBMS_APPLICATION_INFO的动作

　　ah

　　动作哈希值

　　Parsing in Cursor #%d dep=%d uid=%ld oct=%d lid=%ld tim=%ld hv=%ld ad=‘%s’

　　Cursor #

　　游标号。也可以用此值获知应用最大打开的游标数。

　　len

　　下面SQL语句的长度

　　dep

　　SQL语句的递归（recursive）深度

　　uid

　　当前方案的用户ID.注意，这并不一定和后面的lid一致，因为可以用

　　alter session set current_schema来修改分析时的方案

　　oct

　　Oracle命令类型（Oracle Command Type）

　　lid

　　用于安全性检查访问权限的用户ID

　　tim

　　定时器，1/100秒

　　ha

　　SQL语句的哈希ID

　　ad

　　V$SQLAREA中此SQL语句的ADDR列

　　EXEC Cursor#：c=%d，e=%d，p=%d，cr=%d，mis=%d，r=%d，dep=%d，og=%d，tim=%d

　　Cursor #

　　游标号

　　c

　　CPU时间，1/100秒

　　e

　　流逝（Elapsed）时间，1/100秒

　　p

　　物理读

　　cr

　　一致（QUERY模式）读（逻辑I/O）

　　cu

　　当前（Current）模式读（逻辑I/O）

　　mis

　　字典缓存中的游标不命中数，说明由于过期已从共享池中清除或从未进入共享池等，而不得不分析此语句

　　r

　　处理的行数

　　dep

　　SQL语句的递归深度

　　og

　　优化器目标：1=ALL ROWS 2=FIRST ROWS 3=RULE 4=CHOOSE

　　tim

　　定时器

　　与EXEC段类似的还有（即取代“EXEC”）：

　　PARSE

　　分析一个语句

　　FETCH

　　从一个游标取出数据行

　　UNMAP

　　用于显示在不需要时从中间结果释放临时段

　　SORT UMAP

　　同UNMAP，指排序段

　　WAIT Cursor#： nam=‘%s’ ela=%d p1=%ul p2=%ul p3=%ul

　　Cursor#

　　游标号

　　nam

　　等待事件名

　　ela

　　流逝时间，1/100秒

　　p1，p2，p3

　　等待事件特定的参数

　　以上为文件头与ALTER SESSION出现的跟踪信息。此后开始出现运行的SQL语句。

　　BIND段

　　cursor#

　　游标号

　　bind N

　　绑定位置，从0开始

　　dty

　　数据类型

　　mxl

　　绑定变量最大长度

　　mal

　　最大数组长度（当使用数组绑定或BULK操作时）

　　scl

　　数值范围（scale）

　　pre

　　精度（precision）

　　oacflg

　　内部标记。若此值为奇数，则绑定变量可能为NULL（允许为NULL）

　　oacfl2

　　内部标记续

　　size

　　缓冲区大小

　　offset

　　用于逐片（piecewise）绑定

　　bfp

　　绑定地址

　　bln

　　绑定缓冲区大小

　　avl

　　真实值长度

　　flag

　　内部标记

　　value

　　绑定值的字符串表示（如果可能，会是一个十六进制dump）

　　其中dty：SELECT text FROM ALL_VIEWS WHERE view_name = ‘USER_VIEWS’ 可看到一个将dty数值转换为字符串表示的函数。

　　此后我们可以看到WAIT段，即真正的等待事件。

　　对于ENQUEUE事件，实际就是锁。可用以下函数（传入参数为p1）判断类型：

CREATE OR REPLACE FUNCTION enqueue_decode(l_p1 in number) return varchar2

AS

  l_str varchar2(25);

BEGIN

  SELECT CHR(BITAND(l_p1, -16777216) / 16777215) ||

         CHR(BITAND(l_p1, 16711680) / 65535) || ‘  ‘ ||

         DECODE(BITAND(l_p1, 65535),

                  0, ‘No lock’,

                  1, ‘No lock’,

                  2, ‘Row-Share’,

                  3, ‘Row-Exclusive’,

                  4, ‘Share’,

                  5, ‘Share Row-Excl’,

                  6, ‘Exclusive’ )

  INTO l_str

  FROM DUAL;

      RETURN l_str;

END;

　　XCTEND（事务边界）段记录了提交等：

　　rlbk

　　回滚标记：0 提交 1 回滚

　　rd_only

　　只读标记：0 变化提交或回滚 1 事务只读

　　STAT段记录了运行时SQL真正的执行计划：

　　cursor #

　　游标号

　　id

　　执行计划行号

　　cnt

　　查询计划中流经此步骤的行数

　　pid

　　此步骤的父ID

　　pos

　　执行计划中的位置

　　obj

　　访问的对象的对象ID

　　op

　　操作的文本描述

　　PARSE ERROR段

　　len

　　SQL语句长度

　　dep

　　SQL语句递归深度

　　uid

　　分析的方案

　　oct

　　Oracle命令类型

　　lid

　　权限方案ID

　　tim

　　定时器

　　err

　　ORA错误代码

　　ERROR段

　　cursor #

　　游标数

　　err

　　ORA错误代码

　　tim

　　定时器

　　10.5 DBMS_PROFILER

　　10.6 StatsPack

　　10.7 V$表

　　V$EVENT_NAME

　　说明事件名和p1、p2、p3三个参数。

　　V$FILESTAT和V$TEMPSTAT

　　说明系统I/O概况。

　　V$LOCK

　　说明系统锁的情况。但注意Oracle并不在外部保存行锁，此视图可以找到TM（DML Enqueue）锁，即说明产生了行锁。

　　V$MYSTAT

　　说明当前会话的统计信息。需要V_$STATNAME（不用V$STATNAME，只是V_$STATNAME的一个同义词）和V_$MYSTAT上的SELECT权限。

　　CREATE VIEW MY_STATS AS

　　SELECT a.name， b.value

　　FROM V$STATNAME a， V$MYSTAT b

　　WHERE a.statistic# = b.statistic#；

　　V$OPEN_CURSOR

　　记录所有会话打开的游标。由于Oracle也会缓存已关闭的游标，因此此视图中也会包含已关闭的游标信息。

　　V$PARAMETER

　　说明了所有的init.ora参数。

　　V$SESSION

　　记录数据库的每个会话。需要对V_$SESSION的SELECT权限。

　　V$SESSION_EVENT

　　说明会话的事件情况。

　　V$SESSION_LONGOPS

　　记录CBO认为执行时间超过6秒的命令及进展。

　　V$SESSION_WAIT

　　记录所有正在等待某事件的会话及已等待时间。

　　V$SESSTAT

　　类似V$MYSTAT，但显示所有会话。

　　V$SESS_IO

　　说明会话的I/O信息

　　V$SQL和V$SQLAREA

　　记录SQL信息。建议使用V$SQL，V$SQLAREA是从V$SQL合并而来的视图，代价较高，对已经繁忙的系统是一个负担。

　　V$STATNAME

　　说明了统计号到统计名的映射。

　　V$SYSSTAT

　　记录实例层面的统计信息。当数据库关闭时才清空，也是StatsPack很多数据的来源。

　　V$SYSTEM_EVENT

　　记录实例层面的等待事件信息。也是StatsPack很多数据的来源。

原文转自：http://www.ltesting.net