With your 12.2 upgrade, there are many new features and parameters. This blog post suggests 5 parameters to review as part of your upgrade, and 3 features to check out.
OPTIMIZER_FEATURES_ENABLE – Have you set this to the new version after the upgrade? Don’t forget this important step. If you leave this setting at your old version, what was the point of the upgrade?
AWR_PDB_AUTOFLUSH_ENABLED – this is set to false by default for your PDB, but if you want AWR snapshots enabled, set it to true. If you set it to true in the root, it will be true for all PDBs. The other option is to set it to true in specific PDBs.
AWR_SNAPSHOT_TIME_OFFSET – AWR snapshots normally start on the hour, so if you have many instances on one node can cause a CPU spike. By default, there is no value for the parameter. The value is in seconds, so changing it to less than 3600 would control the number of seconds after the hour the snapshot would start. But there is also a special value of 1000000 in which the offset is based on the database name, and therefore there the offset is considered automatic. If there are many instances on one node, this allows for a reasonable distribution of offset times. If you do not have multiple instances on one node and you are used to thinking of snapshots as hour long intervals from 1:00 to 2:00 for example, do not change this parameter.
Oracle has some recommendations for the following two parameters, and this Oracle blog post has more information.
OPTIMIZER_ADAPTIVE_PLANS – The default is TRUE.
OPTIMIZER_ADAPTIVE_STATISTICS – The default is FALSE.
Optimizer Statistics Advisor – Starting with 12.2 the Optimizer Statistics Advisor will be giving you advice about how to improve your statistics collections. After your upgrade, spend some time reviewing the recommendations and testing any changes in a test environment before implementing them.
Database In-Memory – Database in Memory was new in 12.1 and it was enhanced in 12.2. Make sure your database has enough memory provisioned to make good use of this feature.
RMAN – Oracle 12.2 allows for restore points to be created within a PDB.
Contact Datavail for more help with upgrades and troubleshooting your Oracle database. Datavail is a specialized IT services company focused on Data Management with solutions in BI/DW, analytics, database administration, custom application development, and enterprise applications. We provide both professional and managed services delivered via our global delivery model, focused on Microsoft, Oracle and other leading technologies.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.