Friday, November 25, 2011

OPT_PARAM Hint in Oracle

OPT_PARAM Hint in Oracle 10g and 11g
A new hint OPT_PARAM(parameter value) is available in 10g and 11g to change the execution plan if you want to.   
My demo is:
SQL> set autotrace trace expla
SQL>
SQL> select /*+ opt_param('_hash_join_enabled' 'false') */
2         t1.* , t2.*
3  from   t1, t2
4  where  t1.created = t2.created
5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1677085574

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   300K|    48M|       |   467   (2)| 00:00:06 |
|   1 |  MERGE JOIN         |      |   300K|    48M|       |   467   (2)| 00:00:06 |
|   2 |   SORT JOIN         |      | 10000 |   830K|  2296K|   232   (1)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| T1   | 10000 |   830K|       |    32   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      | 10000 |   830K|  2296K|   232   (1)| 00:00:03 |
|   5 |    TABLE ACCESS FULL| T2   | 10000 |   830K|       |    32   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

4 - access("T1"."CREATED"="T2"."CREATED")
   filter("T1"."CREATED"="T2"."CREATED")
SQL>
SQL> select /*+ opt_param('_hash_join_enabled' 'true') */
2         t1.* , t2.*
3  from   t1, t2
4  where  t1.created = t2.created
5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1819147781

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   300K|    48M|    68   (6)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   300K|    48M|    68   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |   830K|    32   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 |   830K|    32   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("T1"."CREATED"="T2"."CREATED")
Note that all parameters cannot be changed.
Hope this helps...
Tamil

No comments:

Post a Comment