Para los que nunca han usado un hint, no son más que instrucciones adicionales al optimizador de costos, que permiten cambiar un plan de ejecución. En pocas palabras, los hints nos permiten decidir en lugar del optimizador.
Existen diferentes tipos de Hints, oracle los suele categorizar de la siguiente forma:
* Single Table (Index, full)
* Multi Table (use_nl, use_hash, leading)
* Query Block (unnest, nomerge)
* Statement (all_rows, first_rows(n))
Pero en este caso me voy a enfocar a una subcategoria, los "Global Hints".
En mi ejemplo de la vida real, me encontré con sentencias SQL similares a las siguientes
SELECT MIN(CLP.CAMPO_FECHA) FROM
XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
SELECT MIN(CLP.CAMPO_FECHA_2)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
Estas sentencias SQL tenían un pésimo plan de ejecución, ya que la vista "XX_VISTA_1", contenía un inline view, y oracle decidía combinar el inline view con el outer query en una simple sentencia SQL (esto no siempre es una buena idea).
Existían en realidad unas 6 sentencias SQL similares con el mismo problema.
Tras revisar el código de la vista, me di cuenta de que eso era lo que Oracle estaba realizando, y la solución era sencilla... Cambiar el código de la vista para que no hiciera el merge del inline view:
CREATE OR REPLACE FORCE VIEW "APPS"."XX_VISTA_1"
("ROW_ID", CAMPO_FECHA, ...) AS
SELECT /*+ NO_MERGE(msi) */
pdc.ROWID row_id,
msi.FECHA campo_fecha,
...
La realidad es que esta es una de las peores soluciones y realmente no fue lo que recomendé, pero fue lo que el cliente decidió hacer como primer intento.
Internamente para ellos era más fácil modificar 1 vista que modificar 6 programas.
Lo que sucedió fue que a partir de la modificación de la vista, hubo sentencias SQL como la siguiente
SELECT DECODE (:a1,NULL, 'Normal',(
SELECT SECUENCIA_DSP FROM APPS.XX_VISTA_1
WHERE CAMPO_NUMBER =
(SELECT MAX (CAMPO_NUMBER)
FROM APPS.XX_VISTA_1
WHERE CAMPO_NUMBER_2 = :a2
AND CAMPO_TEXTO = 'Active'
AND CAMPO_TEXTO_2
IN ('P', 'O', 'I', 'F')))
) FROM DUAL;
Que al cambiar el código en la vista empezó a tener un desempeño muy pobre, cambiando su plan de ejecución a varios Full Table Scans.
Entonces, si el código de la vista no debe de ser cambiado, ¿qué es lo que se puede usar?
Global Hints
Los hints que se suelen usar en select, updates y deletes, generalmente van relacionados a una tabla que existe dentro del mismo query block. Y no a tablas relacionadas con vistas internas.
La solución son los Global Hints, es decir, se puede usar un Global Hint para decirle al optimizador de costos cómo esolver una vista que existe en nuestro query block.
En mi ejemplo, podemos decirle a la vista que no haga el merge del inline view desde el query principal, y de esta forma evitar la modificación de la vista.
SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA) FROM
XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA_2)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
De esta forma se estabilizaron los planes de ejecución de 6 sentencias SQL y no se afectó el desempeño de las demás sentencias que hacían uso de la vista.
En el manual de Oracle de performance tuning, vienen dos ejemplos muy claros sobre el uso de global hints
Ejemplo 1
CREATE OR REPLACE VIEW v AS
SELECT
e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
( SELECT *
FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
AND e1.salary = ( SELECT
max(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;
SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */ *
FROM v;
SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ *
FROM v;
SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ *
FROM v;
Ejemplo 2
CREATE OR REPLACE VIEW v1 AS
SELECT *
FROM employees
WHERE employee_id < 150;
CREATE OR REPLACE VIEW v2 AS
SELECT v1.employee_id employee_id, departments.department_id department_id
FROM v1, departments
WHERE v1.department_id = departments.department_id;
SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
FULL(v2.departments) */ *
FROM v2
WHERE department_id = 30;
