Stored Outlines

Las outlines es el conjunto de hints de una sentencia que permite mantener el plan de ejecución aunque se produzcan cambios en el sistema.

— Creamos el entorno

CREATE TABLE TAB_OUTLINE AS
SELECT level AS id,
TRUNC(DBMS_RANDOM.value(1,1000)) AS record_type,
‘Description for ‘ || level AS description
FROM dual
CONNECT BY level outline.sql
SET LONG 100
COL name FOR A30
COL SQL_TEXT FOR A100
COL CATEGORY FOR A30
SELECT NAME, CATEGORY, SQL_TEXT FROM DBA_OUTLINES WHERE CATEGORY LIKE ‘%&categoria%’;

cat >outline_hint.sql
SET LONG 100
COL name FOR A30
COL HINT FOR A100
COL CATEGORY FOR A30
SELECT NODE, STAGE, JOIN_POS, HINT FROM DBA_OUTLINE_HINTS WHERE NAME LIKE ‘%&name%’;

VARIABLE valor NUMBER;
BEGIN
:valor:=10;
END;
/

— Query de referencia

SELECT ID, DESCRIPTION FROM TAB_OUTLINE WHERE RECORD_TYPE=:valor;
/*
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | 10 | 290 | 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB_OUTLINE | 10 | 290 | 16 (0)| 00:00:01 |
———————————————————————————
*/

— Creamos un outline directamente con la query

CREATE OUTLINE out_tab_outline FOR CATEGORY mogu_outlines
ON SELECT ID, DESCRIPTION FROM MOGU.TAB_OUTLINE WHERE RECORD_TYPE=:valor;

SQL>@outline.sql
/*
NAME CATEGORY SQL_TEXT
—————————— —————————— ———————————————————————-
OUT_TAB_OUTLINE MOGU_OUTLINES SELECT ID, DESCRIPTION FROM MOGU.TAB_OUTLINE WHERE RECORD_TYPE=:valor
*/

SQL>@outline_hint.sql
/*

NAME NODE STAGE JOIN_POS HINT
—————————— ———- ———- ———- ——————————————-
OUT_TAB_OUTLINE 1 1 1 FULL(@»SEL$1″ «TAB_OUTLINE»@»SEL$1″)
OUT_TAB_OUTLINE 1 1 0 OUTLINE_LEAF(@»SEL$1″)
OUT_TAB_OUTLINE 1 1 0 ALL_ROWS
OUT_TAB_OUTLINE 1 1 0 DB_VERSION(‘12.1.0.2’)
OUT_TAB_OUTLINE 1 1 0 OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
OUT_TAB_OUTLINE 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
*/

— Tambien la podemos crear desde un sqlid almacenado en la v$sql. Sera necesario el hash_value

BEGIN
DBMS_OUTLN.create_outline(
hash_value => 1991164805,
child_number => 0,
category => ‘MOGU_OUTLINES’);
END;
/

@outline.sql
/*
NAME CATEGORY SQL_TEXT
—————————— —————————— ———————————————————————–
OUT_TAB_OUTLINE MOGU_OUTLINES SELECT ID, DESCRIPTION FROM MOGU.TAB_OUTLINE WHERE RECORD_TYPE=:valor
SYS_OUTLINE_16041414534146802 MOGU_OUTLINES SELECT ID, DESCRIPTION FROM TAB_OUTLINE WHERE RECORD_TYPE=:valor
*/

SQL>@outline_hint.sql
/*
NAME NODE STAGE JOIN_POS HINT
—————————— ———- ———- ———- —————————————————
OUT_TAB_OUTLINE 1 1 1 FULL(@»SEL$1» «TAB_OUTLINE»@»SEL$1″)
OUT_TAB_OUTLINE 1 1 0 OUTLINE_LEAF(@»SEL$1″)
OUT_TAB_OUTLINE 1 1 0 ALL_ROWS
OUT_TAB_OUTLINE 1 1 0 DB_VERSION(‘12.1.0.2’)
OUT_TAB_OUTLINE 1 1 0 OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
OUT_TAB_OUTLINE 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_16041414534146802 1 1 1 FULL(@»SEL$1» «TAB_OUTLINE»@»SEL$1″)
SYS_OUTLINE_16041414534146802 1 1 0 OUTLINE_LEAF(@»SEL$1»)
SYS_OUTLINE_16041414534146802 1 1 0 ALL_ROWS
SYS_OUTLINE_16041414534146802 1 1 0 DB_VERSION(‘12.1.0.2’)
SYS_OUTLINE_16041414534146802 1 1 0 OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
SYS_OUTLINE_16041414534146802 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
*/

— Creamos un indice para que cambie el plan de ejecucion

CREATE INDEX IDX_RECORD ON TAB_OUTLINE(RECORD_TYPE);

SELECT ID, DESCRIPTION FROM TAB_OUTLINE WHERE RECORD_TYPE=:valor;
/*
—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 10 | 290 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB_OUTLINE | 10 | 290 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_RECORD | 10 | | 1 (0)| 00:00:01 |
—————————————————————————————————
*/

— Habilitamos para que se usen las OUTLINES
‘nota se puede usar a nivel de sesion y a nivel de sistema’

ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=MOGU_OUTLINES;

SELECT ID, DESCRIPTION FROM TAB_OUTLINE WHERE RECORD_TYPE=:valor;
/*
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | 10 | 290 | 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB_OUTLINE | 10 | 290 | 16 (0)| 00:00:01 |
———————————————————————————

Note
—–
– outline «SYS_OUTLINE_16041414534146802» used for this statement << ‘MOGU_OUTLINES’);
END;
/