Oracle SQL性能调节的方法
简单介绍一下使用tkprof工具调整Oracle SQL语句性能的方法。 —————————– # 用tkprof工具测量执行时间
首先在SQL*Plus中执行以下SQL语句，开启执行记录功能。
alter session set timed_statistics=true;
alter session set sql_trace=true;
然后执行要测量时间的SQL语句：
select
user_id,
min(site_id) keep (dense_rank first order by dt desc) as site_id
from charlee_test
where site_id in (10,13)
group by user_id
最后关闭记录功能：
alter session set sql_trace=false;
alter session set timed_statistics=false;
这样就记录下了SQL语句的执行log。那么这些log保存在什么地方呢？可以通过以下语句来查看：
select value from v$parameter where name='user_dump_dest';
VALUE
---------------------------------
/opt/oracle/admin/scott/udump
退出SQL*Plus回到shell（或者另开启一个终端），执行如下命令：
ll /opt/oracle/admin/scott/udump/*.trc
利用生成时间找到刚刚生成的log文件，假设为 ora_11111.trc。执行如下命令将该文件转化为可以阅读的格式：
$ tkprof /opt/oracle/admin/scott/udump/ora_11111.trc ora_11111.prf
explain=scott/password aggregate=no sys=no sort=fchela
使用文本编辑器打开生成的 ora_11111.prf，内容如下：
********************************************************************************
select user_id, min(site_id) keep (dense_rank first order by dt desc) as site_id
from charlee_test
where site_id in (10,13)
group by user_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 4
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (WBRRADM)
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT GROUP BY (cr=7 r=0 w=0 time=153 us)
5 TABLE ACCESS FULL CHARLEE_TEST (cr=7 r=0 w=0 time=68 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 SORT (GROUP BY)
5 TABLE ACCESS (FULL) OF 'CHARLEE_TEST'
该报告的第一部分是执行的SQL语句。第二部分是关于此语句的统计信息，其中各个字段的内容如下：
- count: OCI进程执行的次数
- cpu: 消耗的CPU时间
- elapsed: 总共执行时间
- disk: 将数据从硬盘读取到缓存中的次数
- query: number of buffers gotten for consistent read
- current: number of buffers gotten in current mode
- rows: 查询返回的行数
下面的Misses in library cache during parse的值为1表明在共有区域中没有找到缓存，因此需要执行该SQL语句。 其下是执行计划以及每一步执行的代价。代价的描述中各个值的含义如下：
- cr: 从缓存读入的块数
- r: 从磁盘读入的块数
- w: 向磁盘写入的块数
- time: 合计经过的时间
接下来：
Rows Row Source Operation
------- ---------------------------------------------------
6 TABLE ACCESS FULL CHARLEE_TEST (cr=9 r=5 w=0 time=304 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
6 TABLE ACCESS (FULL) OF 'CHARLEE_TEST'
利用Excel将此部分整理，计算相邻两步的时间差，即可知道哪一步占用时间最大。
利用EXPLAIN PLAN语句解释执行计划
SQL语句的执行过程不了解的人，很难明白执行计划的意思。Oracle为我们提供的EXPLAIN PLAN可以将执行计划的详细内容输出。
首先要创建EXPLAIN PLAN语句所需的PLAN_TABLE表。在SQL*Plus中执行以下脚本：
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
然后执行EXPLAIN PLAN：
explain plan for
select
user_id,
min(site_id) keep (dense_rank first order by dt desc) as site_id
from charlee_test
where site_id in (10,13)
group by user_id
执行下面的脚本可以查看解释结果：
@$ORACLE_HOME/rdbms/admin/utlxpls.sql
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT GROUP BY | | | | |
|* 2 | TABLE ACCESS FULL | CHARLEE_TEST | | | |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CHARLEE_TEST"."SITE_ID"=10 OR
"CHARLEE_TEST"."SITE_ID"=13)
Note: rule based optimization