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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

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

 

 


The above text is an excerpt from:

Oracle SQL Tuning & CBO Internals
ISBN 0-9745993-3-6

by Kimberly Floss


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.