關於oracle11g與12C只放圖簡單測試
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於oracle session的簡單測試OracleSession
- 關於一個簡單介面的併發測試與優化記錄優化
- 關於 Android 單元測試Android
- Python容器相關簡單效能測試Python
- 12C 單例項-測試庫-圖形介面-安裝步驟單例
- Angular單元測試如何只執行指定的測試用例，提高測試速度Angular
- 關於前端開發談談單元測試前端
- 關於單例及靜態變數測試單例變數
- 關於IOS測試iOS
- 關於 RabbitMQ 的安裝與簡單使用MQ
- mysql簡單效能測試MySql
- sql trace 簡單測試SQL
- 基於函式index的一點簡單測試！函式Index
- 關於orm的個人測試——SqlSugar與FreeSqlORMSqlSugar
- 關於發版測試的認知與案例
- 關於軟體驗證中的單元測試
- 關於安卓 sdk 測試安卓
- Oracle logmnr簡單測試Oracle
- mysqlimport匯入簡單測試MySqlImport
- （一）Jmeter簡單介面測試JMeter
- Windows IO 效能簡單測試Windows
- Windows 10 TechPreview簡單測試WindowsView
- 簡單的 ping 測試
- Jmeter效能測試簡單使用JMeter
- try的簡單效能測試
- oracle 12c pdb測試：建立、開關、刪除Oracle
- Flutter 官方嘗試放只“鴿子”來簡化Native外掛開發Flutter
- JavaScript圖片簡單等比例縮放JavaScript
- 單元測試與MockitoMockito
- 單元測試與 PowerMockMock
- PHP 單元測試與資料庫測試PHP資料庫
- 簡單對比測試了幾個基於 swoole 的框架框架
- 介面測試之基於LoadRunner的一個簡單示例
- 關於測試流程的思考
- 請叫高手關於測試
- 【PG效能測試】pgbench效能測試工具簡單使用
- 讓 API 測試變的簡單API
- Linux的OOMkiller簡單測試LinuxOOM