Analyzing query access paths

You can use an access plan graph to see the flow of data from node to node in your database network, as well as the estimated costs, statistics, and selectivity information associated with each branch of the flow.

Generating an access plan graph

When you run a tuning job on a single query for the first time, the Query Tuning tool uses a default set of EXPLAIN statements to obtain information about the access path chosen by the DSM for the selected query or workload. This information can be displayed in DSM in the form of an access plan graph.
  1. Run the tuning wizard to create a tuning job.
  2. From the Advanced options panel, choose the Generate access plan graph option.
  3. After you have run your tuning job, select Optimize > Tuning Jobs, select the tuning job from the explorer view table, and click View Results.
  4. From the results pop-up, click the Access Plan Graph tab.
    The access plan graph is displayed, offering three separate views of the database objects being queried and the operations being performed:
    • A flowchart view , read from left to right, bottom to top
    • A tree view of expandable records, read left to right, top to bottom
    • A table view, read left to right, top to bottom

Comparing access plans of queries within a workload

  1. Run a tuning job on a workload, first using one name and again using another, prior to clicking Done
  2. When the tuning job is complete, click View Results.
  3. From the tuning results pop-up, click the Index Script tab.

    A list of index recommendations generated by the Index Advisor is displayed, which includes the estimated cost and performance improvement of creating all of the recommended indexes.

  4. Click Compare Access Plan, and then return to the Query and Workload Job page.

    You will see a job of type Workload Access Plan Comparison

  5. When the job's status reads Succeeded, click View Results.
    A new Tuning Results page opens, broken into two main sections:
    • Explain Snapshots - shows a high-level summary of the performance improvement or cost
    • Statements - lists all of the query statements included in the workload
  6. Click View to compare the changes made to the access plan graph.