甲骨文中的GATHER_TABLE_STATS和执行计划

Question

运行执行计划而不收集表统计信息(使用dbms_stat.gather_table_stat)和收集表统计信息，然后运行执行计划会在执行plan.Can中发生任何更改，请解释收集表统计信息以及执行计划如何依赖于收集统计信息。

Answer 1

执行计划是基于统计数据的。如果你知道这一点，下面的例子对你没有用，请说出来，我会删除这篇文章。谢谢。例如，我创建了两个表：

create table ag_test1 (id1 number, val varchar2(50));
alter table ag_test1 add constraint pk_ag_test1 primary key (id1);
create table ag_test2 (id2 number, val varchar2(50));
alter table ag_test2 add constraint pk_ag_test2 primary key (id2);

之后，我收集了这张表的统计数据：

begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'AG_TEST1', cascade =>true);
  dbms_stats.gather_table_stats(ownname => user,tabname => 'AG_TEST2', cascade =>true);
end;

并运行查询：

select /* test_query_1 */
       t1.val as tbl1, t2.val as tbl2
from ag_test1 t1
join ag_test2 t2 on (t1.id1 = t2.id2);

查看执行计划(正如您所记得的，表包含0行)：

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                |             |     1 |    80 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |     1 |    80 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | AG_TEST1    |     1 |    40 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_AG_TEST2 |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| AG_TEST2    |     1 |    40 |     0   (0)|          |
--------------------------------------------------------------------------------------------

让我们填充这个表(每个表中有1M行)：

insert all
when 1=1 then 
  into ag_test1 (id1, val)
  values (lvl, val1)
when 1=1 then 
  into ag_test2 (id2, val)
  values (lvl, val2)
select level as lvl, 'TBL1_VAL_'||level as val1, 'TBL2_VAL_'||level as val2
from dual
connect by level <=1000000;
commit;

再次运行查询并查看：

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                |             |     1 |    80 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |     1 |    80 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | AG_TEST1    |     1 |    40 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_AG_TEST2 |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| AG_TEST2    |     1 |    40 |     0   (0)|          |
--------------------------------------------------------------------------------------------

什么都没改变。1米行的执行计划等于0行。因为Oracle使用软解析(查找和使用以前的计划)。让我们用no_invalidate子句(no_invalidate => false)收集统计信息：

begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'AG_TEST1', cascade =>true, no_invalidate => false);
  dbms_stats.gather_table_stats(ownname => user,tabname => 'AG_TEST2', cascade =>true, no_invalidate => false);
end;

看看执行计划：

---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |       |  5120 (100)|          |
|*  1 |  HASH JOIN         |          |  1000K|    40M|    31M|  5120   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| AG_TEST1 |  1000K|    20M|       |   995   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| AG_TEST2 |  1000K|    20M|       |   995   (1)| 00:00:01 |
---------------------------------------------------------------------------------------