exec dbms_stats.gather_schema_stats(ownname=>'u1',options=>'gather',cascade=>true); --收集schema下面所用对象的统计信息。
exec dbms_stats.gather_table_stats(ownname=>'u1',tabname=>'t',cascade=>true); --收集指定表包括索引的统计信息。
select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables; --查看统计信息。
首先创建一张表,里面id=1的记录只有一条,然后收集了统计信息。
SQL> create table t as select 1 id,o.* from dba_objects o where rownum = 1;
表已创建。
SQL> create index t_ind on t(id);
索引已创建。
SQL> select count(*) from t;
COUNT(*)
----------
1
SQL> select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;
TABLE_NAME NUM_ROWS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ----------- -------------------
T
SQL> exec dbms_stats.gather_table_stats(ownname=>'u1',tabname=>'t',cascade=>true);
PL/SQL 过程已成功完成。
SQL> select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;
TABLE_NAME NUM_ROWS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ----------- -------------------
T 1 73 2011-11-05 23:38:15
然后插入5万多条id=99的记录。没有再次收集统计信息。
SQL> insert into t select 99 id,o.* from dba_objects o;
已创建50293行。
SQL> commit;
提交完成。
SQL> select count(*) from t where id = 99;
COUNT(*)
----------
50293
查看统计信息还是旧的:
SQL> select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;
TABLE_NAME NUM_ROWS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ----------- -------------------
T 1 73 2011-11-05 23:38:15
根据谓词条件id=99查询,可以看见这个表中除了1条记录,其它的都是id=99,返回了5万多条记录。这里应该走全表扫描才对的,可是选择了索引。
SQL> select * from t where id = 99;
已选择50293行。
执行计划
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 73 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=99)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7633 consistent gets
126 physical reads
11464 redo size
2408070 bytes sent via SQL*Net to client
37272 bytes received via SQL*Net from client
3354 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50293 rows processed
重新收集统计信息,再次查询就对了,全部扫描!
SQL> set autotrace off;
SQL> exec dbms_stats.gather_table_stats(ownname=>'u1',tabname=>'t',cascade=>true);
PL/SQL 过程已成功完成。
SQL> select table_name,num_rows,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tables;
TABLE_NAME NUM_ROWS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ----------- -------------------
T 50294 96 2011-11-05 23:45:09
SQL> set autotrace traceonly;
SQL> select * from t where id = 99;
已选择50293行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50289 | 4714K| 170 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 50289 | 4714K| 170 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=99)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7475 consistent gets
0 physical reads
0 redo size
2408070 bytes sent via SQL*Net to client
37272 bytes received via SQL*Net from client
3354 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50293 rows processed
可以看出统计信息的重要性了吧,会影响一个sql的执行计划。