Access Path Scientific Analysis Part II

SQL Hints
Administrators embed hints in a SQL statement to influence the optimizer to choose a particular access path.

By using hints, you are telling Oracle that your access path is better than the one the optimizer is choosing. It’s a safe assumption that most of us aren’t as smart as the optimizer. Let it make the choice, unless you are certain the optimizer is choosing the incorrect access path.

But what happens if the optimizer is making incorrect decisions? Before you begin adding hints to SQL or freezing access paths using Optimizer Plan Stability or 10G Profiles, consider taking the following steps first:

• Determine if it is actually an incorrect access path that is causing the performance problem. It may be some external influence affecting the SQL (hardware, workload, and so on).
  
  
• Identify and review the SQL taking the bad access path for proper SQL coding techniques.
  
  
• Verify that statistics have been generated on the tables and indexed columns. The Oracle-supplied utility DBMS_STATS is currently the preferred method of collecting statistics.
  
  
• Review parameters that affect SQL optimization (optimizer_mode, optimizer_index_cost_adj, optimizer_index_caching, optimizer_dynamic_sampling, optimizer_features_enable, optimizer_max_permutations).
  
  
• Investigate system statistics. Is it activated? Is it configured correctly if it is activated? Should it be activated?
  
  
• Does the application use bind variables? If so, investigate bind peeking quirks.
  
  
• Check for skewed data. Consider using histograms to compensate.
  
  
• Go to Metalink and review optimization bugs for your release. Oracle could have already identified your issue and fixed it.
  
  OK, so you have performed all of the actions cited previously and you find that the optimizer is actually making an incorrect decision. Regardless of what some industry pundits may tell you, the optimizer is NOT infallible; it can make mistakes. Oracle created hints for a reason, and wouldn’t have made them public if it didn’t think we really needed them from time to time. If you are forced to add hints to the query to improve its performance, do so intelligently and judiciously.

Using Hints to Compare Oracle Access Paths
OK, now that I have provided you with my standard warning on hints, the intent of this blog is to learn how to use hints to influence access paths for testing purposes. Using hints will allow us to evaluate the affect that different access paths have on SQL statement performance. We will run the statement without any modification, review the access path and performance statistics, use a hint to (hopefully) change the access path, run the statement again and compare the before and after results. Since hints can be embedded in virtually any SQL statement, they will provide us with an easy mechanism to learn more about access paths. We are on our way to becoming database performance scientists!

We'll begin our access path scientific analysis by using a very basic set of hints to influence the optimizer to choose a different access path. The hints I will be using in my introductory demo are:

• Hints for optimization mode - We will be asking Oracle to optimize the statement using different optimization goals. Since we are using Oracle9i for my demo, we'll be asking it to use choose, first_rows, all_rows and rule.
  
  
• Hints for access paths - Access path hints ask the optimizer to choose the access path it recommends. We'll be asking Oracle to use an index that it didn't choose in the original access path it generated. We'll also be asking the optimizer to choose a full table scan instead of using an index.
  
  
• Hints for join operations - Oracle provides several different join methods for statements that join one, or more, tables together. We'll ask the optimizer to choose nested loop, merge scan and hash joins.
  
  
• Hints for join order - Oracle only joins two tables at a time. If multiple tables are joined, join order also describes the overall order of the tables being accessed. Oracle will join two tables and create an intermediate result set which is then used as input to the next join.
  
  Join order plays a significant role in query performance. Both in the outer and inner tables selected and the overall join order. In general, you want to reduce the number of rows processed as soon as you can during the processing of a given SQL statement. The sooner you can reduce the number of rows being sent to future operations, the faster the query will usually run. We'll ask the optimizer to choose different join orders to determine the impact it has on SQL performance.

The bullets above are just a subset of all of the hints that are available. For a complete listing (and their definitions), please refer to the Oracle Database Performance and Tuning Guide for your release. You'll find the documentation on Oracle's Technet website.

Recommended Toolsets
The two tools that I most often use to review SQL performance during my own scientific analysis is SQL*PLUS Autotrace and SQL Trace. Please refer to my blog titled "Access Path Identification - Part IV" to learn more about using SQL*PLUS Autotrace and SQL Trace utilities to evaluate SQL access paths and SQL statement performance.

Demo Document
The intent of this demo is not to train you to identify which access path is most optimal for a given situation. Its intent is to help you gain experience interacting with the Oracle optimizer. Take it from your friendly ex-Oracle instructor, spending time experimenting with the optimizer and analyzing the performance statistics that different access paths generate is critical to your tuning education. There really is no substitute for time spent "in the seat" performing your own scientific analysis on query optimization.

The demo will show you how to use hints and the ALTER SESSION SQL statement to influence the optimizer to take a different access path than it normally would. You can then compare the the access paths and their associated performance statistics to obtain a better understanding of what accces path is best for your test queries.

Here's a link to the demo document. It is in Word format for ease of reading.

In my next blog, we'll discuss what SQL statements to use for your testing and what to look for when you compare the results.

Thanks for reading.

Chris Foot
Oracle Ace