Access Paths VII – Access Path Education

Introduction
This blog is not about the SQL tuning process, the last 6 blogs contain a plethora of information on how to identify performance issues and drill down to find the poorly performing SQL. It's intent is also to not teach you to tune, it's focus is to help you begin, or brush up, on access paths and SQL tuning. Remember the Foot Rule of Thumb "The mark of being a good DBA is not knowing everything, its knowing where to look when you don't".

Gaining a well-rounded and deep understanding of Oracle access paths and SQL performance is a wonderfully challenging task. One of the hurdles is the time required to learn how to tune SQL. DBAs are being challenged with ever-increasing workloads, shrinking DBA staffs and increasingly complex technologies to support.

In addition, the majority of shops view database administrators as much more than just "table jockeys." The DBA is often seen as the go-to technician because of their traditionally strong problem solving skills. The DBA is also viewed as the IT staff's technical generalist because of the working knowledge they have in many different facets of information technology. Those of us that have been working in this profession for any time at all understand that the term "database administration" is really a misnomer. We have to know everything from application and data design to network communications and operating systems (and everything in-between).

As a result, many of us don't have enough time to dig deep into the many different facets of the Oracle database environment. Oracle has recognized this issue and has created the various advisors in 10G to assist us in the monitoring and tuning process. I have written dozens of blogs on the toolsets provided in 10G that are designed to reduce the amount of time we spend administering, troubleshooting and tuning the Oracle Ecosystem (database, operating system, hardware server).

The Importance of Understanding Oracle Access Paths
That being said, all DBAs must have a firm understanding of Oracle access paths and SQL tuning. During my career, I've learned that there is nothing that can drag down an Oracle Ecosystem than poorly performing SQL statements.

That's when management suddenly doesn't care about how much you know about RAC, standby databases and data design. They want the database "fixed" and running smoothly again. They suddenly have a single-minded purpose. They quickly begin the chant, the chant that can only be described as the management mantra - "is it fixed yet, is it fixed yet, is it fixed yet, is it fixed yet, is it fixed yet….."

I've actually had my entire chain of command stand behind me in order of where they fit into the management food chain (team lead, project manager, section manager, division manager, VP…) while I was working on a database performance issue. I turned around and chuckled when I saw the lineup of managers. Funny, they didn't share my humor.

You need to get the performance problem fixed, you're nervous and you have a 15 page SQL statement on your screen. This is where a strong education in Oracle access paths becomes "somewhat handy."

SQL Tuning Topics You Must Understand
Here's a quick laundry list of topics you'll need to know. All of them are important. I have tried to include resources later in this blog that will provide you with information on all of them. You can use this listing to check off the topics during the education process.

• The Oracle release's impact on optimization and SQL tuning. Each new release contains features that affect access paths and SQL performance. Sometimes good and sometimes not so good.
  
  
• Oracle parameters that affect the optimizer. There are a handful or two of startup parameters that can influence the SQL optimization process. I have provided several articles below on this topic.
  
  
• Optimizer modes - Rule, choose, first_rows, all_rows. Each of the modes influence the optimizer to create access paths for the type of workload the database is responsible for supporting. For example, the first_rows optimizer mode may be OK for online transaction processing (read a record/write a record) but probably won't generate efficient access paths for a data warehouse database where millions of records are summarized.
  
  
• Oracle data statistics - How the optimizer uses them, how they affect access paths.
  
  
• Oracle system statistics - Later releases of Oracle can also incorporate the system load information during optimization.
  
  
• Basic access paths
  • Index only - Oracle is able to read all of the data required to satisfy the query's data needs from the index structure alone.
  • Index to table - Oracle uses a row identifier to probe the table to satisfy the data request. Why read all of the rows in a table if you can use an index structure to retrieve just the rows you need?
  • Full table scan - Oracle reads all rows from the table. If the statement is going to read the majority of a table's rows, why would you want it to needlessly traverse an index to get the data? You are reading extra index blocks for no reason. You also need to learn the impact that the high-water mark has on full table scans. Oracle will scan the table to the last block used (as opposed to the last block that actually contains data).
    
    
• Join access paths - Used when the statement retrieves data based on matches between two tables (i.e. retrieve all of the employees that have the department name of "Welding"). The employee information is contained in the employee table and the department information (including the department name) is in the department table.
  • Nested loop join - Good path when the join is accessing a small subset of rows.
  • Hash join - Efficient access path for joins that access larger sets of data.
  • Sort merge join - Sorts rows to allow quicker access during the join.
  • Cartesian join - The tables being joined do not have join clauses that relate the two tables together.
  • Outer joins - An outer join returns all of the rows that satisfy the particular join condition and returns additional rows from one table that do not satisfy the join condition.
    
    
