How to know Automatic Optimizer Statistics Collection is enabled?

sakthikumar
sakthikumar used Ask the Experts™
on
How to know Automatic Optimizer Statistics Collection is enabled/disabled?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
like is there any data dictionary view through which I can look into it.
Oracle dba
Top Expert 2009
Commented:
if you didn't switch it off, then it's on
and assuming the startup parameter in the pfile/spfile for
STATISTICS_LEVEL = TYPICAL or ALL

you can enable and disable it via dbms_auto_task
http://docs.oracle.com/cd/E25054_01/server.1111/e16638/stats.htm#CIHDGIID

with this query you can see if the 11g job BSLN_MAINTAIN_STATS_JOB is enabled and scheduled

Select owner, job_name, enabled, state, last_start_date, next_run_date, logging_level
FROM SYS.DBA_SCHEDULER_JOBS
where owner = 'SYS'
  and Job_name IN ('BSLN_MAINTAIN_STATS_JOB', 'GATHER_STATS_JOB');

Open in new window


if upgraded from 10g you'll also notice GATHER_STATS_JOB
and this will be disabled

Author

Commented:
excellent.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial