optimización y alto rendimiento (met. de afinamiento de sistemas)
TRANSCRIPT
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
1/69
OPTIMIZACION Y ALTO RENDIMIENTO
Curso Manejo e Implementación de Archivos
Cat. Ing. Alvaro Díaz A. (Secc. A+)
Cat. Ing. Oscar Paz (Secc. A-)
Universidad de San Carlos de Guatemala
Segundo Semestre 2015
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
2/69
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
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
3/69
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
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
4/69
CONTENIDO
AFINANDO SQL Estándares de SQL
Utilizar bind-variables Utilizar alias
El Optimizador de SQL
Afinamiento de SQL
Sentido común en SQL HERRAMIENTAS DE DIAGNÓSTICO
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
5/69
CONTENIDO
METODOLOGIA DE AFINAMIENTO Inspección Inicial
Identificar posibles problemas
Recolectar información mediante mediciones
Elaborar diagramas Resumen
Análisis
Identificación de causas y efectos
Priorizar tareas
Conclusiones y Recomendaciones
Elaborar Cronogramas
Acciones Correctivas
Presentación de Resultados
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
6/69
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 mantenimientotanto de software como hardware.
Beneficios Humanos
Incrementa la productividad, a la vez que satisface a los clientes de la organización Quién afina ?
El diseñador debe comunicar el diseño del sistema para que cualquier personapueda entender el flujo de datos en una aplicación.
Los desarrolladores de aplicación deben comunicar las estrategias deimplementación que escogen y aquellos módulos y sentencias SQL pueden serrápida y fácilmente identificadas durante la tarea de afinamiento.
El administrador de la base de datos debe monitorear y documentar lasactividades del sistema cuidadosamente y aquellos rendimientos inusuales delsistema que pueden ser identificados y corregidos.
Los administradores de hardware y software deben documentar y comunicar lasconfiguraciones del hardware y software del sistema para que cualquierapueda diseñar y administrar sistemas efectivamente.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
7/69
INTRODUCCION Cuando se afina ?
El tiempo mas efectivo que se tiene para afinar es durante la fasede diseño, obteniendo los máximos beneficios al menor costo.Esto podemos observarlo en las siguientes figuras:
Costo Vrs. Tiempo
Producción
DesarrolloDiseño
0
5
10
15
20
25
0 2 4 6 8
Tiempo
C o s t o
Beneficio Vrs. Tiempo
Diseño
Desarrollo
Producción
0
5
10
15
20
25
0 2 4 6 8
Tiempo
B e n e f i c i o
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
8/69
Causas de Problemas de Performance
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
9/69
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 noconsideran 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
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
10/69
Causas de Problemas de Performance
Problemas con el diseño y desarrollo
Base de Datos: Estos problemas son principalmente causados por DBA’Sque no consideran los siguientes puntos:
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 yotros 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 elDBMS.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
11/69
Causas de Problemas de Performance
Problemas con Recursos del Sistema
Para obtener un mejor performance usted debe conocer cuatro componentesdel ambiente de máquina que interactúan y afectan el performance delsistema, éstos componentes son :
Memoria
Entrada/Salida en discos y controladores
CPU
Redes
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
12/69
Causas de Problemas de Performance
Problemas con I/O de discoLa carga del disco debe ser distribuida eficientemente. Por
ejemplo, cuando las tablas, índices y rollback son creadosson asignados a una localidad inicial. Si esta localidad es
excedida, Oracle debe asignar extensiones adicionales. Elacceso a los datos es más eficiente si las extensiones soncontiguas e independientes según el tipo de segmento.
Problemas con CPULos problemas de la CPU frecuentemente ocurren cuandomuchos procesos están tratando de usar la CPU al mismotiempo.
Problemas con la RedLos cuellos de botella en la red ocurren cuando la cantidadde datos que necesitan ser transferidos a través de la redexceden la capacidad de la misma.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
13/69
Diseñando para un mejor Performance
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
14/69
Diseñando para un mejor performance
Afinando el Modelo de Datos
Desnormalizando una Base de DatosHace la codificación mas compleja
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 operacionesABC-altas, bajas y cambios-)
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
15/69
Diseñando para un mejor performance
Afinando Índices : Hay una serie de preguntas que debenser respondidas antes de asignar índices:
Debo indexar la llave primaria de una tabla ?
Debo indexar la llave foránea de una tabla ?
Necesito otros índices ?
Como puedo reforzar el uso de índices ?
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
16/69
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 250registros y las columnas no son usadas dentro de losestatutos de un join de SQL, no defina índice.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
17/69
Diseñando para un mejor performance
Afinando Índices :
Debo indexar la llave foránea de una tabla ?
Es la llave foránea usada para chequear la integridadreferencial ?
Es la llave foránea usualmente parte de una clausula Where ?
Si es así defina un índice, si no, no lo defina.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
18/69
Diseñando para un mejor performance
Afinando Índices :
Necesito otros índices ?
Si la tabla tiene miles de entradas, índices extra podríanayudarlo a evitar largas búsquedas en la tabla
Tome en cuenta que el exceso en el uso de índices puedebajar el performance en las sentencias Insert, Delete yUpdate.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
19/69
Diseñando para un mejor performance
Afinando Índices :
Como puedo reforzar el uso de índices ?
Coordinando el uso y definición de índices con el DBA, elprogramador y el equipo de Control de Calidad.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
20/69
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 uncosto en performance. Oracle Corporation diceque éste costo es similar a la ejecución de unasentencia SQL en la que el constraint deintegridad se traduciría.
Existen algunas otras implicaciones de performanceque usted debe de ser consciente de usar en undiseño eficaz de constraints.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
21/69
Diseñando para un mejor performance
Constraints: Primary Key Constraints: una llave primaria refuerza la
unicidad, es raro que una tabla no requiera un constraintde llave primaria. Al agregar dicho constraint a una tablase crea un índice, asegúrese de proveer los detalles del
tamaño al índice en las especificaciones del diseño. Unique Key Constraints: en éste tipo de constraints
también se chequea la unicidad, pero permite que lascolumnas de la llave sean nulas, también se crea uníndice.
Foreign Key Constraints: chequea que la tabladependiente (hija) tenga una tupla en la tabla referenciada(padre).
Check Constraints: son utilizados en una columna de latabla para especificar una condición que debe ser cierta.
Un caso típico es el caso en el cual una columna FLAG tienesólo dos valores valido: ON u OFF.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
22/69
Diseñando para un mejor performance
Triggers:
Esta es otra buena opción para el diseño de deaplicaciones, estos son usados a menudo pararegistros de auditoría. Los triggers a nivel de tuplahan sido conocidos como la causa de severasdegradaciones de performance cuando sonutilizados inapropiadamente, es importante quemantenga el código de sus triggers simples, tenga
cuidado de triggers que realizan actualizaciones enotras tablas que también contienen triggers.
Nota: Los Constraints han sido optimizados para realizar chequeosde integridad de datos. No use un trigger para realizar el
trabajo que puede hacer un constraint.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
23/69
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. Los triggers toman efecto en filas que son
modificadas en la tabla después de que el triggerha sido incorporado.
Un trigger no puede leer o modificar filas en unatabla que tiene una llave foránea apuntando a latabla dueña del trigger.
Un trigger no puede contener sentencias COMMIT,ROLLBACK ó SAVEPOINT.
Un trigger no puede ejecutar sentencias DDL, talcomo CREATE TABLE.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
24/69
Diseñando para un mejor
performance Query Performance
Parallel Query
Versiones más recientes de los más conocidosDBMS, introducen la opción de Parallel Query, locual puede acelerar: La creación de índices.
La carga de datos en la base de datos.
La consulta de datos
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
25/69
Afinando SQL
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
26/69
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 NAME FROM S_CUSTOMER WHERE ID = 212;
SELECT NAME FROM S_CUSTOMER WHERE ID = 213;
SELECT NAME FROM S_CUSTOMER WHERE ID = :b1;
SELECT NAME FROM s_customer WHERE id = 212;
SELECT NAME FROM S_CUSTOMER WHERE id =212;
SELECT NAME
FROM S_CUSTOMER
WHERE id =212;
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
27/69
Afinando SQL
Consejos para afinar el SQL:
Cuando varios programadores están desarrollando una aplicacióncada uno tiene su propio estilo, preferencias y tendencias, auncuando cada uno esta produciendo un código eficaz, su futuromantenimiento puede darle un verdadero dolor de cabeza.
A menudo cuando no se aplican normas en la codificación significaque solo la persona que escribió el código lo puede entender.
Antes de iniciar a codificar una aplicación es importante definirun estándar de programación.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
28/69
Afinando SQLConsejos 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 consultadareducirá el tiempo de análisis de sintaxis y previene errores.
Considerando el siguiente ejemplo:
SELECT E.emp_no, name, tax_no, c.comp_code, comp_name
FROM company C,
Emp E
WHERE E.comp_code = C.Comp_Code
Es mejor utilizar los Alias como se muestra a continuación:
SELECT E.emp_no, E.name, E.tax_no, C.Comp_Code, C.Comp_name
FROM Company C,
Emp E
WHERE E.comp_code = C.comp_code
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
29/69
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:
(Non-Sharable SQL)
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);
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
30/69
El Optimizador de SQLEl 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 quese encuentra en su aplicación y escoje el plan de ejecuciónoptimo, o recupera el path, para la sentencia. El plan deejecución es la secuencia física de pasos que el RDBMS debetomar para realizar una operación que usted ha especificado.
Para deducir el path de búsqueda optimo, el optimizador considera
varias áreas como por ejemplo:
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
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
31/69
Optimizador basado en reglasEl 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 Condicion
1 ROWID = Constant
2 Cluster join with unique or primary key = Constant
3 Hash cluster key with unique or primary key = Constant
4 Entire unique concatenated index = Constant
5 Unique indexed column = Constant6 Entire cluster key =Corresponding cluster key of other
table in the same cluster
7 Hash cluster key = Constant
8 Entire cluster key = Constant
9 Entire non-UNIQUE concatenated index = Constant
10 Non-UNIQUE index merge
11 Entire concatenated index =lower bound
12 Most leading columns of concatenated index = Constant
13 indexed column BETWEEN low value an high value or indexed column LIKE
"ABC%" (Bounded range)14 Non-UNIQUE indexed column between low value and high value or indexed
column like 'ABC%' (Bounded range)
15 UNIQUE indexed column o constant (Unbounded range)
16 Non-UNIQUE indexed column or constant (unbounded range)
17 Equality on nonindexed =column or constant (sort/merge join)
18 MAX or MIN of single indexed columns
19 ORDER BY ent ire index
20 Full table scans
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
32/69
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
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
33/69
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_noAND E.locn = L.locn
Es mas eficiente de la siguiente manera :
SELECT ..........
FROM emp E,
location L,
catecory C
WHERE E.cat_no = C.cat_no
AND E.locn = L.locn
AND E.emp_no BETWEEN 1000 AND 2000
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
34/69
Optimizador basado en reglas Competencia de índices
Preferencia por índices únicos
Por la precedencia en las reglas, el optimizador basado en la regla siempreva 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 unlado de la comparación en la cláusula WHERE.
Operaciones que anulan un índice:
!=,
NOT IN
NOT EXISTS
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
35/69
Optimizador basado en CostosCuando 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 lasentencia SQL fuerza a esta sentencia a seguir el path de acceso queusted 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
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
36/69
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
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
37/69
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)
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
38/69
Afinando SQLConsejos 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’
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
39/69
Afinando SQLConsejos 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 COUNT(*)
FROM emp
WHERE emp_mgr = E.emp_no)
OR (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)
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
40/69
Afinando SQL
USANDO OR’S SIN COMPETENCIA DE INDICES
SELECT ....
FROM emp E
WHERE 25 < ( SELECT COUNT(*)
FROM emp
WHERE emp_mgr = E.emp_no)
OR (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)
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
41/69
Afinando SQL
Uso de ROWID
SELECT ROWID
INTO :emp_rowid
FROM emp
WHERE emp.emp_no = 5643
FOR UPDATE;
.
.
.
UPDATE emp
SET emp.name = ........
WHERE ROWID = :emp_rowid;
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
42/69
Afinando SQL
Reduciendo el número de viajes a la Base de Datos
METODO 1
SELECT emp_name,salary,grade
FROM emp
WHERE empno = 123;
SELECT emp_name,salary,grade
FROM emp
WHERE empno = 567;
METODO 2
DECLARE CURSOR C1 (E_no NUMBER) IS
SELECT emp_name,salary,grade
FROM emp
WHERE empno = E_no;
BEGIN
OPEN C1(123);
FETCH C1 INTO .........;
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;
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
43/69
Afinando SQL
Uso de valores null
Deshabilitar índices
SELECT account_name, trans_date, ammount
FROM transaction
WHERE substr(account_name,1,7) = ‘CAPITAL’;
SELECT account_name, trans_date, ammount
FROM transaction
WHERE account_name LIKE ‘CAPITAL%’;
SELECT account_name, trans_date, ammount
FROM transaction
WHERE amount != 0;
SELECT account_name, trans_date, ammount
FROM transaction
WHERE amount > 0;
SELECT account_name, trans_date, ammount
FROM transaction
WHERE TRUNC(trans_date) = TRUNC(SYSDATE)
SELECT account_name, trans_date, ammount
FROM transaction
WHERE trans_date BETWEEN TRUNC(SYSDATE)
AND TRUNC(SYSDATE)+0.99999;
SELECT account_name, trans_date, ammount
FROM transaction
WHERE account_name || account_type = ‘AMEXA’
SELECT account_name, trans_date, ammount
FROM transaction
WHERE account_name = ‘ AMEX’
AND account_type = ‘ A ’
SELECT account_name, trans_date, ammount
FROM transaction
WHERE amount + 3000 < 5000
SELECT account_name, trans_date, ammount
FROM transaction
WHERE amount < 2000
SELECT account_name, trans_date, ammount
FROM transaction
WHERE account_name = NVL(:acc_name,account_name);
SELECT account_name, trans_date, ammount
FROM transaction
WHERE account_name LIKE NVL(:acc_name,’%’);
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
44/69
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_type, lookup_hist_type(H.hist_type),
COUNT(*)
FROM emp_history H
GROUP BY H.emp_no, H.Hist_Type;
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
45/69
Afinando SQL
Joins en lugar de EXISTS
EXISTS en lugar de JOINS
EXISTS en lugar de DISTINCT NO EXISTS en lugar de NOT IN
IN o UNION en lugar de OR
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
46/69
Herramientas de Diagnóstico
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
47/69
Herramientas de Diagnóstico
ANALIZE
EXPLAIN PLAN
SQL_TRACE
TKPROF
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
48/69
Herramientas de Diagnóstico
ANALIZELos objetos de la base de datos necesitan ser analizadospara tener estadísticas disponibles para eloptimizador basado en costos.
La sintaxis de la sentencia para analizar es la siguiente:
ANALYZE
TABLE XXX COMPUTE STATISTICS
INDEX ESTIMATE STATISTICS
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
49/69
Herramientas de Diagnóstico
EXPLAIN PLANEl comando EXPLAIN PLAN despliega el plan deejecución escogido por el optimizador de ORACLEpara las cláusulas SELECT, UPDATE, INSERT YDELETE. El plan de ejecución es la sentencia de
operaciones que ORACLE realiza para ejecutar lassentencias. Examinando el plan de ejecuciónusted puede ver como ORACLE ejecuta sussentencias SQL.
Antes de ejecutar el EXPLAIN PLAN, debe existiruna tabla de salida llamada PLAN_TABLE. Usteddebe correr el archivo ULTXPLAN.SQL para crearesta tabla.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
50/69
Herramientas de DiagnósticoOperaciones y Opciones producidas por el EXPLAIN
PLAN
OPERACION OPCION DESCRIPCION
AND EQUAL Una operación que acepta multiples sets de ROWID y regresala intersección de los sets, eliminando duplicados.
CONNECT BY Un retorno de filas en un orden jerárquico para una consultaque contenga una cláusula CONNECT BY
CONCATENATION Una operación que acepta múltiples sets de filas y regresa launión, todos los sets.
COUNT Una operación que cuenta el Número de filas seleccionadas dela tabla.
STOPKEY Una operación que cuenta donde el número de filas retornadases limitado por la expresión ROWNUM en la cláusula
WHERE.FILTER Una operación que acepta un set de filas, elimina algunas de
ellas, y regresa el resto.
FIRST ROW Un retorno de sólo la primera fila seleccionada por el query.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
51/69
Herramientas de DiagnósticoOperaciones y Opciones producidas por el EXPLAIN
PLANOPERACION OPCION DESCRIPCION
FOR UPDATE Una operación que devuelve y busca las filas seleccionadas por el query que contiene una cláusula FOR UPDATE
INDEX UNIQUE SCAN Un retorno de un simple ROWID de un índice
RANGE SCAN Un retorno de una o más ROWIDs de un índice. Valoresindexados son buscados en orden ascendente
RANGE SCAN
DESCENDING
Un retorno de una o más ROWIDs de un índice. Valores
indexados son buscados en orden descendente
INTERSECTION Una operación que acepta dos sets de filas y regresa laintersección de los sets, eliminando duplicados
MERGE JOIN+ 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.
OUTER Una operación de merge join para ejecutar una sentencia outer
join.
CONNECT BY Un retorno de fila en un orden jerárquico por un query quecontenga una cláusula CONNECT BY.
MINUS 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
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
52/69
Herramientas de DiagnósticoOperaciones y Opciones producidas por el EXPLAIN
PLANOPERACION OPCION DESCRIPCION
NESTED LOOPS+ Una operación que acepta dos sets de filas, un set de salida yun 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.
NESTED LOOPS+ OUTER Un operación LOOP para ejecutar una sentencia outer join
PROJECTION Una operación interna
REMOTE Un retorno de datos de una base de datos remota
SEQUENCE Una operación que involucra acceso a valores de unasecuencia
SORT AGGREGATE Un retorno de una simple fila que es el resultado de aplicar una función de grupo a un grupo de filas seleccionadas
UNIQUE Una operación que ordena un set de filas para eliminar duplicados
GROUP BY Una operación que ordena un set de filas en grupos para unaconsulta con una cláusula GROUP BY
JOIN Una operación que ordena un set de filas antes de unaoperación merge join
ORDER BY Una operación que ordena un set de filas para un query conuna cláusula OREDER BY
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
53/69
Herramientas de DiagnósticoOperaciones y Opciones producidas por el EXPLAIN
PLAN
OPERACION OPCION DESCRIPCION
TABLE ACCESS* FULL Un retorno de todas las filas de una tabla
CLUSTER Un retorno de filas de una tabla basada en un valor de la clavedel cluster indexado
HASHUn retorno de filas de uana tabla basada en un valor de la
clave del hash cluster
BY ROWID Un retorno de una fila de una basada en sus ROWID
UNION Una operación que acepta dos sets de filas y regresa la uniónde los sets, eliminando duplicados
VIEW Una operación que ejecuta una consulta a una vista y entoncesretorna las filas resultantes de otra operación
ó
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
54/69
Herramientas de Diagnóstico
EXPLAIN PLANFormato 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;
i d Di ó i
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
55/69
Herramientas de Diagnóstico
SQL_TRACEUtilidad que escribe un archivo de rastro conteniendo estadísticas de
performance.
Parámetros a inicializar en el init.ora con SQL_TRACE
SQL_TRACEUSER_DUMP_DEST
TIMED_STATISTICS
MAX_DUMP_FILE_SIZE
TRUEDirectorio
TRUE
number
Como habilitar el SQL_TRACE :
SQL*Plus Alter session set SQL_TRACE TRUE;
H i d Di ó i
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
56/69
Herramientas de Diagnóstico
TKPROFUtilidad que traslada a información legible el archivo generado por
SQL_TRACE, mostrando también el plan de ejecución de lasentencia.
TKPROF tracefile listfile [SORT = parameters]
[EXPLAIN = usr/pass]
tracefile = Nombre del archivo que contiene las estadísticas generadas
Listfile = Nombre del archivo de salida del TKPROF
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
57/69
METODOLOGIA DE AFINAMIENTO DE
SISTEMAS
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
58/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
La Metodología de afinamiento de un sistema se basará en lossiguientes cuatro pasos:
1. Identificación de problemas2. Análisis
3. Acciones Correctivas
4. Presentación de Resultados
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
59/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
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 enlos cuales se ve de manera gráfica cuales son los problemasque son mas frecuentes o puntos críticos en el sistema yque pueden ser la causa del bajo desempeño, comoresultado se deberá saber cual es el estado actual delsistema.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
60/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
Identificación de problemas
Para esta tarea se deberán seguir los siguientes pasos :
1. Identificar posibles problemas
Fragmentación
Mala utilización del espacio ocupado
Uso inadecuado de memoria
Aplicaciones (SQL) críticas
Accesos sin índices
Uso inadecuado de la integridad referencial (locks o full-Scans)2. Recolectar información mediante mediciones
3. Elaborar diagrama de Pareto
4. Resumen.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
61/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
Análisis
El resultado de un proceso puede atribuirse a una multitud defactores, y es posible encontrar la relación causa-efecto de esosfactores. Podemos determinar la estructura de una relación múltiple
de causa-efecto observándola sistemáticamente. Es difícil solucionarproblemas complicados sin tener en cuenta esta estructura, la cualconsta de una cadena de causas y efectos, y el método para expresaresto en forma sencilla y fácil es un diagrama de causa-efecto. Elresultado final del análisis será un documento de conclusiones delsistema y un cronograma de actividades que se deberán seguir paraafinar el sistema. Se deberán seguir los siguientes pasos:
- Identificación de causas y efectos- Priorizar tareas- Conclusiones y Recomendaciones- Elaborar Cronograma
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
62/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
Análisis: Identificación de causas y efectos
Se buscan todas las causas posibles que puedan afectar a lascaracterísticas de rendimiento del sistema. Como base sehan identificado de manera general un grupo de causas quehacen reflejar un bajo rendimiento en un sistema, sinembargo se podrá conocer con mayor detalle otra lista decausas al analizar el sistema con mayor profundidad.
DiagramaC f t
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
63/69
Uso inadecuado de memoria
Bajo
rendimiento enun sistema
Acceso a disco (I/O)
Indices Tablas
Datafiles
Fragmentación
Diccionario de
datos
Tamaño de
bloques de datos
Distribución de
datos
Pagineo
Array size
Tamaño
del SGA
Tamaño de bloques de
memoria
Normalización/
Denomarlización
Mal uso de
clusters
Ciclos que causen
redundacia
Diseño
Indices
Consideraciones
de acceso
concurrente
Bloqueos por chequeo
de Integridad
Referencial sin indices
Programación
Por integridad
referencial
Bloqueos
Compartido
No uso de indices
Bind variables
Acceso
redundantes
Tabla pivote
Mal uso del
optimizador
Hints Alias
Causa-efecto
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
64/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
Análisis: Priorizar tareas
Una vez completa la información sobre las causas y efectos
el paso siguiente es asignar la importancia de cada factor.Todos los factores no se relacionan necesariamente enforma estrecha con la característica, de manera que semarcarán esos factores que parecen tener un efectoparticularmente significativo sobre la característica y se lesasignará la prioridad correspondiente.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
65/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
Análisis: Conclusiones y Recomendaciones
Se elaborará un documento donde se resumen los factorescríticos del sistema recomendándose las técnicas posibles ausarse que incrementen el rendimiento del sistema y eltiempo y recursos que tomará cada mejora.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
66/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
Análisis: Elaborar Cronograma
Se detallarán todas las actividades en orden de mas alta
prioridad a la más baja, especificando la fecha de inicio yfinalización de cada una.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
67/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
Acciones correctivas
Se ejecutarán todas las actividades detalladas en el cronograma,
acompañadas de su respectiva documentación donde seexplicarán las técnicas utilizadas para su corrección.Además se recabará toda la información del desempeño decada factor identificado con anterioridad (Punto 1) comoproblema
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
68/69
METODOLOGIA DE AFINAMIENTO DESISTEMAS
Presentación de Resultados
Basándose en los datos y el diagrama inicial se elaborará unnuevo diagrama de Pareto para su comparación donde se
observarán el impacto de las mejoras implementadas.
-
8/20/2019 Optimización y Alto Rendimiento (Met. de Afinamiento de Sistemas)
69/69
REFERENCIAS
Oracle Performance Tuning,Segunda Edición
Gurry&Corrigan, Editorial O’ReillyOracle Performance Tuning
Tips&Techniques
Richard J. Niemiec Editoral Osborne McGrawHill
Presentación compartida porBlanco – Silva Consultores.