• Join order - Oracle only joins two tables at a time. The first one accessed is known as the outer table, while the second one is defined as the inner table. The outer table is accessed first and is used to probe the inner table. 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.
  
  
• Subqueries - A select within a select statement. Can be one of the trickier statements to tune, especially when you have multiple subqueries embedded within each other.
  
  
• Indexes and selectivity
  • B-tree indexes are good for column(s) that have many unique values (high selectivity)
  • Bitmap indexes are used for column(s) that do not have many unique values (low selectivity)
  • How SQL statement predicates can determine if an index can be used. There are times when the way a statement is coded prevents Oracle from choosing an index as the access path. A common problem that often leads to poor performance. You do get a chance to flog the application developer responsible, though.
    
    
• Types of index access paths
  • Index unique scans - The SQL statement accesses an index using a column (or columns) that are defined in a unique or primary key index with an equality condition.
  • Index range scans - Oracle scans a set of entries in the index to satisfy a query.
  • Index skip scans - Oracle is able to break down a multi-column index and view them as smaller subindexes. This is achieved by Oracle "skipping" the leading columns in the index and using columns that appear later in the index's definition.
  • Full scans - Oracle scans all of the index entries. Kind of like a tablescan on an index.
  • Fast full scan - Oracle is able to satisfy the data retrieval requirements for a particular table by scanning one of the table's indexes.
    
    
• Sorting - Many operations require the database to sort a result set. It could be that the query wants to return the data in a particular order (ORDER BY, GROUP BY). In addition, some joins require that the data be sorted during the operation's execution. You'll need to understand why sorts are performed and the impact they have on performance.
  
  
• Views - Views can really complicate the tuning process. You think you are accessing a few tables in a query and then find that you are actually joining views together that also contain join operations.
  
  
• Hints - My next blog will show you how to use hints to influence access paths and how hints can be used to educate yourself on the performance of a particular access path operation.
  
  
• Bind variables and bind peeking - In the first and second blogs of this series, I described the impact that bind peeking can have on SQL statement optimization. Bind peeking may lead to the predicted access path not matching the access path taken during execution.
  
  
• Query transformation - Oracle can rewrite a query during the optimization process. Learn how Oracle uses view merging, predicate pushing, OR expansion and subquery unnesting to attempt to improve execution performance. This will occur on a regular basis and you need to understand how query transformation works and how it affects SQL performance.
  
  
• Local predicates vs join predicates - A local predicate accesses a bind or hardcoded variable (i.e. emp_id = :empid, emp_id = 13344) while a join predicate is used to join two tables together (i.e. emp.dept_id = dept.dept_id)
  
  
• Predicate usage - You need to understand how predicate usage affects indexes and access path generation. I have provided information below that discusses predicate conditions (=, >, <, etc) as well as how predicates affect index utilization and access paths.
  
  
• Operation cardinality - The number of rows returned by a particular access path operation. Importance of operation cardinality is magnified as the number of tables accessed in the query increases. As stated previously, the sooner you can reduce the rows sent to future operations, the better your query will perform.
  
  
• Skewed data and histograms - What happens when you have an index built upon a column in a million row table that has twenty occurrences of the value "OUT OF STOCK" and the rest of the column values contain the value "IN STOCK". This is an extreme case, but the impact is that even though you may access the table looking for an "OUT OF STOCK" value, Oracle will most likely perform a table scan. You'll be searching close to a million values, while you need to retrieve only twenty of them. An index would be a much better access path, but Oracle sees that the column has such poor selectivity that it won't choose it. A histogram identifies skewed data and is able to provide the optimizer with the information it needs to make a more educated decision when choosing between a table scan and index access.
  
  
• Parallel processing and partitioning - A best practice for large data stores is to partition data into smaller subsets of data. Partitioning allows data to be broken down into these smaller subsets yet still be viewed as a single-entity by the application. Parallel processing breaks a single request for data into one or more processes that access the data in parallel and return the data to the calling application.
  
  
• Parsing - Learn the differences between hard parses vs soft parses. When the application sends a statement to the database for processing, one of the first steps in the execution process is called a parse. Oracle will check the statement's syntax, check security, generate the access path, etc.. Like most operations the less steps it needs to perform the better. You will need to understand the impact that bind variables have on the parsing process and how the hard parse/soft parse ratio affects query and database performance.

Access Path Education
You can start your education on the different access paths that are available to the optimizer by reading Oracle's Database Performance Tuning Guide that is provided in each Oracle release's documentation. Before you buy third-party books on any topic, I highly suggest that you read Oracle's documentation first. The importance of this suggestion bears repeating - READ ORACLE's DOCUMENTATION FIRST. Here's a link to the 10G Database Performance Tuning Guide.

You'll need to create an account, but its free to register and the process is painless. Virtually every topic that I described above is covered in the Database Performance Tuning Guide. It is very important that you read the guide that pertains to the release that you are working with. Each release contains enhancements to the optimizer as well as new features that affect the optimization process.

