- 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.
