Lock表统计信息的隐患测试

2021-11-29 15:34:02

当lock表的统计信息时，如果后续创建索引成功，会导致索引统计信息为null这会为Oracle判断执行计划带来严重干扰，下面测试这个现象，并给出总结建议。
SQL> create table customers
2 (cust_id number primary key,
3 cust_name varchar2(200),
4 rating varchar2(1) not null )
5 partition by list (rating)
6 (
7 partition pA values ('A'),
8 partition pB values ('B')
9 );

insert into customers values(1,'name1','A');
insert into customers values(2,'name2','B');
SQL> commit;

Commit complete.

SQL> select * from customers;
CUST_ID CUST_NAME RA
---------- ------------------------------ --
1 name1 A
2 name2 B

创建表后统计信息没有自动收集
SQL>select table_name,last_analyzed from user_tables where table_name='CUSTOMERS'

TABLE_NAME LAST_ANALYZED
-------------------- -------------------
CUSTOMERS
分区表统计信息
SQL> select table_name,partition_name, last_analyzed from user_tab_partitions where table_name='CUSTOMERS'
TABLE_NAME PARTITION_NAME LAST_ANALYZED
-------------------- ------------------------------ -------------------
CUSTOMERS PA
CUSTOMERS PB

手工收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname =>'SYS',tabname => 'CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> select table_name,last_analyzed from user_tables where table_name='CUSTOMERS';

TABLE_NAME LAST_ANALYZED
-------------------- -------------------
CUSTOMERS 2021/11/29 14:37:01

SQL> select table_name,partition_name, last_analyzed from user_tab_partitions where table_name='CUSTOMERS';

TABLE_NAME PARTITION_NAME LAST_ANALYZED
-------------------- ------------------------------ -------------------
CUSTOMERS PA 2021/11/29 14:37:00
CUSTOMERS PB 2021/11/29 14:37:01

下面使用force选项

下面锁定表的统计信息
SQL> exec dbms_stats.lock_table_stats(ownname => 'SYS', tabname => 'CUSTOMERS');

PL/SQL procedure successfully completed

查询统计信息是否被锁定
SQL> select PARTITION_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,STATTYPE_LOCKED from user_tab_statistics where table_name='CUSTOMERS';

PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN STATTYPE_L
------------------------------ ---------- ---------- ---------------------- ----------
2 2 11 ALL
PA 1 1 11 ALL
PB 1 1 11 ALL

尝试收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname =>'SYS',tabname => 'CUSTOMERS');
BEGIN dbms_stats.gather_table_stats(ownname =>'SYS',tabname => 'CUSTOMERS'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40751
ORA-06512: at "SYS.DBMS_STATS", line 40035
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 39324
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at "SYS.DBMS_STATS", line 40732
ORA-06512: at line 1
下面使用force选项，收集分区表的统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'CUSTOMERS', granularity => 'PARTITION', force => TRUE);

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name, last_analyzed from user_tab_partitions where table_name='CUSTOMERS';

TABLE_NAME PARTITION_NAME LAST_ANALYZED
-------------------- ------------------------------ -------------------
CUSTOMERS PA 2021/11/29 15:04:49
CUSTOMERS PB 2021/11/29 15:04:49

下面尝试创建索引
SQL> create index idx_cust_name on customers(cust_name) online;

Index created.
下面查询改索引的统计信息
SQL> select BLEVEL,LEAF_BLOCKS,NUM_ROWS,LAST_ANALYZED,STATTYPE_LOCKED from user_ind_statistics where INDEX_NAME='IDX_CUST_NAME';

BLEVEL LEAF_BLOCKS NUM_ROWS LAST_ANALYZED STATTYPE_L
---------- ----------- ----------------------- ------------------- ----------
ALL

下面尝试收集索引统计信息
SQL> exec dbms_stats.gather_index_stats(ownname => 'SYS',indname => 'IDX_CUST_NAME');
BEGIN dbms_stats.gather_index_stats(ownname => 'SYS',indname => 'IDX_CUST_NAME'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 30110
ORA-06512: at "SYS.DBMS_STATS", line 30024
ORA-06512: at "SYS.DBMS_STATS", line 30001
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 29312
ORA-06512: at "SYS.DBMS_STATS", line 30097
ORA-06512: at line 1
报错
后续如何收集这种情况下的统计信息呢
SQL> exec dbms_stats.gather_index_stats(ownname => 'SYS',indname => 'IDX_CUST_NAME',force => TRUE);

PL/SQL procedure successfully completed.
下面确认：

SQL> select BLEVEL,LEAF_BLOCKS,NUM_ROWS,LAST_ANALYZED,STATTYPE_LOCKED from user_ind_statistics where INDEX_NAME='IDX_CUST_NAME';

BLEVEL LEAF_BLOCKS NUM_ROWS LAST_ANALYZED STATTYPE_L
---------- ----------- ---------- ------------------- ----------
0 1 2 2021/11/29 15:24:17 ALL

这里出现一个问题，也就是在表统计信息被锁定情况下，使用online关键字创建索引时，可以创建成功，但是统计信息没有，这对于优化器而言缺少了
准确的计算依据，可能导致执行计划不是优。

下面我们用创建索引的默认指令compute statistics显示创建索引

SQL> create index idx_cust_name on customers(cust_name) online compute statistics;
create index idx_cust_name on customers(cust_name) online compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
发现此时报错
虽然此时报错但是毕竟可以提示用户潜在问题所在，而使用online方式虽然创建成功，但是索引没有统计数据，可能导致执行计划异常，所以建议创建索引
的规范中使用如下方式创建索引，以避免索引没有统计信息导致的执行计划异常。

总结：
1 创建索引使用online compute statistics选项，防止索引创建成功但是统计信息为空导致执行计划异常。
2 收集统计信息好不要使用force => TRUE,根据上面分析自己取舍吧。
3 Oracle的优化器根据cost成本值确定执行计划，所以必须保障统计信息的准确，包括表和索引等对象。
4 查询统计信息时，表、索引都要查并且包含STATTYPE_LOCKED字段。

收集统计信息
SQL> exec dbms_stats.lock_table_stats(ownname => '<owner name>', tabname => 'SALES');
SQL> exec dbms_stats.gather_table_stats(ownname => '<owner name>', tabname => 'SALES', granularity => 'PARTITION', force => TRUE);
******
SQL> exec dbms_stats.lock_table_stats(ownname => '<owner name>', tabname => 'SALES');
SQL> exec dbms_stats.gather_table_stats(ownname => '<owner name>', tabname => 'SALES', partname => 'SALES_Q2_2006', force => TRUE);