Oracle：在分區表上全域資訊與分區資訊如何影響執行計畫

最近在研究DBMS_STATS包，下面是關於根據granularity的值的不同影響執行計畫的內容，我們通過DBMS_STATS包執行對錶或者索引統計資訊的時候指定幾個參數，分別為GLOBAL、PARTITION和SUBPARTITION。我將通過大量執行個體主要說明全域資訊和分區資訊如何影響執行計畫。

我的環境:

OS : Oracle Enterprise Linux 5.5 64Bit

DB Type : Oracle Restart

DB Version : 11.2.0.3

– 建立實驗對象分區表，並灌入資料

luocs@MAA> create table ltb3(id number,

2  name varchar2(100),

3  type varchar2(100)

4  ) partition by hash(id)

5  partitions 5

6  /

Table created.

luocs@MAA> insert into ltb3 select object_id, object_name, object_type from all_objects;

21111 rows created.

luocs@MAA> commit;

Commit complete.

luocs@MAA> create index inx_ltb3_local on ltb3(id) local;

Index created.

-- 剛剛初始化之後，沒有表或表分區的統計資訊，但新建立的本地索引分割區卻收集了統計資訊

luocs@MAA> col partition_name for a30

luocs@MAA> select PARTITION_NAME, NUM_ROWS, LAST_ANALYZED from user_tab_partitions where table_name='LTB3';

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED

------------------------------ ---------- -----------------------

SYS_P101

SYS_P102

SYS_P103

SYS_P104

SYS_P105

luocs@MAA> select PARTITION_NAME, STATUS, NUM_ROWS, LAST_ANALYZED from user_ind_partitions where index_name='INX_LTB3_LOCAL';

PARTITION_NAME                 STATUS             NUM_ROWS LAST_ANALYZED

------------------------------ ---------------- ---------- -----------------------

SYS_P106                       USABLE                 2653 19-JAN-2013 22:30:32

SYS_P107                       USABLE                 5234 19-JAN-2013 22:30:32

SYS_P108                       USABLE                 5414 19-JAN-2013 22:30:32

SYS_P109                       USABLE                 5232 19-JAN-2013 22:30:32

SYS_P110                       USABLE                 2578 19-JAN-2013 22:30:32

luocs@MAA> select num_rows, avg_row_len, last_analyzed from user_tables where table_name='LTB3';

NUM_ROWS AVG_ROW_LEN LAST_ANALYZED

---------- ----------- -----------------------

luocs@MAA> col INDEX_NAME for a25

luocs@MAA> select INDEX_NAME, NUM_ROWS, STATUS, LAST_ANALYZED from user_indexes where table_name='LTB3';

INDEX_NAME                  NUM_ROWS STATUS           LAST_ANALYZED

------------------------- ---------- ---------------- -----------------------

INX_LTB3_LOCAL                 21111 N/A              19-JAN-2013 22:30:32

-- 手動進行了分析,預設情況下是全域分析

luocs@MAA> exec dbms_stats.gather_table_stats(user,'ltb3',cascade=>true);

PL/SQL procedure successfully completed.

luocs@MAA> select PARTITION_NAME, NUM_ROWS, LAST_ANALYZED from user_tab_partitions where table_name='LTB3';

PARTITION_NAME                   NUM_ROWS LAST_ANALYZED

------------------------------ ---------- -----------------------

SYS_P101                             2653 19-JAN-2013 22:57:13

SYS_P102                             5234 19-JAN-2013 22:57:13

SYS_P103                             5414 19-JAN-2013 22:57:13

SYS_P104                             5232 19-JAN-2013 22:57:13

SYS_P105                             2578 19-JAN-2013 22:57:13

luocs@MAA> select PARTITION_NAME, STATUS, NUM_ROWS, LAST_ANALYZED from user_ind_partitions where index_name='INX_LTB3_LOCAL';

PARTITION_NAME                 STATUS             NUM_ROWS LAST_ANALYZED

------------------------------ ---------------- ---------- -----------------------

SYS_P106                       USABLE                 2653 19-JAN-2013 22:57:13

SYS_P107                       USABLE                 5234 19-JAN-2013 22:57:13

SYS_P108                       USABLE                 5414 19-JAN-2013 22:57:13

SYS_P109                       USABLE                 5232 19-JAN-2013 22:57:13

SYS_P110                       USABLE                 2578 19-JAN-2013 22:57:13

luocs@MAA> select num_rows, avg_row_len, last_analyzed from user_tables where table_name='LTB3';

NUM_ROWS AVG_ROW_LEN LAST_ANALYZED

---------- ----------- -----------------------

21111          32 19-JAN-2013 22:57:13

luocs@MAA> select INDEX_NAME, NUM_ROWS, STATUS, LAST_ANALYZED from user_indexes where table_name='LTB3';

INDEX_NAME                  NUM_ROWS STATUS           LAST_ANALYZED

------------------------- ---------- ---------------- -----------------------

INX_LTB3_LOCAL                 21111 N/A              19-JAN-2013 22:57:13