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)
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 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 220.127.116.11, can do that job.
Now, we can start extracting column usage with the following statement
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.
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.