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);
Subscribe to:
Post Comments (Atom)
Posts
3 comments:
Thanks for the great information Vikram. Just wondering, is there any reason to choose the degree=20 or its just oracle recommendation.
Thanks,
Rohit
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.
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)
Post a Comment