Using Hints With The Cost-Based Optimizer

The rule-based optimizer is supposed to be obsolete with the introduction of Oracle version 8 in 1997, but its replacement—the cost-based optimizer—is available today. Unlike the rule-based optimizer, which uses heuristics to determine the access path, the cost-based optimizer uses data statistics to determine the most efficient way to service the request.

Here is a summary of the most common hints that can be added to SQL:

•  ALL_ROWS—This is the cost-based approach designed to provide the best overall throughput.

•  CLUSTER—Requests a cluster scan of the table(s).

•  FIRST_ROWS—This is the cost-based approach designed to provide the best response time.

•  FULL—Requests the bypassing of indexes, doing a full-table scan.

•  INDEX—Requests the use of the specified index. If no index is specified, Oracle will choose the best index.

•  ROWID—Requests a ROWID scan of the specified table.

•  RULE—Indicates that the rule-based optimizer has been invoked (sometimes to the absence of table statistics).

•  ORDERED—Requests that the tables should be joined in the order that they are specified. For example, if you know that a STATE table has only 50 rows, you may want to use this hint to make STATE the driving table.

•  USE_NL—Requests a nested loop operation with the specified table as the driving table.

•  USE_MERGE—Requests a sort merge operation.

Remember, the cost-based optimizer will only be as accurate as the statistics that are computed from the tables. Your DBA will need to create a periodic cron (a Unix-based job scheduling utility) job to re-estimate statistics for all tables that are volatile and change columns frequently. While a full ANALYZE TABLE xxx ESTIMATE STATISTICS will interrogate every row of the table, a faster method can be used by issuing ANALYZE TABLE ESTIMATE STATISTICS SAMPLE nn ROWS. By taking a sample of the rows within the table, the statistics generation will run much faster. Keep in mind that one of the things that the ANALYZE command reviews is the selectivity and distribution of values within an index. As such, care should be taken to sample at least 100 rows from each table.

Here is a test that created a set of three tables, dept, dept1, dept2, each with an index on deptno and set optimizer_goal as RULE in the init.ora. Listing 4.9 shows the results of the query.

Listing 4.9 The results of the query.

SELECT /*+ INDEX(dept DEPT_PRIMARY_KEY) INDEX(dept2 i_dept2)
INDEX(dept1 i_dept1)*/
dept.deptno, dept1.dname, dept2.loc
FROM dept, dept1, dept2
WHERE dept.deptno=dept1.deptno AND
dept1.deptno=dept2.deptno

Misses in library cache during parse: 1
Optimizer hint: RULE
Parsing user id: 48  (DON)

Rows     Execution Plan
------  --------------------------------------------------
      0  SELECT STATEMENT   OPTIMIZER HINT: RULE
      4    MERGE JOIN
      4      SORT (JOIN)
      4        NESTED LOOPS
      5          INDEX (RANGE SCAN) OF 'DEPT_PRIMARY_KEY' (UNIQUE)
      4          TABLE ACCESS (BY ROWID) OF 'DEPT1'
      8            INDEX (RANGE SCAN) OF 'I_DEPT1' (NON-UNIQUE)
      4      SORT (JOIN)
      4        TABLE ACCESS (BY ROWID) OF 'DEPT2'
      5          INDEX (RANGE SCAN) OF 'I_DEPT2' (NON-UNIQUE)

Listing 4.10 shows what we receive without any hints.

Listing 4.10 The query results for no hints.

SELECT dept.deptno, dept1.dname, dept2.loc
FROM dept, dept1, dept2
WHERE dept.deptno=dept1.deptno AND
dept1.deptno=dept2.deptno

Misses in library cache during parse: 1
Optimizer hint: RULE
Parsing user id: 48  (JACK)

Rows     Execution Plan
------  --------------------------------------------------
      0  SELECT STATEMENT   OPTIMIZER HINT: RULE
      4    NESTED LOOPS
      4      NESTED LOOPS
      4        TABLE ACCESS (FULL) OF 'DEPT2'
      4        TABLE ACCESS (BY ROWID) OF 'DEPT1'
      8          INDEX (RANGE SCAN) OF 'I_DEPT1' (NON-UNIQUE)
      4      INDEX (UNIQUE SCAN) OF 'DEPT_PRIMARY_KEY' (UNIQUE)

If we add a hint for the dept2 index, the full-table scan would be on dept1, and so on.

Tuning PL/SQL

PL/SQL is the acronym for Procedure Language/SQL, the standard procedural language for online Oracle applications. PL/SQL is commonly used within Oracle’s SQL*Forms application framework, but the popularity of PL/SQL for non-SQL*Forms applications has re-emerged because of the benefits of using Oracle stored procedures, which must be written with PL/SQL. PL/SQL offers the standard language constricts, including looping, IF statement structures, assignment statements, and error handling.

There are several problems with PL/SQL, each of which warrant special attention.

PL/SQL offers two types of SQL cursors, the “explicit” cursor and the “implicit” cursor. Explicit cursors are manually declared in the PL/SQL:

DECLARE
CURSOR C1 IS
SELECT last_name
FROM CUSTOMER
WHERE
cust_id = 1234;

However, it is possible to issue the SQL statement directly in PL/SQL without specifying the cursor name. When this happens, Oracle opens an implicit cursor to handle the request. Implicit cursors create a tremendous burden for Oracle, as the implicit cursor must always reissue a fetch command to be sure that only a single row was returned by the query. This will double the amount of fetch statements for the query. The moral is simple: Always declare all cursors in your PL/SQL.

PL/SQL allows certain types of correlated subqueries to run much faster than a traditional Oracle SQL query. Consider a situation where a bank maintains a general ledger table and a transaction table. At the end of the banking day, the check transaction table is applied to the GENERAL_LEDGER table, making the requisite deductions from the account_balance column. Let’s assume that the GENERAL_LEDGER table contains 100,000 rows and 5,000 daily checks need to be processed.

A traditional SQL query (shown in Listing 4.11) to accomplish the updating of account_balance would involve a correlated subquery.

Listing 4.11 Using a traditional SQL query.

UPDATE GENERAL_LEDGER
SET account_balance = account_balance -
   (SELECT check_amount FROM TRANSACTION
     WHERE
     TRANSACTION.account_number = GENERAL_LEDGER.account_number)
WHERE
EXISTS
(SELECT 'x' FROM TRANSACTION
 WHERE
 TRANSACTION.account_number = GENERAL_LEDGER.account_number);