Oracle statistics job for imporved performance

  • Document ID:KM492231
  • Creation Date:28-Aug-2008
  • Modified Date:28-Aug-2008
    • Micro Focus Products:
      universal cmdb

Question

Oracle 10g has automatic statistics job. Oracle 9.x does not manage statistics automatically and require to manually deploy a statistics job for improve database performance.

Answer

Oracle 10g automatic statistics job is scheduled to run every night except weekends. If discoveries are scheduled on weekends. It is suggested to change the statistics schedule to run every night, so slow performance is not observed on Mondays.

For Oracle 9i use the following for statistics

AD-HOC STATISTICS JOB:

To run the statistics job ad-hoc, log in via SQLPlus as the system account, and run the following command:

    begin DBMS_STATS.GATHER_SCHEMA_STATS (ownname => '<name of mam's oracle user>', cascade => TRUE) ; end;
SCHEDULED STATISTICS JOB:

Alternatively, the statistics job can be installed by running the create_statistics_job.bat script under <MAM 6.x install path>\J2F\CMDB\dbscripts\oracle or <UCMDB 7.x install path>\J2F\CMDB\dbscripts\oracle\utils with the following syntax:
    create_statistics_job.bat <schema> <password> <db alias> <hour>
Once the job is installed, log in via SQLPlus, and execute the job number in order to gather statistics at least once before it begins working on its interval as defined by the <hour> parameter above.
    select job from user_jobs where upper(what) like '%GATHER_SCHEMA_STATS%';
    exec dbms_job.run(<job number from above>);

It is recommended to run scheduled statistics job every night.

For details please use the Database Guide available with the product. Refer to Deploying and Maintaining MS SQL or Oracle Server Chapter in the document.

For MAM 6.x refer to MAMPrepare.pdf for UCMDB 7.x refer to DatabaseGuide.pdf