灌入大量数据后手工采集统计信息的重要性 - 数据库编程

1. 创建测试表TBL_STAT，及索引，但不插入记录

SQL> create table TBL_STAT as select * from dba_objects where 1<>1;
Table created.

SQL> create index idx_tbl_stat on tbl_stat (object_id);
Index created.

SQL> select count(*) from tbl_stat;
COUNT(*)
----------
0

2. 检索TBL_STAT的执行计划 SQL> explain plan for select object_name from tbl_stat where object_id = 1;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2448091186
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TBL_STAT | 1 | 79 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement
17 rows selected.

发现按照索引字段查询使用的是全表扫描。
3. 手工收集TBL_STAT表的统计信息 SQL> exec dbms_stats.gather_table_stats(ownname=>'DCSOPEN', tabname=>'TBL_STAT', estimate_percent=>100);
PL/SQL procedure successfully completed.

4. 再次检索TBL_STAT表

SQL> explain plan for select object_name from tbl_stat where object_id = 1;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3529113932
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBL_STAT | 1 | 79 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TBL_STAT | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
14 rows selected.

发现这次用到了索引范围扫描，说明收集统计信息让Oracle可以选择正确的执行计划路径。
5. 插入100万的测试记录 SQL> begin

2 for i in 1 .. 10 loop
3 insert into tbl_stat select * from dba_objects;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from tbl_stat;
COUNT(*)
----------
1190725

6. 查看检索TBL_STAT表的执行计划 SQL> explain plan for select object_name from tbl_stat where object_id = 1;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3529113932
-------------------------

灌入大量数据后手工采集统计信息的重要性(一)