SQL Tuning Tools   «Prev 

Oracle SQL Efficiency

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


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

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.

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.

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.

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

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.