HINTS ¿Cómo afectar el plan de ejecución? Por defecto, el SGBD tomará en cuenta el camino de...

HINTSHINTS¿Cómo afectar el plan de ejecución?

Por defecto, el SGBD tomará en cuenta el camino de ejecución (Execution Path) determinado por el optimizador interno y de acuerdo a éste, tomará uno de los métodos para ejecutarlo. Por medio de la utilización de Hints, se puede forzar a que el SGBD ejecute una sentencia específica con el método deseado.

Para utilizar los Hints, es necesario colocarlos dentro de la sentencia SQL a ejecutar.


La sintaxis de la sentencia será:

SELECT /*+ [HINTS]*/ [columnas] FROM…

Los Hints que se utilizarán para las pruebas de comparación serán:

USE_NL(tabla): Fuerza al Optimizador a utilizar el método Nested Loops, usando tabla como la “driving table” (primera tabla) en dicho algoritmo.

USE_MERGE(tabla): Fuerza al optimizador a utilizar el método Sort Merge.USE_HASH(tabla): Fuerza al Optimizador a utilizar el método Hash.

• También es válido especificar ambas tablas en el hint, por ejemplo:

USE_NL(tabla1 tabla2), donde tabla1 será la driving table

• En caso de usar alias en la consulta, éstos deberán ser usados en lugar de los nombres “originales” de las tablas


ORDERED: Fuerza al optimizador a reunir las tablas en el orden en el que aparecen en la cláusula FROM.

INDEX(tabla [, campo]): Fuerza al optimizador a utilizar el índice sobre el campo especificado de la tabla. Si no fue especificado ninguno, se asume que se usará el de la clave primaria.

NO_INDEX(tabla [, campo]): Realiza la operación opuesta al HINT anterior.

Algunas veces en el modelo objeto-relacional (usando REFs), no es posible conocer el nombre de la tabla, es por esto que se recurre a hallar el alias (nombre interno) de las mismas así:


El nombre interno que adopta una tabla en una consulta se puede obtener así (sólo funciona a partir de Oracle 10g):

1. Realizar un Explain Plan sobre la consulta específica

2. Luego realizar la siguiente consulta:

SELECT plan_table_outputFROM TABLE(dbms_xplan.display('plan_table',null,'all'));


La función DBMS_XPLAN.DISPLAY acepta 3 parámetros, en su orden son:

table_name - Nombre de la tabla , donde se guardan los datos del explain plan, el valor por defecto es 'PLAN_TABLE'.

statement_id - Id de la sentencia del plan a ser mostrado, el valor por defecto es NULL.

format - Controla el nivel de detalle a ser mostrado, el valor por defecto es 'TYPICAL'. Otros valores incluyen 'BASIC', 'ALL', 'SERIAL'.


Esta consulta trae (además de otros datos), los alias de las tablas involucradas en la o las sentencias que se encuentran en la tabla del plan de ejecución (en este caso se llama PLAN_TABLE).

Por ejemplo, si se obtiene que los alias de 2 tablas son B@SEL$1, y P000003$@SEL$1 y se desea reunir estas tablas mediante el método Sort Merge, la sentencia a utilizar será:

SELECT /*+ USE_MERGE(B@SEL$1 P000003$@SEL$1)*/ [columnas] FROM …

• Drop table test_for_ep;• Create table test_for_ep (a number, b

varchar2(100));

• Select * from test_for_ep where a = 5;Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS

1 0 TABLE ACCESS (FULL) OF 'TEST_FOR_EP'

• Drop index test_for_ep_ix;• Create index test_for_ep_ix on test_for_ep (a);

• Select /*+ index(test_for_ep) */ *

From test_for_ep where a = 5;• Execution Plan----------------------------------------------------------


1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_FOR_EP' (TABLE)

2 1 INDEX (RANGE SCAN) OF 'TEST_FOR_EP_IX' (INDEX)

• Drop table test_for_ep_a;• Create table test_for_ep_a (aa number primary key, ab

varchar2(100));

• Drop table test_for_ep_b;• Create table test_for_ep_b (ba number references

test_for_ep_a, bb varchar2(100) primary key);

• Select *From test_for_ep_a a, test_for_ep_b b Where a.aa=b.ba;

Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'TEST_FOR_EP_B' (TABLE)

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_FOR_EP_A' (TABLE)

4 3 INDEX (UNIQUE SCAN) OF 'SYS_C0012921' (INDEX (UNIQUE))

Produce el mismo plan:• Select /*+ use_nl(a) */ *

From test_for_ep_a a, test_for_ep_b b Where a.aa=b.ba;

En cambio:• Select /*+ use_merge(a b) */ *

From test_for_ep_a a, test_for_ep_b b Where a.aa=b.ba;

Execution Plan

---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS ()

1 0 MERGE JOIN

2 1 SORT (JOIN)

3 2 TABLE ACCESS (FULL) OF 'TEST_FOR_EP_A' (TABLE)

4 1 SORT (JOIN)


• Select /*+ use_hash(a) */ *

From test_for_ep_a a, test_for_ep_b b

Where a.aa=b.ba;• Execution Plan----------------------------------------------------------


1 0 HASH JOIN

2 1 TABLE ACCESS (FULL) OF 'TEST_FOR_EP_A' (TABLE)


Ahora:

• Select /*+ use_hash(a) */ *From test_for_ep_a a, test_for_ep_b b Where a.aa<b.ba;

¿Por qué en este caso no se realiza el hash?

Comparación de Rendimiento entre el Comparación de Rendimiento entre el Modelo Relacional y el Objeto-Modelo Relacional y el Objeto-

RelacionalRelacional

Caso de Estudio:

Para analizar resultados de rendimiento de distintas operaciones sobre los modelos Relacional y Objeto-Relacional, se utilizarán dos modelos específicos:

• El primero es un modelo basado en una librería, donde se almacenan datos de clientes, libros, facturas, etc.

• El segundo es un modelo geográfico, el cual ayudará a estudiar el comportamiento de los punteros (REF) en el modelo objeto-relacional en comparación con el modelo relacional.

RESULTADOSRESULTADOSModelo 1 (Librería)

RESULTADOSRESULTADOSModelo 2 (Geográfico)

Tablas Muchos Datos Pocos Datos

Vendedor 192 192 Factura 12000 1200 Detalle 23481 2363 Libro 20000 2000

Categoría 50 50 Editorial 100 100

Tablas Cantidad Continente 5

País 200 Región 1000

Departamento 10000 Municipio 1000000

RESULTADOSRESULTADOSTamaño de las tablas utilizadas en los modelos para

las consultas

Tamaño de las Tablas para el Modelo 1

Tamaño de las Tablas para el Modelo 2

RESULTADOSRESULTADOSPruebas con GROUP BY (Sencillo)

Las consultas utilizadas se muestran a continuación:

Para el modelo relacional

SELECT nombre, COUNT(*)FROM libro l, categoria cWHERE l.cod_cate = c.codigoGROUP BY nombre;

Para el modelo objeto relacional

SELECT l.ref_cate.nombre, COUNT(*)FROM libro lGROUP BY l.ref_cate.nombre;

RESULTADOSRESULTADOSPruebas con GROUP BY

TASA ( f + g) / h d / e i / j k / ( f + g)

VALORNORMAL

Menor que10

1(o cercano a 1)

Mientrasmayor mejor

Menor del10% D

ATO

S

3.82 1 10 0.03141361 Muchos MODELORELACIONAL 0.6 1 10 0.2 Pocos

13.14 1 10 0.00913242 MuchosMODELOOBJETO-

RELACIONAL 1.98 1 10 0.72727272 Pocos

Resultados para las consultas de GROUP BY simple


Como se puede ver, el modelo objeto-relacional se comporta con un rendimiento más bajo con respecto al modelo relacional, tanto con muchos como con pocos datos (tomando en cuenta que mientras se

aumenten los datos el rendimiento del modelo objeto-relacional mejora).


Se observa que el modelo relacional aventaja al modelo objeto-relacional en la tasa de lecturas de disco a lecturas lógicas cuando se efectúan sobre pocos datos, sin embargo, al aumentar su cantidad, se observa que el modelo objeto-relacional aventaja al modelo relacional.


(utilizando las cláusulas HAVING y ORDER BY)


Para el modelo relacional

SELECT e.nombre, COUNT(*)FROM libro l, editorial eWHERE l.cod_edit = e.codigoGROUP BY e.nombreHAVING COUNT(*) > 200ORDER BY e.nombre;

Para el modelo objeto relacional

SELECT l.ref_edit.nombre, COUNT(*)FROM libro lGROUP BY l.ref_edit.nombreHAVING COUNT(*) > 200ORDER BY l.ref_edit.nombre;



TASA ( f + g) / h d / e i / j k / ( f + g)

VALOR NORMAL

Menor que 10

1 (o cercano a 1)

Mientras mayor mejor

Menor del 10% D

ATO

S

4.340909091 1 11.25 0.97905759 Muchos MODELO RELACIONAL 0.581395349 1 11.25 0.92 Pocos

14.6 1 11.25 0.00761035 Muchos MODELO

OBJETO-RELACIONAL 2.152173913 1 11.25 0 Pocos

Resultados para GROUP BY utilizando las cláusulas

HAVING Y ORDER BY



En la tasa de bloques leídos a filas procesadas, se observa de nuevo la ventaja que posee el modelo relacional con respecto al modelo objeto-relacional especialmente cuando se ejecuta sobre muchos datos.



De manera contraria a lo observado anteriormente, en la tasa de lecturas de disco a lecturas lógicas, se observa un comportamiento pobre en el modelo relacional, mostrando ser un modelo muy costoso (en este caso) comparado con el modelo objeto-relacional, el cual presenta valores bastante buenos.

RESULTADOSRESULTADOSPruebas con SUBCONSULTAS

(utilizando la cláusula EXISTS)


Para el modelo relacionalSELECT *FROM cliente cWHERE NOT EXISTS ( SELECT * FROM factura f WHERE f.ced_cli = c.cedula);

Para el modelo objeto relacionalSELECT *FROM cliente cWHERE NOT EXISTS ( SELECT * FROM factura f WHERE f.ref_cli = REF(c));



TASA ( f + g) / h d / e i / j k / ( f + g)

DATOSVALOR NORMAL

Menor que 10

1(o cercano a

1)


Menor del 10%

MODELO RELACIONAL

0.978723404

1 11.5 0 Muchos

1.4375 1 11.5 0 Pocos

MODELO OBJETO-

RELACIONAL

1862.413043

1 11.5 0 Muchos

207.2857143

1 11.5 0PocosResultados para las Subconsultas utilizando la cláusula EXISTS



En este caso se puede observar que en la tasa de bloques leídos a filas procesadas, hay una diferencia bastante significativa entre ambos modelos mostrando una ganancia bastante visible del modelo relacional sobre el modelo objeto-relacional, que en este caso muestra una eficiencia baja.


(utilizando la cláusula IN)


Para el modelo relacionalSELECT *FROM clienteWHERE cedula NOT IN ( SELECT ced_cli FROM factura );

Para el modelo objeto-relacionalSELECT *FROM cliente cWHERE REF(c) NOT IN (SELECT ref_cli FROM factura );



TASA ( f + g) / h d / e i / j k / ( f + g)

DATOSVALOR NORMAL

Menor que 10

1(o cercano a

1)


Menor del 10%

MODELO RELACIONAL

0.978723404

1 11.5 0Muchos

1.4375 1 11.5 0 Pocos

MODELO OBJETO-

RELACIONAL

1862.41304 1 11.5 0 Muchos

207.285714 1 11.5 0Pocos

Resultados para las Subconsultas utilizando la cláusula IN



De manera análoga a las pruebas realizadas sobre las Subconsultas utilizando la cláusula EXISTS, las pruebas realizadas con la cláusula IN muestran que en la tasa de bloques leídos a filas procesadas el modelo relacional ofrece de nuevo un rendimiento bastante superior con respecto al modelo objeto-relacional.



También se puede observar de acuerdo a los resultados, que aunque la cláusula EXISTS debería ser más eficiente que la

cláusula IN, en este caso particular, los dos tipos de Subconsultas ofrecen un rendimiento exactamente igual.En las demás tasas, en ambos casos, los dos modelos se

comportaron de manera eficiente

RESULTADOSRESULTADOSPruebas con AUTOREUNIONES

(SELF JOIN’s)

En este caso solo se realizaron las pruebas con una cantidad fija de datos. A continuación se muestran las consultas utilizadas para el caso.

Para el modelo RelacionalSELECT a.nombre, d.nombre as bisabueloFROM vendedor a, vendedor b, vendedor c, vendedor dWHERE a.ced_rec = b.cedulaAND b.ced_rec = c.cedulaAND c.ced_rec = d.cedula;

Para el modelo Objeto relacional.SELECT v.nombre, v.ref_rec.ref_rec.ref_rec.nombre as bisabueloFROM vendedor vWHERE v.ref_rec.ref_rec.ref_rec.nombre IS NOT NULL;


(SELF JOIN’s)

A pesar de que para mostrar los mismos resultados, las consultas se escriben de manera diferente, se puede observar que los planes de ejecución (EXPLAIN PLAN) para ambos modelos son prácticamente iguales.

Modelo Relacional Modelo Objeto-Relacional


(SELF JOIN’s)

TASA ( f + g) / h d / e i / j k / ( f + g)

VALOR NORMAL

Menor que 10 1(o cercano a 1)


Menor del 10%

MODELO RELACIONAL

