Hints Globales (Global Hints)

Como se puede ver en mi entrada de Mi primer tuning, desde hace aproximadamente 10 años he usado hints en Oracle.

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;