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


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 specify that the SQL tuning set will contain AWR snapshots.

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



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.


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:


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

       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 

     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

       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 dbms_stats.create_extended_stats(ownname => '''||owner||''',tabname => '''||table_name||''', extension => '''||extension||''') from dual ;'
      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.


 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.


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

  1. ss says:

    The screen shots are too small and can’t be seen.

    • iiotzov says:


      There are two easy things you can do to help you see the pictures better:
      ->increase the zoom level of your browser
      ->click on each picture ( I know that would work for IE, not sure for the other browsers)

      Hope that helps,


Leave a Reply

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

You are commenting using your 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

%d bloggers like this: