使用AUTOTRACE功能查看执行计划和统计信息时，执行计划中的COST（%CPU）这项数据是根据表最新的分析结果，而得到的。因此这项值有可能是不真实的。请看下面这个测试。表TEST数据量有1千万。

SQL> select * from test;

11394428 rows selected.

Elapsed: 00:03:30.80

Execution Plan

----------------------------------------------------------

Plan hash value: 511800663

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 174K| 9191K| 397 (1)| 00:00:05 |

| 1 | TABLE ACCESS FULL| test | 174K| 9191K| 397 (1)| 00:00:05 |

-----------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

156 recursive calls

0 db block gets

889643 consistent gets

130462 physical reads

0 redo size

1082613512 bytes sent via SQL*Net to client

8356428 bytes received via SQL*Net from client

759630 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

11394428 rows processed

SQL>EXEC dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'TEST');

PL/SQL procedure successfully completed

11394428 rows selected.

Elapsed: 00:03:30.95

Execution Plan

----------------------------------------------------------

Plan hash value: 511800663

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 11M| 836M| 35483 (1)| 00:07:06 |

| 1 | TABLE ACCESS FULL| TEST | 11M| 836M| 35483 (1)| 00:07:06 |

-----------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

889617 consistent gets

130460 physical reads

0 redo size

1082613512 bytes sent via SQL*Net to client

8356428 bytes received via SQL*Net from client

759630 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

11394428 rows processed

从上面的测试结果可以看到前后的COST值不一样，当然前者没有分映真实情况，而在对表进行实时分析后，再查看SQL的执行计划，COST值就对了。但时我们也可以看到前后两次的物理读都是一样，这里也提示，如果我们在分析一个SQL语句的性能时，最可靠的还是统计分析数值，当执行计划里的COST值我们感觉不可信时，就不要它了，直接根据物理读和内存读来看SQL的性能如何。

ITPub博客

执行计划中COST（%CPU）列值不可信

最新文章