Oracle Database Discussions

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production


******************** List stale statistics for Schema IGR using dbms_stats.gather_database_stats **************************
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
IF ObjList(i).ownname = 'IGR' THEN
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END IF;
END LOOP;
END;

.....
IGR.USER_INFO TABLE 189 < --------------------- 189 Tables with stale statistics

PL/SQL procedure successfully completed.

**************************** Check date IGR Table(s) last analyzed ***********************************************

SELECT MAX(last_analyzed) last_analyzed
FROM dba_tables
WHERE owner = 'IGR'

LAST_ANALYZED
------------------
14-DEC-11


*************************** Check status of Task 'auto optimizer stats collection' **********************************

SELECT client_name, job_name, job_status, job_start_time
FROM dba_autotask_job_history
WHERE client_name = 'auto optimizer stats collection'
ORDER BY job_start_time


CLIENT_NAME JOB_NAME JOB_STATUS JOB_START_TIME
----------------------------------- ------------------------- ------------ ---------------------------------------------
auto optimizer stats collection ORA$AT_OS_OPT_SY_11952 SUCCEEDED 12-FEB-12 10.07.23.059313 AM EUROPE/LONDON
auto optimizer stats collection ORA$AT_OS_OPT_SY_11955 SUCCEEDED 12-FEB-12 02.07.33.879238 PM EUROPE/LONDON
auto optimizer stats collection ORA$AT_OS_OPT_SY_11958 SUCCEEDED 12-FEB-12 06.07.43.783262 PM EUROPE/LONDON
auto optimizer stats collection ORA$AT_OS_OPT_SY_11961 SUCCEEDED 12-FEB-12 10.07.54.206488 PM EUROPE/LONDON

*********************************************************************************************************

Any suggestions welcome


Thanks

Steve