How To Discard Hints

Many times when you try testcase of execution plans for a sql statement you can need ignoring the hints used in the statements. Here I propose a way of turn off all hints, at level session, for perform the testcases. We goint to use the _optimizer_ignore_hints parameter, of course, it is a hidden parameter, please be carefull.
i) Create a table for the testcase with an index;

TVCASE> create table unodostres as select * from dba_objects;

Tabla creada.

Transcurrido: 00:00:00.69
TVCASE> create index myindex on unodostres (object_id);

Índice creado.

ii) Execute the next query to discover the natural plan, in this case it use the index, it’s correct;

Transcurrido: 00:00:00.23
TVCASE> explain plan for
  2  select * from unodostres where object_id = 5678;

Explicado.

Transcurrido: 00:00:00.07
TVCASE> sta ver_plan

PLAN_TABLE_OUTPUT
___________________________________________________________________________________________________________________
Plan hash value: 3410081772

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| UNODOSTRES |
|*  2 |   INDEX RANGE SCAN          | MYINDEX    |
--------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=5678)

iii) we force the full scan using a hint;

TVCASE> explain plan for
  2  select /*+ full (unodostres)*/ * from unodostres where object_id = 5678;

Explicado.

Transcurrido: 00:00:00.03
TVCASE> sta ver_plan

PLAN_TABLE_OUTPUT
___________________________________________________________________________________________________________________
Plan hash value: 3083713855

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   762 |   154K|   207   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| UNODOSTRES |   762 |   154K|   207   (1)| 00:00:03 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=5678)

iv) now use the _optimizer_ignore_hints a true and repeat the las query;

TVCASE> alter session set "_optimizer_ignore_hints" = true;

Sesión modificada.

Transcurrido: 00:00:00.04
TVCASE> explain plan for
  2  select /*+ full (unodostres)*/ * from unodostres where object_id = 5678;

Explicado.

Transcurrido: 00:00:00.03
TVCASE> sta ver_plan

PLAN_TABLE_OUTPUT
___________________________________________________________________________________________________________________
Plan hash value: 3410081772

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| UNODOSTRES |
|*  2 |   INDEX RANGE SCAN          | MYINDEX    |
--------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=5678)

Hint full is ignored.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s