Home » RDBMS Server » Performance Tuning » The differential between Index hint and no hint (Oracle 10gr2, RHEL5)
The differential between Index hint and no hint [message #634013] Mon, 02 March 2015 20:46 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Good morning,

Today, I have got a case make me wonder. The SQL cause I/O scan, when I break them in lines, I saw something especial, some sub-query chosen by index hint.

sql> select /*+ index(um IDX_pos_product_cat_um2)*/ pc.id
                                        , nvl(um.base_um_product_cat_id, pc.id) bpc_id
                                        , nvl(um.base_um_rate, 1) br
                                from CLOUD_369.pos_product_cat  pc
                                    ,CLOUD_369.pos_product_cat_um um
                                where pc.id=um.link_um_product_cat_id(+) 


See at the sql statistics
Execution Plan
----------------------------------------------------------
Plan hash value: 1316796469

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         | 27990 |   519K|  2264   (1)| 00:00:28 |
|*  1 |  HASH JOIN OUTER             |                         | 27990 |   519K|  2264   (1)| 00:00:28 |
|   2 |   INDEX FAST FULL SCAN       | PK_POS_PRODUCT_CAT      | 27990 |   136K|    16   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| POS_PRODUCT_CAT_UM      | 27416 |   374K|  2246   (1)| 00:00:27 |
|   4 |    INDEX FULL SCAN           | IDX_POS_PRODUCT_CAT_UM2 | 27416 |       |    63   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

   1 - access("PC"."ID"="UM"."LINK_UM_PRODUCT_CAT_ID"(+))



and when I remove the hint, I saw
sql> select  pc.id
  2                                          , nvl(um.base_um_product_cat_id, pc.id) bpc_id
  3                                          , nvl(um.base_um_rate, 1) br
  4                                  from CLOUD_369.pos_product_cat  pc
  5                                      ,CLOUD_369.pos_product_cat_um um
  6                                  where pc.id=um.link_um_product_cat_id(+)
  7  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3010009056

--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    | 27990 |   519K|    74   (5)| 00:00:01 |
|*  1 |  HASH JOIN OUTER      |                    | 27990 |   519K|    74   (5)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| PK_POS_PRODUCT_CAT | 27990 |   136K|    16   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | POS_PRODUCT_CAT_UM | 27416 |   374K|    56   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - access("PC"."ID"="UM"."LINK_UM_PRODUCT_CAT_ID"(+))


2 differents of COST, one with hint cause 2264 and the other by 74. The execution time was 0.28s vs 0.1s.

May you explain to me?
Thank you!
Re: The differential between Index hint and no hint [message #634014 is a reply to message #634013] Mon, 02 March 2015 20:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>May you explain to me?
CBO is/was smarter than the person who added the hint.
Re: The differential between Index hint and no hint [message #634015 is a reply to message #634014] Mon, 02 March 2015 20:56 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, BlackSwan, by quick reply.
Re: The differential between Index hint and no hint [message #634026 is a reply to message #634013] Tue, 03 March 2015 01:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
In your first plan, you have an INDEX FULL SCAN followed by (as you work up the plan) TABLE ACCESS BY INDEX ROWID of POS_PRODUCT_CAT_UM. This is an iterative process: for every key retrieved by the index scan, Oracle must find the matching row. It has to do this 27416 times. In the second plan, you simply scan the whole table in one operation. Since you want every row POS_PRODUCT_CAT_UM, the table scan is far more efficient than flying all over the place with thousands of index lookups.
Re: The differential between Index hint and no hint [message #634027 is a reply to message #634026] Tue, 03 March 2015 01:51 Go to previous message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, John Watson, your reply is very cleanly
Previous Topic: v$service_stats information
Next Topic: Database today's load vs yesterday
Goto Forum:
  


Current Time: Fri Mar 29 10:18:25 CDT 2024