Databases Reference
In-Depth Information
4.
Create a regular table (
MYSTATS
) to store the statistics:
EXEC DBMS_STATS.CREATE_STAT_TABLE (OWNNAME => 'SH', -
STATTAB => 'MYSTATS', TBLSPACE => 'EXAMPLE');
5.
Export the statistics collected in the data dictionary about
SH
schema to the
MYSTATS
table created in the previous step:
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME => 'SH', -
STATTAB => 'MYSTATS');
6.
Inspect the execution plan for a simple query (with the statistics in place):
SET AUTOT TRACE EXP
SELECT
C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM sh.CUSTOMERS C
WHERE C.CUST_YEAR_OF_BIRTH = 1949;
7.
Delete the statistics on
SH
schema:
EXEC DBMS_STATS.DELETE_SCHEMA_STATS (OWNNAME => 'SH');
8.
Execute the query in step 6, again (without the statistics in place):
SELECT
C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM sh.CUSTOMERS C
WHERE C.CUST_YEAR_OF_BIRTH = 1949;
9.
Import the statistics from the
MYSTATS
table:
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME => 'SH', -
STATTAB => 'MYSTATS');
10. Execute the query in step 6, again (with the statistics again in place):
SELECT
C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM sh.CUSTOMERS C
WHERE C.CUST_YEAR_OF_BIRTH = 1949;
11. Drop the
MYSTATS
table:
SET AUTOT OFF
EXEC DBMS_STATS.DROP_STAT_TABLE ('SH','MYSTATS');