0.50617284 1 11.57142857 0

MODELO OBJETO-

RELACIONAL0.802469136 1 11.57142857 0

Resultados para las autoreuniones


(SELF JOIN’s)

Como se puede observar, en la tasa de bloques leídos a filas procesadas, aunque ambos modelos presentan rendimientos bastante eficientes y la diferencia no es muy significativa, el modelo relacional aventaja al modelo objeto-relacional. En las otras tasas, de la misma manera que en los casos anteriores, ambos modelos presentaron buen rendimiento.

RESULTADOSRESULTADOS

Antes de continuar con los resultados obtenidos del modelo 1, se realizará la prueba con el modelo geográfico, el cual se desarrolló para observar el rendimiento de los punteros (REF) del modelo objeto-relacional y su uso, comparado con las reuniones normales del modelo Relacional

RESULTADOSRESULTADOSPruebas con el Modelo Geográfico

Estas son las consultas utilizadas:

Para el modelo RelacionalSELECT /*+ ORDERED NO_INDEX(c) NO_INDEX(p)*/ c.nombre, p.nombre, r.nombre, d.nombre, m.nombreFROM municipio m, departamento d, region r, pais p, continente cWHERE c.id = p.id_contAND p.id = r.id_paisAND r.id = d.id_regiAND d.id = m.id_depto;

Para el modelo Objeto relacional.SELECT m.ref_depto.ref_regi.ref_pais.ref_cont.nombre, m.ref_depto.ref_regi.ref_pais.nombre, m.ref_depto.ref_regi.nombre, m.ref_depto.nombre, m.nombreFROM municipio m;

Nótese la gran diferencia de escritura para obtener los mismos resultados


TASA ( f + g) / h d / e i / j k / ( f + g)

VALOR NORMAL

Menor que 10

1(o cercano a

1)


Menor del 10%

MODELO RELACIONAL

0.003805 1 14.99970001

0.88436268

MODELO OBJETO-

RELACIONAL

0.074357

1

14.999700

01

0.0800059

1

Resultados para el modelo geográfico


A pesar de que en la tasa de bloques leídos a filas procesadas tanto el modelo relacional como el objeto-relacional presentaron rendimientos bastante buenos y eficientes, se sigue observando que el modelo relacional presenta un mejor rendimiento general en estos casos.


Todo lo contrario sucede en la tasa de lecturas de disco a lecturas lógicas en la cual, el rendimiento del modelo objeto-relacional es mucho mejor que el del modelo relacional, el cual presentó un rendimiento bastante pobre lo cual debe ser tomado en cuenta a la hora de escoger el modelo más adecuado para trabajar.

RESULTADOSRESULTADOSPruebas con Reuniones (Joins)

Ahora, se analizará en detalle el rendimiento de las consultas de reunión realizadas sobre ambos modelos.

Debe tenerse en cuenta que en este caso, se trabajará con una de las colecciones del modelo objeto-relacional, una TABLAS ANIDADA.

Se analizarán los casos donde se reúnen las 2 tablas (una de ellas tabla anidada) y otro caso en el que se reúnen 3 tablas (de nuevo, solo una de ellas es anidada, la cual será “detalle” en el modelo de la librería).

Se analizarán los casos con muchos y pocos datos.

RESULTADOSRESULTADOSReunión con 2 Tablas


Para el Modelo RelacionalSELECT /*+ USE_MERGE(d) */ f.fecha, d.cant, d.valor_detalle FROM factura f, detalle dWHERE f.numero = d.num_fact;

Para el Modelo Objeto-RelacionalSELECT /*+ USE_MERGE(d) */ f.fecha, d.cantidad, d.valor_detalleFROM factura f, TABLE(f.detalle) d;

RESULTADOSRESULTADOSReunión con 2 Tablas (Muchos Datos)

Resultados para las pruebas con muchos datos en ambos modelos.


EXPLAIN PLAN para la reunión con 2 tablas, utilizando el método Sort Merge


Bloques leídos (f+g) sobre filas procesadas (h).

Como se puede observar en la figura, el resultado del Nested Loops es demasiado pobre, por lo tanto, se observarán los resultados sin este método en la siguiente diapositiva


Bloques leídos (f+g) sobre filas procesadas (h), sin el método Nested Loops.

Se observa que aunque ambos modelos presentan un muy buen rendimiento, el modelo Relacional se comporta mejor que el Objeto-Relacional, además el método Merge ha sido el que mejor rendimiento ha presentado en esta tasa específica.


Lecturas de Disco (k) sobre lecturas lógicas. (f+g)

