« Comenzando a estudiar Oracle Database 12cExámen Beta 1z1-028 : Oracle Database Cloud Administration »

Hint OPT_PARAM



Leyendo algunas notas me encontré con un parámetro muy útil llamado OPT_PARAM, el cual nació en Oracle10gr2, pero del cual hay sólo notas en Metalink ...







Caso habitual , te has enfrentado a veces en la necesidad de probar algún parámetro y claro, debes hacer un ALTER SESSION o derechamente hacer una modificación al init o spfile y probar los cambios..

Pues este HINT sirve para eso, comprobar el estado de un cambio de parámetros a nivel de sentencia SQL, con lo cual podemos probar online cualquier modificación, el cambio sólo sirve para aquellos parámetros que hacen una modificación al optimizador, no se puede modificar por ejemplo la ruta de los archives |-|




¿Cómo funciona? Con los siguientes ejemplos , les quedará claro el como..

1) Creamos 2 tablas de ejemplo , llamadas t y t2
create table t as select * from dba_objects;
create table t2 as select * from dba_objects;


2) Hacemos un simple join entre ambas tablas , al ver el plan de ejecución , podremos observar que hace un HASH JOIN

SQL> select t.owner
  2    from t ,
  3         t2
  4   where t.object_id = t2.object_id;

72659 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2663495741

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 63977 |  2686K|   569   (1)| 00:00:07 |
|*  1 |  HASH JOIN         |      | 63977 |  2686K|   569   (1)| 00:00:07 |
|   2 |   TABLE ACCESS FULL| T2   | 63977 |   812K|   283   (1)| 00:00:04 |
|   3 |   TABLE ACCESS FULL| T    | 69344 |  2031K|   284   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - access("T"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


3) Ahora vamos a deshabilitar el HASH JOIN , con el OPT_PARAM , ejecutamos el plan de ejecución

SQL> select /*+ opt_param('hash_join_enabled','false') */
  2         t.owner
  3    from t ,
  4         t2
  5   where t.object_id = t2.object_id;

72659 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 219814191

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 63977 |  2686K|       |  1447   (1)| 00:00:18 |
|   1 |  MERGE JOIN         |      | 63977 |  2686K|       |  1447   (1)| 00:00:18 |
|   2 |   SORT JOIN         |      | 63977 |   812K|  2520K|   592   (2)| 00:00:08 |
|   3 |    TABLE ACCESS FULL| T2   | 63977 |   812K|       |   283   (1)| 00:00:04 |
|*  4 |   SORT JOIN         |      | 69344 |  2031K|  5448K|   855   (1)| 00:00:11 |
|   5 |    TABLE ACCESS FULL| T    | 69344 |  2031K|       |   284   (1)| 00:00:04 |
------------------------------------------------------------------------------------

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

   4 - access("T"."OBJECT_ID"="T2"."OBJECT_ID")
       filter("T"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


Según la documentación de Oracle , los únicos parámetros modificables a través del OPT_PARAM son OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING y STAR_TRANSFORMATION_ENABLED, pero en realidad se pueden modificar parámetros normales y ocultos, por ejemplo _FIX_CONTROL , HASH_JOIN_ENABLED, etc


La documentación oficial del hint OPT_PARAM
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF51119


Espero les sirva

by Ligarius
22.05.13. 12:15:32. 446 words, 6541 views. Categories: Tuning / Performance ,