SECRET OF INCREMENTAL STATISITCS – IMPROVE SPEED FOR STATISTICS COLLECTION OF PARTITIONED TABLES

Incremental statistics is similar to incremental backup where only the updated changes will be recorded using synopsis table. This option is introduced for large partitioned tables where global statistics often perform full table scans regardless of the number of partitions in the table.

Consider an example where you write a research thesis paper. Due to tiredness, you slept off. Next day, again you take previous day notes and continue from where you left. This is good and efficient. But what if you need to write the paper from the beginning daily with the updated content?

Similarly a table has 100 partitions and only one partition requires a modification. In this case, if the whole 100 partitions need to be scanned, then it is suboptimal and time consuming.

To overcome this challenge, oracle come up with incremental stats which consider changed records and update the changes at global level via synopsis

Synopsis in general means a brief summary of something. (https://languages.oup.com/google-dictionary-en)

NDV which means number of distinct value is a statistics for getting the cardinality estimates of the each partition. Example if a column has 100 males and 50 females, then NDV = 2 (male + female)

Check the partitioned table and part count. There are 11 partitions in the table.

kIsH@zxd00<^>col owner for a15
kIsH@zxd00<^>col table_name for a15
kIsH@zxd00<^>select owner,table_name,partition_count from dba_part_tables where table_name='XSAL';

OWNER           TABLE_NAME      PARTITION_COUNT
--------------- --------------- ---------------
KISH            XSAL                         11

Check total count of table

kIsH@zxd00<^>select count(*) from XSAL;

  COUNT(*)
----------
   2000000

Check the stats preference at global level. Check if incremental parameter is set to false.

In order for incremental statistics to function normally, ‘PUBLISH‘ parameter should be set to true because the statistics of individual partition will be gathered and should be established to the global level at table.

Both granularity and estimate percent should be auto. These values cannot be manually calculated because static value is not always applicable in a changing database. Script available in https://alphaoragroup.com/2022/02/14/sql-to-check-statistics-preferences-oracle/?preview=true

STATS_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AUTOSTATS_TARGET =====> AUTO
CASCADE =====> DBMS_STATS.AUTO_CASCADE
CONCURRENT =====> OFF
DEGREE =====> NULL
ESTIMATE_PERCENT =====> DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY =====> AUTO
INCREMENTAL =====> FALSE
INCREMENTAL_LEVEL =====> PARTITION
INCREMENTAL_STALENESS =====> ALLOW_MIXED_FORMAT
METHOD_OPT =====> FOR ALL COLUMNS SIZE AUTO
OPTIONS =====> GATHER
PUBLISH =====> TRUE
STALE_PERCENT =====> 10

13 rows selected.

There are huge modifications with inserts on table partitions on XSAL

kIsH@zxd00<^>select partition_name,inserts from dba_tab_modifications where table_name='XSAL';

PARTITION_NAME     INSERTS
--------------- ----------
P_TIR               100023
SYS_P387            199080
SYS_P388            200256
SYS_P389            200470
SYS_P390            199965
SYS_P391            200017
SYS_P392            199834
SYS_P393            201160
SYS_P394             99708
SYS_P395            199809
SYS_P396            199678

11 rows selected.

Insert data into only one partition and observe the time taken for statistics gather without enabling incremental statistics. It took 8.2 seconds to complete.

kIsH@zxd00<^>insert /*+append parallel(8)*/ into xsal
  2  select xid.nextval,
       dbms_random.string('a','10'),
           decode(round(dbms_random.value(0,10)),0,'TINNEVELI',1),
           floor(dbms_random.value(1,10)),
           to_date(trunc(dbms_random.value(to_char(date '1979-01-01','J'),
                                           to_char(date '9999-12-31','J'))),'J')
from (select level from dual connect by level <= 100),
(select level from dual connect by level <= 100),
(select level from dual connect by level <= 100);  3    4    5    6    7    8    9   10

1000000 rows created.

Elapsed: 00:00:16.84
kIsH@zxd00<^>commit;

Commit complete.

Elapsed: 00:00:00.02
kIsH@zxd00<^>
kIsH@zxd00<^>
kIsH@zxd00<^>
kIsH@zxd00<^>exec dbms_stats.set_global_prefs('INCREMENTAL','FALSE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
kIsH@zxd00<^>exec dbms_stats.set_table_prefs('KISH','XSAL','INCREMENTAL','FALSE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
kIsH@zxd00<^>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'KISH',TABNAME => 'XSAL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.25

Either full table scan or full index scan is used for statistics gather on partition without incremental option

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  HASH JOIN  (cr=0 pr=0 pw=0 time=242 us starts=1 cost=4 size=47 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=220 us starts=1 cost=4 size=47 card=1)
         0          0          0    STATISTICS COLLECTOR  (cr=0 pr=0 pw=0 time=63 us starts=1)
         0          0          0     TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=0 pr=0 pw=0 time=31 us starts=1 cost=3 size=43 card=1)
         0          0          0      INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=25 us starts=1 cost=2 size=0 card=1)(object id 36)
         1          1          1       FAST DUAL  (cr=0 pr=0 pw=0 time=1 us starts=1 cost=2 size=0 card=1)
         0          0          0    INDEX RANGE SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=4 card=1)(object id 47)
         0          0          0   INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=4 card=1)(object id 47)

