Highlighted
Established Member..
Established Member..
199 views

PPM Support Tip: Big things that the Oracle DBA may miss when upgrading their database for PPM

Below are some best practices from Oracle and it seems that many DBAs are not following them.  In large databases they make are said to make very large performance differences.


References:

[1] https://docs.oracle.com/database/121/UPGRD/afterup.htm#UPGRD60056
[2] https://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG733

1.    Check fixed object statistics and any other SYSTEM related stats collection.  Not only the PPM Schema must be considered when moving to RAC or upgrading a database.

select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

tells you if fixed objects were ever gathered.
[1]  4.6.3 Gather Fixed Objects Statistics with DBMS_STATS
A few days after upgrading Oracle Database, a best practice is to gather fixed objects statistics with the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS PL/SQL procedure. This can have a positive impact on overall database performance. DBMS_STATS.GATHER_FIXED_OBJECTS_STATS also displays a recommendation to remove all hidden or underscore parameters and events frominit.ora/spfile.
Because of the transient nature of the x$ tables, it is important that you gather fixed objects statistics when there is a representative workload on the system. This may not always be feasible on large systems due to additional resources needed to gather the statistics. If you cannot do this during peak load, then you should do it after the system has warmed up and the key types of fixed object tables have been populated.
To gather statistics for fixed objects, run the following PL/SQL procedure:
SQL> execute dbms_stats.gather_fixed_objects_stats;

2.    AUD$ archiving and purging and write mode

[2] You should periodically archive and then purge the audit trail to prevent it from growing too large. Archiving and purging both frees audit trail space and facilitates the purging of the database audit trail.

select segment_name table_name, bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$');


•Immediate-write mode. This setting writes all audit records to the audit trail immediately. However, be aware that database performance may be affected.
•Queued-write mode. This setting, which is the default write mode, queues the audit records in memory to be written periodically to the AUDSYS schema audit table. To set the size of the SGA, set the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter. The default size is 1 MB, and you can enter a range of 1 through 30.

Labels (2)
0 Likes
0 Replies
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.