基于Oracle的SQL优化（崔华著）-整理笔记-工具集
一、脚本display_cursor_9i.sql是可以得到SQL的真实执行计划，使用示例
使用示例，请看以下case
|
1、执行测试sql： SELECT T1.*,T2.* FROM T_0504 T1,T_0504 T2;
2、找出该sql的 HASH_VALUE、CHILD_NUMBER select t1.SQL_TEXT,t1.HASH_VALUE,t1.CHILD_NUMBER from v$sql t1 where t1.SQL_TEXT like 'SELECT T1,*,T2.* FROM T_0504 T1,T_0504 T2%';
------------------------------------------------------------------------------------------------------------------------------ SELECT T1,*,T2.* FROM T_0504 T1,T_0504 T2 4015614548 0
3、现在执行脚本display_cursor_9i.sql是可以得到上述SQL的真实执行计划的： -------SQL> @'E:\display_cursor_9i.sql' HASH_VALUE CHILD_NUMBER
SQL> @'E:\display_cursor_9i.sql' 4015614548 0 |
二、存储过程PRINTSQL.prc 打印对应spid或sid所对应的sql以及其执行计划，使用示例
|
输入参数： i_n_id: 输入的spid或sid i_vc_idtype : 输入的ID的类型，'SPID'表示输入的是spid，'SID'表示输入的是sid.
使用示例，请看以下case
|
三、 oracle自己的dbms_xplan包 ，使用示例。必须在执行sql之后马上执行哦
|
select * from table (dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number,'advanced'))； |
四、XPLAN包-清晰地看到执行计划中每一步的执行顺序
|
XPLAN包其实是对DBMS__ XPLAN包的封装，使用XPLAN包就可以很清晰地看到执行计划中每一步的执行顺序。执行顺序在XPLAN包的显示结果中以列Order来显示，Order的值从1开始递增，表示执行顺序的先后。
使用示例，请看以下case
1、执行测试sql语句： SELECT T1.*,T2.* FROM T_0504 T1,T_0504 T2;
2、找出该sql的 HASH_VALUE、CHILD_NUMBER select t1.SQL_TEXT,t1.HASH_VALUE,t1.CHILD_NUMBER,sql_id from v$sql t1 where t1.SQL_TEXT like 'SELECT T1,*,T2.* FROM T_0504 T1,T_0504 T2%';
------------------------------------------------------------------------------------------------------------------------------ SELECT T1,*,T2.* FROM T_0504 T1,T_0504 T2 4015614548 0 5d0g6kk0ryp4g
3.xplan检查该sql的执行计划附带各个执行步骤的先后顺序 select * from table(xplan.display_cursor('5d0g6kk0ryp4g',0,'advanced')) SQL_ID 5d0g6kk0ryp4g, child number 0 ------------------------------------- select t1.*,t2.* from t_0504 t1,t_0504 t2
Plan hash value: 4118920280
--------------------------------------------------------------------------------------- | Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | 5 | SELECT STATEMENT | | | | 20M(100)| | | 1 | 4 | MERGE JOIN CARTESIAN| | 5258M| 950G| 20M (1)| 69:37:26 | | 2 | 1 | TABLE ACCESS FULL | T_0504 | 72517 | 6869K| 290 (1)| 00:00:04 | | 3 | 3 | BUFFER SORT | | 72517 | 6869K| 20M (1)| 69:37:23 | | 4 | 2 | TABLE ACCESS FULL | T_0504 | 72517 | 6869K| 288 (1)| 00:00:04 | ---------------------------------------------------------------------------------------
五、sosi.txt就是这样的一个脚本，SOSI是Show Optimizer Statistics Infonnation的缩写 指定要查看统计信息的表名就可以了。 sosi.txt支持分区表，它的显示分为三部分，分别是表级别的统计信息，分区级别的统计信息和子分区级别的 统计信息，其典型的显示结果为如下所示。
表级别的显示输出
分区级别的显示输出
子分区级别的输出
|
收集表的统计信息
|
对表T2收集统计信息，并且是以估算模式，采样的比例同样为I5%: soL> exec dbms_stats.gather_table_stats (ownname => 'SCOTT',tabname => 'T2',estimate_percent=> 15,method_opt -> 'FOR TABLE',cascade => false) ;
PL/soL procedure successfully completed
|
|
对表T2收集统计信息，并且是以计算模式收集，用DBMS_STATS包实现的方法就是将估算模式的采样比例（即参数ESTIMATE PERCENT）设为100%或NULL: SQL> exec dbms_stats.gather_table_stats (ownname => 'SCOTT',tabname => 'T2',estimate_percent=> 100,method_opt => 'FOR TABLE',cascade => false) ;
PL/SQL procedure successfully completed
SQL> exec dbrns_stats.gather_table_stats (ownname => 'SCOTT',tabname => 'T2',estimate_percent=> NULL,method opt => 'FOR TABLE' , cascade => false) ;
PL/SQL procedure successfully completed
从如下内容可以看出，现在只有表T2有统计信息，表T2的列和索引IDX T2均没有相关的统计信息。 而且因为采用的是计算模式，计算模式会扫描目标对象的所有数据，所以统计结果和实际结果是匹配的，比如 表T2的实际数据量为71，964，这里Oracle计算出来的T2的数据量也是71，964:
|
|
对表T2收集完统计信息后，对表T2的列OBJECT NAME和OBJECT ID以计算模式收集统计信息（不收集直方图）： soL> exec dbms_stats.gather_table_stats (ownname => 'SCOTT',tabname => 'T2',estimate_percent=> 100,method_opt => 'for columns size 1 object_name object_id',cascade => false) ;
PL/SQL procedure successfully completed
|
|
以计算模式收集索引IDX_T2的统计信息： SQL> exec dbms_stats .gather_index stats (ownname => ' SCOTT ' ,indname =>'IDX_T2 ',estimate_percent =>100) ;
PL/soL procedure successfully completed
|
|
删除表T2、表T2的所有列及表T2的所有索引的统计信息 SQL> exec dbms stats.delete_table_stats (ownname => 'SCOTT' ,tabname => 'T2 ' ) ;
PL/soL procedure successfuLly completed
从如下结果可以看到，我们刚才收集的表T2、表T2的列OBJECT NAME、OBJECT ID以及索引IDX T2 的统计信息已经全部被删除了：
|
|
一次性以计算模式收集表T2、表T2的所有列和表T2上的所有索引的统计信息，执行如下语句就可以了： SQL> exec dbms_stats.gather_table_stats (ownname => 'SCOTT',tabname => 'T2',estimate_percent=> 100,cascade=> true) ;
PL/soL procedure successfully completed
|
