Databases Reference
In-Depth Information
12. Set up automatic statistics gathering for schema
SH
:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME => 'SH', -
OPTIONS => 'GATHER AUTO');
13. Use automatic sampling size when collecting statistics:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME => 'SH', -
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, -
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');
How it works...
In step 2, we collect statistics over the
CUSTOMERS
table, using the
GATHER_TABLE_STATS
procedure of the
DBMS_STATS
package, identifying the table to collect statistics over and
other parameters for this operation.
In step 3, we query the
DBA_TABLES
dictionary view to see some results of the previous
operation. Observe some parameters of the table—such as, number of rows, blocks, and
average row length—and some information about the previous statistics collection phase—such
as, the sample size used and the last analyzed timestamp—if we have global or user stats.
We can see these operations in the following screenshot:
In step 4, we create a table
MYSTATS
on the tablespace
EXAMPLE
, to store statistics data
in a regular table. In step 5, we copy
SH
schema statistics from the data dictionary to the
MYSTATS
table. This operation is useful when we want to transfer statistics among different
databases, to avoid recollecting them, which can be a time consuming task.
