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 s