Check last analyzed column which has equal time for all partitions. This concludes that the whole table is scanned even though we inserted data into one partition P_TIR

TABLE_NAME           PARTITION_NAME         NUM_ROWS LAST_STATS     GLO AVG_ROW_LEN HIGH_VALUE
-------------------- -------------------- ---------- -------------- --- ----------- --------------------
XSAL                 P_TIR                    500196 24-03-22 22:34 YES          38 'TINNEVELI'
XSAL                 SYS_P387                 698771 24-03-22 22:34 YES          35 'LISBON'
XSAL                 SYS_P388                 699475 24-03-22 22:34 YES          35 'LONDON'
XSAL                 SYS_P389                 700120 24-03-22 22:34 YES          37 'CANBERRA'
XSAL                 SYS_P390                 700292 24-03-22 22:34 YES          35 'MOSCOW'
XSAL                 SYS_P391                 699726 24-03-22 22:34 YES          35 'MUMBAI'
XSAL                 SYS_P392                 700590 24-03-22 22:34 YES          35 'BERLIN'
XSAL                 SYS_P393                 701718 24-03-22 22:34 YES          35 'MADRID'
XSAL                 SYS_P394                 349921 24-03-22 22:34 YES          33 'ROME'
XSAL                 SYS_P395                 698830 24-03-22 22:34 YES          34 'PARIS'
XSAL                 SYS_P396                 700265 24-03-22 22:34 YES          39 'WASHINGTON'
XSAL                 SYS_P454                2850096 24-03-22 22:34 YES          30 '1'

If incremental is false then set to true to enable synopsis on the columns and incremental statistics gather.

