Question: I
want to change my automatic optimizer statistics collection.
I execute this command to see the automatic statistics collection
mode:
select
dbms_stats.get_param ('AUTOSTATS_TARGET') from dual;
It gives value as ORACLE but I want to change it AUTO.
Answer:
By default, Oracle collects statistics for the SQL optimizer via the default
of
autostats_target = auto.
Sadly, this is confusing
because this command appears to be similar in function to this command to
disable statistics collection:
SQL> exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
It astonishes me how many shops prohibit any un-approved production
changes and yet re-analyze schema stats weekly. Evidently, they do not
understand that the purpose of schema re-analysis is to change their
production SQL execution plans, and they act surprised when performance
changes!
See these
important notes on
Oracle automatic statistics refreshing with dbms_stats
Me, I don't like surprises, and when we automatically
re-analyze schema statistics, the optimizer can force thousands of SQL
statements to change execution plans! If we are satisfied that our SQL
workload is already optimal, we can set autostats_target to "oracle"
to turn off
automatic statistics collection in 10g and beyond.
exec
dbms_stats.set_param('autostats_target','oracle');
This is a good move if you already have optimal CBO
statistics and don't want to risk changing your SQL execution plans.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
and include the URL for the page.
