“ORA-20008 Number of extensions …” – what to do about this unnecessary error

October 28, 2012

There are times when Oracle puts restrictions to save us from our own bad practices. For instance, committing in triggers is forbidden so we do not make a mess, among other things.

And then there is “ORA-20008: Number of extensions in table ….. already reaches the upper limit (20)”. We can create thousands of tables, each of them with thousands of columns, but when we create column groups/extended stats, we face severe limitations – 20 or 10% of the number of columns. Why?!? It takes little disk space to accommodate those statistics. The incremental impact on the time to gather stats would be quite small as well.

In short, this restriction has no technologies or business sense, it is probably trivial to correct, but it is still there (as of 11.2.0.3.0).

Some would think that this restriction would almost never affect a “real” system. I disagree! A fact table in a data-warehouse environment could be queried in many different ways – and that’s the beauty of the Kimball approach. I have personally seen this situation in my systems a couple of times so far.

Then what can we do about it?

If the extended statics were generated automatically using the methods here

http://iiotzov.wordpress.com/2011/11/01/get-the-max-of-oracle-11gr2-right-from-the-start-create-relevant-extended-statistics-as-a-part-of-the-upgrade

http://blogs.oracle.com/optimizer/entry/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload

, then we can weed out the extended statistics that do not help the CBO a lot, and put in place only those extended stats that make a significant difference.

