【效能優化】CBO,RBO在ORACLE 10g 中的應用

奇怪的執行計劃 10g 開始 oracle 就預設使用cbo 來代替rbo 來選擇合適的執行計劃，但是今天遇到的一個例子，有點例外：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      CHOOSE
optimizer_secure_view_merging        boolean     TRUE

SQL> create table t as select * from dba_objects;

Table created.

SQL> set timing on
SQL> set autot traceonly
SQL> select count(*) from t;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362

-----------------------------------
| Id  | Operation                    | Name |
-----------------------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE      |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        737  consistent gets
        732  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看出 該執行計劃使用的是rbo 並提示考慮使用cbo 。而當對該表進行資訊統計之後，執行計劃就會使用cbo。

SQL> exec dbms_stats.gather_table_stats (user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.52
SQL> select count(*) from t;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   163   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE      |      |     1 |                  |          |
|   2 |   TABLE ACCESS FULL | T  | 53482 |   163   (1)| 00:00:02 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
         42  recursive calls
          0  db block gets
        741  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

由此看見在沒有進行資訊統計的情況下，10g 會使用 RBO 而進行了資訊統計後就會使用 CBO 。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-672778/，如需轉載，請註明出處，否則將追究法律責任。