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根据统计信息分析之后说了算,下面两条语句的执行计划的逻辑读都是一样的

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

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

 

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


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

 

 
  1. CREATE TABLE T1 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=10000;--大表
  2. CREATE TABLE T2 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=10;--小表
  3. SELECT COUNT(1) FROM T1,T2;
  4. SELECT COUNT(1) FROM T2,T1;
  5.  
  6. 执行计划
  7. ----------------------------------------------------------
  8. Plan hash value: 4259280259
  9.  
  10. ----------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Cost (%CPU)| Time |
  12. ----------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | 1 | 60 (2)| 00:00:01 |
  14. | 1 | SORT AGGREGATE | | 1 | | |
  15. | 2 | MERGE JOIN CARTESIAN| | 100K| 60 (2)| 00:00:01 |
  16. | 3 | TABLE ACCESS FULL | T2 | 10 | 3 (0)| 00:00:01 |
  17. | 4 | BUFFER SORT | | 10000 | 57 (2)| 00:00:01 |
  18. | 5 | TABLE ACCESS FULL | T1 | 10000 | 6 (0)| 00:00:01 |
  19. ----------------------------------------------------------------------
  20. 统计信息
  21. ----------------------------------------------------------
  22. 0 recursive calls
  23. 0 db block gets
  24. 22 consistent gets
  25. 0 physical reads
  26. 0 redo size
  27. 527 bytes sent via SQL*Net to client
  28. 519 bytes received via SQL*Net from client
  29. 2 SQL*Net roundtrips to/from client
  30. 1 sorts (memory)
  31. 0 sorts (disk)
  32. 1 rows processed

 

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


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

 

下面来个例子,

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

查看执行计划

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

实际上使用了索引

总结:网上的经验可能是某些人看了某些文章,然后在特定的场景下测试了效果一样,甚至没有测试就以讹传讹,容易误导人,Oracle脚本中有多优化的观点不同的版本等场景下因为执行计划的不同 而导致结论不一样,因此要根据实际出发,最好再亲自测一下,DBA不会告诉你这么的,因为他们自己还要混饭吃的。
不过说不定我这盘文章中有些结论也是错的,自己测一遍才知道
其实ORACLE优化还是有很多小细节需要注意的,也有很多的方法,有机会的话再与大家分享
posted @ 2017-10-06 17:15  jack_ou  阅读(329)  评论(0编辑  收藏  举报