oracle 11g中小测:
SQL> create table tab(a number,b number);
Table created.
SQL> insert into tab select rownum,rownum from dual connect by level <=10000;
10000 rows created.
SQL> update tab set b=5 where b between 6 and 9995;
9990 rows updated.
SQL> commit;
Commit complete.
SQL> show user
USER is "GUIJIAN"
SQL> create index tab_b_idx on tab(b);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
SQL>
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
PL/SQL procedure successfully completed.
SQL> SET LINE 120 PAGESIZE 9999
SQL> COL TABLE_NAME FOR A40
SQL> COL COLUMN_NAME FOR A30
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_tab_histograms where TABLE_NAME='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ------------------------------ --------------- --------------
TAB B 0 1
TAB B 1 10000
SQL> SET AUTOT TRACEONLY
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 157166354
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 7000 | 4 (0) | 00:00:01 |
| 1 | RESULT CACHE | fthfr4cg5hx1d9xyqptnrkvasm | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 7000 | 4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB_B_IDX | 1000 | | 2 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=5)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(GUIJIAN.TAB); attributes=(ordered); name="select * from tab where b=5"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1369 consistent gets
0 physical reads
0 redo size
212182 bytes sent via SQL*Net to client
7846 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> SET AUTOT OFF
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true);
PL/SQL procedure successfully completed.
SQL> SET AUTOT TRACEONLY
SQL> select * from tab where b=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 157166354
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8fvut8ab0yprn28nk7n8zfgqxx | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TAB | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB_B_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=1)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(GUIJIAN.TAB); attributes=(ordered); name="select * from tab where b=1"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
520 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 tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 69937 | 7 (0)| 00:00:01 |
| 1 | RESULT CACHE | 2g2jk82jgbp6tfn51zxvwrg9ms | | | | |
|* 2 | TABLE ACCESS FULL | TAB | 9991 | 69937 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"=5)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(GUIJIAN.TAB); name="select * from tab where b=5"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
687 consistent gets
0 physical reads
0 redo size
212182 bytes sent via SQL*Net to client
7846 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> /
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ------------------------------ --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
TAB A 0 1
TAB A 1 10000
12 rows selected.
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true,method_opt=>'FOR COLUMNS B size 8');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ------------------------------ --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
TAB A 0 1
TAB A 1 10000
SQL>
12C中小测试:
SQL> update tab set b=5 where b between 6 and 9995;
9990 rows updated.
SQL> commit;
Commit complete.
SQL> create index tab_b_idx on tab(b);
Index created.
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_tab_histograms where TABLE_NAME='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------------------------ --------------- --------------
TAB B 0 1
TAB B 1 10000
SQL> set autot traceonly
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3198923545
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 700 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 100 | 700 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | 1000 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1370 consistent gets
0 physical reads
0 redo size
249496 bytes sent via SQL*Net to client
7878 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab where b=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3198923545
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
605 bytes sent via SQL*Net to client
552 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 tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3198923545
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 707 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 101 | 707 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1370 consistent gets
0 physical reads
0 redo size
249496 bytes sent via SQL*Net to client
7878 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> set autot off
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------------------------ --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
TAB A 0 1
TAB A 1 10000
12 rows selected.
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 8');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------------------------ --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9998
TAB B 9997 9999
TAB B 9998 10000
TAB A 0 1
TAB A 1 10000
10 rows selected.
SQL> set autot traceonly
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3198923545
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 707 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 101 | 707 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=5)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1370 consistent gets
0 physical reads
0 redo size
249496 bytes sent via SQL*Net to client
7878 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL>
差异:11g与12C在执行计划中的rows中评估值存在较大差异......下班了,待续............
SQL> create table tab(a number,b number);
Table created.
SQL> insert into tab select rownum,rownum from dual connect by level <=10000;
10000 rows created.
SQL> update tab set b=5 where b between 6 and 9995;
9990 rows updated.
SQL> commit;
Commit complete.
SQL> show user
USER is "GUIJIAN"
SQL> create index tab_b_idx on tab(b);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
SQL>
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
PL/SQL procedure successfully completed.
SQL> SET LINE 120 PAGESIZE 9999
SQL> COL TABLE_NAME FOR A40
SQL> COL COLUMN_NAME FOR A30
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_tab_histograms where TABLE_NAME='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ------------------------------ --------------- --------------
TAB B 0 1
TAB B 1 10000
SQL> SET AUTOT TRACEONLY
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 157166354
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 7000 | 4 (0) | 00:00:01 |
| 1 | RESULT CACHE | fthfr4cg5hx1d9xyqptnrkvasm | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB | 1000 | 7000 | 4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB_B_IDX | 1000 | | 2 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=5)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(GUIJIAN.TAB); attributes=(ordered); name="select * from tab where b=5"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1369 consistent gets
0 physical reads
0 redo size
212182 bytes sent via SQL*Net to client
7846 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> SET AUTOT OFF
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true);
PL/SQL procedure successfully completed.
SQL> SET AUTOT TRACEONLY
SQL> select * from tab where b=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 157166354
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 8fvut8ab0yprn28nk7n8zfgqxx | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TAB | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB_B_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=1)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(GUIJIAN.TAB); attributes=(ordered); name="select * from tab where b=1"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
520 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 tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 69937 | 7 (0)| 00:00:01 |
| 1 | RESULT CACHE | 2g2jk82jgbp6tfn51zxvwrg9ms | | | | |
|* 2 | TABLE ACCESS FULL | TAB | 9991 | 69937 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"=5)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(GUIJIAN.TAB); name="select * from tab where b=5"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
687 consistent gets
0 physical reads
0 redo size
212182 bytes sent via SQL*Net to client
7846 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> /
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ------------------------------ --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
TAB A 0 1
TAB A 1 10000
12 rows selected.
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------------- ------------------------------ --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
TAB A 0 1
TAB A 1 10000
SQL>
12C中小测试:
SQL> update tab set b=5 where b between 6 and 9995;
9990 rows updated.
SQL> commit;
Commit complete.
SQL> create index tab_b_idx on tab(b);
Index created.
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_tab_histograms where TABLE_NAME='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------------------------ --------------- --------------
TAB B 0 1
TAB B 1 10000
SQL> set autot traceonly
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3198923545
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 700 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 100 | 700 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | 1000 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1370 consistent gets
0 physical reads
0 redo size
249496 bytes sent via SQL*Net to client
7878 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab where b=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3198923545
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
605 bytes sent via SQL*Net to client
552 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 tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3198923545
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 707 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 101 | 707 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1370 consistent gets
0 physical reads
0 redo size
249496 bytes sent via SQL*Net to client
7878 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL> set autot off
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------------------------ --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
TAB A 0 1
TAB A 1 10000
12 rows selected.
SQL> exec dbms_stats.gather_table_stats('GUIJIAN','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 8');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ------------------------------ --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9998
TAB B 9997 9999
TAB B 9998 10000
TAB A 0 1
TAB A 1 10000
10 rows selected.
SQL> set autot traceonly
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3198923545
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 707 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 101 | 707 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB_B_IDX | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=5)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1370 consistent gets
0 physical reads
0 redo size
249496 bytes sent via SQL*Net to client
7878 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
SQL>
差异:11g与12C在执行计划中的rows中评估值存在较大差异......下班了,待续............
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28612416/viewspace-1562586/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28612416/viewspace-1562586/