It is not uncommon for a complex SQL query to have dozens, or even hundreds of entries in the PLAN table.
For these complex queries, there are a host of tools that are offered to assist in interpreting the output.
The most popular tools include:
Oracle SQL Analyzer – By Oracle
Q Diagnostic Center – By Savant Software
SQLab – by Quest Software
To see how these tools work, let us examine a common DBA query to select all users that do not have privileges that are granted via a role (e.g., a direct GRANT to a table). Here we see a non-correlated subquery:
SELECT
USERNAME
FROM
dba_users
WHERE
USERNAME NOT IN
(SELECT
GRANTEE
FROM
dba_role_privs
)
;
As illustrated in the Slideshow in Lesson 7, the main part of the select statement simply selects the username from dba_users. The most informative part, however, is in the WHERE clause.
Here we select username where there is not a matching row in the dba_role_privs table.
View the code below to see the complex EXPLAIN PLAN.
OPERATION
----------------------------- -----------------------------------
OPTIONS OBJECT_NAME POSITION
----------------------------- ----------------------- ----------
SELECT STATEMENT
FILTER
MERGE JOIN
SORT JOIN 1
NESTED LOOPS 1
NESTED LOOPS 1
NESTED LOOPS 1
MERGE JOIN 1
SORT JOIN 1
NESTED LOOPS 1
TABLE ACCESS FULL USER_ASTATUS_MAP 1
TABLE ACCESS BY INDEX ROWID PROFILE$ 2
INDEX RANGE SCAN I_PROFILE 1
SORT TABLE ACCESS FULL USER$ 1
TABLE ACCESS CLUSTER TS$ 2
INDEX UNIQUE SCAN I_TS# 1
TABLE ACCESS CLUSTER TS$ 2
INDEX UNIQUE SCAN I_TS# 1
TABLE ACCESS BY INDEX ROWID PROFILE$ 2
INDEX RANGE SCAN I_PROFILE 1
SORT JOIN 2
TABLE ACCESS FULL PROFNAME$ 1
VIEW DBA_ROLE_PRIVS 2
SORT GROUP BY 1
NESTED LOOPS 1
NESTED LOOPS OUTER 1
NESTED LOOPS 1
TABLE ACCESS FULL USER$ 1
TABLE ACCESS BY INDEX ROWID SYSAUTH$ 2
INDEX RANGE SCAN I_SYSAUTH1 1
INDEX UNIQUE SCAN I_DEFROLE1 2
TABLE ACCESS CLUSTER USER$ 2
INDEX UNIQUE SCAN I_USER# 1
34 rows selected.
We can simplify this output to make it more understandable by using the Oracle SQL Analyzer tool.
We simply call-up the query and explain it.
SELECT username
FROM dba_users
WHERE NOT username IN
(SELECT grantee FROM dba_role_privs)
By pressing "Compact View", we see a condensed display of the explain plan for this query.
In this case, we see that the query consists primarily of a nested loop join between two views.
The next lesson will conclude this module discussing the explain-plan utility.