|
第三章 SQL 调整 1.SQL性能测量工具
TKPROF
全名为Trace Kernel Profile,用来格式化跟踪文件,是一个命令行工具,基本的格式如下:
tkprof tracefile outputfile …
可选参数及说明如下:
Option Description
EXPLAIN 为每个SQL语句产生执行计划,指定用户方案和密码,使用其中的PLAN_TABLE表
TABLE 当EXPLAIN选项生效时可用,指定替代PLAN_TABLE的表
SYS 启用该选项时不包含递归调用
SORT 指定SQL语句的排序方式
RECORD 指定文件名,将SQL语句写入,排除递归的SQL
PRINT 限定只输出指定数量的分析SQL语句
INSERT 指定一个文件名,容纳相关的插入SQL语句,将分析的SQL及统计插入TKPROF_TABLE
AGGREGATE 指定多用户共用的SQL语句统计方式
WAITS 指定是否统计跟踪文件中的等待事件
SORT选项可分三类,解析选项,执行选项和取数据选项(PRS,EXE,FCH),根椐这三种操作占用相关资源来排序。
TKPROF针对SQL的统计也分为三类:解析,执行,取数,具体的统计选项见下表:
统计名称 统计描述
Count Parse,Execute,Fetch发生的次数
CPU 占用CPU秒
Elapsed 经过秒
Disk 从磁盘读取的数据块数
Query 从SGA中读取的回滚段块数
Current 从SGA中读取的一致性数据块数
Rows 执行INSERT,UPDATE,DELETE影响的行数或者FETCH返回的行数
从TKPROF的统计结果观察,发现下列特征的SQL可能需要调优:
1. 消耗过度的CPU资源;
2. 花费太长的时间在Parse,Execute,Fetch阶断;
3. 从磁盘读数据块太多而从SGA中读块很少;
4. 存取太多的数据块(从SGA或者磁盘)而返回的行数很少;
TOP SQL
这个工具从Enterprise Manager Console中启动,有与TKPROF类似的统计,用于确定需要调整的SQL语句。
EXPLAIN PLAN
用EXPLAIN PLAN FOR sql语句产生执行计划,然后再从PLAN_TABLE中查询;
执行计划中各步骤的执行顺序遵照如下原则:
1. 从上到下;
2. 从右到左(或者说,从缩进最多的到缩进最少的);
3. 索引操作不参与上面的规则,索引从属于表操作,先于表操作。
执行计划的详尽解释超出本书范围,需要参考其它资料;
AUTOTRACE
AUTOTRACE综合了TKPROF和EXPLAIN PLAN FOR的功能;
配置AUTOTRACE包括以下步骤:
1. 保证需要AUTOTRACE功能的用户下有PLAN_TABLE或者有该表的全局同义词和那个基表的存取权限;
2. 创建PLUSTRACE角色,并将该角色权限赋给相应的用户,或者相应会话有这个角色对应的权限;
3. 相关脚本:%ORACLE_HOME%\rdbms\admin\utlxplan.sql,%ORACLE_HOME%\sqlplus\admin\plustrce.sql
AUTOTRACE的特点:
1. 只能在SQL*Plus的会话中执行;
2. 产生执行计划前会实际执行SQL,这点与EXPLAIN PLAN FOR不同;
3. 在发出SQL之前,需执行SET AUTOTRACE ON语句。
SET AUTOTRACE语句各选项的意义:
选项 描述
ON 显示查询结果,执行计划,统计
ON STATISTICS 显示查询结果,统计
ON EXPLAIN 显示查询结果,执行计划
TRACEONLY 显示执行计划,统计
TRACEONLY STATISTICS 显示统计
OFF 关掉AUTOTRACE
STATSPACK
STATSPACK中也包含几种方法排序的SQL,通常只显示排序值大于一定阀值的SQL,这些阀值可能改变:
SQL类型 说明
SQL ordered by Gets 根椐逻辑I/O来排序
SQL ordered by Reads 根椐物理I/O来排序
SQL ordered by Executions 根椐执行次数来排序
SQL (Executions) ordered by Parse Calls 根椐分析次数来排序 |
|