数据库Oracle 11.2.0.4 RAC 2节点，业务反馈SQL执行缓慢，发现执行计划是全表扫描，前一天是走索引很快，猜测是统计信息不准确导致。
后续对分区表99G，收集统计信息后，业务反馈SQL走索引，恢复正常。
一、实际上收集统计信息的操作
观察如下链接
http://www.oracleplus.net/arch/1158.html
https://www.cnblogs.com/kawashibara/p/9762724.html
小结
收集统计信息，使用cascade=>true，不加分区参数，会对表全局，及级联的索引全局索引收集统计信息；
partname=>'PARTTABLE_1998'，只对指定分区的统计信息进行改变，表的全局，及其它分区信息不改变；
granularity=>'GLOBAL'，只修改分区表的全局统计信息，但是具体的分区统计信息不改变；
granularity=>'AUTO'，默认,oracle自动根据分区类型选择粒度，会对全局和所有分区统计信息均发生改变；
granularity=>'ALL'，分区表全局+分区表均收集统计信息，明确指定；
SQL> select owner,table_name,PARTITIONED,last_analyzed from dba_tables where owner='Oxx' ORDER BY 3; OWNER TABLE_NAME PAR LAST_ANALYZED ------------------------------ ------------------------------ --- ------------------- Oxx OMxx NO 2020-05-12 22:00:06 Oxx OMxx_LOG YES 2020-05-13 13:59:33 [xx]$ cat analy.sh sqlplus / as sysdba <<EOF set timing on set time on spool /home/oracle/analyze.log select sysdate from dual; exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'xx', tabname=>'xx',granularity=>'ALL',cascade=>true,estimate_percent=>30,degree=>6); exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'xx', tabname=>'xx',cascade=>true,estimate_percent=>30,degree=>6); exit; EOF -- Elapsed: 00:52:19.97
Elapsed: 00:02:13.45
--查询段大小
select sum(bytes)/1024/1024/1024 from dba_segments where owner='xx' and segment_name='xx';
--查询session信息
set pagesize 2000
set linesize 210
col sql_id for a14
col s_time for a12
col status for a6
col event format a30
col username for a14
col osuser for a8
col p1 for 9999999
col sid for 9999
col p2 for 999999
col p3 for 9999999999999
col program format a25
Col machine for a20
col serial# format 99999
select substr(b.program, 1, 25) program, b.sid, b.serial#, b.username, substr(b.osuser, 1, 8) osuser,
b.sql_id, substr(b.machine, 1, 20) machine, b.status, to_char(b.SQL_EXEC_START, 'dd hh24:mi:ss') s_time,
(case when b.STATE = 'WAITING' then b.event else 'CPU' end) event from v$session_wait a, v$session b
where a.sid = b.sid and status = 'ACTIVE' and not (b.type = 'BACKGROUND' and b.state = 'WAITING'
and b.wait_class = 'Idle') Order by program, sid, sql_id;
--查询统计信息收集进度
set linesize 200 pagesize 50
col opname for a40
col target for a30
col message for a40
select sid,opname,target,sofar,totalwork,round(sofar/totalwork,4)*100 per,message FROM
V$SESSION_LONGOPS where (sid,serial#) in (select sid,serial# from v$session where type='USER')
and sofar<>totalwork order by sid,serial#;
二、分区表统计信息收集测试
收集统计信息的参数变化可以看如下链接 https://www.cnblogs.com/jerome-lamb/p/7535014.html --创建测试对象 create table range_part_tab(id number, deal_date date, area_code number, contents varchar2(4000)) partition by range(deal_date) ( partition p1 values less than(to_date('2017-02-01','yyyy-mm-dd')), partition p2 values less than(to_date('2017-03-01','yyyy-mm-dd')), partition p3 values less than(to_date('2017-04-01','yyyy-mm-dd')), partition p4 values less than(to_date('2017-05-01','yyyy-mm-dd')), partition p5 values less than(to_date('2017-06-01','yyyy-mm-dd')), partition p6 values less than(to_date('2017-07-01','yyyy-mm-dd')), partition p7 values less than(to_date('2017-08-01','yyyy-mm-dd')), partition p8 values less than(to_date('2017-09-01','yyyy-mm-dd')), partition p9 values less than(to_date('2017-10-01','yyyy-mm-dd')), partition p10 values less than(to_date('2017-11-01','yyyy-mm-dd')), partition p11 values less than(to_date('2017-12-01','yyyy-mm-dd')), partition p12 values less than(to_date('2018-01-01','yyyy-mm-dd')), partition p_max values less than(maxvalue)) ; insert into range_part_tab (id,deal_date,area_code,contents) select rownum, to_date(to_char(sysdate-365,'J')+ trunc(dbms_random.value(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum <= 100000; SQL> r 1* insert into RANGE_PART_TAB select * from RANGE_PART_TAB commit; SQL> select sum(bytes)/1024/1024 from user_segments; SUM(BYTES)/1024/1024 -------------------- 1539 SQL> create index RANGE_PART_TAB_ind_id on RANGE_PART_TAB(id) local; SQL> select TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,SAMPLE_SIZE,PARTITIONED,LAST_ANALYZED from user_tables; TABLE_NAME STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE PAR LAST_ANALYZED ------------------------------ -------- ---------- ---------- ------------ ----------- ------------- --- RANGE_PART_TAB VALID YES --创建索引后,会自动收集索引的统计信息，但是不包括表的！ SQL> select index_name,TABLE_NAME,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,STATUS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED,PARTITIONED from user_indexes; INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL PAR -------------------------------------------------------------------------------- -------- ------------------------------ RANGE_PART_TAB_IND_ID RANGE_PART_TAB 2 7094 100000 N/A 3200000 3200000 19-MAY-20 YES SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,SAMPLE_SIZE,LAST_ANALYZED,HIGH_VALUE from user_tab_partitions order by 2; TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE LAST_ANAL HIGH_VALUE ------------------------------ ------------------------------ ---------- ---------- ------------ ----------- --------- -------------------------------------------------------------------------------- RANGE_PART_TAB P1 TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P10 TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P11 TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P12 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P2 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P3 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P4 TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P5 TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P6 TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P7 TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P8 TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P9 TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P_MAX MAXVALUE 13 rows selected. SQL> select index_name,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED from user_ind_partitions; INDEX_NAME PARTITION_NAME STATUS BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS SAMPLE_SIZE LAST_ANAL ------------------------------ ------------------------------ -------- ---------- ----------- ------------- ---------- ----------- --------- RANGE_PART_TAB_IND_ID P_MAX USABLE 2 7094 100000 3200000 3200000 19-MAY-20 RANGE_PART_TAB_IND_ID P9 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P8 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P7 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P6 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P5 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P4 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P3 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P2 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P12 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P11 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P10 USABLE 0 0 0 0 19-MAY-20 RANGE_PART_TAB_IND_ID P1 USABLE 0 0 0 0 19-MAY-20 13 rows selected. 收集统计信息 exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'YZ', tabname=>'RANGE_PART_TAB',cascade=>true,estimate_percent=>30,degree=>6); SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,SAMPLE_SIZE,LAST_ANALYZED,HIGH_VALUE from user_tab_partitions order by 2; TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE LAST_ANAL HIGH_VALUE ------------------------------ ------------------------------ ---------- ---------- ------------ ----------- --------- -------------------------------------------------------------------------------- RANGE_PART_TAB P1 0 0 0 19-MAY-20 TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P10 0 0 0 19-MAY-20 TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P11 0 0 0 19-MAY-20 TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P12 0 0 0 19-MAY-20 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P2 0 0 0 19-MAY-20 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P3 0 0 0 19-MAY-20 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P4 0 0 0 19-MAY-20 TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P5 0 0 0 19-MAY-20 TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P6 0 0 0 19-MAY-20 TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P7 0 0 0 19-MAY-20 TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P8 0 0 0 19-MAY-20 TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P9 0 0 0 19-MAY-20 TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P_MAX 3198140 196328 0 959442 19-MAY-20 MAXVALUE 13 rows selected. SQL> select TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,SAMPLE_SIZE,PARTITIONED,LAST_ANALYZED from user_tables; TABLE_NAME STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE PAR LAST_ANAL ------------------------------ -------- ---------- ---------- ------------ ----------- --- --------- RANGE_PART_TAB VALID 3195423 196328 0 958627 YES 19-MAY-20 收集统计信息后,可以发现表分区的统计信息及表的总体统计信息都有了。 2.数据变更 insert into range_part_tab (id,deal_date,area_code,contents) select rownum, to_date(to_char(to_date('2018-03-01','yyyy-mm-dd')-365,'J')+ trunc(dbms_random.value(0,365)),'J'), ceil(dbms_random.value(590,599)), rpad('*',400,'*') from dual connect by rownum <= 100000; SQL> commit; SQL> select count(*) from RANGE_PART_TAB; COUNT(*) ---------- 3300000 10万新增数据,总大小是330万,3%的数据变更，达不到统计信息过旧的条件。 SQL> select table_name,PARTITION_NAME,STALE_STATS from user_TAB_STATISTICS; TABLE_NAME PARTITION_NAME STA ------------------------------ ------------------------------ --- RANGE_PART_TAB NO RANGE_PART_TAB P1 NO RANGE_PART_TAB P3 NO RANGE_PART_TAB P8 NO RANGE_PART_TAB P12 NO RANGE_PART_TAB P2 NO RANGE_PART_TAB P7 NO RANGE_PART_TAB P6 NO RANGE_PART_TAB P9 NO RANGE_PART_TAB P5 NO RANGE_PART_TAB P11 NO RANGE_PART_TAB P10 NO RANGE_PART_TAB P4 NO RANGE_PART_TAB P_MAX NO 14 rows selected. 收集10%粒度！ exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'YZ', tabname=>'RANGE_PART_TAB',cascade=>true,estimate_percent=>10,degree=>6); SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,SAMPLE_SIZE,LAST_ANALYZED,HIGH_VALUE from user_tab_partitions order by 2; TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE LAST_ANAL HIGH_VALUE ------------------------------ ------------------------------ ---------- ---------- ------------ ----------- --------- -------------------------------------------------------------------------------- RANGE_PART_TAB P1 0 0 0 19-MAY-20 TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P10 8441 558 0 5079 19-MAY-20 TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P11 8153 494 0 4836 19-MAY-20 TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P12 8443 558 0 5161 19-MAY-20 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P2 0 0 0 19-MAY-20 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P3 8472 558 0 5061 19-MAY-20 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P4 8261 494 0 5316 19-MAY-20 TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P5 8511 558 0 5260 19-MAY-20 TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P6 8290 494 0 5049 19-MAY-20 TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P7 8382 558 0 4919 19-MAY-20 TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P8 8538 494 0 4993 19-MAY-20 TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P9 8266 494 0 4868 19-MAY-20 TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P_MAX 3211810 196328 0 321181 19-MAY-20 MAXVALUE 13 rows selected. 加上分区级联收集参数？ exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'YZ', tabname=>'RANGE_PART_TAB',granularity=>'ALL',cascade=>true,estimate_percent=>30,degree=>6); SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,SAMPLE_SIZE,LAST_ANALYZED,HIGH_VALUE from user_tab_partitions order by 2; TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE LAST_ANAL HIGH_VALUE ------------------------------ ------------------------------ ---------- ---------- ------------ ----------- --------- -------------------------------------------------------------------------------- RANGE_PART_TAB P1 0 0 0 19-MAY-20 TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P10 8303 558 0 5010 19-MAY-20 TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P11 8344 494 0 5100 19-MAY-20 TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P12 8620 558 0 5218 19-MAY-20 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P2 0 0 0 19-MAY-20 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P3 8278 558 0 4973 19-MAY-20 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P4 8189 494 0 5024 19-MAY-20 TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P5 8530 558 0 2559 19-MAY-20 TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P6 8161 494 0 4976 19-MAY-20 TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P7 8793 558 0 2638 19-MAY-20 TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P8 8520 494 0 2556 19-MAY-20 TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P9 8483 494 0 2545 19-MAY-20 TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA RANGE_PART_TAB P_MAX 3217647 196328 0 965294 19-MAY-20 MAXVALUE 13 rows selected. 有一些误差，但是影响不是很大？在数据不均匀的情况下,使用Oracle自动收集粒度,易造成采样比例过少,最终导致数据变化过大。 <1g 100% 1g~5g 30% >5g 10%
根据实际测试，可以发现使用auto参数就是收集分区表统计信息，不加granularity=>'ALL'明确指定，则默认使用AUTO会自动对表全局、表分区均收集统计信息，但是粒度由Oracle自行控制,如果使用ALL后就是明确指定表对象、表分区均百分百收集，具体比例看参数控制。
在生产环境100G大表的情况下，使用AUTO默认情况下，很可能造成数据收集的信息不准确，最终导致影响SQL的执行，建议使用granularity=>'ALL’明确