Access Path Scientific Analysis Part II

We continue to analyze the effects that initialization parameters, statistics and hints have on SQL statement access paths. In this blog, we’ll take a look at the hints we can use to influence the optimizer to select an access path that is different from the one it would normally choose. We’ll also review a few of the tools that we will be using to monitor and compare SQL statement access paths and performance for our upcoming tests.

In my last post, we reviewed the parameters that we will be modifying to influence the access paths. In this blog, we’ll take a look at the hints we can use to ask the optimizer to select an access path that is different than the one it would normally choose.

Additional Sources For Tuning Information
There will be times when I point you to different sources of information. The sites I will send you to are the sites I trust. First and foremost is the Oracle documentation. You need to READ THE MANUALS!!! Sorry, just reverting back to my life as an Oracle instructor. During class I would often pound the desk and yell out – “YOU MUST READ THE MANUALS!!!!”. They really do contain a wealth of information on the database environment you are administering including SQL performance and the tuning process. I understand that many of you want the “abridged” and shortened version to get just the information you need, but the manuals have come a long way and really are quite beneficial. They are much more to the point and are very good at giving you the pertinent information you are looking for.

So here is my first reading assignment. It’s a section of the 11G Performance and Tuning Guide that discusses optimization. If you read this section, you will have a VERY FIRM FOUNDATION of how the optimizer works and factors that influence the optimization process. The information provided covers everything from statistics to access path descriptions. You need to spend the 40 minutes or so reading this if you want to have a better understanding of the Oracle optimizer. The only really complex discussion is on Transforming Queries. The rest of the information is easily understood and just as clear as any third-party offering. We’ll cover query transformation in a couple of weeks.

I will also send you to various other websites. These folks (Tom Kyte, Jonathan Lewis, Hotsos crew, etc.) have spent countless hours deep in the internals of the database. There is really no need for me to take information that is accrurately presented in an easy-to-read form and regurgitate it.

So, let’s take a look at SQL Hints and how we can use them to investigate different types of access paths.

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.
• 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. Review histograms and their effect on skewed data.
• 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 effect 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, Oracle10G and 11G for my demos, we’ll be asking it to choose, first_rows, all_rows and rule. We’ll be using Oracle9i to show the rule hint only (since I already have one set up) and then get back to 10G/11G.
• 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 accessed 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.  Here’s a link to the 11G documentation on hints.  Before continuing this series, I highly suggest you read this section of the 11G documentation that I provided earlier in this blog that describes the different access paths.

Recommended Toolsets
You need to be careful about the tools that you use when you are using access path information to tune queries. Some tools, like the old-fashioned “explain plan into PLAN_TABLE” and SQL*PLUS Autotrace are predictions of the access path a given query will take while V$SQL_PLAN and SQL TRACE output is the access path actually taken during statement execution. A lot of this has to do with bind variables and bind peeking. We will definitely learn a lot more about access paths changing at runtime. But for the sake of this discussion, let’s operate under the premise that one of the ways we can reduce the likelihood of an access path change occurring is to replace the bind variables with hardcoded values when we run the explain. Which is exactly why we will be using hardcoded values for most of our tests.

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 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 Hint Demo Doc.   It is in Word format for ease of reading.

Now that you understand how hints can be used to influence access paths, you can use this information to assist you in determining how the different access paths change query performance. You can retrieve a SELECT statement that is running in the database, use hints to influence the access path and see the changes the different access paths have. We’ll learn more about selecting representative SQL statements in upcoming blogs.

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
Director Of Service Delivery