Oracle SQL Efficiency

SELECT
  USERNAME
FROM
  dba_users
WHERE
  USERNAME NOT IN
  (SELECT
     GRANTEE
   FROM
     dba_role_privs);

1) Consider a common DBA query against the data dictionary to show all users who do not have privileges that are granted by means of a role. This query runs in 18 seconds.

2) To tune this query, we could re-write the same query to utilize temporary tables. This query runs in less than three seconds.

3) Another good example is between SQL that is executed with the rule-based optimizer versus the cost-based optimizer. This query uses the rule hint.

4) This query is identical to the previous query with the exception that it uses the cost-based "first-rows" hint. This is a common type of DBA query that joins a table against itself to display tables that have extended.

5) The EXPLAIN PLAN for the rule-based query above uses two index range scans with AND EQUAL test, inside a nested loop followed by a full-table scan.

6) The EXPLAIN PLAN for second query (cost-based) does an index range scan, a table access by ROWID, and a full-table scan (TABLE ACCESS FULL).

7) Because the cost-based query is invoking full-table scans "TABLE ACCESS FULL," we would expect the rule-based optimizer will out perform the cost-based optimizer for this query. By executing teh SQL IN SQL*Plus with the SET TIMING ON option, we see the dramatic difference in execution time.