Access Path Identification - Part III

In my last blog we learned about the two data objects that contain the access path raw data - PLAN_TABLE and V$SQL_PLAN. If you don't have a firm understanding of Oracle's plan table and V$SQL objects, it may be a good time to review the information contained in that blog before continuing on.

Explain Plan VS V$SQL_PLAN
In previous blogs of this series, we learned that the output in the PLAN_TABLE is a prediction of the access path a given query will take while V$SQL_PLAN is the access path actually taken during execution. We also learned 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.

Retrieiving Data from the PLAN_TABLE
Let's take a look at a couple of tools we can use to retrieve data from our plan table. If you read my last blog, you know that Oracle supplies statements that retrieve data from the plan table and displays it in an easy-to-read format.

Here's the statement that we will be using in our example:

SQL>
SQL>
SQL> explain plan into system.plan_table for
2 select a.employee_id, a.last_name, b.department_id, b.department_name,
3 c.street_address, c.postal_code, c.city, c.state_province
4 from hr.employees a, hr.departments b, hr.locations c
5 where a.department_id=b.department_id
6 and b.location_id=c.location_id
7 and a.employee_id = 174
8 order by a.last_name;

Explained.

When we run the statement, Oracle will insert rows into our plan table that describe the statement's access path. I'm not using bind variables for simplicity sake, not because I'm afraid of an access path change during execution.

Let's use Oracle's stored procedure to retrieve the contents from our plan table and format the output:

08:54:53 orcl9i> select * from table(dbms_xplan.display);

Here is the output from the above statement's execution.

DBMS_XPLAN Package
The DBMS_XPLAN package is the "go to" procedure for displaying the contents of plan tables as well as:

• Access paths from statements (cursors) that are loaded in the cursor cache.
• Access paths from statements stored in the Automatic Workload Repository.

DBMS_XPLAN.DISPLAY
The statement "SELECT * FROM table(dbms_xplan.display);" asks Oracle to display the current contents of our default plan table. The DBMS_XPLAN.DISPLAY function accepts "TABLE_NAME", "STATEMENT_ID" and "FORMAT" as input.

• TABLE_NAME allows you to specify a non default name for the plan table you are accessing.
  
  
• STATEMENT_ID allows you to select a specific access path from a plan table that contains access paths from different statements. Please read my two earlier blogs on access paths to learn how to set the STATEMENT_ID.
  
  
• FORMAT allows a user to choose from four different output formats:
  
  • BASIC - Displays the operation ID, object name and operation option. Provides limited information for a quick review
  • TYPICAL - Default option that displays partition pruning, parallel execution information and predicates. This is the one I use the most
  • ALL - All information from TYPICAL and also includes projection information
  • SERIAL - Does not display any information on parallel execution
    

Here's a few quick examples:

SELECT * FROM table(DBMS_XPLAN.DISPLAY('save_plan_table'));
Retrieves the contents from a non-default plan table.

SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'stmt_1'));
Retrieves a statement from our default plan table that has a statement ID of "stmt_1".

DBMS_XPLAN.DISPLAY_CURSOR
Not only can we use DBMS_XPLAN to retrieve the contents of our plan table, we can also use the DISPLAY_CURSOR function to display the access path that a statement used during execution. We are able to provide the program with a few input variables that will allow us to search the cursor cache for individual SQL statements:

• SQL_ID - The statement's SQL identifier. This value can be found by accessing either V$SQL or V$SQLAREA. You can search VSQL/V$SQLAREA's SQL_TEXT column for the text of the SQL statement you are looking for and use the statement's SQL_ID as input to DBMS.XPLAN.DISPLAY_CURSOR.
  
  I've provided a few examples of how to search these two tables for a statement's text in an earlier blog. You'll need to add the SQL_ID column in the SELECT clause of your searches. The example below shows the addition of the SQL_ID column to the statement I provided as an example in my previous blog:

  SQL> select sql_id, address, executions, buffer_gets, sql_text from v$sqlarea
  where sql_text like '%select a.employee_id, a.last_name%' order by buffer_gets desc;

• CHILD_NUMBER - Child number of the child cursor. If you don't supply this value, you will retrieve all child cursors from the SQL_ID you are using as input.
  
  
• FORMAT - Allows a user to choose different display outputs (discussed earlier in this blog).

Here's an example of using DBMS_XPLAN.DISPLAY_CURSOR to retrieve access path information for all child cursors that have a statement ID of 'xuponh8k9hjyk'.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('xuponh8k9hjyk'));