Oracle Classroom Education
OK, since I'm an ex-Oracle Instructor, you could have predicted that my next recommendation was to sign-up for an Oracle class on SQL performance tuning. Here's a previous blog that provides you with a few hints and tips to obtain the most from your classroom experience.

Oracle Education offers a class that focuses on SQL tuning. Here's an excerpt from the class description provided on the Oracle Education Website:

What you will learn:
--------------------------------------------------------------------------------
This course is designed to give the experienced SQL Developer or DBA a firm foundation in SQL tuning techniques. The participant learns the necessary knowledge and skills to effectively tune SQL in the Oracle Database 10g. They learn about tuning methodology as well proactive tuning and reactive tuning methods. Students are introduced to the benefits of the new automatic tuning mechanisms available in Oracle Database 10g. On completion of the course they are able to compare and contrast the steps involved to tune manually as in prior releases as well as use the automatic SQL tuning features provided in the current release. Students gain a thorough conceptual understanding of the Oracle Optimizer, and reinforce instructor-led learning with structured hands-on practices. The course uses a series of challenge-level workshops, allowing students to "play, discover, and learn" at their own level and pace. The students learn to use the Oracle diagnostic tools and facilities: Automatic SQL Tuning components, EXPLAIN, SQL Trace and TKPROF, SQL*Plus AUTOTRACE. Students also learn to influence the behavior of the Optimizer by changing the physical schema and modifying SQL statement syntax.

Metalink Notes
Oracle's premier web support service is available to all customers who have current support service contracts. Oracle MetaLink allows customers to log and track service requests. Metalink also allows users to search Oracle's support and bug databases. The website contains a patch and patchset download area, product availability, product life-cycle information and technical libraries containing whitepapers and informational documents. A few of the white papers and notes that pertain to SQL tuning, optimization and access paths are provided below (do a search using the note number on the main page in Metalink to retrieve the note):

• 199083.1 - Query Tuning Overview - Lots of good links to other articles.
• 398838.1 Frequently Asked Questions. Very good discussion on the optimizer. Discusses queries not using indexes.
• 248971.1 - Query tuning best practices. LOTS of links to other notes. Links discuss a wide range of topics. From parameters that affect optimization to system statistics impact on optimization.
• 35934.1 - Common Issues and Misconceptions about the cost based optimizer.
• 68735.1 Diagnostics for Query tuning.
• 67522.1 - Diagnosing why a query doesn't use an index. Very helpful.
• 207434.1 - Tuning Queries - Quick and Dirty Solution.
• 372431.1 - Troubleshooting a new queries.
• 154354.1 - Using the FIRST_ROWS for fast query response.
• 100229.1 - Measuring Index Selectivity.
• 41954.1 - Hash join operations - a little dated but still valid.

Third Party Books
If you want to learn how the optimizer works, I highly suggest that you read Jonathan Lewis's book titled Cost-Based Oracle Fundamentals. It is one of the most educational and informative books I have ever read on the cost-based optimizer. Here's my review of the book for DBAZine. If I could only recommend one book on the Oracle optimizer, Jonathan's book would be it. Jonathan also maintains a blog that focuses on a wide range of topics but does include a lot of discussions on Oracle tuning.

Tom Kyte's Ask Tom Website also provides a lot of information on SQL tuning, access paths and proper coding techniques. One of Tom's trademarks is to use a snippet of code to reinforce the information he is conveying. I'm a big fan of using examples and a big fan of Tom's website.

Articles and Presentations
Article by Don Burleson on cost based optimization. Discusses parameters that affect the cost based optimizer. Statistics, helpful hints and tips.

Advanced CBO Article and Presentation. Advanced discussion on how statistics influence the optimizer, why the optimizer doesn't always take the correct access path. Written By Wolfgang Breitling.

Article on the Oracle 10G Costing model changes and how they influence access paths. Listing of parameters that affect optimization. Author is Kimberly Floss.

Oracle Statistics Article - System statistics affects on the optimizer. Very good article that provides lots of details. Written by Jonathan Lewis

Article on why Oracle won't use an index (when you think it should). Excellent article written by Jonathan Lewis.

Test, Test, Test
Experience pays. You need to spend time "in the seat" learning how to tune. Read the above information and find a database that you can use as a test environment. You need to work with tables that have small numbers of rows and queries that return small result sets. You also need to run queries that access tables with high numbers of rows and return large result sets.

I'll show you how to use hints to change access paths in then next blog. Change the access path with a hint and run the query. Influence the optimizer to take a different access path and join order and keep a tuning record of the changes and runtimes.

Don't' fall into the trap of favoring one access path or join method for all situations. I once overheard a conversation between a developer that just moved from an online transaction system to the data warehouse team and the data warehouse DBA. The developer was looking at an access path and stated "I hate hash joins." The warehouse DBA stated "Not in this environment you won't". All access paths and join methods have a place in Oracle optimization. Its up to you to learn which ones apply for a given situation.

Thanks for Reading,

Chris Foot
Oracle Ace