Access Path Analysis

Different techniques exist for showing the access path for a given SQL statement.

Db2

First, create a PLAN_TABLE if your database doesn’t already have one. You can find a sample Create statement in the Db2 Performance Tuning guide.

Include the SQL statement in the following and execute it via a utility like SPUFI:

DELETE FROM PLAN_TABLE WHERE QUERYNO=nnn;
EXPLAIN PLAN SET QUERYNO=nnn FOR statement;

where nnn is a number you assign to this statement.

Retrieve the plan from the PLAN_TABLE with the following Select statement:

SELECT QBLOCKNO, PLANNO, TNAME, ACCESSNAME, METHOD, ACCESSTYPE, MATCHCOLS, INDEXONLY, PREFETCH, SORTC_GROUPBY
FROM PLAN_TABLE
WHERE QUERYNO=nnn
ORDER BY QBLOCKNO, PLANNO;

The table contains other plan information; these are generally the most pertinent columns for PS/nVision queries.

Oracle

First, create a PLAN_TABLE if your database does not already have one. Here is a sample Create statement:

CREATE TABLE PLAN_TABLE(STATEMENT_ID VARCHAR2(254),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE  NUMERIC,
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMERIC,
ID NUMERIC,
PARENT_ID NUMERIC,
POSITION NUMERIC,
other long);

You can use SQL*Plus to evaluate access plans interactively. First, include the SQL statement in the following code and execute it.

DELETE FROM PLAN_TABLE WHERE QUERYNO=nnn;
EXPLAIN PLAN SET STATEMENT_ID = 'nnn' FOR
statement;

where nnn is an identifier you assign to this statement.

Retrieve the plan from the PLAN_TABLE with the following Select statement:

SELECT LPAD(' ',2*LEVEL)||OPERATION,OPTIONS,OBJECT_NAME,
  OBJECT_INSTANCE,SEARCH_COLUMNS
FROM PLAN_TABLE
WHERE STATEMENT_ID='nnn'
CONNECT BY PRIOR ID = PARENT_ID
  AND STATEMENT_ID='nnn'
START WITH ID = 1
  AND STATEMENT_ID='nnn'
ORDER BY ID;

The plan is retrieved in a hierarchical tree format, in which the steps are evaluated from inside out, and then top to bottom. The first step listed (not indented) is actually the final step in the plan, and it is preceded by the step on the following line. For example, a Join is presented first, followed by two indented lines showing the two tables joined and the indexes used to access them.