引言：这是我在学习谭老师SQL优化课程后的总结和实验，这里我主要通过sql_trace和10046事件来分析了sql资源消耗的情况，包含了 “结果集重用优化和分区索引优化”两部分，希望通过这些给一些初次涉及优化的DBA一些思路和启发。希望大家提出宝贵意见！
1.自己构造两条不同的SQL来完成相同的查询,注意结果集重用对SQL性能的影响
四个主人公：alan leonarding sun xy
故事：这4个小伙伴刚刚毕业，毕业后都顺利的找到了工作，现在已经工作半年了，一次他们聚在了一起，由于虚荣心的驱动他们想比一比谁的工资攒的多，大家都是学IT出身，于是他们做了一个leo_salary表来计算他们这半年来的平均工资！
创建了leo_salary表，雇员id 名/字 薪水 月份
LS@LEO> create table leo_salary (employee_id number,name varchar(20),salary number,month date);
Table created.
LS@LEO> insert into leo_salary values (1,'alan',1000,to_date('2012_01_01','yyyy-mm-dd')); alan同学工资
insert into leo_salary values (1,'alan',1100,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1200,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1300,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1400,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1500,to_date('2012_06_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',200,to_date('2012_01_01','yyyy-mm-dd')); leonarding同学工资
insert into leo_salary values (2,'leonarding',210,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',220,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',230,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',240,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',250,to_date('2012_06_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',400,to_date('2012_01_01','yyyy-mm-dd')); sun同学工资
insert into leo_salary values (3,'sun',410,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',420,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',430,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',440,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',450,to_date('2012_06_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',600,to_date('2012_01_01','yyyy-mm-dd')); xy同学工资
insert into leo_salary values (4,'xy',610,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',620,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',630,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',640,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',650,to_date('2012_06_01','yyyy-mm-dd'));
commit;
LS@LEO> select count(*) from leo_salary;
COUNT(*)
----------
24
LS@LEO> select * from leo_salary;
EMPLOYEE_ID NAME SALARY MONTH
----------- -------------------- ---------- -------------------
1 alan 1000 2012-01-01 00:00:00
1 alan 1100 2012-02-01 00:00:00
1 alan 1200 2012-03-01 00:00:00
1 alan 1300 2012-04-01 00:00:00
1 alan 1400 2012-05-01 00:00:00
1 alan 1500 2012-06-01 00:00:00
2 leonarding 200 2012-01-01 00:00:00
2 leonarding 210 2012-02-01 00:00:00
2 leonarding 220 2012-03-01 00:00:00
2 leonarding 230 2012-04-01 00:00:00
2 leonarding 240 2012-05-01 00:00:00
2 leonarding 250 2012-06-01 00:00:00
3 sun 400 2012-01-01 00:00:00
3 sun 410 2012-02-01 00:00:00
3 sun 420 2012-03-01 00:00:00
3 sun 430 2012-04-01 00:00:00
3 sun 440 2012-05-01 00:00:00
3 sun 450 2012-06-01 00:00:00
4 xy 600 2012-01-01 00:00:00
4 xy 610 2012-02-01 00:00:00
4 xy 620 2012-03-01 00:00:00
4 xy 630 2012-04-01 00:00:00
4 xy 640 2012-05-01 00:00:00
4 xy 650 2012-06-01 00:00:00
24 rows selected.
LS@LEO> select name,avg(salary) avg_salary from leo_salary group by name; 这4个人平均工资
NAME AVG_SALARY
-------------------- ----------
leonarding 225
alan 1250
xy 625
sun 425
他们又开始琢磨，想看看大家加在一起的平均基数是多少？
LS@LEO> select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name);
AVG(AVG_SALARY)
---------------
631.25 基数
于是alan提议，比一比谁的平均工资超过了基数？
set timing on 显示执行时间
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name));
NAME AVG_SALARY
-------------------- ----------
alan 1250 只有一位选手出线
Elapsed: 00:00:00.02
很明显“select name,avg(salary) avg_salary from leo_salary group by name”子句已经被重复执行了，可以通过with子句，可以将该子句独立出来，并重用其查询结果！
LS@LEO> with name_avg_salary as (select name,avg(salary) avg_salary from leo_salary group by name)
select * from name_avg_salary t where t.avg_salary > (select avg(avg_salary) from name_avg_salary);
NAME AVG_SALARY
-------------------- ----------
alan 1250
Elapsed: 00:00:00.03
小结：我们把子句【with name_avg_salary as (select name,avg(salary) avg_salary from leo_salary group by name)】的结果作为临时变量存起来，在其后的查询中，可以直接使用name_avg_salary变量，如同name_avg_salary是一个真实存在的数据表一样。
下面我们用另一种写法来构造相同的查询，我没有重用原来的结果集，而是重新把所有工资加起来在整除
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary);
NAME AVG_SALARY
-------------------- ----------
alan 1250 也得到同样的结果，但比第一种耗时较长
Elapsed: 00:00:00.04
小结：我没有重用原来的结果集，而是重新把所有工资加起来在整除之后对比得出结论，这种方法显然没有第一种高效，会有更多的资源开销，这也体现出减少数据块的访问是SQL的优化之道！
注：由于我的样本数据较少，偶尔会出现两种sql耗时一样的情况，后面我会用sql_trace工具来详细分析sql性能指标)
“后来我们一致决定，让攒的最多alan同学请客Happy！”
我们用sql_trace工具测试一下上述2个sql资源消耗情况
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_SALARY',method_opt=>'for all indexed columns size 2',cascade=>TRUE); 收集表leo_salary的统计信息
PL/SQL procedure successfully completed.
LS@LEO> alter session set tracefile_identifier='my_salary'; 设置trace文件标识
LS@LEO> alter session set sql_trace=true; 打开trace功能，追踪sql资源消耗情况
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name)); 重用结果集语句
NAME AVG_SALARY
-------------------- ----------
alan 1250
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary); 不重用结果集语句
NAME AVG_SALARY
-------------------- ----------
alan 1250
LS@LEO> alter session set sql_trace=false;
注：直接退出sqlplus工具也可以终止sql_trace功能
-rw-r----- 1 oracle oinstall 8955 Aug 26 20:41 leo_ora_20711_my_salary.trc
$ tkprof leo_ora_21094_my_salary.trc leo_my_salary.txt sys=no
-rw-r--r-- 1 oracle oinstall 8389 Aug 26 20:45 leo_my_salary.trc
more leo_my_salary.txt
select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from
(select name,avg(salary) avg_salary from leo_salary group by name))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=5160 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=4819 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=290 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=320 us)
4 VIEW (cr=7 pr=0 pw=0 time=286 us)
4 SORT GROUP BY (cr=7 pr=0 pw=0 time=234 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=222 us)
********************************************************************************
select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 0 14 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=39433 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=39313 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=238 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=99 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=218 us)
********************************************************************************
小结：从资源消耗的时间来看，结果集重用比不重用可以节约不少时间，所以说重用结果集可以提高我们的效率
10046事件分析sql执行性能
LS@LEO> alter session set events '10046 trace name context forever,level 12'; 启动10046事件，执行了同样的两条sql
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name));
NAME AVG_SALARY
-------------------- ----------
alan 1250
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary);
NAME AVG_SALARY
-------------------- ----------
alan 1250
LS@LEO> alter session set events '10046 trace name context off'; 关闭10046事件
-rw-r----- 1 oracle oinstall 7386 Aug 26 21:11 leo_ora_21593.trc
$ tkprof leo_ora_21593.trc leo_my_salary1.txt sys=no 过滤trace文件
-rw-r--r-- 1 oracle oinstall 8405 Aug 26 21:18 leo_my_salary1.txt
$ more leo_my_salary1.txt
select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name))
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.01 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=14532 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=14089 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=11004 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=406 us)
4 VIEW (cr=7 pr=0 pw=0 time=289 us)
4 SORT GROUP BY (cr=7 pr=0 pw=0 time=228 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=279 us)
Elapsed times include waiting on following events: 等待事件
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=15467 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=15240 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=322 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=193 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=265 us)
Elapsed times include waiting on following events: 等待事件
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
小结：从资源消耗的时间来看，第一个sql还是比第二个sql效率高些！
2.对于分区表， 当查询范围在一个分区以内以及跨分区查询时，比较本地索引和全局索引的性能差异。
创建一个分区表 leo_my_partition1
LS@LEO> create table leo_my_partition1
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select * from dba_objects;
前5个分区的记录数基本上分布的比较均匀
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p1); leo_p1分区有953条记录
COUNT(*)
----------
953
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p2); leo_p2分区有1000条记录
COUNT(*)
----------
1000
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p3); leo_p3分区有1000条记录
COUNT(*)
----------
1000
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p4); leo_p4分区有988条记录
COUNT(*)
----------
988
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p5); leo_p5分区有975条记录
COUNT(*)
----------
975
LS@LEO> select count(*) from leo_my_partition1 partition (leo_max); leo_max分区有5448条记录
COUNT(*)
----------
5448
下面增加些赠送的小知识和需要记住的话，并不是我实际操作的
本地索引-有几个分区就有几个索引
create index leo_my_partition1_index_local on leo_my_partition1(object_id) local; 别忘了关键字“local”哦
解释：
1.这是本地索引，每个分区一个索引，有几个分区就有几个索引，要么所有分区都建本地索引，要么所有分区都不建本地索引
2.truncate delete update insert 都不会影响本地索引
3.如果不指定具体分区索引名那么系统会自动起名字
全局索引-一个分区表只能有一个全局索引
create index leo_my_partition1_index_local on leo_my_partition1(object_id); 这是全局索引
解释：
1.增加和删除分区会导致全局索引失效
2.truncate 操作会导致全局索引失效
3.delete 操作不会导致全局索引失效
4.使用“update global indexes”选项实现truncate分区时保证全局索引有效
LS@LEO> alter table leo_my_partition1 truncate partition leo_p1 update global indexes;
下面我要创建的是自己命名的本地分区索引
LS@LEO> create index leo_my_partition1_index_local on leo_my_partition1(object_id)
local
(
partition leo_my_partition1_index_local_1 ,
partition leo_my_partition1_index_local_2 ,
partition leo_my_partition1_index_local_3 ,
partition leo_my_partition1_index_local_4 ,
partition leo_my_partition1_index_local_5 ,
partition leo_my_partition1_index_local_max
);
2 3 4 5 6 7 8 9 10 partition leo_my_partition1_index_local_1 ,
*
ERROR at line 4:
ORA-00972: identifier is too long
报错，真奇怪，查询一下
$ oerr ora 972 格式：oerr 错误类型 错误代码
00972, 00000, "identifier is too long"
// *Cause: An identifier with more than 30 characters was specified.
// *Action: Specify at most 30 characters.
ORA-00972 identifier is too long
Cause: The name of a schema object exceeds 30 characters. Schema objects are
tables, clusters, views, indexes, synonyms, tablespaces, and usernames.
Action: Shorten the name to 30 characters or less.
原因：模式对象名不能超过30个字符，解决把名字长度减少，例如表名 视图名 索引名 同义词 表空间啊 名称的长度都不能超过30个字符哦
LS@LEO> create index leo_my_partition1_index_local on leo_my_partition1(object_id) 本地分区索引创建成功
local
(
partition local_index_1 ,
partition local_index_2 ,
partition local_index_3 ,
partition local_index_4 ,
partition local_index_5 ,
partition local_index_max
);
Index created.
检查本地分区索引是否有效
LS@LEO> select index_name,status,tablespace_name,partitioned from dba_indexes where table_name in ('LEO_MY_PARTITION1');
INDEX_NAME STATUS TABLESPACE_NAME PAR
------------------------------ -------- ------------------------------ ---
LEO_MY_PARTITION1_INDEX_LOCAL N/A YES
创建另一个分区表leo_my_partition2
LS@LEO> create table leo_my_partition2
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select * from dba_objects;
每个分区的记录数和partition1差不多
LS@LEO> select count(*) from leo_my_partition2 partition (leo_p1);
select count(*) from leo_my_partition2 partition (leo_p2);
select count(*) from leo_my_partition2 partition (leo_p3);
select count(*) from leo_my_partition2 partition (leo_p4);
select count(*) from leo_my_partition2 partition (leo_p5);
select count(*) from leo_my_partition2 partition (leo_max);
COUNT(*)
----------
953
LS@LEO>
COUNT(*)
----------
1000
LS@LEO>
COUNT(*)
----------
1000
LS@LEO>
COUNT(*)
----------
988
LS@LEO>
COUNT(*)
----------
975
LS@LEO>
COUNT(*)
----------
5462
LS@LEO>
我们给partition2表创建全局分区索引
LS@LEO> create index global_index_1 on leo_my_partition2(object_id); 创建成功
Index created.
检查全局分区索引是否有效
LS@LEO> select index_name,status,tablespace_name,partitioned from dba_indexes where table_name in ('LEO_MY_PARTITION2');
INDEX_NAME STATUS TABLESPACE_NAME PAR
------------------------------ -------- ------------------------------ ---
GLOBAL_INDEX_1 VALID USERS NO
收集2张表的统计信息
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION1',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION2',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> alter session set tracefile_identifier='my_partition'; 设置trace文件标识
LS@LEO> alter session set sql_trace=true; 打开trace功能，追踪sql资源消耗情况
当查询范围在一个分区以内，本地索引和全局索引的性能比较
LS@LEO> select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type; 符合条件的对象类型的分类
OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 25
LOB 44
PACKAGE 4
PACKAGE BODY 2
TABLE 318
INDEX 376
SYNONYM 6
CLUSTER 10
TYPE 68
9 rows selected.
LS@LEO> select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type; 符合条件的对象类型的分类
OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 25
LOB 44
PACKAGE 4
PACKAGE BODY 2
TABLE 318
INDEX 376
SYNONYM 6
CLUSTER 10
TYPE 68
9 rows selected.
当查询范围跨分区时，本地索引和全局索引的性能比较
LS@LEO> select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
CONSUMER GROUP 4
SEQUENCE 40
QUEUE 8
PROCEDURE 14
LOB 67
PACKAGE 124
PACKAGE BODY 102
LIBRARY 35
TYPE BODY 11
TRIGGER 1
TABLE 429
INDEX 498
SYNONYM 1481
VIEW 1888
FUNCTION 5
CLUSTER 10
TYPE 196
RESOURCE PLAN 3
18 rows selected.
LS@LEO> select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
CONSUMER GROUP 4
SEQUENCE 40
QUEUE 8
PROCEDURE 14
LOB 67
PACKAGE 124
PACKAGE BODY 102
LIBRARY 35
TYPE BODY 11
TRIGGER 1
TABLE 429
INDEX 498
SYNONYM 1481
VIEW 1888
FUNCTION 5
CLUSTER 10
TYPE 196
RESOURCE PLAN 3
18 rows selected.
LS@LEO> alter session set sql_trace=false; 关闭trace功能
注：直接退出sqlplus工具也可以终止sql_trace功能
-rw-r----- 1 oracle oinstall 13492 Aug 26 12:57 leo_ora_10271_my_partition.trc 这就是我们输出的trace文件
tkprof工具的介绍
答：tkprof工具是用来过滤和汇总trace文件的，因为原始trace文件可读性是比较差，里面有许多跟我们诊断sql语句关系不太大的信息，所以我们需要使用tkprof工具来规范trace文件的格式，使过滤出来的文件更具有可读性！
使用tkprof工具来生成我们需要的SQL分析文件
格式：tkprof trace文件 过滤后文件 参数
$ tkprof leo_ora_10271_my_partition.trc leo_test.txt sys=no 【sys=no 不输出sys用户运行sql语句，默认是yes，实际上设置成no更具有可读性】
TKPROF: Release 10.2.0.1.0 - Production on Sun Aug 26 19:05:34 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
-rw-r--r-- 1 oracle oinstall 10060 Aug 26 19:05 leo_test.txt
tkprof 帮助文件，在命令行下直接敲入tkprof回车，就会显示出来
$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
$ more leo_test.txt
TKPROF: Release 10.2.0.1.0 - Production on Sun Aug 26 19:05:34 2012 版本信息
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: leo_ora_10271_my_partition.trc trace文件名字
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed 当前操作被执行了多少次
cpu = cpu time in seconds executing cpu执行时间
elapsed = elapsed time in seconds executing 整个执行时间
disk = number of physical reads of buffers from disk 磁盘物理读
query = number of buffers gotten for consistent read 一致性读(逻辑读，通常查询使用的方式)
current = number of buffers gotten in current mode (usually for update) 当update时操作数据块
rows = number of rows processed by the fetch or execute call 处理的记录数
********************************************************************************
当查询范围在一个分区以内，本地索引和全局索引的性能比较
select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次执行
Fetch 2 0.00 0.00 0 15 0 9 2次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 15 0 9
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 优化器模式ALL_ROWS：所有数据全部处理完后返回结果
Parsing user id: 27
Rows Row Source Operation 行源操作
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=6130 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=47987 us) 关注消耗的时间
853 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=7737 us)
********************************************************************************
select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.01 0 0 0 0 1次执行
Fetch 2 0.00 0.00 0 15 0 9 2次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 0 15 0 9
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 优化器模式ALL_ROWS：所有数据全部处理完后返回结果
Parsing user id: 27
Rows Row Source Operation 行源操作
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=21773 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=13817 us) 关注消耗的时间
853 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=5271 us)
********************************************************************************
小结：执行计划走的是全表扫描(只扫描了第一个分区)，CBO认为全表扫描性能最优，但我们从消耗的时间来看leo_my_partition1比leo_my_partition2花的时间长，leo_my_partition2性能更优！
select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次执行
Fetch 3 0.00 0.01 0 78 0 18 3次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.03 0 78 0 18
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 优化器模式ALL_ROWS：所有数据全部处理完后返回结果
Parsing user id: 27
Rows Row Source Operation 行源操作
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=16643 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=123072 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=41851 us)
********************************************************************************
select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次执行
Fetch 3 0.00 0.01 0 78 0 18 3次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.03 0 78 0 18
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 优化器模式ALL_ROWS：所有数据全部处理完后返回结果
Parsing user id: 27
Rows Row Source Operation 行源操作
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=16707 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=1007722 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=69092 us)
********************************************************************************
小结：执行计划走的是全表扫描(扫描了1-5分区)，CBO认为全表扫描性能最优，但我们从消耗的时间来看leo_my_partition1比leo_my_partition2花的时间少，leo_my_partition1性能更优！
10046事件分析sql执行性能
解释：10046事件是发生在sql的执行过程中，它能够全面分析出sql执行过程和所消耗的资源，让你了解这条sql是否是最优的，10046事件有4个级别
LEVEL 1 等同于SQL_TRACE的功能
LEVEL 4 在LEVEL 1基础上增加绑定变量信息
LEVEL 8 在LEVEL 1基础上增加等待事件信息
LEVEL 12 等同于LEVEL 4+LEVEL 8，即有绑定变量信息又有等待事件信息
我们再次收集一下表统计信息
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION1',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION2',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> alter session set events '10046 trace name context forever,level 12'; 启动10046事件，执行了同样的四条sql
select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type;
select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type;
select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type;
select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type;
LS@LEO> alter session set events '10046 trace name context off'; 关闭10046事件
$ tkprof leo_ora_19950.trc leo_test1.txt sys=no 过滤trace文件
TKPROF: Release 10.2.0.1.0 - Production on Sun Aug 26 20:11:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
$ more leo_test1.txt
select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type
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 15 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 15 0 9
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=6073 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=79555 us)
853 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=11261 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00 客户端等待服务器响应
SQL*Net message from client 2 0.04 0.05 服务器等待客户端请求
********************************************************************************
select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type
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 15 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 15 0 9
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=4112 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=15652 us)
853 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=4537 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
小结：这次也是走的全表扫描，似乎leo_my_partition1比leo_my_partition2花的时间长，leo_my_partition12性能更优！10046事件比SQL_TRACE多了一项等待事件
select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type
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 3 0.00 0.01 0 78 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.01 0 78 0 18
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=17019 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=83714 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=25027 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00 客户端等待服务器响应
SQL*Net message from client 3 0.01 0.01 服务器等待客户端请求
********************************************************************************
select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type
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 3 0.00 0.00 0 78 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 78 0 18
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=6602 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=78720 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=29760 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.01 0.01
********************************************************************************
小结：这次也是走的全表扫描，似乎leo_my_partition1比leo_my_partition2花的时间长，leo_my_partition12性能更优！10046事件比SQL_TRACE多了一项等待事件！
Leonarding
2012.8.26
天津&autumn
分享技术~收获快乐
Blog：http://space.itpub.net/26686207
2148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
