在Oracle 11.2.0.1.0下dbms_stats.gather_table_stats收集直方图不准
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> --制造一些数据
SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> update test set object_id=2;
SQL> update test set object_id=1 where rownum=1;
SQL> commit;
SQL> create index ind_t_object_id on test(object_id);
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);
SQL> --看看数据的分布
SQL> select object_id,count(1) from test group by object_id;
OBJECT_ID COUNT(1)
---------- ----------
1 1
2 72415
SQL> set autotrace traceonly
SQL> --应该是要走索引
SQL> select * from test where object_id = 1;
运行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36208 | 3359K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 36208 | 3359K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1039 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> --应该是要走全表扫描
SQL> select * from test where object_id = 2;
已选择72415行。
运行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36208 | 3359K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 36208 | 3359K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
2940934 bytes sent via SQL*Net to client
53435 bytes received via SQL*Net from client
4829 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72415 rows processed
SQL> set autotrace off
SQL> col TABLE_NAME format a10;
SQL> col COLUMN_NAME format a10;
SQL> col ENDPOINT_ACTUAL_VALUE format a10;
SQL> col ENDPOINT_NUMBER format 9999999;
SQL> col ENDPOINT_VALUE format 999999;
SQL>--直方图有问题。又一次收集直方图
SQL> select * from user_tab_histograms s where s.table_name='TEST' and column_name='OBJECT_ID';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
TEST OBJECT_ID 0 1
TEST OBJECT_ID 1 2
SQL> exec dbms_stats.gather_table_stats(user, 'test',cascade=>true, method_opt=>'for columns object_id size 2');
SQL> set autotrace traceonly
SQL> --还是不正确
SQL> select * from test where object_id = 1;
运行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36208 | 3359K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 36208 | 3359K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1039 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> select * from user_tab_histograms s where s.table_name='TEST' and column_name='OBJECT_ID';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
TEST OBJECT_ID 5391 2
SQL> --仅仅实用analyze收集直方图
SQL> analyze table test compute statistics for table for columns object_id size 2;
SQL> select * from user_tab_histograms s where s.table_name='TEST' and column_name='OBJECT_ID';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
TEST OBJECT_ID 1 1
TEST OBJECT_ID 72416 2
SQL> set autotrace traceonly
SQL> select * from test where object_id = 1;
运行计划
----------------------------------------------------------
Plan hash value: 255872589
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 99 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test where object_id = 2;
运行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72415 | 7001K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 72415 | 7001K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
2940934 bytes sent via SQL*Net to client
53435 bytes received via SQL*Net from client
4829 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72415 rows processed
SQL> set autotrace off
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> --制造一些数据
SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> update test set object_id=2;
SQL> update test set object_id=1 where rownum=1;
SQL> commit;
SQL> create index ind_t_object_id on test(object_id);
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);
SQL> --看看数据的分布
SQL> select object_id,count(1) from test group by object_id;
OBJECT_ID COUNT(1)
---------- ----------
1 1
2 72415
SQL> set autotrace traceonly
SQL> --应该是要走索引
SQL> select * from test where object_id = 1;
运行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36208 | 3359K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 36208 | 3359K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1039 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> --应该是要走全表扫描
SQL> select * from test where object_id = 2;
已选择72415行。
运行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36208 | 3359K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 36208 | 3359K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
2940934 bytes sent via SQL*Net to client
53435 bytes received via SQL*Net from client
4829 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72415 rows processed
SQL> set autotrace off
SQL> col TABLE_NAME format a10;
SQL> col COLUMN_NAME format a10;
SQL> col ENDPOINT_ACTUAL_VALUE format a10;
SQL> col ENDPOINT_NUMBER format 9999999;
SQL> col ENDPOINT_VALUE format 999999;
SQL>--直方图有问题。又一次收集直方图
SQL> select * from user_tab_histograms s where s.table_name='TEST' and column_name='OBJECT_ID';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
TEST OBJECT_ID 0 1
TEST OBJECT_ID 1 2
SQL> exec dbms_stats.gather_table_stats(user, 'test',cascade=>true, method_opt=>'for columns object_id size 2');
SQL> set autotrace traceonly
SQL> --还是不正确
SQL> select * from test where object_id = 1;
运行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36208 | 3359K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 36208 | 3359K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1039 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> select * from user_tab_histograms s where s.table_name='TEST' and column_name='OBJECT_ID';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
TEST OBJECT_ID 5391 2
SQL> --仅仅实用analyze收集直方图
SQL> analyze table test compute statistics for table for columns object_id size 2;
SQL> select * from user_tab_histograms s where s.table_name='TEST' and column_name='OBJECT_ID';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
TEST OBJECT_ID 1 1
TEST OBJECT_ID 72416 2
SQL> set autotrace traceonly
SQL> select * from test where object_id = 1;
运行计划
----------------------------------------------------------
Plan hash value: 255872589
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 99 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test where object_id = 2;
运行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72415 | 7001K| 290 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 72415 | 7001K| 290 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
2940934 bytes sent via SQL*Net to client
53435 bytes received via SQL*Net from client
4829 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72415 rows processed
SQL> set autotrace off
