Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, August 10, 2007

Gather statistics for SYS and SYSTEM schemas before adpatch

It is a best practice to gather statistics for SYS and SYSTEM schemas before begining a patch in Apps through adpatch. Metalink Note 290028.1 further substantiates this as there is a possibility that patches may hang while executing adpcpset.pls, which is called when you apply a patch. I had raised an SR with Oracle to get their confirmation on collecting statistics for SYS, SYSTEM, APPS and APPLSYS schemas and this what they said:

"No applsys or apps is not going to improve adpatch performance.You should gather stats for the SYS and SYSTEM schema. This is the official postion of dvlp."


So be sure to run these two sql statements before begining any adpatch task:

SQL> exec dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);

SQL> exec dbms_stats.gather_schema_stats('SYSTEM',cascade=>TRUE,degree=>20);

3 comments:

Anonymous said...

Thanks for the great information Vikram. Just wondering, is there any reason to choose the degree=20 or its just oracle recommendation.

Thanks,
Rohit

Vikram said...

Hi Rohit,

degree=20 is the degree of parallelism. You are correct it is just an Oracle recommendation. You can adjust this value to suit your environment.

Anonymous said...

Hi,

To gather the system stats, you need to run
Exec dbms_stats.gather_system_stats('start')

Exec dbms_stats.gather_system_stats('stop')

or

Exec dbms_stats.gather_system_stats('interval', interval=>N)