HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle SQL Hints --- Outline Data Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 04:12:37

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

 

Outline Data Hints

QB_NAME

Usage: QB_NAME(<Valid String>)

Description: Defines a name for a query block, which could be used in other hints as a part of global hint format.

HELLODBA.COM>exec sql_explain('select /*+full(@INV U@INV)*/* from t_tables t where exists (select /*+qb_name(inv)*/1 from t_users u where user_id = :A)', 'BASIC OUTLINE');

 

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      … …

      OUTLINE(@"SEL$1")

      OUTLINE(@"INV")

      OUTLINE_LEAF(@"SEL$1")

      … …

      END_OUTLINE_DATA

  */

DB_VERSION

Usage: DB_VERSION(<数据库版本>)

Description: It might be a passive hint which indicates the database version of the generated execution plan. It can help to troubleshooting SQL performance issues after RDBMS upgraded. It can be observed in outline data of any execution plan.

IGNORE_OPTIM_EMBEDDED_HINTS

Usage: IGNORE_OPTIM_EMBEDDED_HINTS 

Description: Instructs the optimizer to ignore the embedded hints. This hint normally be used inSQL Profiler, Stored Outline and other auxiliary data.

HELLODBA.COM>exec sql_explain('SELECT /*+ FULL(O) IGNORE_OPTIM_EMBEDDED_HINTS */ * FROM t_objects o where object_id<:A', 'TYPICAL OUTLINE');

 

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

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |              |  2379 |   283K|    10   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_OBJECTS    |  2379 |   283K|    10   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_OBJECTS_PK |   428 |       |     2   (0)| 00:00:01 |

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

IGNORE_WHERE_CLAUSE

Usage: IGNORE_WHERE_CLAUSE

Description: Instructs the optimizer to ignore the hints following this hint.

HELLODBA.COM>exec sql_explain('SELECT /*+ full(O) IGNORE_WHERE_CLAUSE full(u)*/ COUNT(*) from t_objects O, t_users u where o.object_id=:A and u.user_id=:B and o.owner=u.username', 'BASIC');

 

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

| Id  | Operation                     | Name       |

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

|   0 | SELECT STATEMENT              |            |

|   1 |  SORT AGGREGATE               |            |

|   2 |   NESTED LOOPS                |            |

|   3 |    TABLE ACCESS BY INDEX ROWID| T_USERS    |

|   4 |     INDEX UNIQUE SCAN         | T_USERS_PK |

|   5 |    TABLE ACCESS FULL          | T_OBJECTS  |

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

NO_ACCESS

Usage: NO_ACCESS([<@block>]<View>)

Description: Indicates that the optimizer did not access to the specified view when analyzing the query block, which means it did not adopt any related optimizing method on the view.

HELLODBA.COM>exec sql_explain('SELECT /*+ qb_name(M) no_merge(v) */* FROM t_tables t, v_objects_sys v WHERE t.owner =v.owner and t.table_name = v.object_name AND v.status = :A', 'TYPICAL OUTLINE');

 

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

| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |               | 15643 |  5560K|   325   (3)| 00:00:04 |

|*  1 |  HASH JOIN          |               | 15643 |  5560K|   325   (3)| 00:00:04 |

|   2 |   TABLE ACCESS FULL | T_TABLES      |  2696 |   634K|    28   (4)| 00:00:01 |

|   3 |   VIEW              | V_OBJECTS_SYS | 15643 |  1878K|   296   (3)| 00:00:03 |

|*  4 |    TABLE ACCESS FULL| T_OBJECTS     | 15643 |   855K|   296   (3)| 00:00:03 |

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

 

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      … …

      NO_ACCESS(@"M" "V"@"M")

      FULL(@"M" "T"@"M")

      OUTLINE(@"M")

      OUTLINE_LEAF(@"M")

      … …

      END_OUTLINE_DATA

  */

OPTIMIZER_FEATURES_ENABLE

Usage: OPTIMIZER_FEATURES_ENABLE(<版本号>)

Description: Instructs the optimizer to enable the compatible features of specified version only.

HELLODBA.COM>alter session set OPTIMIZER_FEATURES_ENABLE='10.2.0.4';

 

Session altered.

 

HELLODBA.COM>exec sql_explain('SELECT /*+OPTIMIZER_FEATURES_ENABLE(DEFAULT)*/ * from t_users','BASIC OUTLINE', FALSE);

… …

 

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      … …

      END_OUTLINE_DATA

  */

OPT_PARAM

Usage: OPT_PARAM(<Optimizing Parameter> Value)

Description: Sets the optimizing parameters

HELLODBA.COM>exec sql_explain('SELECT /*+QB_NAME(M) OPT_PARAM(''optimizer_index_cost_adj'' 60)*/ * from t_users u where user_id<:A','BASIC OUTLINE');

… …

 

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      … …

      OPT_PARAM('optimizer_index_cost_adj' 60)

      … …

      END_OUTLINE_DATA

  */

OUTLINE

Usage: OUTLINE([<@Block>])

Description: Builds an outline for the specified query block.

Refer to the demo in other hints

OUTLINE_LEAF

Usage: OUTLINE_LEAF([<@Block>])

Description: Builds an outline leaf for the specified query block. The outline leaf cannot be transformed.

Demo (Please note that the view cannot be merged due to the built outline leaf):

HELLODBA.COM>exec sql_explain('SELECT /*+ qb_name(M) OUTLINE_LEAF(@M) */* FROM t_tables t, v_objects_sys v WHERE t.owner =v.owner and t.table_name = v.object_name AND v.status = :A', 'TYPICAL OUTLINE');

 

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

| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |               | 15643 |  5560K|   325   (3)| 00:00:04 |

|*  1 |  HASH JOIN          |               | 15643 |  5560K|   325   (3)| 00:00:04 |

|   2 |   TABLE ACCESS FULL | T_TABLES      |  2696 |   634K|    28   (4)| 00:00:01 |

|   3 |   VIEW              | V_OBJECTS_SYS | 15643 |  1878K|   296   (3)| 00:00:03 |

|*  4 |    TABLE ACCESS FULL| T_OBJECTS     | 15643 |   855K|   296   (3)| 00:00:03 |

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

RBO_OUTLINE

Usage: RBO_OUTLINE

Description: Instructs the optimizer to construct the outline data based on RBO.

Refer to demo in RULE hint

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat