Oracle 10g SQL tuning with hints

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.