Statistics Package Manipulation in Oracle

You have seen how statistics are the basis for many of the decisions of the cost-based optimizer. As the statistics for a database change, they can influence the decisions made by the cost-based optimizer. Once you update the statistics in the data dictionary, the cost-based optimizer uses the new statistics.
2 Questions that one must ask:

1. How will you know if the new statistics will result in an optimal execution plan for a set of particular queries?
2. Once the new statistics are gathered, are the old ones lost?

• Using DBMS_STATS to preserve Statistics
  You may have been wondering why DBMS_STATS includes procedures to collect statistics that are not stored in the data dictionary. Because the optimizer only uses the dictionary statistics, and saving statistics outside of the dictionary will not influence the optimizier. The key lies in the ability to export and import statistics from the data dictionary. By exporting statistics from the data dictionary to an external table, you preserve them, even after running another procedure in DBMS_STATS. You can import the saved statistics later to overwrite the newly gathered statistics. This capability means you can test the new statistics to see if they have a positive effect on the optimization of your queries. If the new statistics are not satisfactory, you can import the old, saved statistics. To understand the ability to import and export statistics, you have to understand the use of some parameters for the import and export procedures, as shown in the following series of images.

Prior to Oracle 10g, adjusting optimizer parameters was the only way to compensate for sample size issues with dbms_stats. As of Oracle 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats have made adjustments to these parameters less important. With other things the same, always adjust CBO statistics before adjusting optimizer parameters. The CBO gathers information from many sources, and he has the goal of using DBA-provided metadata to make the most desireable execution plan decision: Let us examine the following areas of CBO statistics and see how to gather quality statistics for the CBO and how to create an appropriate CBO environment for your database.

• Obtaining Statistics for the CBO
  The choices of executions plans made by the CBO are only as good as the statistics available to it. The old-fashioned analyze table and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance. As we may know, the CBO uses object statistics to choose the best execution plan for all SQL statements.
  The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better statistics result in faster SQL execution plans. Here is a sample execution of dbms_stats with the OPTIONS clause:
  

   
  exec dbms_stats.gather_schema_stats( - 
   ownname          => 'SCOTT', - 
   options          => 'GATHER AUTO', - 
   estimate_percent => dbms_stats.auto_sample_size, - 
   method_opt       => 'for all columns size repeat', - 
   degree           => 34 - 
   )
  

Here is another dbms_stats example that creates histograms on all indexes columns:

BEGIN
dbms_stats.gather_schema_stats(
ownname=>'TPCC',
METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
CASCADE=>TRUE,
ESTIMATE_PERCENT=>100);
END;
/

An Oracle DBA should be able to perform the following tasks using the DBMS_STATS package:

1. creating an external table for storing statistics,
2. exporting existing statistics to the table,
3. gathering a new set of statistics,
4. importing the previously saved statistics, and
5. dropping the external statistics table.

To create a statistics table in Oracle SQL*Plus, you can use the CREATE_STAT_TABLE procedure from the DBMS_STATS package. This table can be used to store and manage statistics related to tables, indexes, columns, and more. Here is an example of how to create a statistics table:

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE (
    ownname          => 'SCHEMA_NAME', 
    stattab          => 'STATISTICS_TABLE_NAME', 
    tblspace         => 'TABLESPACE_NAME'
  );
END;
/

In the above code:

1. SCHEMA_NAME should be replaced with the name of the schema where you want to create the statistics table.
2. STATISTICS_TABLE_NAME is the name you want to assign to your new statistics table.
3. TABLESPACE_NAME is the name of the tablespace where the statistics table will be created. This parameter is optional; if you don't specify it, Oracle will use the default tablespace for the schema.

After running this code, you will have a new statistics table. This table can be used in combination with other DBMS_STATS procedures to export and import statistics between different tables or even databases. For example, you can export statistics from a table into your new statistics table with the EXPORT_TABLE_STATS procedure:

