|
首先要声明两个知识点 （）RBO&CBO Oracle有两种执行优化器一种是RBO(Rule Based Optimizer)基于规则的优化器这种优化器是基于sql语句写法选择执行路径的另一种是CBO（Cost Based Optimizer）基于规则的优化器这种优化器是Oracle根据统计分析信息来选择执行路径如果表和索引没有进行分析Oracle将会使用RBO代替CBO如果表和索引很久未分析CBO也有可能选择错误执行路径不过CBO是Oracle发展的方向自i版本来已经逐渐取代RBO （）AUTOTRACE 要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能它显示了sql语句的执行路径我们能看到Oracle内部是怎么执行sql的这是一个非常好的辅助工具在sql调优里广泛被运用我们来看一下怎么运用AUTOTRACE ① 由于AUTOTRACE自动为用户指定了Execution Plan因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE如果没有的话请运行utlxplansql脚本（它在$ORACLE_HOME/rdbms/admin目录中） ② AUTOTRACE可以通过运行plustrcesql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置用sys用户登陆然后运行plustrcesql后会建立一个PLUSTRACE角色然后给相关用户授予PLUSTRACE角色然后这些用户就可以使用AUTOTRACE功能了 ③ AUTOTRACE的默认使用方法是set autotrace on但是这方法不总是适合各种场合特别当返回行数很多的时候Set autotrace traceonly提供了只查看统计信息而不查询数据的功能 SQL> set autotrace on SQL> select * from test; A
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (FULL) OF TEST Statistics
recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed SQL> set autotrace traceonly SQL> select * from testtest; Execution Plan
SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (FULL) OF TEST Statistics
recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed Hints是Oracle提供的一个辅助用法按字面理解就是提示的意思确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径特别适用于sql调整的时候使用方法如下 {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]] */ 具体可参考Oracle SQL Reference 有了前面这些知识点接下来让我们来看一下什么时候索引是不起作用的以下列出几种情况 （）类型不匹配时 SQL> create table testtestindex (a varchar()b number); 表已创建 SQL> create index ind_cola on testtestindex(a); 索引已创建 SQL> insert into testtestindex values(); 已创建 行 SQL> commit; 提交完成 SQL> analyze table testtestindex compute statistics for all indexes; 表已分析 SQL> set autotrace on; SQL> select /*+RULE */* FROM testtestindex where a=;(使用基于rule的优化器数据类型匹配的情况下) A B Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX INDEX (RANGE SCAN) OF IND_COLA (NONUNIQUE)（使用了索引ind_cola） ―――――――――――――――――――――――――――――――――― SQL> select /*+RULE */* FROM testtestindex where a=;（数据类型不匹配的情况） A B Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE TABLE ACCESS (FULL) OF TESTINDEX（优化器选择了全表扫描） （）条件列包含函数但没有创建函数索引 SQL> select /*+ RULE */* FROM testtestindex where upper(a)= A;（使用了函数upper()在列a上）; A B a Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE TABLE ACCESS (FULL) OF TESTINDEX（优化器选择全表扫描）
创建基于函数的索引 SQL> create index testind_fun on testtestindex(upper(a)); 索引已创建 SQL> insert into testindex values(a); 已创建行 SQL> commit; 提交完成 SQL> select /*+ RULE*/* FROM testtestindex where upper(a)=A; A B a Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE TABLE ACCESS (FULL) OF TESTINDEX (在RULE优化器下忽略了函数索引选择了全表扫描)
SQL> select * FROM testtestindex where upper(a) =A; A B a Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost= Card= Bytes=) INDEX (RANGE SCAN) OF IND_FUN (NONUNIQUE) (Cost= Car d=)(CBO优化器使用了ind_fun索引) （）复合索引中的前导列没有被作为查询条件 创建一个复合索引 SQL> create index ind_com on testtestindex(ab); 索引已创建 SQL> select /*+ RULE*/* from testtestindex where a=; A B Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE INDEX (RANGE SCAN) OF IND_COM (NONUNIQUE)(条件列表包含前导列时使用索引ind_com) SQL> select /*+ RULE*/* from testtestindex where b=; 未选定行 Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE TABLE ACCESS (FULL) OF TESTINDEX(条件列表不包括前导列是选择全表扫描)
（）CBO模式下选择的行数比例过大优化器采取了全表扫描 SQL> select * from testtestindex where a=; A B Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF TESTINDEX (Cost= Card= Bytes=) （表一共行选择比例为%所以优化器选择了全表扫描） ―――――――――――――――――――――――――――――――――― 下面增加表行数 SQL> declare i number; begin for i in loop insert into testtestindex values (to_char(i)i); end loop; end; / PL/SQL 过程已成功完成 SQL> commit; 提交完成 SQL> select count(*) from testtestindex; COUNT(*)
SQL> select * from testtestindex where a=; A B Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) INDEX (RANGE SCAN) OF IND_COM (NONUNIQUE) (Cost= Card= Bytes=) （表一共行选择比例为/=%所以优化器选择了索引扫描） （）CBO模式下表很久没分析表的增长明显优化器采取了全表扫描 SQL> select * from testtestindex where a like %; A B 已选择行 Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF TESTINDEX (Cost= Card= Bytes=) (表一共行选择比例为/>%优化器选择了全表扫描) ―――――――――――――――――――――――――――――――――― 增加表行数 SQL> declare i number; begin for i in loop insert into testtestindex values (to_char(i)i); end loop; end; / PL/SQL 过程已成功完成 SQL> commit; 提交完成 SQL> select count(*) from testtestindex; COUNT(*)
SQL> select * from testtestindex where a like %; A B 已选择行 Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF TESTINDEX (Cost= Card= Bytes=) (表一共行选择比例为/<%优化器选择了全表扫描选择路径是错误的) ――――――――――――――――――――――――――――― 给表做分析 SQL> analyze table testtestindex compute statistics for table for all indexed c olumns for all indexes; 表已分析 SQL> select * from testtestindex where a like %; A B 已选择行 Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost= Card= Bytes=) INDEX (RANGE SCAN) OF IND_COLA (NONUNIQUE) (Cost= Ca rd=) （经过分析后优化器选择了正确的路径使用了ind_cola索引） |
