第 章 优化策略与工具
标识问题
我的方法
绑定变量与分析（再次）
不使用绑定变量将增加语句分析除了消耗CPU时间外还会增加字典高速缓存上的闩锁
显示会话等待的事件V$SESSION_EVENT具体事件名和含义可以参考Oracle Reference Manual的附录Oracle Wait Events
CURSOR_SHARING
CURSOR_SHARING参数缺省为EXACT若指定为FORCE则优化器可能将语句中所有的常数转换为绑定变量虽然减少了语句分析但是也会带来如下副作用
优化器可供利用的信息可能减少从而改变执行路径例如条件中对于某个特定值索引有较好的选择性改为绑定变量时优化器并不会发现这一点
查询输出格式发生变化虽然返回的数据长度不变但列的长度可能改变例如对于SELECT id tom name from emp name应该为VARCHAR（）但是由于tom被改为绑定变量则可能name的显示长度变为
查询计划更难评估由于语句的改变EXPLAIN PLAN看到的查询与数据库看到的可能不一致从而使AUTOTRACE等的输出与实际执行路径不一致
因此完善的应用系统不应当依靠CURSOR_SHARING来提高效率仅能作为权宜之计
SQL_TRACE TIMED_STATISTICS与TKPROF
TIMED_STATISTICS并不会对系统产生过大负担因此建议设置为TRUE
启动跟蹤
SQL_TRACE可在系统或会话级激活激活后跟蹤文件将产生至initora参数USER_DUMP_DEST（专用服务器）或 BACKGROUND_DUMP_DEST（MTS）指定的目录而文件大小通过MAX_DUMP_FILE_SIZE控制其设置有如下三种方法
仅数值以OS块为单位
数值+K/M指定文件绝对大小
UNLIMITED无上限
一般只需要设置M就足够了
激活SQL_TRACE的几种常用方式如下
ALTER SESSION SET SQL_TRACE=TRUE|FALSE
SYSDBMS_SYSTEMSET_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 TRACE NAME CONTEXT FOREVER LEVEL ;
END IF;
END;/
使用并解析TKPROF输出
激活SQL_TRACE后通过如下查询检查SPID
SELECT aspid FROM v$process a v$session b
WHERE aaddr = bpaddr
AND baudsid = userenv（sessionid）
此SPID就包含在跟蹤文件的文件名中
UNIX系统中若你不在Oracle的管理组中则生成的跟蹤文件所在目录可能无法访问此时需要设定initora参数_trace_files_public = true
TKPROF语法 TKPROF *trc *txt
其他用法可以直接运行TKPROF查看一般常用选项就是sort可以根据某些参数值排序
对跟蹤文件输出的一些解释
i 行
PARSE阶段包括了软分析（在SHARED_POOL中找到语句）和硬分析
EXECUTE阶段对SELECT几乎为空对UPDATE则几乎是全部工作的体现
FETCH阶段对SELECT是几乎所有的工作对UPDATE则为空
ii 列
COUNT事件发生的次数
CPU消耗的CPU时间（CPU秒）
ELAPSED总体运行时间
DISK磁盘物理I/O
QUERY一致读模式访问的块数也包括了从回滚段读取的块数
CURRENT访问的当前信息数据块（而不是一致读模式）例如SELECT时读取数据字典内容修改时也需要访问数据字典内容以写
ROWS所涉及的行数
需要注意的现象
i 高的PARSE COUNT/EXECUTE COUNT（接近%）且EXECUTE COUNT大于
即执行语句时分析的次数如果过高可能是软分析也过多了对一个会话应该是分析一次反复执行
ii 对几乎所有SQLEXECUTE COUNT都是
可能没有使用绑定变量在一个真实应用中应该很少看到不同的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缺省为每次取行
v 极大的DISK COUNT
较难推断但若DISK COUNT = QUERY + CURRENT MODE BLOCK COUNT则说明几乎所有数据都来自磁盘此时需要考虑SGA大小和此查询效率
vi 极大的QUERY COUNT或CURRENT COUNT
SQL工作量很大需要注意
EXPLAIN PLAN问题
跟蹤文件中显示的是真正执行的路径TKPROF也支持EXPLAIN=XXX/XXX选项不建议使用其输出是转换跟蹤文件当时优化器选择的执行路径并是利用数据库的EXPLAIN工具与真实路径时不完全一致的
使用与解析原始跟蹤文件
EVENTS跟蹤
ALTER SESSION SET EVENTS trace name context forever level N
N= 同标准SQL_TRACE
N= 增加获得绑定变量值
N= 增加获得查询级的等待事件
N= 增加获得绑定变量值和查询级的等待事件
原始跟蹤文件分段解析
文件头含有时间数据库版本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
定时器/秒
ha
SQL语句的哈希ID
ad
V$SQLAREA中此SQL语句的ADDR列
EXEC Cursor#c=%de=%dp=%dcr=%dmis=%dr=%ddep=%dog=%dtim=%d
Cursor #
游标号
c
CPU时间/秒
e
流逝（Elapsed）时间/秒
p
物理读
cr
一致（QUERY模式）读（逻辑I/O）
cu
当前（Current）模式读（逻辑I/O）
mis
字典缓存中的游标不命中数说明由于过期已从共享池中清除或从未进入共享池等而不得不分析此语句
r
处理的行数
dep
SQL语句的递归深度
og
优化器目标=ALL ROWS =FIRST ROWS =RULE =CHOOSE
tim
定时器
与EXEC段类似的还有（即取代EXEC）
PARSE
分析一个语句
FETCH
从一个游标取出数据行
UNMAP
用于显示在不需要时从中间结果释放临时段
SORT UMAP
同UNMAP指排序段
WAIT Cursor# nam=%s ela=%d p=%ul p=%ul p=%ul
Cursor#
游标号
nam
等待事件名
ela
流逝时间/秒
ppp
等待事件特定的参数
以上为文件头与ALTER SESSION出现的跟蹤信息此后开始出现运行的SQL语句
BIND段
cursor#
游标号
bind N
绑定位置从开始
dty
数据类型
mxl
绑定变量最大长度
mal
最大数组长度（当使用数组绑定或BULK操作时）
scl
数值范围（scale）
pre
精度（precision）
oacflg
内部标记若此值为奇数则绑定变量可能为NULL（允许为NULL）
oacfl
内部标记续
size
缓沖区大小
offset
用于逐片（piecewise）绑定
bfp
绑定地址
bln
绑定缓沖区大小
avl
真实值长度
flag
内部标记
value
绑定值的字符串表示（如果可能会是一个十六进制dump）
其中dtySELECT text FROM ALL_VIEWS WHERE view_name = USER_VIEWS 可看到一个将dty数值转换为字符串表示的函数
此后我们可以看到WAIT段即真正的等待事件
对于ENQUEUE事件实际就是锁可用以下函数（传入参数为p）判断类型
CREATE OR REPLACE FUNCTION enqueue_decode(l_p in number) return varchar
AS
l_str varchar();
BEGIN
SELECT CHR(BITAND(l_p ) / ) ||
CHR(BITAND(l_p ) / ) || ||
DECODE(BITAND(l_p )
No lock
No lock
RowShare
RowExclusive
Share
Share RowExcl
Exclusive )
INTO l_str
FROM DUAL;
RETURN l_str;
END;
XCTEND（事务边界）段记录了提交等
rlbk
回滚标记 提交 回滚
rd_only
只读标记 变化提交或回滚 事务只读
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
定时器
DBMS_PROFILER
StatsPack
V$表
V$EVENT_NAME
说明事件名和ppp三个参数
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 aname bvalue
FROM V$STATNAME a V$MYSTAT b
WHERE astatistic# = bstatistic#
V$OPEN_CURSOR
记录所有会话打开的游标由于Oracle也会缓存已关闭的游标因此此视图中也会包含已关闭的游标信息
V$PARAMETER
说明了所有的initora参数
V$SESSION
记录数据库的每个会话需要对V_$SESSION的SELECT权限
V$SESSION_EVENT
说明会话的事件情况
V$SESSION_LONGOPS
记录CBO认为执行时间超过秒的命令及进展
V$SESSION_WAIT
记录所有正在等待某事件的会话及已等待时间
V$SESSTAT
类似V$MYSTAT但显示所有会话
V$SESS_IO
说明会话的I/O信息
V$SQL和V$SQLAREA
记录SQL信息建议使用V$SQLV$SQLAREA是从V$SQL合并而来的视图代价较高对已经繁忙的系统是一个负担
V$STATNAME
说明了统计号到统计名的映射
V$SYSSTAT
记录实例层面的统计信息当数据库关闭时才清空也是StatsPack很多数据的来源
V$SYSTEM_EVENT
记录实例层面的等待事件信息也是StatsPack很多数据的来源
