OPTIMIZACION Y ALTO RENDIMIENTO
Curso Curso Manejo Manejo e Imp Implem lementa entación ción de Archivos Archivos Cat. Ing. Alvaro Díaz A. (Secc. A+) Cat. Ing. Oscar Paz (Secc. A-) Universidad de San Carlos de Guatemala Segundo Semestre 2011
CONTENIDO
INTRODUCCION Porque se afina un Sistema ? Quien afina ? Cuando se afina ?
CAUSAS DE PROBLEMAS DE PERFORMANCE Problemas con el diseño y desarrollo Problemas con Recursos Problemas con I/O de disco Problemas con CPU Problemas de la red
CONTENIDO
DISEÑANDO PARA UN MEJOR PERFORMANCE
Afinando el modelo de datos Afinando Índices Desnormalizando una Base de Datos Constraints Triggers Performance de Querys Parallel Query
CONTENIDO
AFINANDO SQL
Estándares de SQL Utilizar bind-variables El Optimizador de SQL Afinamiento de SQL Sentido común en SQL
HERRAMIENTAS DE DIAGNÓSTICO
CONTENIDO
METODOLOGIA DE AFINAMIENTO
Inspección Inicial
Análisis
Identificar posibles problemas Recolectar información mediante mediciones Elaborar diagramas
Identificación de causas y efectos Priorizar tareas Conclusiones y Recomendaciones Elaborar Cronogramas
Acciones Correctivas Presentación de Resultados
INTRODUCCION
Porqué se afina un Sistema ?
Beneficios económicos para la Empresa • • •
Evita incurrir en costos adicionales de equipo. Con un adecuado afinamiento se obtiene un mejor performance. Al disminuir el equipo utilizado se disminuyen también los costos de mantenimiento tanto de software como hardware.
Beneficios Humanos
Incrementa la productividad, a la vez que satisface a los clientes de la organización
El diseñador debe comunicar el diseño del sistema para que cualquier persona pueda entender el flujo de datos en una aplicación. Los desarrolladores de aplicación deben comunicar las estrategias de implementación que escogen y aquellos módulos y sentencias SQL pueden ser rápida y fácilmente identificadas durante la tarea de afinamiento. El administrador de la base de datos debe monitorear y documentar las actividades del sistema cuidadosamente y aquellos rendimientos inusuales del sistema que pueden ser identificados y corregidos. Los administradores de hardware y software deben documentar y comunicar las configuraciones del hardware y software del sistema para que cualquiera pueda diseñar y administrar sistemas efectivamente.
INTRODUCCION
Cuando se afina ?
El tiempo mas efectivo que se tiene para afinar es durante la fase de diseño, obteniendo los máximos beneficios al menor costo. Esto podemos observarlo en las siguientes figuras:
Beneficio Vrs. Tiempo
Costo Vrs. Tiempo 25
25
20
20
o 15 t s o C 10
Producción Desarrollo
5
Diseño
o i c 15 i f e n 10 e B
Diseño
Desarrollo Producción
5
0
0 0
2
4
Tiempo
6
8
0
2
4
Tiempo
6
8
Causas de Problemas de Performance
Causas de Problemas de Performance
Problemas con el diseño y desarrollo
Diseño: Los problemas en el diseño son causados por diseñadores que no
consideran los puntos siguientes: Performance considerado cuando se selecciona una arquitectura Performance considerado cuando se crea el modelo de datos Programas diseñados adecuados para una base de datos relacional Programas diseñados adecuados para la configuración de hardware usada. Programas: Los principales problemas son : Inapropiado uso de índices Uso incorrecto del optimizador Uso incorrecto de la opción procedural
Causas de Problemas de Performance
Problemas con el diseño y desarrollo
Base de Datos: Estos problemas son principalmente causados por DBA’S
ue no consideran los si uientes untos: Uso efectivo de los recursos de la máquina. Uso efectivo de la memoria. Configurar los parámetros de INIT.ORA para evitar contención de redo logs y otros objetos. Sistemas : Algunos problemas ocurren como resultado de: Otros sistemas que afecten al DBMS. Un sistema operativo no afinado. La configuración o tamaño de la máquina que es inadecuada para soportar el DBMS.
Causas de Problemas de Performance
Problemas con Recursos del Sistema Para obtener un mejor performance usted debe conocer cuatro componentes del ambiente de máquina que interactúan y afectan el performance del sistema, éstos componentes son :
Memoria Entrada/Salida en discos y controladores CPU Redes
Causas de Problemas de Performance
Problemas con I/O de disco La carga del disco debe ser distribuida eficientemente. Por ejemplo, cuando las tablas, índices y rollback son creados son asignados a una localidad inicial. Si esta localidad es excedida, Oracle debe asignar extensiones adicionales. El acceso a los datos es más eficiente si las extensiones son conti uas e inde endientes se ún el ti o de se mento.
Problemas con CPU Los problemas de la CPU frecuentemente ocurren cuando muchos procesos están tratando de usar la CPU al mismo tiempo.
Problemas con la Red Los cuellos de botella en la red ocurren cuando la cantidad de datos que necesitan ser transferidos a través de la red exceden la capacidad de la misma.
Diseñando para un mejor Performance
Diseñando para un mejor performance
Afinando el Modelo de Datos
Desnormalizando una Base de Datos
Sacrifica flexibilidad Mejora el tiempo para obtener datos (select) pero desmejora el tiempo de la actualización de datos (update, delete, insert o también llamados operaciones ABC-altas, bajas y cambios-)
Diseñando para un mejor performance
Afinando Índices : Hay una serie de preguntas que deben ser respondidas antes de asignar índices:
Debo indexar la llave primaria de una tabla ? Necesito otros índices ? Como puedo reforzar el uso de índices ?
Diseñando para un mejor performance Afinando Índices :
Debo indexar la llave primaria de una tabla ?
Es única la llave primara ? Los índices refuerzan la unicidad.
Si es así defina un índice (usualmente) Pero si el volumen esperado de la tabla es menor de 250 registros y las columnas no son usadas dentro de los estatutos de un join de SQL, no defina índice.
Diseñando para un mejor performance
Afinando Índices :
Debo indexar la llave foránea de una tabla ?
s a ave or nea usa a para c equear a n egr a referencial ? Es la llave foránea usualmente parte de una clausula Where ?
Si es así defina un índice, si no, no lo defina.
Diseñando para un mejor performance
Afinando Índices :
Necesito otros índices ?
Si la tabla tiene miles de entradas índices extra odrían ayudarlo a evitar largas búsquedas en la tabla Tome en cuenta que el exceso en el uso de índices puede bajar el performance en las sentencias Insert, Delete y Update.
Diseñando para un mejor performance
Afinando Índices :
Como puedo reforzar el uso de índices ?
, programador y el equipo de Control de Calidad.
Diseñando para un mejor performance Constraints: La integridad de los datos toma fuerza a través del uso de constraints, sin embargo estos tienen un costo en performance. Oracle Corporation dice que éste costo es similar a la ejecución de una sentencia SQL en la que el constraint de integridad se traduciría. Existen algunas otras implicaciones de performance que usted debe de ser consciente de usar en un diseño eficaz de constraints.
Diseñando para un mejor performance
Constraints:
Primary Key Constraints: una llave primaria refuerza la
unicidad, es raro que una tabla no requiera un constraint de llave primaria. Al agregar dicho constraint a una tabla se crea un índice, asegúrese de proveer los detalles del tamaño al índice en las es ecificaciones del diseño. Unique Key Constraints: en éste tipo de constraints también se chequea la unicidad, pero permite que las columnas de la llave sean nulas, también se crea un índice. Foreign Key Constraints: chequea que la tabla dependiente (hija) tenga una tupla en la tabla referenciada (padre). Check Constraints: son utilizados en una columna de la tabla para especificar una condición que debe ser cierta. Un caso típico es el caso en el cual una columna FLAG tiene sólo dos valores valido: ON u OFF.
Diseñando para un mejor performance
Triggers: Esta es otra buena opción para el diseño de de aplicaciones, estos son usados a menudo para registros de auditoría. Los triggers a nivel de tupla degradaciones de performance cuando son utilizados inapropiadamente, es importante que mantenga el código de sus triggers simples, tenga cuidado de triggers que realizan actualizaciones en otras tablas que también contienen triggers.
Nota: Los Constraints han sido optimizados para realizar chequeos de integridad de datos. No use un trigger para realizar el trabajo que puede hacer un constraint.
Diseñando para un mejor performance Triggers: Tome nota de las siguientes restricciones: No se puede especificar un trigger en las tablas del diccionario de Datos del DBMS. os r ggers oman e ec o en as que son modificadas en la tabla después de que el trigger ha sido incorporado. Un trigger no puede leer o modificar filas en una tabla que tiene una llave foránea apuntando a la tabla dueña del trigger. Un trigger no puede contener sentencias COMMIT, ROLLBACK ó SAVEPOINT. Un trigger no puede ejecutar sentencias DDL, tal como CREATE TABLE.
Diseñando para un mejor performance Query Performance Parallel Query
DBMS, introducen la opción de Parallel Query, lo cual puede acelerar: La creación de índices. La carga de datos en la base de datos. La consulta de datos
Afinando SQL
Afinando SQL
Pasos estándar para la resolución de SQL
Chequeo de sintaxis (estructura del SQL, paréntesis, Etc..)
Buscar en el shared area
Buscar en el diccionario de datos (Seguridad, Privilegios, Etc..)
Calcular el path de búsqueda (Rule-based o Cost-based)
Salva el plan de ejecución Ejemplo: Las siguientes sentencias SQL no son iguales y no se compartirán en el SGA
SELECT SELECT SELECT SELECT SELECT SELECT FROM WHERE
NAME FROM S_CUSTOMER WHERE ID = 212; NAME FROM S_CUSTOMER WHERE ID = 213; NAME FROM S_CUSTOMER WHERE ID = :b1; NAME FROM s_customer WHERE id = 212; NAME FROM S_CUSTOMER WHERE id =212; NAME S_CUSTOMER id =212 ;
Afinando SQL Consejos para afinar el SQL: Cuando varios programadores están desarrollando una aplicación cada uno tiene su propio estilo, preferencias y tendencias, aun cuando cada uno esta produciendo un código eficaz, su futuro mantenimiento puede darle un verdadero dolor de cabeza. A menudo cuando no se aplican normas en la codificación significa que solo la persona que escribió el código lo puede entender. Antes de iniciar a codificar una aplicación es importante definir un estándar de programación.
Afinando SQL Consejos para afinar el SQL: Usar Alias : El uso de alias en las tablas y la inclusión de los prefijos en todos los nombres de columnas cuando más de una tabla es consultada reducirá el tiempo de análisis de sintaxis y previene errores. Considerando el siguiente ejemplo: SELECT FROM
E.emp_no, name, tax_no, c.comp_code, comp_name company C, Emp E
WHERE
E.comp_code = C.Comp_Code
Es mejor utilizar los Alias como se muestra a continuación: SELECT FROM
E.emp_no, E.name, E.tax_no, C.Comp_Code, C.Comp_name Company C, Emp E
WHERE
E.comp_code = C.comp_code
Afinando SQL Consejos para afinar el SQL: Utilizar bind variables : Se aprovecha mejor el shared area si se utilizan bind variables. Ya que no es lo mismo: SELECT * FROM emp WHERE emp_no = 123; SELECT * FROM emp WHERE emp_no = 987;
(Sharable SQL) SELECT * FROM emp WHERE emp_no = :B1; (Bind value:123) SELECT * FROM emp WHERE emp_no = :B1; (Binde value:987);
El Optimizador de SQL El optimizador de Oracle es un recurso del sistema que está escondido pero es extremadamente importante. Una parte del kernel de Oracle, el optimizador examina cada sentencia SQL que se encuentra en su aplicación y escoje el plan de ejecución optimo, o recupera el path, para la sentencia. El plan de ejecución es la secuencia física de pasos que el RDBMS debe tomar para realizar una operación que usted ha especificado. Para deducir el path de búsqueda optimo, el optimizador considera varias áreas como or e em lo:
Las tablas de la base de datos que su sentencia necesitará accesar Alguna condición que deben satisfacer los datos (la cláusula WHERE) La localización física de la tabla (SQL distribuido)
Optimizadores que existen: Optimizador basado en reglas Optimizador basado en costos
Optimizador basado en reglas El optimizador basado en reglas utiliza un conjunto de reglas de precedencia el cual es manejado por 20 reglas de oro las cuales instruyen al optimizador en como determinar el path de ejecución. Rango 1 2 3 4 5
7 8 9 10 11 12 13 14 15 16 17 18 19 20
Condicion ROWID Cluster join with unique or primary key Hash cluster key with unique or primary key Entire unique concatenated index Unique indexed column
Hash cluster key Entire cluster key Entire non-UNIQUE concatenated index Non-UNIQUE index merge Entire concatenated index Most leading columns of concatenated index indexed column BETWEEN low value an high value or indexed column LIKE "ABC%" (Bounded range) Non-UNIQUE indexed column between low value and high value or indexed column like 'ABC%' (Bounded range) UNIQUE indexed column o constant (Unbounded range) Non-UNIQUE indexed column or constant (unbounded range) Equality on nonindexed MAX or MIN of single indexed columns ORDER BY entire index Full table scans
= Constant = Constant = Constant = Constant = Constant table in the same cluster = Constant = Constant = Constant =lower bound = Constant
=column or constant (sort/merge join)
Optimizador basado en reglas
TABLA PIVOTE Join de dos tablas: Tabla TAB1 de 16,384 registros Tabla TAB2 de 1 registro TABLA TAB2 como PIVOTE SELECT count(*) FROM TAB1, TAB2;
0.96 Segundos
TABLA TAB1 como PIVOTE SELECT count(*) FROM TAB2, TAB1;
26.09 Segundos
Optimizador basado en reglas
TABLA INTERSECCIÓN Join de tres tablas: SELECT
...........
FROM
location L, category C, emp E
WHERE
E.emp_no BETWEEN 1000 AND 2000
AND
E.cat_no = C.cat_no
AND
E.locn
= L.locn
Es mas eficiente de la siguiente manera : SELECT
..........
FROM
emp E, location L, catecory C
WHERE AND AND
E.cat_no
=
C.cat_no
E.locn
=
L.locn
E.emp_no BETWEEN 1000 AND 2000
Optimizador basado en reglas
Competencia de índices
Preferencia por índices únicos Por la precedencia en las reglas, el optimizador basado en la regla siempre va a preferir utilizar la llave única. Suprimiendo el uso de índices Para que una sentencia SQL utilice el índice, las columnas que pertenecen al índice deben estar solos (sin funciones u operaciones que lo anulen) en un lado de la comparación en la cláusula WHERE. Operaciones que anulan un índice: !=, <> NOT IN NOT EXISTS
Optimizador basado en Costos Cuando utilizamos el optimizador basado en costos, podemos tunear manualmente las sentencias SQL, pasando sobre las decisiones del optimizador actual. Incluyendo sus propios hints dentro de la sentencia SQL fuerza a esta sentencia a seguir el path de acceso que usted desea en lugar del calculado por el optimizador actual. SELECT
/*+ hint */ .....
UPDATE
/*+ hint */ .....
DELETE
/*+ hint */ .....
Algunos Hints imporantes :
ALL_ROWS : Optimiza para el mejor rendimiento de acceso a los registros FIRST_ROWS : Siempre escogerá usar un índice sobre un full scan CHOOSE : Fuerza el uso del optimizador basado en costos. RULE : Fuerza el uso del optimizador basado en la regla. FULL : Fuerza al uso de un fulll scan en la tablas. ROWID : Fuerza a una búsqueda por ROWID en la tabla especificada
Optimizador basado en Costos Hints (Continuación)
FULL
USE_CONCAT
HASH
ORDERED
INDEX
USE_NL
INDEX_ASC
USE_MERGE
INDEX_DESC
CACHE
AND_EQUAL
NO_CACHE
PARALEL
NOPARALEL
Optimizador basado en Costos
Cuando los Hints son ignorados ?
Hints mal escritos Inconsistencia
Con tablas Con índices
Identificación valida de la tabla Localización invalida del hint Versiones viejas de PL/SQL (2.0)
Afinando SQL Consejos para afinar el SQL: Uso eficiente de la cláusula WHERE: SELECT
........
FROM
emp E
WHERE
emp_salary > 50000
AND
emp_type = ‘MANAGER’
AND
25 < ( SELECT
COUNT(*)
FROM
emp
WHERE
emp_mgr = E.emp_no)
Es mejor SELECT
........
FROM
emp E
WHERE
25 < ( SELECT
COUNT(*)
FROM
emp
WHERE
emp_mgr = E.emp_no)
AND
emp_salary > 50000
AND
emp_type = ‘MANAGER’
Afinando SQL Consejos para afinar el SQL: Uso eficiente de la cláusula WHERE: USANDO AND’S SIN COMPETENCIA DE INDICES SELECT
........
FROM
emp E
WHERE
25 < ( SELECT
OR
COUNT(*)
FROM
emp
WHERE
em _ _m r = E.em _no) _
(emp_salary > 50000 AND
emp_type = ‘MANAGER’)
Es mejor SELECT
........
FROM
emp E
WHERE
(emp_salary > 50000
AND
emp_type = ‘MANAGER’)
OR
25 < ( SELECT
COUNT(*)
FROM
emp
WHERE
emp_mgr = E.emp_no)
Afinando SQL
USANDO OR’S SIN COMPETENCIA DE INDICES SELECT .... FROM emp E WHERE 25 < ( FROM WHERE _
SELECT COUNT(*) emp emp_mgr = E.emp_no) _
Es mejor SELECT .... FROM emp E WHERE (emp_salary > 50000 AND emp_type = ‘MANAGER’) OR 25 < ( SELECT COUNT(*) FROM emp WHERE emp_mgr = E.emp_no)
= ‘
’
Afinando SQL
Uso de ROWID SELECT ROWID INTO :emp_rowid FROM emp WHERE emp.emp_no = 5643 FOR UPDATE; . . . UPDATE SET WHERE
emp emp.name = ........ ROWID = :emp_rowid;
Afinando SQL
Reduciendo el número de viajes a la Base de Datos
METODO 1
METODO 2 DECLARE CURSOR C1 (E_no NUMBER) IS
SELECT
emp_name,salary,grade
SELECT
FROM
emp
FROM
emp
WHERE
empno = 123;
WHERE
empno = E_no;
emp_name,salary,grade
BEGIN SELECT
emp_name,salary,grade
OPEN C1(123);
FROM
emp
FETCH C1 INTO .........;
WHERE
empno = 567;
CLOSE C1; OPEN C1(567); FETCH C1 INTO .........; CLOSE C1; END;
METODO 3 SELECT
A.emp_name,A.salary,A.grade, B.emp_name,B.salary,B.grade
FROM
emp A, emp B
WHERE
A.emp_no = 123
AND
B.emp_no = 567;
Afinando SQL
Uso de valores null Deshabilitar índices
SELECT
account_name, trans_date, ammount
SELECT
account_name, trans_date, ammount
FROM
transaction
FROM
transaction
WHERE
substr(account_name,1,7) = ‘CAPITAL’;
WHERE
account_name LIKE ‘CAPITAL%’;
SELECT
account_name, trans_date, ammount
SELECT
account_name, trans_date, ammount
FROM
transaction
FROM
transaction
WHERE
amount != 0;
WHERE
amount > 0;
SELECT
account_name, trans_date, ammount
SELECT
account_name, trans_date, ammount
FROM
transaction
FROM
transaction
WHERE
TRUNC(trans_date) = TRUNC(SYSDATE)
WHERE
trans_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE)+0.99999;
SELECT
account_name, trans_date, ammount
SELECT
account_name, trans_date, ammount
FROM
transaction
FROM
transaction
WHERE
account_name || account_type = ‘AMEXA’
WHERE
AMEX’ account_name = ‘
AND
account_type = ‘ A ’
SELECT
account_name, trans_date, ammount
SELECT
FROM
transaction
FROM
account_name, trans_date, ammount transaction
WHERE
amount + 3000 < 5000
WHERE
amount < 2000
SELECT
account_name, trans_date, ammount
SELECT
account_name, trans_date, ammount
FROM
transaction
FROM
transaction
WHERE
account_name = NVL(:acc_name,account_name );
WHERE
account_name LIKE NVL(:acc_name,’%’);
Afinando SQL
Full scan via Parallel Query CREATE TABLE XXXXX PARALLEL (DEGREE N);
SELECT /*+ FULL(H) PARALLEL(H,8) */ H.emp_no, lookup_emp(H.emp_no), H.hist _t
e
looku _hist _t
COUNT(*) FROM
emp_history H
GROUP
BY H.emp_no, H.Hist_Type;
e H.hist _ t
e
Afinando SQL Joins en lugar de EXISTS EXISTS en lugar de JOINS EXISTS en lugar de DISTINCT en ugar e IN o UNION en lugar de OR
Herramientas de Diagnóstico
Herramientas de Diagnóstico ANALIZE EXPLAIN PLAN SQL_TRACE TKPROF
Herramientas de Diagnóstico
ANALIZE Los objetos de la base de datos necesitan ser analizados para tener estadísticas disponibles para el optimizador basado en costos. La sintaxis de la sentencia para analizar es la siguiente: ANALYZE TABLE XXX COMPUTE STATISTICS INDEX ESTIMATE STATISTICS
Herramientas de Diagnóstico
EXPLAIN PLAN El comando EXPLAIN PLAN despliega el plan de ejecución escogido por el optimizador de ORACLE para las cláusulas SELECT, UPDATE, INSERT Y DELETE. El plan de ejecución es la sentencia de o eraciones ue ORACLE realiza para ejecutar las sentencias. Examinando el plan de ejecución usted puede ver como ORACLE ejecuta sus sentencias SQL. Antes de ejecutar el EXPLAIN PLAN, debe existir una tabla de salida llamada PLAN_TABLE. Usted debe correr el archivo ULTXPLAN.SQL para crear esta tabla.
Herramientas de Diagnóstico Operaciones y Opciones producidas por el EXPLAIN PLAN OPERACION ANDEQUAL
OPCION
CONNECT BY CONCATENATION COUNT STOPKEY
FILTER FIRSTROW
DESCRIPCION Una operación que acepta multiples sets de ROWID y regresa la intersección de los sets, eliminando duplicados. Un retorno de filas en un orden jerárquico para una consulta que contenga una cláusula CONNECT BY Una operación que acepta múltiples sets de filas y regresa la unión, todos los sets. Una operación que cuenta el Número de filas seleccionadas de la tabla. Una operación que cuenta donde el número de filas retornadas es limitado por la expresión ROWNUM en la cláusula WHERE. Una operación que acepta un set de filas, elimina algunas de ellas, y regresa el resto. Un retorno de sólo la primera fila seleccionada por el query.
Herramientas de Diagnóstico Operaciones y Opciones producidas por el EXPLAIN PLAN OPERACION FOR UPDATE INDEX
OPCION
UNIQUE SCAN RANGE SCAN RANGE SCAN DESCENDING
INTERSECTION MERGE JOIN+
OUTER CONNECT BY MINUS
DESCRIPCION Una operación que devuelve y busca las filas seleccionadas por el query que contiene una cláusula FOR UPDATE Un retorno de un simple ROWID de un índice Un retorno de una o más ROWIDs de un índice. Valores indexados son buscados en orden ascendente Un retorno de una o más ROWIDs de un índice. Valores indexados son buscados en orden descendente Una operación que acepta dos sets de filas y regresa la intersección de los sets, eliminando duplicados Una operación que acepta dos sets de filas, cada una ordenada por el valor específico, combina cada fila de un set con la fila correspondiente del otro, y regresa el resultado. Una operación de merge join para ejecutar una sentencia outer join. Un retorno de fila en un orden jerárquico por un query que contenga una cláusula CONNECT BY.
Una operación que acepta dos sets de filas y retorna filas que aparecen en el primer set pero no en el segundo, eliminando duplicados
Herramientas de Diagnóstico Operaciones y Opciones producidas por el EXPLAIN PLAN OPERACION NESTED LOOPS+
OPCION
NESTED LOOPS+ PROJECTION REMOTE SEQUENCE
OUTER
SORT
AGGREGATE
DESCRIPCION Una operación que acepta dos sets de filas, un set de salida y un set de entrada. Oracle compara cada fila del set de salida con cada fila del set de entrada y regresa aquellas filas que satisfacen una condición. Un operación LOOP para ejecutar una sentencia outer join Una o eración interna Un retorno de datos de una base de datos remota
UNIQUE GROUP BY JOIN ORDER BY
Una operación que involucra acceso a valores de una secuencia Un retorno de una simple fila que es el resultado de aplicar una función de grupo a un grupo de filas seleccionadas Una operación que ordena un set de filas para eliminar duplicados Una operación que ordena un set de filas en grupos para una consulta con una cláusula GROUP BY Una operación que ordena un set de filas antes de una operación merge join Una operación que ordena un set de filas para un query con una cláusula OREDER BY
Herramientas de Diagnóstico Operaciones y Opciones producidas por el EXPLAIN PLAN OPERACION TABLE ACCESS*
UNION VIEW
OPCION FULL CLUSTER
DESCRIPCION Un retorno de todas las filas de una tabla
HASH
Un retorno de filas de una tabla basada en un valor de la clave del cluster indexado Un retorno de filas de uana tabla basada en un valor de la
BY ROWID
Un retorno de una fila de una basada en sus ROWID Una operación que acepta dos sets de filas y regresa la unión de los sets, eliminando duplicados Una operación que ejecuta una consulta a una vista y entonces retorna las filas resultantes de otra operación
Herramientas de Diagnóstico EXPLAIN PLAN Formato anidado para la salida del EXPLAIN PLAN: Accept a1 SELECT LPAD(‘’,2*(LEVEL-1))||operation||’’|| options||’’||object_name||’’||DECODE(id,0,’Cost=‘||position) “Query Plan” FROM
plan_table
START
WITH id=0
AND
statement_id like &a1
CONNECT BY PRIOR id = parent_id AND statement_id like &a1;
Herramientas de Diagnóstico SQL_TRACE Utilidad que escribe un archivo de rastro conteniendo estadísticas de performance.
Parámetros a inicializar en el init.ora con SQL_TRACE
SQL _ TRACE USER_DUMP_DEST TIMED_STATISTICS MAX_DUMP_FILE_SIZE
TRUE Directorio TRUE number
Como habilitar el SQL_TRACE :
SQL*Plus Alter session set SQL_TRACE TRUE;
Herramientas de Diagnóstico TKPROF Utilidad que traslada a información legible el archivo generado por SQL_TRACE, mostrando también el plan de ejecución de la sentencia. TKPROF tracefile listfile SORT = arameters [EXPLAIN = usr/pass ]
tracefile = Nombre del archivo que contiene las estadísticas generadas Listfile = Nombre del archivo de salida del TKPROF
METODOLOGIA DE AFINAMIENTO DE SISTEMAS
METODOLOGIA DE AFINAMIENTO DE SISTEMAS La Metodología de afinamiento de un sistema se basará en los sigu siguie ient ntes es cuat cuatro ro paso pasos: s: 1. 2. 3. 4.
Iden Identitififica caci ción ón de prob proble lema mass Análisis Acciones Acciones Correct Correctiva ivass Pres Pr esen enta taci ción ón de Resu Resultltad ados os
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Identificación de problemas Es necesario identificar los problemas y cuantificarlos para tener una referencia inicial sobre el estado actual del sistema, para esto se usan los denominados diagramas de Pareto en los cuales se ve de manera gráfica cuales son los problemas que son mas frecuentes o puntos críticos en el sistema y que pueden ser la causa del bajo desempeño, como resultado se deberá saber cual es el estado actual del sistema.
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Identificación de problemas Para esta tarea se deberán seguir los siguientes pasos : Identi Identific ficar ar posibl posibles es proble problemas mas
1.
2. 3. 4.
Fragmentación Mala Mala utili utiliza zaci ción ón del del espa espaci cioo ocup ocupad adoo Uso Uso inad inadec ecua uado do de memo memori riaa Aplica Aplicacio ciones nes (SQL) (SQL) crític críticas as Acce Acceso soss sin sin índi índice cess Uso Uso inad inadec ecua uado do de la inte integr grid idad ad refe refere renc ncia iall (loc (locks ks o full full-S -Sca cans ns))
Recole Recolecta ctarr inform informaci ación ón median mediante te medicio mediciones nes Elab Elabor orar ar diag diagra rama ma de Pare Pareto to Resumen.
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Análisis El resultado de un proceso puede atribuirse a una multitud de factores, y es posible encontrar la relación causa-efecto de esos factores. Podemos determinar la estructura de una relación múltiple de causaefecto observándola sistemáticamente. Es difícil solucionar problemas complicados sin tener en cuenta esta estructura, la cual consta de una cadena de causas y efectos, y el método para expresar esto en forma sencilla y fácil es un diagrama de causa-efecto. El resultado final del análisis será un documento de conclusiones del sistema y un cronograma de actividades que se deberán seguir para afinar el sistema. Se deberán seguir los siguientes pasos: - Identificación de causas y efectos - Priorizar tareas - Conclusiones y Recomendaciones - Elaborar Cronograma
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Análisis: Identificación de causas y efectos Se buscan todas las causas posibles que puedan afectar a las . han identificado de manera general un grupo de causas que hacen reflejar un bajo rendimiento en un sistema, sin embargo se podrá conocer con mayor detalle otra lista de causas al analizar el sistema con mayor profundidad.
Diagrama Causa-efecto Uso inadecuado de memoria
Acceso a disco (I/O) Array size
Tamaño de bloques de memoria
Indices Tablas Datafiles
Tamaño de bloques de datos
Fragmentación
Distribución de datos
Tamaño del SGA
Diccionario de datos Pagineo
Normalización/ Denomarlización
Bajo rendimiento en un sistema Por integridad referencial
Indices
Bind variables Consideraciones de acceso concurrente
Mal uso de clusters
Bloqueos
Acceso redundantes
Compartido
Ciclos que causen redundacia
Bloqueos por chequeo de Integridad Referencial sin indices
Tabla pivote Mal uso del o timizador
No uso de indices Hints
Diseño Programación
Alias
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Análisis: Priorizar tareas Una vez completa la información sobre las causas y efectos . Todos los factores no se relacionan necesariamente en forma estrecha con la característica, de manera que se marcarán esos factores que parecen tener un efecto particularmente significativo sobre la característica y se les asignará la prioridad correspondiente.
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Análisis: Conclusiones y Recomendaciones
críticos del sistema recomendándose las técnicas posibles a usarse que incrementen el rendimiento del sistema y el tiempo y recursos que tomará cada mejora.
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Análisis: Elaborar Cronograma Se detallarán todas las actividades en orden de mas alta rioridad a la más baja, especificando la fecha de inicio y finalización de cada una.
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Acciones correctivas Se ejecutarán todas las actividades detalladas en el cronograma, acompa a as e su respec va ocumen ac n on e se explicarán las técnicas utilizadas para su corrección. Además se recabará toda la información del desempeño de cada factor identificado con anterioridad (Punto 1) como problema
METODOLOGIA DE AFINAMIENTO DE SISTEMAS Presentación de Resultados Basándose en los datos y el diagrama inicial se elaborará un observarán el impacto de las mejoras implementadas.
