Databases Reference
In-Depth Information
Solution
The topic of Oracle's optimizer and statistics collection is both broad and complex. In this recipe, we will provide
guidance on how to validate and configure statistics collection on an Exadata database in a step-by-step process and
ideally provide a framework to guarantee that your optimizer statistics are being gathered accurately and efficiently.
Specifically, you will learn the following:
•
How to identify how your optimizer statistics are currently being gathered for you
automatically on Oracle 11gR2
•
How to gather object statistics automatically and set statistics collection preferences for
automatic sample sizing, parallel degree determination, cascade options, granularity
determination, histogram population, and stale statistics thresholds
•
How to gather system statistics on Exadata
•
How to gather fixed object and data dictionary statistics
Validating Optimizer Statistics Collection with an AutoTask Job
In Oracle 11gR2, optimizer statistics collection is automated by default and will be gathered using an Oracle AutoTask
job. To validate that this is the case in your Oracle database, run the query in Listing 21-17:
Listing 21-17.
lst21-17-stats-autotask.sql
SQL> select ac.client_name,ac.status,
at.task_name,
sp.program_action
from dba_autotask_client ac,
dba_autotask_task at,
dba_scheduler_programs sp
where ac.client_name='auto optimizer stats collection'
and ac.client_name=at.client_name
and upper(at.task_name)=upper(sp.program_name)
/
AutoTask Client Status Task Name Program
-------------------------------- ------- ------------------ --------------------------------
auto optimizer stats collection ENABLED gather_stats_prog dbms_stats.gather_database_stats
_job_proc
SQL> select ac.client_name,ac.window_group,wm.window_name,
to_char(win.next_start_date,'DD-MON-RR HH24:MI:SS') next_start_date
from dba_autotask_client ac,
dba_scheduler_wingroup_members wm,
dba_scheduler_windows win
where ac.client_name='auto optimizer stats collection'
and ac.window_group=wm.window_group_name
and wm.window_name=win.window_name
/
Search WWH ::
Custom Search