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.
|
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
|
