最近在研究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
-- 查看查询执行计划
luocs@MAA> select count(*) from ltb3 partition(SYS_P101);
COUNT(*)
----------
2653
luocs@MAA> select count(*) from ltb3 where id < 4000;
COUNT(*)
----------
3833
luocs@MAA> set autot trace exp stat
luocs@MAA> select count(*) from ltb3 where id < 4000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2331304906
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 9 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 5 | | | | |
| 2 | PARTITION HASH ALL| | 3400 | 17000 | 9 (0)| 00:00:01 | 1 | 5 |
|* 3 | INDEX RANGE SCAN | INX_LTB3_LOCAL | 3400 | 17000 | 9 (0)| 00:00:01 | 1 | 5 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<4000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
luocs@MAA> select count(*) from ltb3 partition(SYS_P101);
Execution Plan
----------------------------------------------------------
Plan hash value: 4136567958
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION HASH SINGLE| | 2653 | 14 (0)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS FULL | LTB3 | 2653 | 14 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
54 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- 再插入数据，统计信息并不会改变
luocs@MAA> insert into ltb3 select * from ltb3;
21111 rows created.
luocs@MAA> commit;
Commit complete.
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
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 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> exec dbms_stats.gather_table_stats(user,'ltb3',cascade=>true,granularity=>'partition');
PL/SQL procedure successfully completed.
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
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 5306 19-JAN-2013 23:07:39
SYS_P102 10468 19-JAN-2013 23:07:39
SYS_P103 10828 19-JAN-2013 23:07:39
SYS_P104 10464 19-JAN-2013 23:07:39
SYS_P105 5156 19-JAN-2013 23:07:39
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 5306 19-JAN-2013 23:07:39
SYS_P107 USABLE 10468 19-JAN-2013 23:07:39
SYS_P108 USABLE 10828 19-JAN-2013 23:07:39
SYS_P109 USABLE 10464 19-JAN-2013 23:07:39
SYS_P110 USABLE 5156 19-JAN-2013 23:07:39
-- 这时候发现仅仅更新了分区的统计信息
-- 查看查询语句的统计信息
luocs@MAA> select count(*) from ltb3 where id < 4000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2331304906
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 9 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 5 | | | | |
| 2 | PARTITION HASH ALL| | 3400 | 17000 | 9 (0)| 00:00:01 | 1 | 5 |
|* 3 | INDEX RANGE SCAN | INX_LTB3_LOCAL | 3400 | 17000 | 9 (0)| 00:00:01 | 1 | 5 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<4000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
luocs@MAA> select count(*) from ltb3 partition(SYS_P101);
Execution Plan
----------------------------------------------------------
Plan hash value: 4136567958
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION HASH SINGLE| | 5306 | 14 (0)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS FULL | LTB3 | 5306 | 14 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
54 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- 而我们此时的查询结果如下
luocs@MAA> select count(*) from ltb3 partition(SYS_P101);
COUNT(*)
----------
5306
luocs@MAA> select count(*) from ltb3 where id < 4000;
COUNT(*)
----------
7666
-- 可见查询分区里头的数据(partition(SYS_P101))时候,CBO估算出的行数非常正确,选择执行计划也正确,而id<4000查询语句，CBO依然选择旧的执行计划，估算出的行数也不正确。
-- 进行全局更新下
luocs@MAA> exec dbms_stats.gather_table_stats(user,'ltb3',cascade=>true,granularity=>'global');
PL/SQL procedure successfully completed.
luocs@MAA> select num_rows, avg_row_len, last_analyzed from user_tables where table_name='LTB3';
NUM_ROWS AVG_ROW_LEN LAST_ANALYZED
---------- ----------- -----------------------
42222 32 19-JAN-2013 23:13:08
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 42222 N/A 19-JAN-2013 23:13:08
-- 这样CBO估算出的行数也正确了
luocs@MAA> select count(*) from ltb3 where id < 4000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2331304906
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 18 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 5 | | | | |
| 2 | PARTITION HASH ALL| | 6800 | 34000 | 18 (0)| 00:00:01 | 1 | 5 |
|* 3 | INDEX RANGE SCAN | INX_LTB3_LOCAL | 6800 | 34000 | 18 (0)| 00:00:01 | 1 | 5 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<4000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
– 那我们看一下一开始仅进行分区分析会怎样
-- 删除统计信息
luocs@MAA> exec dbms_stats.delete_table_stats(user,'ltb3');
PL/SQL procedure successfully completed.
-- 发现删除表的统计信息级联删除索引统计信息
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> 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 N/A
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
SYS_P107 USABLE
SYS_P108 USABLE
SYS_P109 USABLE
SYS_P110 USABLE
-- 进行分区级分析
luocs@MAA> exec dbms_stats.gather_table_stats(user,'ltb3',cascade=>true,granularity=>'partition');
PL/SQL procedure successfully completed.
-- 发现分区级分区也更新了全局信息，这也就是合并分区信息了
luocs@MAA> select num_rows, avg_row_len, last_analyzed from user_tables where table_name='LTB3';
NUM_ROWS AVG_ROW_LEN LAST_ANALYZED
---------- ----------- -----------------------
42222 32 19-JAN-2013 23:17:16
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 42222 N/A 19-JAN-2013 23:17:16
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 5306 19-JAN-2013 23:17:16
SYS_P102 10468 19-JAN-2013 23:17:16
SYS_P103 10828 19-JAN-2013 23:17:16
SYS_P104 10464 19-JAN-2013 23:17:16
SYS_P105 5156 19-JAN-2013 23:17:16
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 5306 19-JAN-2013 23:17:16
SYS_P107 USABLE 10468 19-JAN-2013 23:17:16
SYS_P108 USABLE 10828 19-JAN-2013 23:17:16
SYS_P109 USABLE 10464 19-JAN-2013 23:17:16
SYS_P110 USABLE 5156 19-JAN-2013 23:17:16
-- 但之后进行全局分析，这时发现它仅更新全局信息，并不分析分区信息
luocs@MAA> exec dbms_stats.gather_table_stats(user,'ltb3',cascade=>true,granularity=>'global');
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 5306 19-JAN-2013 23:27:42
SYS_P102 10468 19-JAN-2013 23:27:42
SYS_P103 10828 19-JAN-2013 23:27:42
SYS_P104 10464 19-JAN-2013 23:27:42
SYS_P105 5156 19-JAN-2013 23:27:42
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
SYS_P107 USABLE
SYS_P108 USABLE
SYS_P109 USABLE
SYS_P110 USABLE
luocs@MAA> select num_rows, avg_row_len, last_analyzed from user_tables where table_name='LTB3';
NUM_ROWS AVG_ROW_LEN LAST_ANALYZED
---------- ----------- -----------------------
42222 32 19-JAN-2013 23:30:39
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 42222 N/A 19-JAN-2013 23:30:39
-- 那进行分区级分析，则是仅更新分区信息，而不更新全局信息
luocs@MAA> exec dbms_stats.gather_table_stats(user,'ltb3',cascade=>true,granularity=>'partition');
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 5306 19-JAN-2013 23:32:13
SYS_P102 10468 19-JAN-2013 23:32:13
SYS_P103 10828 19-JAN-2013 23:32:13
SYS_P104 10464 19-JAN-2013 23:32:13
SYS_P105 5156 19-JAN-2013 23:32: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 5306 19-JAN-2013 23:32:13
SYS_P107 USABLE 10468 19-JAN-2013 23:32:13
SYS_P108 USABLE 10828 19-JAN-2013 23:32:13
SYS_P109 USABLE 10464 19-JAN-2013 23:32:13
SYS_P110 USABLE 5156 19-JAN-2013 23:32: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
---------- ----------- -----------------------
42222 32 19-JAN-2013 23:30:39
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 42222 N/A 19-JAN-2013 23:30:39
– 再看看单独删除索引收集信息的情况，默认级联删除分区索引信息
luocs@MAA> exec dbms_stats.delete_index_stats(user,'inx_ltb3_local');
PL/SQL procedure successfully completed.
luocs@MAA> select num_rows, avg_row_len, last_analyzed from user_tables where table_name='LTB3';
NUM_ROWS AVG_ROW_LEN LAST_ANALYZED
---------- ----------- -----------------------
42222 32 19-JAN-2013 23:17:16
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 N/A
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 5306 19-JAN-2013 23:17:16
SYS_P102 &nbs,p; 10468 19-JAN-2013 23:17:16
SYS_P103 10828 19-JAN-2013 23:17:16
SYS_P104 10464 19-JAN-2013 23:17:16
SYS_P105 5156 19-JAN-2013 23:17:16
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
SYS_P107 USABLE
SYS_P108 USABLE
SYS_P109 USABLE
SYS_P110 USABLE
PROCEDURE DELETE_INDEX_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
INDNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
CASCADE_PARTS BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
PROCEDURE DELETE_TABLE_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
CASCADE_PARTS BOOLEAN IN DEFAULT
CASCADE_COLUMNS BOOLEAN IN DEFAULT
CASCADE_INDEXES BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
FORCE BOOLEAN IN DEFAULT
-- 不级联删除分区索引信息，这时候分区索引信息将保留
luocs@MAA> exec dbms_stats.delete_index_stats(user,'inx_ltb3_local',cascade_parts=>FALSE);
PL/SQL procedure successfully completed.
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 N/A
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 5306 19-JAN-2013 23:33:48
SYS_P107 USABLE 10468 19-JAN-2013 23:33:48
SYS_P108 USABLE 10828 19-JAN-2013 23:33:48
SYS_P109 USABLE 10464 19-JAN-2013 23:33:48
SYS_P110 USABLE 5156 19-JAN-2013 23:33:48
-- 再收集全局信息以恢复索引收集信息
luocs@MAA> exec dbms_stats.gather_table_stats(user,'ltb3');
PL/SQL procedure successfully completed.
-- 这次不级联删除索引收集信息，只删除表收集信息，也不级联删除分区信息
luocs@MAA> exec dbms_stats.delete_table_stats(user,'ltb3',cascade_parts=>false,cascade_indexes=>false);
PL/SQL procedure successfully completed.
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> 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 42222 N/A 19-JAN-2013 23:35:28
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 5306 19-JAN-2013 23:35:27
SYS_P102 10468 19-JAN-2013 23:35:27
SYS_P103 10828 19-JAN-2013 23:35:27
SYS_P104 10464 19-JAN-2013 23:35:27
SYS_P105 5156 19-JAN-2013 23:35:27
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 5306 19-JAN-2013 23:35:28
SYS_P107 USABLE 10468 19-JAN-2013 23:35:28
SYS_P108 USABLE 10828 19-JAN-2013 23:35:28
SYS_P109 USABLE 10464 19-JAN-2013 23:35:28
SYS_P110 USABLE 5156 19-JAN-2013 23:35:28
11g还有个增量统计，Oracle会增量的收集分区信息来更新全局信息，找机会再研究一下。
扫一扫
5477
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