--Table level
kIsH@zxd00<^>exec dbms_stats.set_table_prefs('KISH','XSAL','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.
or 
--Global level
kIsH@zxd00<^>exec dbms_stats.set_global_prefs('INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.


kIsH@zxd00<^>col INC_OR_FULL for a20
kIsH@zxd00<^>select dbms_stats.get_prefs('INCREMENTAL','KISH','XSAL') as INC_OR_NOT from dual;

INC_OR_NOT
--------------------
TRUE

Always when incremental stats is enabled for first time, it will take lot of time to build the synopsis on the columns and construct metadata. Consequent run will be faster because all synopsis are in place and only the work is to collect changes to partition. It took 2.1 seconds in the second run. First run is not posted for brevity.

kIsH@zxd00<^>insert /*+append parallel(8)*/ into xsal
select xid.nextval,
       dbms_random.string('a','10'),
           decode(round(dbms_random.value(0,10)),0,'TINNEVELI',1),
           floor(dbms_random.value(1,10)),
           to_date(trunc(dbms_random.value(to_char(date '1979-01-01','J'),
                                           to_char(date '9999-12-31','J'))),'J')
from (select level from dual connect by level <= 100),
(select level from dual connect by level <= 100),
(select level from dual connect by level <= 100);  2    3    4    5    6    7    8    9   10

1000000 rows created.

Elapsed: 00:00:17.59
kIsH@zxd00<^>commit;

Commit complete.
kIsH@zxd00<^>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'KISH',TABNAME => 'XSAL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.12

Only the partition which are modified, are analyzed and rest of them are ignored.

kIsH@zxd00<^>col HIGH_VALUE for a20
col PARTITION_NAME for a20
kIsH@zxd00<^>kIsH@zxd00<^>col TABLE_NAME for a20
col HIGH_VALUE for a20
col PARTITION_NAME for a20
col TABLE_NAME for a20
select table_name,
                 partition_name,
                 num_rows,
                 to_char(last_analyzed,'dd-mm-yy hh24:mi') last_stats,
                 global_stats,
                 avg_row_len,
                 high_value
  from dba_tab_partitions
  where table_name='XSAL';kIsH@zxd00<^>kIsH@zxd00<^>kIsH@zxd00<^>kIsH@zxd00<^>  2    3    4    5    6    7    8    9

TABLE_NAME           PARTITION_NAME         NUM_ROWS LAST_STATS     GLO AVG_ROW_LEN HIGH_VALUE
-------------------- -------------------- ---------- -------------- --- ----------- --------------------
XSAL                 P_TIR                    500196 24-03-22 22:32 YES          38 'TINNEVELI'
XSAL                 SYS_P387                 698771 24-03-22 21:55 YES          35 'LISBON'
XSAL                 SYS_P388                 699475 24-03-22 21:55 YES          35 'LONDON'
XSAL                 SYS_P389                 700120 24-03-22 21:55 YES          37 'CANBERRA'
XSAL                 SYS_P390                 700292 24-03-22 21:55 YES          35 'MOSCOW'
XSAL                 SYS_P391                 699726 24-03-22 21:55 YES          35 'MUMBAI'
XSAL                 SYS_P392                 700590 24-03-22 21:55 YES          35 'BERLIN'
XSAL                 SYS_P393                 701718 24-03-22 21:55 YES          35 'MADRID'
XSAL                 SYS_P394                 349921 24-03-22 21:55 YES          33 'ROME'
XSAL                 SYS_P395                 698830 24-03-22 21:55 YES          34 'PARIS'
XSAL                 SYS_P396                 700265 24-03-22 21:55 YES          39 'WASHINGTON'
XSAL                 SYS_P454                2850096 24-03-22 22:32 YES          30 '1'

12 rows selected.

Check the state column which updated to a newer NDV. Previously there where 11 distinct columns and now it is 12

kIsH@zxd00<^>col COLUMN_NAME for a20
kIsH@zxd00<^>col TABLE_NAME for a20
kIsH@zxd00<^>select table_name,column_name,num_distinct from dba_tab_columns where table_name='XSAL';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT
-------------------- -------------------- ------------
XSAL                 ID                       10000000
XSAL                 NAME                      9958400
XSAL                 STATE                          12 <<==
XSAL                 AMOUNT                          9
XSAL                 SDATE                     2889728

After enabling incremental, index range scan is used and synopsis table(WRI$_OPTSTAT_SYNOPSIS_HEAD$) can be visible

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  FILTER  (cr=58 pr=0 pw=0 time=153 us starts=1)
         0          0          0   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=1)
         0          0          0   HASH JOIN  (cr=58 pr=0 pw=0 time=152 us starts=1 cost=32 size=26 card=1)
         0          0          0    TABLE ACCESS BY INDEX ROWID BATCHED WRI$_OPTSTAT_SYNOPSIS_HEAD$ (cr=58 pr=0 pw=0 time=109 us starts=1 cost=3 size=24 card=1)
        60         60         60     INDEX RANGE SCAN I_WRI$_OPTSTAT_SYNOPHEAD (cr=2 pr=0 pw=0 time=72 us starts=1 cost=2 size=0 card=1)(object id 15990)
         0          0          0    COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=0 us starts=0 cost=29 size=8 card=4)

********************************************************************************

Below tables contains the information on synopsis

kIsH@Xhydra<>select OWNER,TABLE_NAME,PARTITIONING_TYPE,SUBPARTITIONING_TYPE from dba_part_tables where TABLE_NAME in ('WRI$_OPTSTAT_SYNOPSIS$','WRI$_OPTSTAT_SYNOPSIS_HEAD$');

OWNER                TABLE_NAME           PARTITIONING_TYPE    SUBPARTITIONING_TYPE
-------------------- -------------------- -------------------- --------------------
SYS                  WRI$_OPTSTAT_SYNOPSI LIST                 HASH
                     S$

Summary:

It is not always recommended to use incremental statistics if partition comes into picture. If there is a small partition which will not be updated for next 10 to 20 years, then there is not of much gain for incremental statistics and rather sampling based stats collection can be utilized. If there is a huge table with daily modifications to each partition in the table, then incremental statistics will be the best strategy in focus of long term goal. Having awareness of the business goals and objectives can lead to choose the right tactics. If the table size reached TB , then its too late to implement incremental because there are limitations of first time slow stat gather and rigorous planning should be incorporated with downtime. So its always better to be proactive.

Leave a Reply