QSolve, Inc.

I’m often asked by clients on how to tune queries or applications that cannot be modified.  By code that cannot be modified, I’m referring to packaged applications (for example, Oracle Applications, PeopleSoft, etc) or generated code (Oracle Warehouse Builder, etc).  So, I thought this would be a good way to start my blog.  While most of this information is available in various posts and articles, the intent of this blog is to (1) gather this information into one article and reference other articles as needed and (2) complement referenced articles with additional information.

A note before we begin…

The information below is meant for educational purposes only, to show features of the optimizer available to DBAs.  The information provided is for advanced DBAs and should be thoroughly evaluated for each situation.  While these suggestions will improve performance, they can also, if not properly researched and tested, make matters worse.

Identifying Poorly Performing Code

The first step in any performance tuning initiative is to identify the code that needs to be tuned.  This is most commonly done by gathering a trace of the process that is slow.  Properly gathering a trace is key to identifying the problem query.  Hotsos has an excellent paper that describes how to gather a trace file from the database.  In my next blog, I’ll write about gathering trace files within Oracle Applications and how to ensure that the code is seeded code versus custom code.

Tuning Suggestions

Statistics

Oracle uses a Cost Based (CBO) method for determining that optimized path for executing a query.  This is now the default method used by Oracle.  In previous releases, Oracle used a Rule Based method for determining the optimal execution path.  Starting with 11g, this option is no longer available.  The Cost Based method uses statistics about the database objects to determine the optimal path.  For this reason, statistics needs to be gathered on the database objects.

• How often should statistics be gathered?
  Statistics should be gathered when there is a considerable change in the volume of data.  Oracle’s general rule of thumb in this matter is if there is a 10% increase, you should gather stats.  That’s not to say that if you application has low volume that you don’t need to gather statistics.  How old the statistics are also impact how the optimizer computes a plan.  Ideally, statistics should be gathered at least once a month and when there are large changes in volume of data.

  Statistics should not be gathered too frequently as well.  Every time statistics are gathered means that there is a change to the base objects.  This causes the database to invalidate the cursors in memory and would have to reparse the query again.  At a recent client, they were using Oracle Warehouse Builder to do all of their data conversion from their legacy systems into Oracle Applications.  They wanted to schedule these jobs to run through the Concurrent Managers and therefore, created a request set to first gather statistics and then run the conversion.  However, due to a bug in OWB, the parse was taking 95% of the time.  There was no need to gather statistics before running the conversion jobs.  Once that step was removed, the conversion process went from taking over three days to run, to completing in less than 4 hours.

• Sample Size
  Oracle defaults to a sample size of 10%.  The sample size is the size Oracle uses to approximate the statistics on an object.  On large objects, especially objects in an Oracle Applications instance, this value does not provide accurate statistics.  A recommended value is 45%.  Using 99% often takes too long to complete.  But again, as with any other performance tuning initiative, different values should be tested to see what works.  Some schemas may require a higher sample size to get accurate statistics.
• Histograms
  Gathering column histograms is a very valuable tool and can greatly influence the execution path the optimizer chooses.  Using histograms tells the optimizer about how the data is distributed within a column.  If a column contain 90% of one value, it may be better to perform a full table scan versus using an index.  I recently presented a paper at the OAUG Connection Point conference in Dubai on using histograms to tune Oracle Applications, which I will make available for download soon.

Add/Remove indexes

Creating indexes on columns will greatly help with performance.  However, if creating indexes on multiple columns, one should consider the order of the columns within the index and ensure that index is highly selective.  Having an unselective index will hurt performance more than help.

The optimizer may use the statistics of an index on a table, even though that column may not be in the query, to get a better idea of the data distribution.  This can be determined by tracing the optimizer (10053 trace) and looking at the permutations that the optimizer goes through.  I recently came across this at a client while helping them tune their conversion process.  The statistics on an unused index were misleading the optimizer.  Since it was a seeded index that was being used by other processes, we couldn’t drop it.  So in order to have the optimizer not take these statistics into account, we disabled the index for the conversion and enabled it afterwards.  Alternatively, we could have deleted the statistics, but the seeded conversion process was gathering statistics on every run, which meant the DBAs would have to continuously monitor the index for new statistics and delete them manually.

Parallelism

By modifying the degree of parallelism on a database object (table or index), it can make a full table scan more or less efficient to the optimizer.  Parallelism is best suited for environments where the I/O subsystem is not a bottleneck and there are space CPU cycles available.  Often times, DBAs will rebuild indexes with a parallel option.  This makes the rebuild go faster.  However, doing so also changes the degree on the index.  This often times goes unnoticed and can cause instability in the database, as it may only occur for certain situations.

Instance Parameters

Tuning instance parameters should be considered as a last ditch effort to tuning a query.  While tuning some of the parameters may help the query in question, it may also be detrimental to other queries.  Metalink notes 396009.1 and 216205.1 are good notes to follow to establish a baseline for the initializations parameters.  Automatic Memory Management should be enabled.  This avoids the need for individual tuning of the different memory caches and pools.  It also improves performance and manageability.  Set the SGA_MAX_SIZE parameter.  This parameter allows you to grow the size of the SGA (SGA_TARGET) dynamically.  Ideally, it should be set to the current size of the SGA plus 50%; thus, allowing the SGA to grow 50% more when needed.

In future blogs, I hope to be able to talk about more techniques, such as using materialized views and outlines.  I’d like to reiterate that these techniques can improve performance greatly, but as with any performance tuning initiative or changes to a system, they should be thoroughly tested to ensure no adverse affects to other parts of the system.