Schedule gather Schema Stats

Schedule gather Schema Stats and Find Request ID:
====================================

Find the status of gather schema statistics:
-----------------------------------------------------

select count(*)
from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r
where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id
and p.user_concurrent_program_name in (
'OnDemand Gather Schema Statistics',
'Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)'
)
and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-1)


Find Gather schema statistics request id:
==========================================

set pages 1000
set line 132
set head on
select  r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like '%Gather Schema Statistics%'
and  r.actual_start_date >= sysdate-2 order by r.requested_start_date;

13037834

run the particular request-id:
===================================

select REQUEST_ID , REQUESTED_BY ,
decode(phase_Code,'P','Pending','R','Running','C','Completed Normal')
PHASE,decode(status_code,'C',' Completed','I',' Inactive ') STATUS from
APPS.fnd_concurrent_requests where REQUEST_ID='&a';  2    3    4
Enter value for a: 12955988

14075076



We have completed the following gather sys level stats for:

Dev  - 1 Hour
Test  - 45 minutes
Prod   - 50 minutes

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ();

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

These should be run as per Oracle Support Note: 457926.1.
Whenever there is a large change in volume of data ( increase or decrease )
And where the database is large and may have large objects – as BG and other sites do.


We will enable a weekly cronjob for the last two to run at the end of the week ( Fri 5pm Perth time )
They are generally ok to run without impact to apps.