Since CBO can use column groups with more columns than are present in predicates (http://www.hotsos.com/sym12/sym_speakers_matuszyk.html) ,we can eliminate extended stats groups that are covered by another extended stats group.

Another way is to measure the dependent columns error (err)/correlation strength (http://iiotzov.wordpress.com/2011/07/05/getting-the-best-of-oracle-extended-statistics) and eliminate those column groups with smallest dependent columns error.

Here is an example:

create table test_tab as
with v1 as (select rownum n from dual connect by level <= 10000)
select
rownum  id , mod (rownum , 100) corr_gr1_col1, mod (rownum , 25) corr_gr1_col2 , mod (rownum ,
50) corr_gr1_col3 ,
200 +  mod (rownum , 11)  corr_gr2_col4 , 200 +  mod (rownum , 22)  corr_gr2_col5 , 200 + mod (
rownum , 33) corr_gr2_col6
from
v1, v1
where
rownum <= 1000000
;

Table created.

select dbms_stats.create_extended_stats (NULL,'TEST_TAB','(corr_gr1_col1,corr_gr1_col2)') from dual

--------------------------------------------------------------------------------

SYS_STU_4QATWX9PNVNKREP$YZIDOX

… 19 more CREATE_EXTENDED_STATS statements

select dbms_stats.create_extended_stats (NULL,'TEST_TAB','(corr_gr1_col1,corr_gr1_col2,corr_gr2_col4,corr_gr2_col5,corr_gr2_col6)') from dual ;
select dbms_stats.create_extended_stats (NULL,'TEST_TAB','(corr_gr1_col1,corr_gr1_col2,corr_gr2_col
*
ERROR at line 1:
ORA-20008: Number of extensions in table SCOTT.TEST_TAB already reaches the
upper limit (20
ORA-06512: at "SYS.DBMS_STATS", line 8415
ORA-06512: at "SYS.DBMS_STATS", line 32587

We can now score each column group and decide whnich ones will remain and which ones will be dropped.

select (count(distinct(corr_gr1_col1) ) * count(distinct(corr_gr1_col3)))/count(distinct(sys_op
_combined_hash(corr_gr1_col1,corr_gr1_col3))) from TEST_TAB ;

(COUNT(DISTINCT(CORR_GR1_COL1))*COUNT(DISTINCT(CORR_GR1_COL3)))/COUNT(DISTINCT(S

--------------------------------------------------------------------------------

50

… a few column group queries

select (count(distinct(corr_gr1_col1) ) * count(distinct(corr_gr2_col4)))/count(distinct(sys_op
_combined_hash(corr_gr1_col1,corr_gr2_col4))) from TEST_TAB ;
(COUNT(DISTINCT(CORR_GR1_COL1))*COUNT(DISTINCT(CORR_GR2_COL4)))/COUNT(DISTINCT(S
--------------------------------------------------------------------------------
1

The column group (corr_gr1_col1,corr_gr2_col4) does not add any value to the CBO, so we can drop it and create  (corr_gr1_col1,corr_gr1_col2,corr_gr2_col4,corr_gr2_col5,corr_gr2_col6) instead.


Get the max of Oracle 11gR2 right from the start – create relevant extended statistics as a part of the upgrade

November 1, 2011

Introduction

As I  posted earlier, dbms_stats.seed_col_usage is a very powerful new function (available in 11gR2) that enables us to get the column groups that are used in filter combinations, join conditions and “group by”s.

There are number of examples out there of how to use this new functionality to generate extended stats based on a running  representative load.

This entry is about how to get a set of column groups from the AWR of production 10g or 11gR1 databases, convert it into a SQL tuning tet,  and use it to generate extended stats for the upgraded 11gR2 database. By following this method, the DBA can get advantage of the power of extended statistics right from the the start, instead of reactively adding them when issues occur.

The more AWR data we have, the smaller chance of missing an important SQL, the better. Thus, the DBA should try to increase the AWR retention as much as possible before the upgrade.

Get a SQL tuning set from a representative workload

First, we have to create a SQL tuning set in the 10g database.

then

then

then specify that the SQL tuning set will contain AWR snapshots.

then specify SQL statements that are to be excluded (none in this case)

then

then

then let the DB job run for a couple of minutes and verify that completed successfully

After that, verify that the SQL tuning set was indeed created and start exporting it to a file

and then specify an Oracle Directory to hold the export (EXPDAT_TS_TEST.DMP)

then verify that the export is done

The next step is to import the SQL tuning set into 11gR2

The export file must be available on the 11gR2 server. Oracle Directory mapping to the directory that holds the file must be created.

then

and then verify that the import was OK

and finally check in the SQL tuning set tab

Extract column groups from the SQL tuning set

Now, we’ll start extracting the column group usage from this SQL tuning set.

First, we need to make sure that no previous work would interfere with this exercise:

select
     *
from
     sys.col_group_usage$

If the query returns nothing, then we are good to go.

If there are records, we should clear them before proceeding. dbms_stats.reset_col_usage function, introduced in  11.2.0.2, can do that job.

Now, we can start extracting column usage with the following statement

exec dbms_stats.seed_col_usage('TS_TEST','SYSTEM',3600);

where “TS_TEST” is the SQL tuning set name ,”SYSTEM” is DB user that owns “TS_TEST” and 3600 is the time limit (in seconds).

This operation could take a while – allocate at least couple of hours for it.

Review column groups 

 dbms_stats.seed_col_usage procedure will populate sys.col_group_usage$. We can also view the results in a user friendly way with dbms_stats.report_col_usage. The function works in SQL*Plus and requires some settings

set long 100000
set lines 120
set pages 0 

select
       dbms_stats.report_col_usage('SCOTT','EMP')
from
       dual ;

where “SCOTT” is the DB user and “EMP” is the table.

Create extended stats in the 11gR2 DB 

To create the extended stats we can use dbms_stats.create_extended_stats function. This is how to create extended stats on an individual table:

set long 100000
set lines 120
set pages 0 

select
     dbms_stats.create_extended_stats('SCOTT','EMP')
from
     dual ;

To create all extended stats for a DB user, we can utilize the following syntax

set long 100000
set lines 120
set pages 0

select
      dbms_stats.create_extended_stats('SCOTT',NULL)
from
       dual ;

Create a SQL script with the extended stats  

Since we do not want to repeat this procedure for every upgraded database, we have to extract a simple SQL script with the create statements for all extended stats, and execute it immediately after upgrade (or more specifically, immediately after the compatible init.ora parameter is updated). The script below can be used to extract the SQL statements

select
     'select dbms_stats.create_extended_stats(ownname => '''||owner||''',tabname => '''||table_name||''', extension => '''||extension||''') from dual ;'
from
      dba_stat_extensions
where
      owner not in ('SYS','SYSTEM');

Please note that just declaring extended stats is not enough. We need to actually  gather statistics for the tables/DB owners that have newly created extended stats to get benefit.

Conclusion

 Best practices are techniques that present little or no risks and consistently deliver in superior results.  The process described here allows the DBA to reap the benefits of a great new feature with very little risk, and should be utilized in every upgrade to Oracle 11gR2.


Follow

Get every new post delivered to your Inbox.