Hola de nuevo. Hoy queremos compartir con vosotros una forma de generar un SQL profile en Oracle para forzar a Oracle a que utilice un plan sobre una query.
Cuando este nuevo plan no existe en memoria es complicado hacerlo con scripts, por lo que os vamos a mostrar una forma de generar directamente el profile con el plan que nosotros necesitemos.
Para ver los pasos hemos generado una tabla llamada test1 con dos índices muy similares, index1 y index2.
La query que utilizamos para la prueba es:
select * from test1 where owner='USUARIO1';
Ambos índices tienen la columna owner en su definición, por lo que la query puede utilizar ambos sin problema. Al revisar el plan de la query vemos:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1938532570
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15420 | 1686K| 1045 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 15420 | 1686K| 1045 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX2 | 46261 | | 107 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
2 - access("OWNER"='USUARIO1')
En este caso está utilizando el índice index2. Si por cualquier motivo queremos que se utilice otro lo que podemos hacer es forzar un plan nuevo y fijarlo para que se utilice siempre con la query.
¿Cómo podemos hacer?, la solución es utilizar el paquete dbms_sqltune de Oracle. Lo primero que tenemos que hacer es conseguir el plan que queremos obtener, en este caso, como queremos utilizar index1 lo que hacemos es forzar el plan con un hint:
select /*+ index(test1 index1) */ * from test1 where owner='USUARIO1';
Al mirar el plan de la query vemos que se está utilizando:
select /*+ index(test1 index1) */ * from test1 where owner='USUARIO1';
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1535041669
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15420 | 1686K| 2016 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 15420 | 1686K| 2016 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX1 | 46261 | | 196 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
2 - access("OWNER"='USUARIO1')
¿Qué necesitamos para generar el SQL Profile?
Una vez que tenemos el plan que buscamos, lo que tenemos que hacer es fijarlo para la primera query. Para ello, lo primero es obtener las indicaciones del optimizador para ese comportamiento. Para ver estas opciones, hay que ejecutar la llamada a dbms_xplan.display con la opción ADVANCED:
SQL> select * from table(dbms_xplan.display(format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1535041669
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15420 | 1686K| 2016 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 15420 | 1686K| 2016 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX1 | 46261 | | 196 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
1 - SEL$1 / TEST1@SEL$1
2 - SEL$1 / TEST1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TEST1"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."OWNER" "TEST1"."NAME"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
2 - access("OWNER"='USUARIO1')
Column Projection Information (identified by operation id):
-------------------------------------------------------------------------------------------------
1 - "OWNER"[VARCHAR2,128], "TEST1"."NAME"[VARCHAR2,128],
"TEST1"."TYPE"[VARCHAR2,12], "TEST1"."LINE"[NUMBER,22],
"TEST1"."TEXT"[VARCHAR2,4000], "TEST1"."ORIGIN_CON_ID"[NUMBER,22]
2 - "TEST1".ROWID[ROWID,10], "OWNER"[VARCHAR2,128], "TEST1"."NAME"[VARCHAR2,128]
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
-------------------------------------------------------------------------------------------------
1 - SEL$1 / TEST1@SEL$1
- index(test1 index1)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[TEST1]]></t><s><![CDATA[SEL$1
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
]]></s></h></f></q>
Con esto conseguimos la información que necesitamos para el sql profile, en este caso:
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TEST1"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."OWNER" "TEST1"."NAME"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Con la información ya podemos generar nuestro propio SQL profile de Oracle:
DECLARE
sql_text clob;
BEGIN
sql_text:=q'^select * from test1 where owner='USUARIO1'^';
dbms_sqltune.import_sql_profile(
sql_text => sql_text,
name=>'SQL_PROFILE_TEST1',
profile => sqlprof_attr(
q'^BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TEST1"@"SEL$1")^',
q'^INDEX_RS_ASC(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."OWNER" "TEST1"."NAME"))^',
q'^OUTLINE_LEAF(@"SEL$1")^',
q'^ALL_ROWS^',
q'^DB_VERSION('19.1.0')^',
q'^OPTIMIZER_FEATURES_ENABLE('19.1.0')^',
q'^IGNORE_OPTIM_EMBEDDED_HINTS^'
),
force_match=>true);
end;
/
En la variable sql_text tenemos que poner la query original, no la que hemos utilizado para forzar el índice. De esta forma asociará la query al plan que necesitamos. También le indicamos el nombre del profile que vamos a generar, en este caso SQL_PROFILE_TEST1. Por último, tenemos que poner las distintas secciones que hemos sacado anteriormente con el formato indicado, respetando el orden y las cadenas de entrecomillado. Si ejecutamos el código correctamente tendremos un sql profile para la query con el comportamiento que queremos que tenga.
Comprobamos que ha funcionado como se espera
En este caso, si volvemos a ejecutar el explain de la query, ya vemos:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1535041669
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15420 | 1686K| 2016 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 15420 | 1686K| 2016 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX1 | 46261 | | 196 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
2 - access("OWNER"='USUARIO1')
Note
-----
- SQL profile "SQL_PROFILE_TEST1" used for this statement
También hay que tener claro que la query tiene que ser exacta, si la modificamos, el optimizador ya no utilizará el sql profile y volverá a utilizar el plan que el motor considere más óptimo:
explain plan for select * from test1 t where owner='USUARIO1';
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1938532570
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15420 | 1686K| 1045 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST1 | 15420 | 1686K| 1045 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX2 | 46261 | | 107 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
2 - access("OWNER"='USUARIO1')
Esperamos que la entrada os sea de utilidad y que si os tenéis que “pegar” con el optimizador de Oracle os sea más sencillo con estos pasos. En caso de que no tengas los conocimientos necesarios, o no tengas un DBA en la empresa. Confía en nuestros profesionales de Oracle para analizar tu entorno y encargarse del mantenimiento de tu entorno Oracle, así como de otras base de datos como SQL Server.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn