Incremental Statistics Gathering

Starting with 11g database has introduced Incremental statistics gathering new feature

Incremental stats will work only for partitioned tables where global statistics are updated incrementally by scanning only the partitions that have changed from the last run. Generally, the latest partitions are the ones where data is inserted and mostly the older partitions remain untouched. With enabling Incremental statistics on a table, only on those partitions statistics are gathered where there are any DMLs on it, the remaining are not scanned which would save a lot of time.
Partitioned tables are big ones and if we need to gather statistics on this regularly, it would be very time-consuming and Incremental statistics gathering reduces the time required to gather statistics

How Incremental statistics works

Incremental global statistics work by storing a synopsis for each partition in the table. A synopsis is a statistical metadata for that partition and the columns in the partition. Each synopsis is stored in the SYSAUX tablespace. Global statistics are then generated by aggregating the partition level statistics and the synopses from each partition, thus eliminating the need to scan the entire table to gather table-level statistics

When a new partition is added to the table, you only need to gather statistics for the new partition. The global statistics will be automatically and accurately updated using the new partition synopsis and the existing partitions’
synopses

Example of Oracle incremental Statistics Gathering feature in Oracle E-Business Suite.

Let us take the example of “XLA_AE_LINES” which is a partition table and it is having many records in a heavily used ERP instance

Test case without setting an incremental preference

-Create a table xla_ae_lines_bkp as a partition table to perform this test case
– Insert data from the xla_ae_lines table

insert into xla.xla_Ae_lines_bkp select * from xla.xla_ae_lines; 
2119409 rows created.
commit;

-set time on timing on

exec fnd_stats.gather_table_stats('XLA','XLA_AE_LINES_BKP');
PL/SQL procedure successfully completed.
Elapsed: 00:00:49.06

It took 49.06 sec to gather stats for the first time after data insertion.
Check the statistics and global and partition level

select table_name,to_Char(last_analyzed,'DD-MON-YY HH24:MI:SS') "last_analyzed" from dba_Tables where table_name like 'XLA_AE_LINES_BKP';
TABLE_NAME           last_analyzed
XLA_AE_LINES_BKP     09-SEP-23 10:04:34  
select partition_name,to_Char(last_analyzed,'DD-MON-YY HH24:MI:SS') "last_analyzed" from dba_Tab_partitions where table_name like 'XLA_AE_LINES_BKP';

PARTITION_NAME       last_analyzed
AP                   09-SEP-23 10:04:15
AR                   09-SEP-23 10:04:16
CE                   09-SEP-23 10:04:16
CST                  09-SEP-23 10:04:23
DPP                  09-SEP-23 10:04:23
FUN                  09-SEP-23 10:04:23
FV                   09-SEP-23 10:04:23

Delete the data from one of the partition to check how statistics are getting calculated

delete from xla.xla_ae_lines_bkp where application_id=222;

Gathering stats using fnd_Stats

exec fnd_stats.gather_table_stats('XLA','XLA_AE_LINES_BKP');
PL/SQL procedure successfully completed.
Elapsed: 00:00:25.06

After deleting the data and running gather stats without setting the preference. It took 25.06 sec

Checking global stats and partition stats

select table_name,to_Char(last_analyzed,'DD-MON-YY HH24:MI:SS') "last_analyzed" from dba_Tables where table_name like 'XLA_AE_LINES_BKP';
TABLE_NAME           last_analyzed
XLA_AE_LINES_BKP    09-SEP-23 10:30:34  

select partition_name,to_Char(last_analyzed,'DD-MON-YY HH24:MI:SS') "last_analyzed" from dba_Tab_partitions where table_name like 'XLA_AE_LINES_BKP';
PARTITION_NAME       last_analyzed
AP               09-SEP-23 10:30:15
AR               09-SEP-23 10:30:16
CE               09-SEP-23 10:30:16
CST              09-SEP-23 10:30:23
DPP              09-SEP-23 10:30:23
FUN              09-SEP-23 10:30:23
FV 09-SEP-23 10:30:23

NOTE : statistics are gathered on all the partitions even though only AR partition data is deleted, last_analyzed is updated for all the partitions

Test case with setting incremental preference

Drop and Recreate the table xla_ae_lines_bkp as a partition table to perform this test case
Insert data from xla_ae_lines table

insert into xla.xla_Ae_lines_bkp select * from xla.xla_ae_lines;

2119409 rows created.

-set time on timing on

exec dbms_stats.set_table_prefs('XLA','XLA_AE_LINES_BKP','INCREMENTAL','TRUE');

Check if the preference is set

select dbms_stats.get_prefs('INCREMENTAL','XLA','XLA_AE_LINES_BKP') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','XLA','XLA_AE_LINES_BKP')
_______________________________________________
TRUE

Gather statistics after setting the preference.

exec fnd_stats.gather_table_stats('XLA','XLA_AE_LINES_BKP');
PL/SQL procedure successfully completed.
Elapsed: 00:00:25.91

After setting the preference it took 25.91 sec to gather statistics for the first time after data insertion
checking stats timing information

select table_name,to_Char(last_analyzed,'DD-MON-YY HH24:MI:SS') "last_analyzed" from dba_Tables where table_name like 'XLA_AE_LINES_BKP'; 
TABLE_NAME           last_analyzed 
XLA_AE_LINES_BKP    09-SEP-23 11:30:34   

select partition_name,to_Char(last_analyzed,'DD-MON-YY HH24:MI:SS') "last_analyzed" from dba_Tab_partitions where table_name like 'XLA_AE_LINES_BKP'; 
PARTITION_NAME       last_analyzed 
AP               09-SEP-23 11:30:15 
AR               09-SEP-23 11:30:16 
CE               09-SEP-23 11:30:16 
CST              09-SEP-23 11:30:23 
DPP              09-SEP-23 11:30:23 
FUN              09-SEP-23 11:30:23 
FV               09-SEP-23 11:30:23

Deleting the data from one partition to see how incremental statistics gathering will help next time when stats are gathered.

delete from xla_ae_lines_bkp where application_id=222; 100233 rows deleted

Gather statistics after deleting data from application_id 222 partition AR

exec fnd_stats.gather_table_stats('XLA','XLA_AE_LINES_BKP'); PL/SQL procedure successfully completedElapsed: 00:00:4.11

After deleting the data for one partition incremental gathering statistics took 4.11 sec
Check global and partition statistics

select table_name,to_Char(last_analyzed,'DD-MON-YY HH24:MI:SS') "last_analyzed" from dba_Tables where table_name like 'XLA_AE_LINES_BKP';  
TABLE_NAME           last_analyzed  
XLA_AE_LINES_BKP    09-SEP-23 11:45:34    

select partition_name,to_Char(last_analyzed,'DD-MON-YY HH24:MI:SS') "last_analyzed" from dba_Tab_partitions where table_name like 'XLA_AE_LINES_BKP';  
PARTITION_NAME       last_analyzed  
AP               09-SEP-23 11:30:15  
AR               09-SEP-23 11:45:16  
CE               09-SEP-23 11:30:16  
CST              09-SEP-23 11:30:23  
DPP              09-SEP-23 11:30:23  
FUN              09-SEP-23 11:30:23  
FV               09-SEP-23 11:30:23

Important things to note, statistics are gathered, last_analyzed date is changed for only AR partition and timing has also reduced.

So we see that Incremental Statistics Gathering reduces timing for gather stats and it can be quite beneficial for large partition tables.

How Incremental statistics works

Example of Oracle incremental Statistics Gathering feature in Oracle E-Business Suite.

Related Posts

Leave a Comment Cancel Reply