Jonas Mason, Principal Architect

My last blog on Non-Default Oracle parameters asked a question in order to prompt a conversation between technical management and DBAs. The blog prompted additional feedback from colleagues here at House of Brick that is worth mentioning, given the impact on performance.

These additional items include:

• cursor_sharing
• optimizer_index_cost_adj
• optimizer_index_caching
• non-standard block sizes
• shared server vs dedicated server
• db_cache_size
• HugePages

CURSOR_SHARING

The default for cursor_sharing for a 12c instance is exact, in which, per Oracle[1]:

The database does not attempt to replace literal values with system-generated bind variables. In this case, the optimizer generates a plan for each statement based on the literal value.

If parsing, due to not using bind variables, is a significant DB wait event, consider setting this parameter to force, in which, per Oracle:

The database replaces all literals with system-generated bind variables. For statements that are identical after the bind variables replace the literals, the optimizer uses the same plan.

The goal of using the cursor_sharing parameter would be to reduce overhead associated with the hard parsing of SQL. Testing and validation of this can be accomplished with a review of a Statspack or AWR report before (and after) similar workloads are executed.

House of Brick has seen cursor_sharing=force used successfully in environments where large amounts of dynamic SQL are used, and bind variables are not used. Be advised however that this is a temporary solution, and the best solution is to fix the code to use bind variables. Dynamic SQL is vulnerable to sql injection and should be corrected.

Shared Server

It is rare to see shared server configurations of Oracle today, as these configurations were more prevalent when memory and CPU were less abundant in 32 bit systems. Shared server, in effect, pools all user sessions into a single (or a much small number of) Oracle process(es), with the total memory usage limited to the size of the user process settings for a single process. However, in modern 64 bit servers where memory and CPU are abundant, dedicated server configurations are now typical.  Generally, a shared server configuration is used to reduce the amount of Oracle memory dedicated to user sessions.

On the other hand, Oracle Apex uses shared servers on HTTP ports, and this cannot be changed. For this reason, it is generally a bad idea to generate large reports through Apex.

I have come across shared server Oracle database configurations recently that were the result of legacy parameters not being cleaned up. The shared server configuration was no longer required on the newer, virtual server with a 64 bit OS. In only one case, have I encountered a client where a shared server configuration was required to accommodate a large application server farm and the connections it generated.

optimizer_index_cost_adj and optimizer_index_caching

Per a colleague here, resetting these parameters was popular when going from 9i to 10g. At other times, adjusting these parameters could fix poor performance after an upgrade. So, the point to consider is, have these parameters lost relevance now that an instance is running 11g or 12c? The Oracle optimizer has improved dramatically since these earlier Oracle versions; however, if you are still running 9i or 10g, there could be a good reason why these parameters have been set.

Non-Standard Block Sizes

Only a data warehouse might require the use of a 16k data block size, and I would still want proof through baseline testing that rest of the stack 1) supports the increased block size, and 2) experiences less latency and reduced SQL execution times. Oracle tests primarily on databases with an 8k block size, as referenced in this  article by Tom Kyte. So I recommend sticking with 8k block sizes for your databases, unless you have a verifiable reason to deviate from Oracle’s standard.

db_cache_size

Setting this value does not set a maximum value, and therefore would not create a bottleneck if set too low. Either unset it, or set it as a reasonable portion of current AMM or ASMM memory based on Statspack or AWR sizing recommendations for peak periods.

These reports can be very helpful in informing the DBA of appropriate memory settings, including PGA, SGA and db_cache_sizes. If you are doing an upgrade from an earlier Oracle version, you can start by setting this to the value that your AWR or Statspack report lists as the DB_CACHE_SIZE. This can help reduce the amount of time it takes Oracle to auto tune the cache.

HugePages

We generally start considering implementing HugePages when SGA is in excess of 15 GB. The additional complexity of setup (though not significant), along with the reduced CPU benefit, isn’t justified on anything less.

Conclusion

Every non-default parameter should have a story that explains and validates it. The story might be a mystery due to resource turnover and lack of change control. Your job might be to solve the mystery, so that your narrative can start somewhere. This narrative creates a context for future changes, such as increased SGA and PGA sizes. Parameters that impact the optimizer and SQL explain plans generated are important to consider given that they can drive up resource utilization if sub-optimal plans are chosen. Having a conversation about parameters is simple enough, and might uncover easy to fix bottlenecks that could facilitate the more efficient use of CPU, memory, and Disk I/O at little extra cost.

[1] https://docs.oracle.com/database/121/TGSQL/tgsql_cursor.htm#TGSQL-GUID-7A0CD3D1-1ECB-4DA4-813A-0D8D79C8B65D