Databases Reference
In-Depth Information
In step 11, we drop the
MYSTATS
table using the proper stored procedure of the
DBMS_STATS
package.
In step 12, we instruct the database to automatically collect statistics on schema
SH
. This
option is turned on by default in Oracle databases from 10
g
onward, thanks to the job
GATHER_STATS_JOB
.
Statistics computation on a large table is a long job and requires both a scan and a sort on
the tables. To reduce the execution time, we can gather statistics using only some of the
data. We can decide the amount of data to be processed by setting the
ESTIMATE_PERCENT
parameter in the
DBMS_STATS.GATHER_SCHEMA_STATS
procedure.
From Oracle Database 9
i
onward, we can assign a
DBMS_STATS.AUTO_SAMPLE_SIZE
value to this parameter. By doing so, the database decides the value to be used to balance
performance and statistics accuracy, as in step 13.
