SQL执行计划Cost与性能之间的的关系

关于执行计划Cost的三个疑问：

1. 执行计划的Cost越低，SQL就一定跑得越快吗？或者说Cost 和 执行时间成比例关系吗？

2. Oracle 默认产生的执行计划是Cost最低的吗？

3. 如果对象的统计信息都是最新的，执行计划就一定是准确和最优的吗？

 

实践出真知：

发现一条SQL，跑很久也没结果出来。

SQL不是很长，但是执行计划很长。

SQL文本：

SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') AS STAT_DATE,
       SYSDATE AS STAT_TIME,
       X.TABLE_NAME,
       NVL(X.NUM_ROWS, 0) AS TABLE_ROWS,
       NVL(ROUND(X.NUM_ROWS * X.AVG_ROW_LEN / 1024, 2), 0) AS TABLE_SIZES,
       Y.CREATED AS CREATE_TIME,
       Z.COMMENTS AS TABLE_COMMENT,
       H .COL_CNT
  FROM ALL_TABLES X,
       ALL_OBJECTS Y,
       ALL_TAB_COMMENTS Z,
       (SELECT H .TABLE_NAME, COUNT(1) AS COL_CNT
          FROM ALL_TAB_COLS H
         WHERE H.OWNER = 'TOSSKA'
         GROUP BY H .TABLE_NAME) H
 WHERE X.TABLE_NAME = Y.OBJECT_NAME
   AND X.TABLE_NAME(+) = Z.TABLE_NAME
   AND X.TABLE_NAME = H.TABLE_NAME
   AND Y.OBJECT_TYPE IN ('TABLE PARTITION', 'TABLE')
   AND X. OWNER = 'TOSSKA'
   AND Y. OWNER = 'TOSSKA'
   AND Z. OWNER = 'TOSSKA'
 ORDER BY X.TABLE_NAME

 

 执行计划：

   执行计划有214个步骤。太长了影响阅读，这里只显示小部分执行计划，大约十分之一吧。

 

在 Tosska SQL Tuning Expert for Oracle 中输入SQL，点击Tune开始自动优化。

一边喝咖啡, 一边等待… 

优化结束后，工具帮我找到了5条更快的等价SQL. 

 

有兴趣的朋友，可以将SQL中的OWNER从TOSSKA改为自己数据库上的用户名（用户下要有大量的表，否则SQL没有返回结果集，看不出效果），亲自试一试。  

 

让我们来分析下6条SQL(原SQL,5条优化后更快的SQL) 执行计划Cost和性能之间的关系。

 

 

下图可以看出来，Cost和SQL执行时间并不成比例关系。

 

 

仔细观察原SQL和优化后的SQL，我们来回答博客开头提出的三个问题。

1. SQL 127 和 SQL 129 的Cost比原始SQL高很多，大约是原始SQL的3倍，但是速度却比原始SQL快很多。换句话说，Cost只是估计值，和真实的速度没有直接关系。
2. 原始SQL的Cost是1330， 等价SQL 130, SQL 135 和 SQL 45的Cost 都比原始SQL低。然而Oracle并没有选中这些Cost更低的执行计划。 换句话说，Cost低的执行计划漏选了。Oracle并没有错，SQL优化器不能遍历所有执行计划，然后找出Cost最低的执行计划；否则寻找Cost最低的执行计划所耗费的时间，可能会超过SQL执行所需要的时间, 得不偿失。
3. CBO 是基于对象的统计信息，根据一堆公式估算执行计划成本和返回行数的，难免会出现误差；当误差级联放大之后，就可能导致产生次优或者不好的执行计划；这也是Oracle推出Adaptive Plan的一个原因；

 

Oracle是目前最强大的数据库，但是优化潜力依然巨大， 这也是众多SQL优化专家和优化工具的价值所在。

感受SQL优化工具的魅力，分享SQL优化故事…

SQL 优化工具下载：

https://www.tosska.cn/tosska-sql-tuning-expert-tse-oracle-free-download-zh/