Acces Path Scientific Analysis Part IV

Introduction
The latest installment in a series of blogs on Oracle access paths. Before we begin,it is important for us to do a quick review of my previous blogs on SQL performance. We'll be using this information during our scientific analysis phase.

• Let's Get Technical! - Using Deductive Reasoning and Communication Skills to Identify and Solve Performance Problems
  The first blog of this series provides some helpful hints on what questions to ask during the initial stages of application performance problem analysis.
  
  
• System Triage Part II - Host Performance Analysis Using Grid Control and Host Commands
  Using operating system commands and 10G Grid Control to monitor database server performance.
  
  
• System Triage Part III - Finding the Top Resource Consumers
  We learned how to use 10G Grid Standalone to identify the top resource consumers for a given instance.
  
  
• System Triage IV - Access Path Identification Part I
  A high-level overview of Oracle query optimization. Also discussed the difference between estimated and runtime access paths.
  
  
• System Triage V - Access Path Identification Part II
  We reviewed the two data objects that contain access path raw data - plan_table and v$sql_plan. In addition, we discussed a few of the V$ dynamic performance views that provide information pertaining to SQL statements executing in our Oracle database environment.
  
  
• Access Path Identification - Part III
  A discussion on the tools we can use to display graphical and text versions of explain plan data including the DBMS_XPLAN package, UTLXPLS.SQL, UTLXPLP.SQL and Oracle 9I Scratchpad (graphical). We also learned how to create our own graphical displays of Oracle access paths.
  
  
• Access Path Identification - Part IV
  An investigation of everyone's favorite performance analysis tools - SQL*PLUS Autotrace and SQL Trace.
  
  
• Access Path Identification - Part V
  A discussion on a couple of 9I Oracle Enterprise Manager tools that we can use to identify Oracle access paths. We also learned how we can use 9I OEM's SQL Analyze and the Virtual Index Wizard to help us better understand access paths, monitor database performance and tune statements running in an Oracle 9I database environment.
  
  
• Access Paths VI - 10G Grid Control SQL Details Panels
  We turned our attention to the 10G Grid Control's access path display panel - the SQL Details Panel. It seems like no matter where you start your investigation in 10G Grid Control, sooner or later, you'll end up with a panel that contains a link to the SQL Details Panels. We took a look at a few of the more popular ways of finding our way to them.
  
  
• Access Paths VII - Access Path Education
  Focuses on the importance of Oracle access path education. The blog contains links to resources and numerous Metalink Notes that provide access path educational information.
  
  
• Access Path Scientific Analysis Part I
  An introduction to Oracle Access Path Scientific Analysis. We learn why it is important for us to become "database performance scientists" if we want to fully understand Oracle access paths and the affect they have on SQL statement performance. We also learned how to select a test system to use as well as document the parameters and database objects that play key roles in the optimization process.
  
  
• Access Path Scientific Analysis Part II
  We learn how to use hints to influence access paths for testing purposes. Using hints allows us to evaluate the affect that different access paths have on SQL statement performance. When we finally begin our access path scientific analysis, we will run the statement without any modification, review the access path and performance statistics, use a hint to change the access path, run the statement again and compare the before and after results. Contains a document that provides a demo on using hints to influence access paths. The demo document also contains a description of some of the basic access paths that Oracle can choose from.
  
  
• Access Path Scientific Analysis Part III
  An overview of the various types of indexes as well as indexing strategies that affect Oracle access path selection. Also includes a list of parameter that can influence the optimizer to favor index access.

Selecting SQL Statements to Analyze
We need to select a few SQL statements that we can use as input to our scientific analysis. Let's take a look at the various tools we can use to select the SQL statements we can use for testing.

• Using 9I Oracle Enterprise Manager - We can use the Session Details Panel to retrieve the SQL text of statements that are currently executing in the database. The Session Details Panel also provides the access path the statement is taking. The blog on 9I OEM will show you how to drill down to find the text of the SQL statement and the access path it is taking.
  
  
• 10G Grid Control - Grid Control' s Execution Plan Panel allows administrators to display the entire text of a SQL statement and the access path it is taking. This blog on 10G Grid Control will show you how to navigate through the various panels to access the Execution Plan Panel.
  
  
• SQL Trace - If we don't have access to the "SGTs" (Sissy GUI Tools), all is not lost. We can also use the SQL Trace utility to capture the text of the SQL statement as well as its access path. To use this method, we would contact one of our friendly application developers and ask them to run a series of batch jobs or online transactions in the environment we have selected as our test bed.
  
  We can then activate SQL Trace, contact the developer to run the selected workload, deactivate the trace and format the trace's output into an easily readable format. SQL Trace provides the benefit of allowing us to capture multiple SQL statement access paths in a single execution. The blog on Autotrace and SQL Trace provides instructions on how to activate the SQL Trace utility.

What Statements Should I Choose?
We are looking for statements that utilize the different Oracle access paths. Try and find statements that use:

• Table scan and index 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.
    
    
  • Full table scan - Oracle reads all rows from the table. 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.
    
    
  • 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.

We don't have to find each and every access path I outlined above. In addition, each statement that does a join will also contain table scan and/or index access paths. Try to find statements that access a single table and statements that join two, three and four tables together. It should be relatively easy to find the nested loop and hash join access paths. Sort merge and outer joins may be a little harder to find. Try and stay away from SQL statements that use parallel processing, are 13 pages long or join 5 or more tables together. We want to start with the basics. We also don't want to use statements that have exorbitantly long execution times. We'll be executing them on a fairly regular basis during testing and waiting hours for the results will lengthen the testing process.

Next Up
We devise our scientific analysis testing process.

Almost Forgot
How are the Daylight Savings Time changes treating you? I may break in one last time to discuss their impact on our organization.

Thanks for Reading,

Chris Foot
Oracle Ace