RelationalDBDesign RelationalDBDesign 


EXPLAIN PLAN  «Prev  Next»
Lesson 9Complex EXPLAIN PLAN outputs
Objective Analyze a complex EXPLAIN PLAN.

Complex EXPLAIN PLAN Outputs

Tools to analyze complicated outputs

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:
  1. Oracle SQL Analyzer – By Oracle
  2. Q Diagnostic Center – By Savant Software
  3. 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.
Explain Plan 1 
SELECT username
 FROM dba_users
 WHERE NOT username IN 
 (SELECT grantee FROM dba_role_privs)

Explain Plan 1
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.