Optimizer statistics – Stale percentage and partitioning

Oracle is full of surprises, dbms_stats and optimizer statistics as well. That’s why I’m starting a new series named “Optimized statistics” in which I’ll cover some gathering and usage issues related to them.

As we know 11g has a cool feature which tracks how much data we had changed in our objects and if it’s more than the 10% /default/ it marks our statistic as stale. This is really useful because we don’t have to waste time scanning objects without having changed reasonable amount of data in them. But is it working for partitions? Well, not really, not till today.

Before we begin, it’s good to get familiar with statistics tracing from this link. Also you can find some information in MOS bug database.

Now let’s make some tests.
First we insure that our stale percentage for our table is 10%:

SQL> select DBMS_STATS.GET_PREFS ('STALE_PERCENT','X','IPT') "STALE%" from dual;

STALE%
----------
10

SQL>

We don’t have stale statistics:

SQL> select OWNER,TABLE_NAME,PARTITION_NAME,STALE_STATS from dba_tab_statistics where TABLE_NAME='IPT';

OWNER                          TABLE_NAME                     PARTITION_NAME                 STA
------------------------------ ------------------------------ ------------------------------ ---
X                              IPT                                                           NO
X                              IPT                            P_START                        NO
X                              IPT                            SYS_P63                        NO
X                              IPT                            SYS_P61                        NO
X                              IPT                            SYS_P62                        NO

SQL>

Now we check how big is our partition and we insert a single row, after that, we check again for stale statistics:

SQL> select count(*) from ipt where part_time=to_date('01-01-2014', 'DD-MM-YYYY');

  COUNT(*)
----------
   4000002

SQL> insert into ipt(part_time) values(to_date('01-01-2014', 'DD-MM-YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> commit;

Commit complete.

Elapsed: 00:00:00.03
RING_INFO;
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select OWNER,TABLE_NAME,PARTITION_NAME,STALE_STATS from dba_tab_statistics where TABLE_NAME='IPT';

OWNER                          TABLE_NAME                     PARTITION_NAME                 STA
------------------------------ ------------------------------ ------------------------------ ---
X                              IPT                                                           NO
X                              IPT                            SYS_P61                        NO
X                              IPT                            P_START                        NO
X                              IPT                            SYS_P63                        NO
X                              IPT                            SYS_P62                        NO

SQL>

No stale statistics are reported, but let’s enable dbms_stats tracing and see what will happen when we try to approximate global stats:

SQL> exec dbms_stats.set_global_prefs('trace',to_char(1+4+8+16+64+2048+4096+8192));

PL/SQL procedure successfully completed.

SQL> begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => '', tabname => 'IPT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, no_invalidate => false, granularity => 'APPROX_GLOBAL AND PARTITION',
method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 4
);
end;
  7  /
DBMS_STATS: Start gather table stats -- tabname: IPT
DBMS_STATS: Started table X.IPT. at 14-FEB-15 05.29.05.150554000 PM +02:00. Execution phase: 1 stattype: DATA
DBMS_STATS: Specified granularity = APPROX_GLOBAL AND PARTITION, Fixed granularity = GLOBAL AND PARTITION
DBMS_STATS: parameters ** pfix:  granularity: APPROX_GLOBAL AND PARTITION gIdxGranularity:  executionPhase: 1 pname:  stime: 02-14-2015 17:29:05 method_opt: FOR ALL COLUMNS SIZE
AUTO
DBMS_STATS: Start construct analyze using SQL .. Execution Phase: 1 granularity: GLOBAL AND PARTITION global_requested:  pfix:
DBMS_STATS: Start gather part -- conctx.global_requested:  gran: GLOBAL AND PARTITION execution phase: 1
DBMS_STATS: gather stats on partition SYS_P61: stats are stale;
DBMS_STATS: Start gather_stats.. pfix:  ownname: Xtabname: IPT pname: SYS_P61 spname:  execution phase: 1

<output omitted>

DBMS_STATS: Finished table X.IPT. at 14-FEB-15 05.29.09.790694000 PM +02:00

PL/SQL procedure successfully completed.

SQL>

Partition statistics are recognized as stale on line 19. That means we just scan 4 000 000 rows to gather new stats on our partition just because we have inserted a single row. To put it in another way, the stale percentage is not working on partition level, the statistics are marked as stale after a single DML. Well Oracle are claiming that this issue is fixed in 12c and indeed it is, so keep calm and migrate to 12c 😉

In 12c the default behavior is the same as in 11g, but at least we have a preference named INCREMENTAL_STALENESS which can control the behavior. So don’t forget to change it for your database.

This issue can cause a lot of troubles for example if we are using hash partitioning /auch../ or if we are changing a bit of data in many partitions. But it’s still nothing compared with the next one, which I’ll cover in my next post 🙂

Cheers,
Kovachev

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s