Question: I need to use the dbms_stats.set_system_stats
to change b y system statistics. How do I invoke the dbms_stats.set_system_stats
procedure?
Answer: An
example of using dbms_stats.set_system_stats
relates to bug 9842771. This bug leads to wrong values in
AUX_STATS$ for
sreadtim and
mreadtim by factor 1,000 therefore guiding the optimizer
sometimes into the totally wrong direction.
The workaround is to overwrite these values manually and
divide them by 10,000. To make this change, use the
dbms_stats.set_system_stats
procedure. See this
MOSC Note:9842771.8 for the above bug for some further
information. This issue is fixed in Oracle Database 11.2.0.3
and above.
The following code uses
dbms_stats.set_system_stats to change system stats, thereby
changing how the optimizer chooses between using an index
and performing a full-table scan:
exec
dbms_stats.set_system_stats('sreadtim',1000);
exec
dbms_stats.set_system_stats('mreadtim',0.01);
exec
dbms_stats.set_system_stats('mbrc',512);
exec
dbms_stats.set_system_stats('cpuspeed',100);
exec
dbms_stats.set_system_stats('maxthr',4000000);
exec
dbms_stats.set_system_stats('slavethr',100000);
