• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Incremental Statistics Gathering in 11g

Incremental Statistics Gathering in 11g

September 15, 2016 by techgoeasy Leave a Comment

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

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us