BEGIN
  DBMS_STATS.EXPORT_TABLE_STATS (
    ownname          => 'SCHEMA_NAME', 
    tabname          => 'TABLE_NAME', 
    stattab          => 'STATISTICS_TABLE_NAME', 
    statid           => 'STATISTICS_ID', 
    statown          => 'SCHEMA_NAME'
  );
END;
/

And you can import them back into a table with the IMPORT_TABLE_STATS procedure:

BEGIN
  DBMS_STATS.IMPORT_TABLE_STATS (
    ownname          => 'SCHEMA_NAME', 
    tabname          => 'TABLE_NAME', 
    stattab          => 'STATISTICS_TABLE_NAME', 
    statid           => 'STATISTICS_ID', 
    statown          => 'SCHEMA_NAME'
  );
END;
/

In both procedures, TABLE_NAME is the name of the table from which you're exporting or to which you're importing statistics, and STATISTICS_ID is a unique identifier for a particular set of statistics in the statistics table. Remember to replace the placeholders in the examples above with your actual schema names, table names, and other relevant information. And, as always, ensure you have the necessary privileges to execute these procedures.

1. You will be entering the relevant commands into the SQL*Plus graphical user interface (GUI), a part of the Oracle Enterprise Manager. You start by entering an EXECUTE call to the CREATE_STAT_TABLE procedure in the DBMS_STATS package. There are three parameters for this procedure: the name of the schema for the table('PETSTORE'), the name of the target table ('HOLDSTAT'), and an empty text string (''), because there will not be a grouping associated with this external statistics table. Enter EXECUTE DBMS_STATS.CREATE_STAT_TABLE('PETSTORE', 'HOLDSTAT', '')and click the Execute button, second from the top in the left toolbar.
2. The CREATE_STAT_TABLE procedure executes successfully, as shown in the lower window of the SQL*Plus GUI. The next step is to use the EXPORT_TABLE_STATS procedure of the DBMS_STATS package. The relevant parameters for this procedure are the name of the schema ('PETSTORE'), the name of the table ('SALE_HEADER'), and the name of the target external statistics table ('HOLDSTAT'), which you will have to precede with the name of the parameter and an arrow pointing to the value (stattab => 'HOLDSTAT'), because it is not the third parameter in order. Enter EXECUTE EXPORT_TABLE_STATS( 'PETSTORE', 'SALE_HEADER', stattab => 'HOLDSTAT') and click the Execute button.
3. The EXPORT_TABLE_STATS procedure executes successfully, as shown in the lower window of the SQL*Plus GUI. The next step is to use the GATHER_TABLE_STATS procedure of the DBMS_STATS package to collect a new set of statistics for the SALE_HEADER table. This procedure takes the same set of parameters as the EXPORT_TABLE_STATS procedure in the previous step. Enter EXECUTE GATHER_TABLE_STATS( 'PETSTORE', 'SALE_HEADER', stattab => 'HOLDSTAT') and click the Execute button.
4. The GATHER_TABLE_STATS procedure executes successfully, as shown in the lower window of SQL*Plus GUI. At this point, you would do some testing to find out if your queries optimize better with the new set of statistics. If they do not, you would want to re-import the statistics you previously exported to return the statistics to their original values. Use the IMPORT_TABLE_STATS procedure in the DBMS_STATS package, with the same parameters as the previous two procedures, to bring in the previously saved statistics. Enter EXECUTE IMPORT_TABLE_STATS( 'PETSTORE', 'SALE_HEADER', stattab => 'HOLDSTAT') and click the Execute button.
5. The IMPORT_TABLE_STATS procedure executes successfully, as shown in the lower window of the SQL*Plus GUI. If you were finished using the external table that held the statistics, your final step would be to drop the table from the database with the DROP_STAT_TABLE procedure in the DBMS_STATS package. This procedure takes two parameters: the name of the schema and the table. Enter EXECUTE DBMS_STATS.DROP_STAT_TABLE('PETSTORE', 'HOLDSTAT') and click the Execute button.
6. The DROP_STAT_TABLE procedure executes successfully, as shown in the lower window of the SQL*Plus GUI. This is the end of the simulation.

In the next lesson, you will learn how to monitor operations in the database.