The PL/SQL Packages and Types References manual provides in-depth instructions on how to use DBMS_XPLAN to retrieve and format access path information from plan tables, AWR and the cursor area. I highly suggest that you go to Oracle's Technet Website and study the information on DBMS_XPLAN. Tom Kyte's website also provides an abundance of information on this utility, how to use it and customize its output. Just hop on over and do a search on DBMS_XPLAN.

The UTLXPLS.SQL and The UTLXPLP.SQL
Oracle also provides two scripts that format plan table contents. The UTLXPLS.SQL script is used to format output for statements that perform serial processing, while UTLXPLP.SQL is used to format output for statements that perform processing in parallel.

Other Scripts
There are literally thousands of customized scripts on the web that you can use to format access path raw data. Find one that formats the output in a style that you prefer and go for it!

Graphically Displaying Access Paths
We'll use the same query we used previously for this next discussion:

SQL> explain plan into system.plan_table for
2 select a.employee_id, a.last_name, b.department_id, b.department_name,
3 c.street_address, c.postal_code, c.city, c.state_province
4 from hr.employees a, hr.departments b, hr.locations c
5 where a.department_id=b.department_id
6 and b.location_id=c.location_id
7 and a.employee_id = 174
8 order by a.last_name;

Let's review the information displayed by DBMS_XPLAN and use it to graphically display the query's access path. We'll also take a look at the output from a query I personally use when creating graphical access path displays to help. Before we begin, it will be beneficial to show you what we are trying to achieve. Let's take a look at the graphical output from 9I Oracle Enterprise Manager's Scratchpad tool. One of the benefits that Scratchpad provides is that with one quick mouse click, we can generate a graphical display of a statement's access path. We'll be reviewing this tool in an upcoming blog.

Graphically displaying a statement's access path will help us to easily understand how the access path works. The graphical display tells us that:

• We are accessing the HR.EMPLOYEES table using a unique scan on the HR.EMP_EMP_ID_PK index. The key words "(UNIQUE SCAN") in the graphical display tells us that we are searching the index for a unique value. When we find the value we are looking for (in this case an EMPLOYEE_ID that has a value of 174), we are using the row's ROWID to access the HR.EMPLOYEES table. DBAs often use the term "local predicate" to designate predicates that use a value to access a specific table.
  
  
• The HR.EMPLOYEES table is the outer table in our nested loop join. We are using the results that satisfy the local predicate (a.employee_id = 174) to access the HR.DEPARTMENTS table. We are looking for rows in the HR.DEPARTMENTS table that have DEPARTMENT_IDs that match the results returned from our local predicate on HR.EMPLOYEES. The predicate "where a.department_id=b.department_id" joins the two tables together and is classified as a "join predicate".
  
  
• We are using another unique scan on the HR.DEPT_ID_PK index, retrieving the row's ROWID from that table and using it to access the HR.DEPARTMENTS table. The HR.DEPARTMENTS table is the inner table of our nested loop join.
  
  
• Rows from HR.EMPLOYEES AND HR.DEPARTMENTS that have matching DEPARTMENT_ID values become the intermediate result set. Rows that don't match are filtered. The result set from this join is used as the outer data set in another nested loop join. The join predicate is "and b.location_id = c.location_id".
  
  
• Like our previous two table accesses, we are using a unique index called HR.LOC_ID_PK to access the HR.LOCATIONS table. HR. LOCATIONS is the inner table in this nested loop join.
  
  
• Data from the intermediate result set and the HR.LOCATIONS table that have matching LOCATION_IDs (and b.location_id=c.location_id) is passed to the next step.

Our Own Graphical Display
Take a look at this graphic. The top query is one that I often use to help me to create a graphical display of a statement's access path. The graphic below is the graphical display I created from its results. The query at the bottom is the output from DBMS_XPLAN. Let's take a look at the information provided from my query and learn how we can use it to create a graphical display of a given query's access path:

• ID - Numeric identifier that tells us the order of the step in relation to other steps. It is important to note that this is not the order in which Oracle performs the steps during the statement's execution. Generally, Oracle will perform the steps from left to right and bottom up during execution.
  
  
• PARENT_ID - Numeric identifier of the step's parent step. Used to determine where to place the step in our graphical display.
  
  
• POSITION - Numeric identifier that provides the position of the step in relation to other child steps that have a common parent step. Like PARENT_ID, it is used to determine where to place the step in our graphical display.

The graphical display helps me to more easily understand a query's access path. You can compare the output from my query to the results returned from DBMS_XPLAN and other Oracle utilities. Once you become more experienced, you will be able to create a graphical display from virtually any output format that Oracle, or you, generate.

Next Up
We'll continue our review of the various tools that we can use to generate and format access path information.

Thanks for Reading,

Chris Foot
Oracle Ace