The Latest Technology Stack News Directly from EBS Development
-
Database Tier
July 17, 2017
Check Your EBS Database Configuration with Database Parameter Settings Analyzer
Database ParaIn addition to helping customers resolve issues via Service Requests, Oracle Support also builds over 60 free diagnostic tools for Oracle E-Business Suite 12.2, 12.0, 12.1, and 11i. These Support Analyzers are non-invasive scripts that run health-checks on your EBS environments. They look for common issues and generate standardized reports summarizing that provide solutions for known issues and recommendations on best practices.
Here's an index to these tools:
Spotlight on Database Parameter Settings Analyzer
We publish a definitive list of Oracle Database initialization parameter settings for the optimal performance of Oracle E-Business Suite 12.2, 12.1, and 12.0:
This document is updated regularly; for example, it was recently updated to account for changes introduced by the April 2017 updates to the AD and TXK utilities.
It can be challenging to keep up with those changes simply by scanning Note 396009.1 yourself. You can automate this process by using the Database Parameter Settings Analyzer:
- EBS Database Parameter Settings Analyzer (Note 1953468.1)
The Database Parameter Settings Analyzer compares your database's parameter settings to the latest recommendations in Note 396009.1. It reports on any differences, and makes recommendations about your sga_target, shared_pool_size, shared_pool_reserved_size and processes parameters based upon the number of active users for your environment.
This tool can be run manually or configured to run as a concurrent request, so it can be scheduled to be run periodically and included in regular database maintenance cycles.
Can this script be run against Production?
Yes. There is no DML in the Analyzer Script, so it is safe to run against Production instances to get an analysis of the environment for a specific instance. As always it is recommended to test all suggestions against a TEST instance before applying to Production.
Related Articles
https://docs.oracle.com/database/121/REFRN/GUID-B6CE9EA3-B846-4AA7-B668-FD3BEE433A24.htm#REFRN10158
Per Oracle 12c Rel1 DBA Reference document (above), default value = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5
But Oracle EBS note recommends it to be 2 * CPU only. What is the reason behind recommending it to set so low?
For example for 16 core CPU with SGA Target set, per DBA reference guide, PARALLEL_MAX_SERVERS can be set at 2 * 16 * 4 * 5 = 640, whereas per EBS note, it can only be set at 32. what will happen if we use the DBA reference guide setting?
Please advise.
Our performance architects reviewed your second question. They said:
Parallel maximum server configuration is a function of cpu core not SGA size. Our recommendation is to set the parallel max server by a maximum factor of two for Oracle applications, taking into account the fact that we do not want to saturate the server and starve the online user flows.
Regards,
Steven