Alternate Oracle Statistics
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.