Access Path Scientific Analysis Part IV – Optimizer and Access Path Educational Resources

A few recommendations from your friendly ex-Oracle instructor on resources that will help you learn more about Oracle access paths. These resources will benefit beginners and tuning gurus alike.  We don’t need to be access path scientists just yet.  What we are attempting to do is learn the fundamentals of access paths to assist us in our upcoming access path analysis efforts.   We’ll have plenty of opportunities to learn as we go.

Introduction
This blog is not about the SQL tuning process. Its intent is also to not teach you to tune, its 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; it’s 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.   Many DBAs never have the luxury of spending some quality time with the Oracle optimize.   If your shop is like any of the shops I have worked in, your days are spent performing dozens upon dozens of administrative functions that range the spectrum.   From RAC database installs to working with application developers on naming conventions – our workloads seem to increase daily, weekly, monthly….  After spending 12 hours ensuring you have your TODO list done, you don’t have much time to do anything else.

One of my upcoming blogs will be titled “The DBA of the Future”.  A future that is already being shown to us by Oracle.   The feature/functionality of the Oracle database environment will continue to grow.   DBAs will no longer be able to be experts in “silos of one or more Oracle features”.   We will be forced to use the intelligent toolsets that Oracle is creating for us.   Gone will be the days that DBAs will be able to spend dedicated hours gaining a deep knowledge of the Oracle system.   DBAs will need to know a lot about a lot of facets of Oracle technology.   Think about Oracle’s expanding toolsets and technologies: RAC, DataGuard, Fine-Grained Auditing, advanced security options, Streams, compression, partitioning/parallelism, Oracle Fusion….   This list can go on for paragraphs.    How can anyone expect the DBA of the future to be an expert in all of these features?    The answer is – they can’t.    But this is better left for another blog.

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 areas 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 a single feature 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).   We will cover the 11G tools in upcoming blogs.

The Importance of Understanding Oracle Access Paths
That being said, all DBAs must make an attempt to 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.  You’ll find this information on many Oracle discussion websites.   Most begin with “can you believe this happened?”

• Oracle parameters that affect the optimizer. There is 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 – Current 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. 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. 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.  I saw 21 subselects embedded in a single SQL statement (along with 1/2 dozen in-line views).   Note to that developer – “just because you can do that doesn’t mean you should.   Doing all the work for an entire application in one SQL statement does NOT showcase your coding skills.”

• Indexes and selectivity
  • B-tree indexes are good for column(s) that have many unique values (high cardinality)
  • Bitmap indexes are used for column(s) that do not have many unique values (low cardinality)
  • 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 table scan on an index.
  • Fast full scan – Oracle uses multi-block reads to retrieve the index blocks.

• 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 - One of my previous blogs showed 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 my next series, I will describe 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 selectivity- The number of rows returned by a particular access path operation. Importance of operation selectivity 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 fewer 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.  Here’s another link to the 11G Database Performance Tuning Guide.   You need to start your education by READING THE DOC!   Trust me on this one.   You have to trust the vendor and it does provide you with a strong foundation upon which to build your education upon.    Third-party information and experience will carry you the rest of the way.

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 11G Database and SQL tuning. Here’s the class description provided by the Oracle Education Website.   Oracle also provides a class that specifically focuses on SQL Tuning. If your shop doesn’t have a travel budget for training, there is also a CDROM version you can purchase.

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):

• 232443.1 – How to identify resource consuming SQL for tuning
• 271196.1 – Automatic SQL Tuning – SQL Profiles
• 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.
• 163563.1 – Troubleshooting – Advanced query tuning.
• 372431.1 – Troubleshooting – Tuning a new query
• 207434.1 – Tuning Queries – Quick and Dirty Solutions.
• 100229.1 – Measuring Index Selectivity.  Older note but still pertinent.
• 41954.1 – Hash join operations – Describes one of the more complex join mechanisms.

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.  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
I have learned a lot from this series of articles and whitepapers written By Wolfgang Breitling. Here are a dozen or so performance and availability articles recommended by Oracle. If presentations are more appealing to you, here’s a presentations from various Oracle Open World conferences.

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.

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 assist 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. It’s up to you to learn which ones apply for a given situation.

Thanks for Reading,

Chris Foot
Oracle Ace
Director Of Service Delivery