View
224
Download
0
Category
Preview:
Citation preview
7/23/2019 Optimizador Oracle
1/34
Optimizador OracleRoberto porto
Martha MorrnJorge Ariza
Integrantes:
7/23/2019 Optimizador Oracle
2/34
Qu es el Optimizador?
La base de datos Oracle utiliza la siguiente arquitectura para el procesamiento delas sentencias SQL que recibe por parte de los usuarios.
7/23/2019 Optimizador Oracle
3/34
Sus principales componentes
son : El parser, o intrprete, que traduce las sentencias SQL, realizando el
Anlisis Sintctico (comprueba la correccin sintctica de las
instrucciones SQL) y Semntico (verifica que los objetos utilizados y
sus atributos existen y son correctos), a la representacin interna
utilizada por Oracle.
El optimizador, que utiliza reglas internas y estadsticas de coste para
determinar la forma ms eficiente de producir los resultados de la
sentencia. La salida del optimizador es un plan ptimo de ejecucin.
El generador de fuentes de registros, que recibe el plan ptimo
desarrollado por el optimizador, y genera el plan de ejecucin : una
sucesin de fuentes de registros estructurados en forma de rbol.
El motor de ejecucin de SQL, que recibe el plan de ejecucin y lo
ejecuta, extrayendo registros de cada una de las fuentes indicadas, y
combinando estos registros, hasta obtener el resultado de la sentencia.
7/23/2019 Optimizador Oracle
4/34
Para cada instruccin SQL ejecutada por Oracle, el
optimizador puede realizar varias de las siguientes
tareas :
1.Evaluacin de expresiones y condiciones : Se evalan expresiones ycondiciones que contengan constantes de la forma ms amplia posible
2.Transformacin de Instrucciones : Para instrucciones complejas quecontengan, por ejemplo, consultas o subconsultas relacionadas, eloptimizador podra transformar la sentencia original en una sentencia
con un join equivalente.3. Eleccin de caminos de acceso : Para cada tabla accedida por lainstruccin, el optimizador elige uno o ms de los caminos de accesodisponibles para obtener los datos de la tabla.
4. Eleccin de orden para combinaciones (joins) : Para cada instruccin
de combinacin entre ms de dos tablas, el optimizador elige qu parde tablas se unen primero, y qu tabla es combinada con el resultado, yas sucesivamente.
5. Eleccin de mtodos de combinacin (join) : Para cada combinacin,el optimizador escoge el mtodo idneo a usar.
7/23/2019 Optimizador Oracle
5/34
Para ejecutar una sentencia SQL, Oracle puede necesitar muchos pasos. En cada uno de estospasos, o bien se recuperan registros de datos de la base de datos, o bien se prepara a aqulla dealguna manera para que el usuario obtenga la salida de su instruccin. La combinacin de pasos
que Oracle usa para ejecutar una instruccin es lo que se denomina Plan de Ejecucin. Un plan deEjecucin incluye: Una va de acceso para cada tabla a la que accede la instruccin. El orden dela combinacin de las tablas, con el apropiado mtodo de combinacin.
Etapas del Plan de Ejecucin
Cada etapa del Plan de ejecucin devuelve un grupo de registros que o bien son usados por elsiguiente paso, o en l ultimo paso, son devueltos al usuario o aplicacin liberando la instruccin
SQL. El grupo de registros devueltos por un paso es conocido como row set, o conjunto deregistros. El origen de estos registros puede ser, o bien, la recuperacin desde un determinadoobjeto de la base de datos, o bien la combinacin de los row sets obtenidos de los pasosanteriores en el plan de ejecucin.
El plan de ejecucin forma un rbol. Para procesarlo, el gestor de base de datos ejecuta en primerlugar los pasos correspondientes a las hojas del rbol. Los registros obtenidos se convierten en
fuentes de los pasos padre de estos. Despus Oracle realiza estos pasos padre, y assucesivamente hasta llegar a la raz del rbol.
El comando EXPLAIN PLAN
El comando EXPLAIN PLAN permite obtener, en forma de tabla, el plan de ejecucin elegido porel optimizador de Oracle para la instruccin SELECT, UPDATE, INSERT y DELETE que se le indique.
Planes de Ejecucin
7/23/2019 Optimizador Oracle
6/34
Tipos de Optimizacin
El gestor de base de datos Oracle, en sus versiones actuales,tiene dos mtodos de optimizacin: el Optimizador Basado enReglas (RBO Rule Based Optimizer) y el Optimizador Basado enCostes (CBO Cost Based Optimizer) :
El optimizador basado en reglas, que ha sido el principalmtodo de optimizacin de Oracle hasta que el optimizadorbasado en costes, introducido en la versin 7, ha ido ganando enprestaciones. Se basa en reglas obtenidas heursticamente porOracle, en base a la experiencia con sucesivas versiones.
El optimizador basado en costes, que tiene en cuenta el estadode la base de datos en el momento de la ejecucin de lasentencia, para determinar cul de los planes de ejecucin esms eficiente. Tambin tiene en cuenta los hints, sugerencias deoptimizacin colocadas como comentarios en la instruccin SQLpor el programador.
7/23/2019 Optimizador Oracle
7/34
Operaciones del Optimizador
Evaluacin de Expresiones, Condiciones y Constantes
El optimizador realiza una serie de operaciones con la sentencia, transformndola ynormalizndola, de manera que sea ms fcil obtener un plan de ejecucin ptimo.Entre estas operaciones est la evaluacin de las expresiones, de las condiciones y delas constantes. Las expresiones constantes son evaluadas durante la optimizacin, de manera que
no se tengan que evaluar una y otra vez durante la ejecucin. Esta evaluacin selimita a los trminos de las condiciones, no a las propias condiciones. Por tanto,siempre es preferible pasar todas las expresiones constantes a un lado de lacondicin, para que Oracle las evale juntas.
Las condiciones IN pueden ser traducidas a mltiples condiciones de igualdad,separadas por operadores OR.
Los operadores NOT pueden ser eliminados, mediante la transformacin de la
condicin que se niega. Esta transformacin puede implicar la introduccin denuevas condiciones con operadores NOT, que podran ser optimizadas a su vez.
Las condiciones que usan el operador BETWEEN son substituidas por condicionesequivalentes que usen los operadores de comparacin >= y
7/23/2019 Optimizador Oracle
8/34
Los operadores ALL seguidos de una lista de valores, se traducen acondiciones individuales separadas por operadores AND. Si van seguidos
de una subconsulta, se traducen a operadores ANY, con una condicincontraria en la subconsulta. El operador ANY obtenido podr sertransformado, a su vez.
La optimizacin de sub-expresiones comunes, busca partes comunes encondiciones separadas por operadores OR, de manera que puedan ser
eliminadas de estas condiciones, y ejecutadas una nica vez. El optimizador puede tener en cuenta las funciones determinsticas
(funciones que devuelven siempre el mismo resultado para los mismosparmetros de entrada) para evaluarlas en tiempo de optimizacin, outilizar resultados almacenados en tiempo de ejecucin. El programador
puede usar la palabra clave DETERMINISTIC para indicar que una de lasfunciones que define es determinstica.
El optimizador basado en costes, adems, puede introducir nuevascondiciones obtenidas por
7/23/2019 Optimizador Oracle
9/34
Expansin de predicados : si una vista no es combinable con la sentencia quehace referencia a ella, se puede transformar su sentencia de creacin,
mediante la introduccin de las condiciones de la sentencia inicialcorrespondientes a la vista, dentro de la sentencia de creacin.
Consultas compuestas, en consultas simples unidas por operadores : el plande ejecucin de una consulta compuesta, se transforma en planes de
ejecucin paralelos para cada una de las subconsultas, unidos por loscorrespondientes operadores (UNION, INTERSECT, MINUS).
7/23/2019 Optimizador Oracle
10/34
Transformacin de
sentenciasSQL es un lenguaje bastante flexible, se pueden utilizar diferentes caminos para lograr el mismoobjetivo. Muchas veces, el optimizador transformar una sentencia en otra que obtenga el mismoresultado, pero sea ms eficiente. Algunas de las transformaciones que aplica el optimizador son :
Consultas con operadores OR, en mltiples consultas unidas por UNION :
si el optimizador encuentra que separar la sentencia inicial en mltiples sentencias, cada unacon una de las condiciones separadas por OR, y despus unirlas mediante el operador
UNION, es ms eficiente que la primera opcin. Subconsultas, en combinaciones (joins) : si la transformacin de la subconsulta en una
combinacin con la consulta principal asegura el mismo resultado que la consulta inicial, eloptimizador puede transformarla y proceder a la optimizacin de la nueva consulta obtenida.
Combinaciones con vistas, en combinaciones con sentencias : si se puede substituir lareferencia a una vista por la referencia a su sentencia de creacin, e intentar la optimizacin
de la nueva sentencia obtenida. No se podr realizar esta substitucin si la vista contiene, ensu sentencia de creacin, operadores UNION, INTERSECT, MINUS, CONNECT BY, la seudo-columna ROWNUM, o las funciones de grupo AVG, COUNT, MAX, MIN y SUM.Combinaciones con vistas complejas : si se ha activado la combinacin de vistas complejas,Oracle puede intentar la tranformacin de vistas con funciones de grupo y clusulas GROUPBY.
7/23/2019 Optimizador Oracle
11/34
Mtodos de combinacin de
tablasLos joins son sentencias que devuelven datos de ms de una tabla (o de una nicatabla referenciada varias veces). Un join esta caracterizado por mltiples tablas en laclusula FROM y la relacin entre estas tablas es definida a travs de la condicinWHERE. El optimizador cuenta con varios mtodos para realizar combinacionesentre tablas, y debe elegir entre ellos el ms eficiente. Entre los principales mtodosde join estn :
Bucles Anidados (Nested Loop Joins) : el optimizador utiliza este mtodo conconjuntos de datos pequeos, y en el caso de que la condicin de join sea un caminode acceso eficiente para la tabla interior. El optimizador determina cul es la tablaconductora, y ejecuta un bucle accediendo a los registros de esta tabla quecumplan las condiciones de la consulta. Para cada registro de la tabla conductora, seejecuta un bucle accediendo a la tabla interior, mediante los valores actuales de latabla conductora. Por tanto, es importante que el camino de acceso a la tabla
interior sea muy eficiente.Bucles Anidados Externos (Nested Loop Outer Joins) : en caso de combinacionesexternas (outer joins), si se cumplen las condiciones de eleccin de Bucles Anidados,se puede utilizar el mtodo de Bucles Anidados Externos, en el que la tablaconductora es siempre la tabla exterior (mientras que, en el mtodo anterior, sepuede elegir a una u otra tabla como tabla conductora).
7/23/2019 Optimizador Oracle
12/34
NESTED LOOP JOINS
7/23/2019 Optimizador Oracle
13/34
7/23/2019 Optimizador Oracle
14/34
Combinaciones Hash (Hash Joins) : este mtodo es usado para enlazar grandes conjuntos de datos, en losque la condicin de join es de igualdad. El optimizador usa la ms pequea de las dos tablas fuentes paraconstruir una tabla hash sobre la clave de join, en memoria. Cuando se recuperan los registros de la tablams grande, se examina la tabla hash para encontrar las filas enlazadas en la otra tabla. Esta situacin esptima cuando la tabla menor es lo suficientemente pequea para caber en la memoria disponible, ya que
el coste se limita a una simple lectura del dato de la tabla menor. Sin embargo, si la tabla hash esdemasiado grande, el optimizador la divide en varias particiones, que se escriben a diferentes segmentostemporales de disco, que son leidos a medida que son necesitados, lo que disminuye la eficiencia.
Combinaciones Hash en Joins Externos (Hash Join Outer Joins) : este mtodo es muy similar al anterior,pero en el caso de combinaciones externas. La tabla conductora es siempre la tabla externa, y seconstruye la tabla hash sobre ella. Se recorre la tabla interior, comprobando en la tabla hash la existenciade datos en la tabla exterior. Se preservan los registros de la tabla exterior que no tengan equivalente en la
interior. Combinaciones Hash (Hash Joins) : este mtodo es usado para enlazar grandes conjuntos de datos, en los
que la condicin de join es de igualdad. El optimizador usa la ms pequea de las dos tablas fuentes paraconstruir una tabla hash sobre la clave de join, en memoria. Cuando se recuperan los registros de la tablams grande, se examina la tabla hash para encontrar las filas enlazadas en la otra tabla. Esta situacin esptima cuando la tabla menor es lo suficientemente pequea para caber en la memoria disponible, ya queel coste se limita a una simple lectura del dato de la tabla menor. Sin embargo, si la tabla hash esdemasiado grande, el optimizador la divide en varias particiones, que se escriben a diferentes segmentos
temporales de disco, que son leidos a medida que son necesitados, lo que disminuye la eficiencia.
Combinaciones Hash en Joins Externos (Hash Join Outer Joins) : este mtodo es muy similar al anterior,pero en el caso de combinaciones externas. La tabla conductora es siempre la tabla externa, y seconstruye la tabla hash sobre ella. Se recorre la tabla interior, comprobando en la tabla hash la existenciade datos en la tabla exterior. Se preservan los registros de la tabla exterior que no tengan equivalente enla interior.
7/23/2019 Optimizador Oracle
15/34
HASH JOINS
7/23/2019 Optimizador Oracle
16/34
HASH JOINS
7/23/2019 Optimizador Oracle
17/34
HASH JOINS
7/23/2019 Optimizador Oracle
18/34
HASH JOINS
7/23/2019 Optimizador Oracle
19/34
HASH JOIN
7/23/2019 Optimizador Oracle
20/34
Se construye una hash table con las filas de la relacin mspequea R.
La funcin hash es basada en el atributo JOIN
Luego se recorre la relacin S secuencialmente
7/23/2019 Optimizador Oracle
21/34
7/23/2019 Optimizador Oracle
22/34
Se recorre la relacin S secuencialmente
De cada tupla S, usar los valores hash R para cada tupla paraprobar que la tabla hash de R coinciden con los valores clave
Si hay un atributo que coincide, esa es la salida
Sino coincide, sigue secuencialmente a la otra tupla.
7/23/2019 Optimizador Oracle
23/34
Combinaciones de Ordenacin y Fusin (Sort Merge Joins) : consisten en una fusin delos dos conjuntos de datos, previamente ordenados por las columnas de la condicin dejoin. No existe el concepto de tabla conductora o directora. Se utilizan para combinarregistros de dos fuentes independientes, especialmente si ya estn ordenadosmediante las columnas de la condicin de join, o si la condicin de join incluye algnoperador que no sea de igualdad. Enotro caso, es mejor utilizar las combinaciones hash.Existe un mtodo similar, para los outer joins, denominado Sort Merge Outer Joins.
Combinaciones Cartesianas (Cartesian Joins) : consisten en la combinacin de dosfuentes de registros, entre las que no hay definida ninguna condicin de join. Su costees muy elevado, en funcin de los volmenes de las dos fuentes de registros.
Combinaciones Externas Completas (Full Outer Joins) : consisten en combinaciones
externas en las que se deben preservar los registros de ambas fuentes de datos (usandolas palabras clave FULL OUTER JOIN).
Anti-Combinaciones (Anti-Joins) : son combinaciones en las que se devuelven losregistros de la primera fuente de datos que no cumplen las condiciones impuestas a lasegunda fuente (operadores NOT IN con subconsultas NOT EXISTS).
Semi-Combinaciones (Semi-Joins) : son combinaciones en las que se devuelven losregistros de la primera fuente de datos que cumplen las condiciones impuestas a la
segunda fuente (operadores IN con subconsultas EXISTS).
7/23/2019 Optimizador Oracle
24/34
Sort-Merge-Join
7/23/2019 Optimizador Oracle
25/34
Sort-Merge-Join
7/23/2019 Optimizador Oracle
26/34
SORT-MERGE-JOIN
7/23/2019 Optimizador Oracle
27/34
SORT MERGE JOIN
7/23/2019 Optimizador Oracle
28/34
Optimizacin de Sentencias en
'Estrella'Algunos data warehouse son diseados como esquemas en estrella, loque incluye una gran tabla de hechos y varias pequeas tablas dedimensiones. En la primera se almacena informacin bsica y en lasdems se guarda informacin de atributos de la tabla de hechos.Una consulta en estrella es una unin entre una tabla de hechos y uncierto nmero de tablas de dimensiones, unidas por relaciones clave
primaria clave ajena, y sin relacin entre las tablas de dimensiones. ElOptimizador Basado en Costes es capaz de identificar este tipo derelacin y de crear un plan de ejecucin eficiente. El OptimizadorBasado en Reglas no presenta actividad optimizadora especfica enestos casos.Una tpica tabla de hechos contiene claves y medidas. Por ejemplo la
medida Importe de ventas y las claves Fecha, Producto y Mercado (queconectan con las correspondientes tablas de dimensiones). La tabla dehechos tiene, normalmente, un indice concatenado en las columnas dela clave para facilitar este tipo de unin o bien indices independientespara cada columna de la clave.
7/23/2019 Optimizador Oracle
29/34
Cmo especificar el tipo de
optimizacin
OPTIMIZADOR
Valor=rule Valor=choose
INIT.ORA
7/23/2019 Optimizador Oracle
30/34
Cmo especificar el tipo de
optimizacin Mediante la modificacin del parmetro de INIT.ORA denominado
OPTIMIZER_MODE, se consigue la determinacin del tipo de optimizacin.Si se da el valor RULE, el optimizador funciona mediante Reglas (RCO). Si seda el valor CHOOSE, el optimizador elegir Costes (CBO), siempre queexistan estadsticas sobre las tablas, o se presenten sugerencias (hints) por
parte del programadador. Si se ha fijado CHOOSE, pero no existenestadsticas o sugerencias, el Optimizador trabajar por Reglas (RCO).Enfuncin de otros valores del parmetro, se utilizar el CBO enfocado a undeterminado objetivo.
7/23/2019 Optimizador Oracle
31/34
Optimizacin basada en costesPor defecto, el punto fuerte del Optimizador Basado en Costes es la cantidad de datos tratados por segundo, altiempo que dedica el mnimo de recursos a la solucin de la query. Es decir, se trabaja en dos direcciones:mnimos recursos aplicables, para obtener en el mnimo tiempo el primer registro accedido por la consultaempleada (mnimo tiempo de respuesta).
No obstante es posible potenciar, segn la tarea a realizar, una u otra prestacin.
Para aplicaciones de tipo batch, como Oracle Reports, es preferible optimizar para la mximaproductividad : se disminuye el tiempo total de tarea, no importando tanto el tiempo de respuesta.
Para aplicaciones interactivas, como Oracle Forms o consultas en SQL*Plus, optimizar para el mejortiempo de respuesta.
El CBO determina cul de los planes de ejecucin es mas eficiente, teniendo en cuenta los caminos de accesodisponibles y factorizando la informacin basada en estadsticas de los objetos del esquema (tablas o ndices)accedidos por la instruccin SQL, adems de las sugerencias de optimizacin colocadas como comentarios en la
instruccin SQL, y el objetivo establecido en OPTIMIZER_MODE. Cuando en Oracle se disean nuevasprestaciones para la base de datos se utiliza exclusivamente la CBO como medio de optimizacin. En generalconsta de tres etapas :
1. Realizacin de planes potenciales de recuperacin de registros.
2. Estimacin del coste por plan teniendo en cuenta las estadsticas almacenadas en el diccionario de datosdisponibles para cada objeto incluido en la query. El calculo de coste se basa en los recursos estimados para elordenador incluyendo Entrada/Salida, CPU y memoria.
3. Seleccin del plan de menor coste.
Caractersticas disponibles con
7/23/2019 Optimizador Oracle
32/34
Caractersticas disponibles conla Optimizacin Basada en
Costes Las siguientes caractersticas estn disponibles slo si se utilizael Optimizador
Basado en Costes (CBO) :
Tablas e ndices particionados, y tablas organizadas por ndices
ndices de clave inversa, e ndices basados en funciones Clusulas SAMPLE en sentencias SELECT
Consultas paralelas y DML paralelo
Transformaciones en estrella, y consultas en estrella
Optimizador extensible Re-escritura de consultas con vistas materializadas
Combinaciones hash
ndices bitmap
Index Skip Scans
7/23/2019 Optimizador Oracle
33/34
Optimizacin basada en
ReglasIntroduccin a la Optimizacin de Reglas
El optimizador basado en reglas ha sido el principal mtodo de optimizacin deOracle hasta que el optimizador basado en costes, introducido en la versin 7, ha idoganando en prestaciones. Actualmente se desaconseja la utilizacin del optimizadorbasado en reglas, excepto para aplicaciones desarrolladas originalmente sobreOracle 6 versiones anteriores, o explcitamente para este optimizador sobre
versiones 7 y posteriores.El parmetro de INIT.ORA denominado OPTIMIZER_MODE determina la eleccin deloptimizador. Si tiene el valor RULE, se utiliza siempre el optimizador basado enreglas. Si tiene el valor CHOOSE, se utiliza el optimizador basado en costes, a no serque no haya estadsticas sobre las tablas, y no se haya indicado hints en lassentencias (entonces se utiliza el basado en reglas).El mtodo empleado por el optimizador basado en reglas es el siguiente : se analiza
la sentencia SQL en cuestin, obtenindose varios planes de ejecucin posibles. Seclasifican estos planes en funcin del ranking de sus operaciones, y se elige el plancon menor ranking. El ranking de operaciones ha sido creado por Oracle, a partir dedatos de tiempos medios de CPU, nmero de operaciones de Entrada/Salida, etc, decada tipo de operacin.
7/23/2019 Optimizador Oracle
34/34
Recommended