一直以来,想弄明白新建的表,是否自动生成了统计信息,以便于对其执行查询时的执行计划的正确选择。今天,就通过一个简单的试验,来弄明白这个,并了解
Analyze后,到底生成了哪些统计信息。
SQL> create table test as select * from dba_objects;
Table created
SQL> set autot traceonly
SQL> select t.object_id, t.data_object_id, t.object_name, t.subobject_name
from test t
where t.owner = 'TEST'; 2 3
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
统计信息
----------------------------------------------------------
85 recursive calls
0 db block gets
1118 consistent gets
0 physical reads
0 redo size
201867 bytes sent via SQL*Net to client
3714 bytes received via SQL*Net from client
297 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4433 rows processed
from test t
where t.owner = 'TEST'; 2 3
已选择4433行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6790 | 835K| 173 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 6790 | 835K| 173 (2)| 00:00:03 |
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6790 | 835K| 173 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 6790 | 835K| 173 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - filter("T"."OWNER"='TEST')
Note
-----
- dynamic sampling used for this statement
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
85 recursive calls
0 db block gets
1118 consistent gets
0 physical reads
0 redo size
201867 bytes sent via SQL*Net to client
3714 bytes received via SQL*Net from client
297 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4433 rows processed
发现'dynamic sampling used for this statement' 的问题,表示执行计划使用的是表的动态采样数据,系统没有对Test表做过统计。再查询user_tables和dba_tables视图,记录信息,便于比较analyze后的信息,从而找到变化的信息。
下面,分析表,再重新查看其执行计划
SQL> ANALYZE TABLE test COMPUTE STATISTICS;
Table analyzed
SQL> select t.object_id, t.data_object_id, t.object_name, t.subobject_name
from test t
where t.owner = 'TEST'; 2 3
SQL> select t.object_id, t.data_object_id, t.object_name, t.subobject_name
from test t
where t.owner = 'TEST'; 2 3
已选择4433行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2609 | 96533 | 173 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 2609 | 96533 | 173 (2)| 00:00:03 |
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2609 | 96533 | 173 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 2609 | 96533 | 173 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - filter("T"."OWNER"='TEST')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1049 consistent gets
0 physical reads
0 redo size
201867 bytes sent via SQL*Net to client
3714 bytes received via SQL*Net from client
297 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4433 rows processed
可以看到,这个时候执行计划就采用了前面Analyze的表分析数据,执行计划更优化了。下面,对比user_tables和dba_tables视图,看哪些数据项发生了变化,也就是分析表产生的结果有哪些了。
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS SAMPLE_SIZE LAST_ANALYZED
---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ----------- -------------
54786 775 121 868 0 97 0 0 54786 2011-1-17 16:
---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ----------- -------------
54786 775 121 868 0 97 0 0 54786 2011-1-17 16:
analyze产出变化得有以上项,未分析之前都是空得,分析之后都有了值,这些数值都是从系统视图sys.tab$ 得到。
通过以上试验,我们得出以下结论:
1、表create后,是不会自动产生统计信息的。
2、表analyze后,会产生哪些统计信息。
3、在表不是必须drop再create的情况下,尽量不要轻易的采用drop再create表的方式,这样不利于统计信息的采集和执行计划的优化。
在做一个实验,看新建索引后是否立刻使用:
1、create table test1 as select * from dba_objects;
2、create index idx_owner on test1(owner);
3、set autotrace traceonly;
4、select * from test1 where wner = 'SYS';
查看执行计划是全表扫描。
5、analyze table test1 compute statistics;
6、 select * from test1 where wner = 'SYS';
查看执行计划是走索引的。
结论:新建索引后没有统计分析,索引是不起作用的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20577218/viewspace-702313/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20577218/viewspace-702313/