RelationalDBDesignRelationalDBDesign 





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                                              2
                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.