-11g database has introduced the 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 latest partitions are the ones where data is inserted and mostly the older partitions are remain untouched. With enabling Incremental statistics on a table, only on those partitions statistics are gathered where there are any DMLs on it, remaining are not scanned which would save lot of time.
-Partitioned tables are big ones and if we need to regularly gather statistics on this, it would be very time consuming and Incremental statistics gathering reduces the time required to gather statistics
Example of Oracle 11g incremental Statistics Gathering feature in Oracle E-Business Suite.
Lets us take the example of xla_ae_lines which is a partition table and it is having many records in heavy used ERP instance
Test case without setting incremental preference
-Create a 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 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-16 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-16 10:04:15 AR 09-SEP-16 10:04:16 CE 09-SEP-16 10:04:16 CST 09-SEP-16 10:04:23 DPP 09-SEP-16 10:04:23 FUN 09-SEP-16 10:04:23 FV 09-SEP-16 10:04:23 GMF 09-SEP-16 10:04:23 IGC 09-SEP-16 10:04:23 IGI 09-SEP-16 10:04:23 LNS 09-SEP-16 10:04:23 OFA 09-SEP-16 10:04:23 OKL 09-SEP-16 10:04:23 OZF 09-SEP-16 10:04:23 PA 09-SEP-16 10:04:24
|
- 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 usign 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 ran 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-16 10:20:26 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-16 10:20:14 AR 09-SEP-16 10:20:14 CE 09-SEP-16 10:20:14 CST 09-SEP-16 10:20:15 DPP 09-SEP-16 10:20:15 FUN 09-SEP-16 10:20:15 FV 09-SEP-16 10:20:15 GMF 09-SEP-16 10:20:15 IGC 09-SEP-16 10:20:15 IGI 09-SEP-16 10:20:15 LNS 09-SEP-16 10:20:16 OFA 09-SEP-16 10:20:16 OKL 09-SEP-16 10:20:16 OZF 09-SEP-16 10:20:16 PA 09-SEP-16 10:20:17
|
NOTE : statistics are gathered on all the partitions even though only AR parition 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-16 10:35:32 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’; 18
PARTITION_NAME last_analyzed —————————— ———————— AP 09-SEP-16 10:35:30 AR 09-SEP-16 10:35:12 CE 09-SEP-16 10:35:10 CST 09-SEP-16 10:35:21 DPP 09-SEP-16 10:35:21 FUN 09-SEP-16 10:35:12 FV 09-SEP-16 10:35:10 GMF 09-SEP-16 10:35:10 IGC 09-SEP-16 10:35:10 IGI 09-SEP-16 10:35:12 LNS 09-SEP-16 10:35:10 OFA 09-SEP-16 10:35:10 OKL 09-SEP-16 10:35:12 OZF 09-SEP-16 10:35:30 PA 09-SEP-16 10:35:12
|
- 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 completed. Elapsed: 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-16 10:45:18 select partition_name,to_Char(last_analyzed,’DD-MON-YY HH24:MI:SS’) “last_analyzed” from dba_Tab_partitions where ta ble_name like ‘XLA_AE_LINES_BKP’; PARTITION_NAME last_analyzed —————————— ———————— AP 09-SEP-16 10:35 :30 AR 09-SEP-16 10:45:18 CE 09-SEP-16 10:35 :10 CST 09-SEP-16 10:35 :21 DPP 09-SEP-16 10:35 :21 FUN 09-SEP-16 10:35 :12 FV 09-SEP-16 10:35 :10 GMF 09-SEP-16 10:35 :10 IGC 09-SEP-16 10:35 :10 IGI 09-SEP-16 10:35 :12 LNS 09-SEP-16 10:35 :10 OFA 09-SEP-16 10:35 :10 OKL 09-SEP-16 10:35 :12 OZF 09-SEP-16 10:35 :30 PA 09-SEP-16 10:35 :12
|
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.
Leave a Reply