OracleStatistics

Automatic statistics job status and history

Whether the nightly optimizer stats job is enabled, and how each run in the last week went. A run that keeps hitting the window end isn't finishing its work.

Not yet verified. How scripts are tested

ora-autotask.sql

1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN client_name HEADING "TASK"     FORMAT A354COLUMN status      FORMAT A105COLUMN started     FORMAT A166COLUMN job_status  HEADING "RESULT"   FORMAT A127COLUMN duration    HEADING "DURATION" FORMAT A308 9SELECT client_name, status10FROM dba_autotask_client;11 12SELECT TO_CHAR(job_start_time, 'YYYY-MM-DD HH24:MI') AS started,13       job_status,14       TO_CHAR(job_duration) AS duration15FROM dba_autotask_job_history16WHERE client_name = 'auto optimizer stats collection'17  AND job_start_time > SYSDATE - 718ORDER BY job_start_time DESC;

Save it as ora-autotask.sql and run it with SQL> @ora-autotask.

Open in denrepo

More Oracle scripts: Statistics

Tables and partitions with stale statisticsApplication objects the optimizer considers stale. The flush at the top makes sure recent DML is counted before the check.
Tables that have never been analyzedApplication tables with no statistics at all, which forces dynamic sampling or guesswork. Locked statistics are shown so you know which ones are…
Gather statistics for a table or schemaTemplates for gathering stats by hand. New statistics can change execution plans, so do this at a quiet time and know which plans matter.