Oracle SQL Tuning and CBO Internal Hints

Call (800) 766-1884 for Oracle support & training

Oracle Tips by Burleson

Oracle SQL Tuning and CBO Internal

Hints

Another feature requiring the cost-based optimizer is hints. There are many hints. A hint overrides any session setting and instance setting in the parameter file. All hints but one, RULE, utilize the cost-based optimizer. Hints are treated as special comments. If you make a syntax error in a hint, Oracle does not return an error message. Instead, your hint is ignored. The hint must be the first part of a SELECT, UPDATE, or DELETE statement. You can combine more than one hint per SQL statement as long as they do not conflict. The following example uses the ORDERED hint. The ORDERED hint tells the cost-based optimizer the join chain. The join chain consists of the tables in the FROM clause from the left to right as you read the FROM clause. The table to the far left in the FROM clause is the first table in the join chain. The first table in the join chain is called the DRIVING table or the OUTER table. The following example uses the ORDERED hint. By reading the output of AUTOTRACE top down, the FIRST table you see is the EMP table. The first table down from the top is the DRIVING table.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT /*+ORDERED */ ENAME, LOC

2 FROM EMP, DEPT -- EMP DRIVES

3 WHERE EMP.DEPTNO = DEPT.DEPTNO;

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=3 Card=14 Bytes=224)

1 0 HASH JOIN

(Cost=3 Card=14 Bytes=224)

2 1 TABLE ACCESS (FULL) OF 'EMP'

(Cost=1 Card=14 Bytes=98)

3 1 TABLE ACCESS (FULL) OF 'DEPT'

(Cost=1 Card=4 Bytes=36)

You can write your hints using one of two formats. The example above uses the C language construct of a comment;” /* comment */”. You can also use double dashes “– –.” However, using double dashes does not permit you to place any column names on the same line as your hint, as the following code indicates. Notice that the driving table is DEPT because it is to the far left in the FROM clause.

SQL> SELECT --+ORDERED

2 ENAME, LOC

3 FROM DEPT, EMP -- DEPT Drives

4* WHERE EMP.DEPTNO = DEPT.DEPTNO;

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=3 Card=14 Bytes=224)

1 0 HASH JOIN

(Cost=3 Card=14 Bytes=224)

2 1 TABLE ACCESS (FULL) OF 'DEPT'

(Cost=1 Card=4 Bytes=36)

3 1 TABLE ACCESS (FULL) OF 'EMP'

(Cost=1 Card=14 Bytes=98)

There are hints to invoke each optimizer: use indexes, use full table scans, invoke a join method for joining tables, the join chain, working with views, working with subqueries, parallel queries, and star queries. Oracle continues to add new hints with each release of Oracle. There are some undocumented hints as well. You can view hints that Oracle writes to itself, recursive calls, in the dynamic performance view v$sqlareaor v$sql.

SQL> SELECT SUBSTR(SQL_TEXT,1,40) CODE

2 FROM V$SQLAREA

3 WHERE SQL_TEXT LIKE '%--+%' OR

4 SQL_TEXT LIKE '%/*+%'

5* ORDER BY SQL_TEXT;

CODE

----------------------------------------

SELECT --+ORDERED ENAME, LOC FROM

SELECT /*+ORDERED */ ENAME, LOC FROM

SELECT SUBSTR(SQL_TEXT,1,40) CODE FROM

select /*+ index(idl_char$ i_idl_char1)

select /*+ index(idl_sb4$ i_idl_sb41) +*

select /*+ index(idl_ub1$ i_idl_ub11) +*

select /*+ index(idl_ub2$ i_idl_ub21) +*

select /*+ rule */ bucket_cnt, row_cnt,

This list is not exhaustive, but it does include most of the hints available.

ALL_ROWS

AND_EQUAL(STATS I1 I2 I3 I4 I5)

APPEND

CACHE(STATS)

CHOOSE

CLUSTER(STATS)

DRIVING_SITE(STATS)

FIRST_ROWS

FULL(STATS)

HASH(STATS)

HASH_AJ(STATS)

HASH_SJ(STATS)

INDEX(STATS I_STATS_REGION)

INDEX_ASC(STATS I_STATS_REGION)

INDEX_COMBINE(STATS IBM_STATS_SEX)

INDEX_COMBINE(STATS)

INDEX_DESC(STATS I_STATS_REGION)

INDEX_FFS(STATS I_STATS_REGION)

INDEX_JOIN

LEADING(STATS)

MERGE(VIEW_NAME)

MERGE_AJ

MERGE_SJ

NOAPPEND

NOCACHE(STATS)

NOPARALLEL

NOPARALLEL_INDEX(STATS,I_STATS_REGION)

NOREWRITE

NO_EXPAND

NO_FACT(STATS)

NO_INDEX(STATS I_STATS_REGION)

NO_MERGE(VIEW_NAME)

NO_PUSH_JOIN_PRED(STATS)

ORDERED

ORDERED_PREDICATES

PARALLEL(STATS,4)

PARALLEL_INDEX(STATS,I_STATS_REGION,4,2)

PQ_DISTRIBUTE(INNER_TABLE,OUT_DIST,

INNER_ DIST)

PUSH_JOIN_PRED(STATS)

PUSH_SUBQ

REWRITE

ROWID(STATS)

RULE

STAR

STAR_TRANSFORMATION

USE_CONCAT

USE_HASH(INNER_TABLE)

USE_MERGE(INNER_TABLE)

USE_MERGE(INNER_TABLE) ORDERED

FULL(STATS)

USE_NL(INNER_TABLE) ORDERED

USE_NL(STATS STATS_HIST)

USE_NL(STATS)

If you use a table alias in the SQL statement, you must use the same alias in your hint. Otherwise, Oracle ignores your hint. The table alias is not case sensitive. The following hint is ignored by Oracle because a table alias of “s” is used, but not referenced in the hint.

SQL> SELECT --+FULL(STATS)

2 *

3 FROM STATS s

4* WHERE REGION = 'NW';

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=4 Card=1 Bytes=21)

1 0 TABLE ACCESS (BY INDEX ROWID) OF

'STATS' (Cost=4 Card=1 Bytes=21)

2 1 INDEX (RANGE SCAN) OF

'I_STATS_REGION' (NONUNIQUE)

(Cost=3 Card=1)

The next example uses a table alias of uppercase “S” to verify that the table alias is not case sensitive. The EXPLAIN PLAN output from AUTOTRACE indicates that the table alias is not case sensitive.

SQL> SELECT /*+FULL(S) */ *

2 FROM STATS s

3 WHERE REGION = 'NW';

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=583 Card=1 Bytes=21)

1 0 TABLE ACCESS (FULL) OF 'STATS'

(Cost=583 Card=1 Bytes=21)