Oracle优化网上常见的5个错误观点

最近系统的研究了一下ORACLE SQL语句性能调优，在此大言不惭的得出一个观点——网上很多性能调优的结论都是错误的或者不周全的。
现在的DBA大牛些都太低调了，不出来斧正一下，小弟来借这个机会吐槽一下，说的不对，欢迎拍砖，特别是版本问题：

转入正题：
网络上大部分结论“可能”适用于ORACLE8或者以前版本（小弟出道晚没见过ORACLE 8），但是针对9i及以后的版本，很多结论都是欠周全的。

下面举几个最常见的问题：

错误观点1、什么时候用IN 什么时候用EXISTS？子查询数据量少用IN 量多用EXISTS

如果你说半连接子查询返回数据少的时候用IN，返回数据多的时候用EXISTS，那么恭喜你，你错了。

纠正：不要轻信网上是如何说的，学了ORACLE最大的感触就是不要记结论，自己实践了才知道，创建两张表练习一下，由于篇幅原因，我这里还是给一个结论——通过执行计划看到，大多数情况下IN 和EXISTS的效率是一模一样的，只是有时候EXISTS不能 SUBSTRING UNNESTING，导致执行计划走FILTER ，执行计划一旦走了FILTER，驱动表是改变不了的（12C 能不能我不清楚），想象一下，主表是1000W，子表返回20条，由于驱动表改变不了，很有可能是大表驱动小表了。

错误观点2、NOT IN 与 NOT EXISTS 子查询量少用NOT IN 否则用NOT EXISTS

NOT IN 与 NOT EXISTS也是一个道理，但是要注意NULL的情况，NULL容易导致无法使用索引，可以创建函数索引或与常量一起做一个组合索引。

错误观点3、WHERE条件有先后顺序，后面的先执行或前面的先执行

如果过滤数据量基本持平的话，两个不同的谓词过滤条件可能会因为脚本的编写而有先后顺序，但是不要因此推断出“WHERE条件有先后顺序，后面的先执行或前面的先执行”，哪个先执行时CBO根据统计信息分析之后说了算，下面两条语句的执行计划的逻辑读都是一样的

可以通过下面的列子来测试一下执行效果

CREATE TABLE TEST02 AS SELECT * FROM DBA_OBJECTS;
SELECT COUNT(*) FROM TEST02 A WHERE A.OWNER='SYS' AND A.OBJECT_ID=29 ;
SELECT COUNT(*) FROM TEST02 A WHERE A.OBJECT_ID=29 AND A.OWNER='SYS' ;

错误观点4、FROM 语句有左右顺序所以要注意书写顺序

同第3点一样，给个例子——下面两个语句的执行计划也是一样的，ORACLE知道哪个该做驱动表，因此没有区别

CREATE TABLE T1 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=10000;--大表
CREATE TABLE T2 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=10;--小表
SELECT COUNT(1) FROM T1,T2;
SELECT COUNT(1) FROM T2,T1;
执行计划
----------------------------------------------------------
Plan hash value: 4259280259
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MERGE JOIN CARTESIAN| | 100K| 60 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 10 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 10000 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 10000 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

错误观点5、避免使用OR来连接条件，否则导致引擎放弃使用索引而进行全表扫描

如：SELECT ID FROM T WHERE NUM=10 OR NUM=11 会全表扫描

下面来个例子，

CREATE TABLE TEST02 AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX TEST_02_IDX_01 ON TEST02 (OBJECT_ID);
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='9.2.0';
SELECT COUNT(*) FROM TEST02 A WHERE A.OBJECT_ID=28 OR A.OBJECT_ID=29 ;

查看执行计划

执行计划
----------------------------------------------------------
Plan hash value: 3430686514
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | INLIST ITERATOR | | | | |
|* 3 | INDEX RANGE SCAN| TEST_02_IDX_01 | 2 | 10 | 2 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=28 OR "A"."OBJECT_ID"=29)

实际上使用了索引

总结：网上的经验可能是某些人看了某些文章，然后在特定的场景下测试了效果一样，甚至没有测试就以讹传讹，容易误导人，Oracle脚本中有多优化的观点不同的版本等场景下因为执行计划的不同而导致结论不一样，因此要根据实际出发，最好再亲自测一下，DBA不会告诉你这么的，因为他们自己还要混饭吃的。
不过说不定我这盘文章中有些结论也是错的，自己测一遍才知道

其实ORACLE优化还是有很多小细节需要注意的，也有很多的方法，有机会的话再与大家分享

posted @ 2017-10-06 17:15 jack_ou 阅读(331) 评论(0) 编辑收藏举报

刷新页面返回顶部

jack_ou

Oracle优化网上常见的5个错误观点

公告