Es en este punto donde se ve la mayor diferencia entre ambos modelos. Al observar la figura, es claro identificar, que el modelo objeto relacional es muy costoso en el uso de recursos, al tener una tasa bastante alta de lecturas de disco. Por el contrario el modelo Relacional presenta costos muy bajos.

RESULTADOSRESULTADOSReunión con 2 Tablas (Pocos Datos)

Resultados para las pruebas con pocos datos en ambos modelos.


Bloques leídos (f+g) sobre filas procesadas (h)

De la misma manera que en el caso anterior, es claro que el método Nested Loops es bastante pobre como se puede observar en la figura.


Bloques leídos (f+g) sobre filas procesadas (h). Sin el método Nested Loops

Al tener en cuenta solo los métodos de Hash y Merge, a una menor escala se puede comprobar que el modelo Relacional sigue obteniendo un mejor rendimiento que el modelo Objeto-Relacional.


Lecturas de Disco (k) sobre lecturas lógicas. (f+g)

Se encuentra que con pocos datos, los diferentes métodos se comportan de maneras muy diferentes. Cuando se utiliza el método Hash es más costoso el modelo Objeto-Relacional, sin embargo lo contrario se observa al utilizar el método Merge, debido a que el modelo Relacional resulta ser muy costoso. Como se puede ver, el método de Nested Loops es poco costoso en ambos modelos.


Ahora se dispondrá a analizar los resultados con tres tablas (siendo solo una de ellas una tabla anidada, la cual es “detalle”).

Es posible hacer combinaciones de métodos de reunión (por ejemplo, realizar la misma consulta utilizando Hash-Merge o Hash-Nested Loops), esto se puede especificar mediante los HINTS.

De la misma manera que en la reunión con 2 tablas, se realizan las pruebas con muchos y pocos datos



Para el Modelo RelacionalSELECT /*+ ORDERED USE_MERGE(d) USE_HASH(f)*/ c.nombre, c.apellidos, f.fecha, d.cant, d.valor_detalleFROM cliente c, factura f, detalle dWHERE c.cedula = f.ced_cliAND f.numero = d.num_fact;

Para el Modelo Objeto-RelacionalSELECT /*+ ORDERED USE_MERGE(d) USE_HASH(B@P00213)*/ f.ref_cli.nombre, f.ref_cli.apellidos, f.fecha, d.cantidad, d.valor_detalleFROM factura f, TABLE(f.detalle) d;


Resultados para las pruebas con muchos datos en ambos modelos (en este caso solo se presentan los datos para las combinaciones con el método Hash.


Se observa la combinación de métodos de reunión de la forma Hash – Merge. En este caso el plan de ejecución muestra que el SGBD utiliza el método Hash para unir las tablas “cliente” y “factura” y luego realiza el método Merge, para reunir el resultado anterior con la tabla “detalle”



En este caso, no se ha encontrado una diferencia muy grande respecto a los resultados anteriormente vistos, por ejemplo, se encuentra que cuando se utiliza en algún punto en método Nested Loops el rendimiento es bastante pobre


Bloques leídos (f+g) sobre filas procesadas (h). Sin el método Nested Loops

Se encuentra que el modelo Relacional sigue teniendo ventaja sobre el modelo Objeto-Relacional, aunque sea por una cifra poco significativa


Lecturas de Disco (k) sobre lógicas. (f+g)

Existe un caso especial en el cual se debe centrar la atención, que es el de el método Hash – Hash, en el caso de lecturas de disco a lecturas lógicas, el cual presenta un costo más elevado que los demás, sin embargo, se encuentra en los niveles aceptables para esta tasa específica.


Se analizan ahora los resultados con pocos registros


Se puede mostrar que la tendencia se ha mantenido, es decir, el modelo Relacional, generalmente se comporta mejor que el modelo Objeto-Relacional. Siendo el método Nested Loops el que muestra el rendimiento más pobre.

Bloques leídos (f+g) sobre filas procesadas (h), Sin el método Nested Loops


El modelo relacional, observando los resultados a menor escala, muestra mejores rendimientos con respecto al modelo Objeto-Relacional, en especial, donde se encuentra involucrado el método Merge.


Lecturas de Disco (k) sobre lecturas Lógicas (f+g)

En la figura se observan los casos en los que hay mayor uso de recursos, sin embargo, en la gran mayoría de ellos, el rendimiento es bastante bueno.

HINTS ¿Cómo afectar el plan de ejecución? Por defecto, el SGBD tomará en cuenta el camino de...

Documents

Transcript of HINTS ¿Cómo afectar el plan de ejecución? Por defecto, el SGBD tomará en cuenta el camino de...