See also:
A few weeks ago we’ve published some parameter recommendations including several underscores but based on an internal discussion (still ongoing) we decided to remove this entry and split up the tasks. The optimizer team will take over parts of it and I’ll post an update as soon as something is published.
Preface
Please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.
We strongly recommend SQL Performance Analyzer to verify the effect of any of those parameters.
How to read this blog post?
Never ever blindly set any underscore or hidden parameters because “somebody said” or “somebody wrote on a blog” (including this blog!) or “because our country has the best tuning experts worldwide” … Only trust Oracle Support if it’s written into a MOS Note or an official Oracle White Paper or if you work with a particular support or consulting engineer for quite a long time who understands your environment.
Important Parameter Settings
- _kks_obsolete_dump_threshold
- Introduced in Oracle 12.1.0.2 as an enhancement to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and its child cursors after the parent cursor has been obsoleted N times.
- Trace files can grow like giant mushrooms due to cursor invalidations
- See
MOS Note:1955319.1; Huge Trace Files Created Containing “—– Cursor Obsoletion Dump sql_id=%s —–“:
for recommendations, range of values and options - Furthermore you may read:
Where do these large trace files come from in Oracle 12c?
- Fix included in DBBP 12.1.0.2.160216
- Fix on-top of 12.1.0.2.13DBEngSysandDBIM
- Since Feb 13, 2016 there’s a one-off available but on Linux only – and only on top of a fresh 12.1.0.2
- The underlying cursor sharing problem needs to be investigated – always.
If you have cursor sharing issues you may set this parameter higher therefore not every invalidation causes a dump, then investigate and solve the issue, and finally switch the parameter to 0 once the issue is taken care of.
Please be aware that switching the parameter to 0 will lead to a lack of diagnostics information in case of cursor invalidations.
- _use_single_log_writer
- Multiple log writers feature in Oracle Database 12c can cause instance recovery fails and unexpected halts
- Be aware of several issues such as:
MOS Note:1957710.1 – ALERT: Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2] ) - MOS Note:1957710.1 gives also recommendations on how to set this parameter
- Apply Patch 21915719 or April DB PSU to
prevent bug 21915719 from
happening. The patch does not repair the inconsistencies
in the online redo log causing the restart/open issue but
prevents it from happening
- memory_target
- See the Oracle Documentation – it combines sga_target and pga_aggregate_targetvalues
- Unexpected failing database upgrades with settings of memory_target < 1GB where equal settings ofsga_target and pga_aggregate_target didn’t cause issues
- It prevents the important use of HugePages
- Avoid memory_target by any chance
- Better use sga_target and pga_aggregate_target instead
- pga_aggregate_limit
- It limits the overall PGA heap size
- See the Oracle Documentation for further details
- It’s not really a limit. According to Frits Hoogland‘s (Enkitec) presentation at DOAG Conference I learned that it does limit the PGA but beyond the defined limit. An error ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT gets triggered – but beyond the defined limit.
Find Frits’ blog post for a lot more details and test scripts here (thanks Stefan for pointing me to it):
https://fritshoogland.wordpress.com/2014/12/15/oracle-database-operating-system-memory-allocation-management-for-pga/
- Be aware that it’s not a true limit.
Monitor your PGA growth – you can’t rely exactly on pga_aggregate_limit
.
Essential MOS Notes for Oracle Database 12.1.0.2
-
Known Issues
MOS Note:1683799.1 – 12.1.0.2 Patch Set – Availability and Known Issues
-
SQL Plan Management
-
Wrong Results and Poor Performance
MOS Note:2034610.1 – Things to Consider to Avoid Poor Performance or Wrong Results on 12.1.0.2
-
Proactive Tuning and Avoiding Performance Issues
MOS Note:1482811.1 – Best Practices: Proactively Avoiding Database and Query Performance Issues
- Complete Checklist for Manual Upgrades to Oracle 12.1.0.2MOS Note: 1503653.1 – Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1).
.
Hi Mike,
How be sure of not having side effects after setting these hidden parameters ?
Cheers
Kais
Kais,
you won’t be surprised by my answer I’d guess 😉
Please see the Preface above:
Preface
Please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.
We strongly recommend SQL Performance Analyzer to verify the effect of any of those parameters.
Cheers
Mike
do you have any parameter recommendation for 12.1.0.2 ? we have applied Jan2017 patch..for GI and DATABASE.
Sure – please browse through the blog as Roy and I gave a lot of recommendations within the past 12 months.
In addition this one is the most important one meaning you’ll have to apply two fixes on top:
https://blogs.oracle.com/UPGRADE/entry/enabling_adaptive_features_of_oracle
Apart from that we can’t give "blindly" recommendations without knowing anything about your environment. A good rule is to have not too many parameter set. The more simple the better.
Cheers
Mike