TUNING

1
TUNING
2
Definiendo el tuning (afinamiento)

• El ajuste de bases de datos debe ser un proceso
  proactivo encaminado a detectar posibles cuellos
  de botella en el gestor de bases de datos así
  como lograr que los tiempos de ejecución de los
  distintos procesos de un sistema disminuyan,
  haciendo uso del menor número de recursos
  posible.

3
AFINAMIENTO EN ORACLE

• Las bases de datos necesitan técnicas para
  mejorar su rendimiento, por lo que su afinamiento
  es imprescindible para obtener su máximo
  aprovechamiento.
• Cuatro grandes áreas de gran importancia para
  lograr ese objetivo.

4
TUNING EN ORACLE

• Cuatro áreas principales SGA(System Global Area)

5
4 Metas de Oracle para
impactar rápidamente
1-Localizar suficiente memoria para
Oracle. 2-Conseguir los datos cargados en la
memoria (cache). 3-Buscando queries
problemáticos que afectan la memoria y
I/O. 4-Afinando los queries problemáticos
6
Meta 1 tenemos suficiente memoria
localizada para Oracle ?
7
Meta 1 tenemos suficiente memoria
localizada para Oracle ?

• Cómo vemos lo que tenemos activado ?
• DB_BLOCK_BUFFERS
• SHARED_POOL_SIZE
• SORT_AREA_SIZE

8
Meta1 tenemos suficiente memoria localizada
para Oracle ?

• Valores del parámetro KEY INIT.ORA
• select name, substr(value,1,40)
• from vparameter where name in
  ('db_block_buffers','db_block_size','shared_pool_s
  ize','sort_area_size')
• Nombre
  Valor
  
• db_block_buffers
  4000
  
• db_block_size
  4096
  
• shared_pool_size
  7000000
  
• sort_area_size
  262144

9
DB_BLOCK_BUFFERS

• Si DB_BLOCK_BUFFERS es bajo, los usuarios podrían
  no tener suficiente espacio en memoria para
  trabajar eficientemente.
• Si DB_BLOCK_BUFFERS es alto, el sistema podría
  comenzar a hacer swap y se podría detener.

10
B. El SHARED_POOL_SIZE

• Esta es la porción de memoria localizada para la
  librería y el cache del diccionario de datos.
• Si el SHARED_POOL_SIZE esta seteado demasiado
  bajo no se aprovecharía adecuadamente el
  DB_BLOCK_BUFFERS.

11
Determinar la Memoria asignada en el
SHARED_POOL_SIZE

• col value for 999,999,999,999 heading Shared
  Pool Size
• col bytes for 999,999,999,999 heading Free
  Bytes
• select to_number(vparameter.value) value,
  vsgastat.bytes,
• (vsgastat.bytes/vparameter.value)100 Percent
  Free
• from vsgastat, vparameter
• where vsgastat.name 'free memory'
• and v parameter .name shared_pool_size
• Shared Pool Size Free Bytes Percent Free
• 100,000,000 82,278,960 82.27896

12
Declaraciones que generan Segmentos Temporales

• Create Index...
• Select .... Order By, Distinct, Group By, Union,
  Intersect, Minus
• Unindexed Joins Correlated Subqueries
• El valor por defecto de la magnitud inicial para
  los segmentos temporales debe ser por lo menos
  tan grande como el valor de sort_area_size.

13
C. Almacenar en memoria en lugar de en segmentos
temporales

• El parámetro SORT_AREA_SIZE de Init.ora localiza
  memoria para efectuar ordenamientos.
• Determina el espacio PER USER localizado en
  memoria principal para cada proceso de
  ordenamiento.
• Si no es suficiente, los segmentos temporales son
  usados.
• Incrementar sort_area_size para reducir I/O a
  disco.
• Causa swapping si la memoria asignada es pequeña.

14
Cache parametro de una tabla

• Examina toda la tabla y liste los mas
  recientemente usados.
• CREATE TABLE TEST_TAB (COL1 NUMBER)
• TABLESPACE USERS
• CACHE
• ALTER TABLE TEST_TAB
• CACHE
• NOCACHE is the Default!

15
Meta 3 Encuentre los queries que están
obstruyendo la memoria y causan problemas de I/O

• Use VSQLAREA para encontrar problemas de Queries

16
Encuentre queries problematicos hurting de
memoria (vsqlarea)

• select disk_reads, sql_text
• from vsqlarea
• where disk_reads gt 10000
• order by disk_reads desc
• Disk_reads SQL_TEXT
• 12,987 select order,columns,types from orders
• where substr(orderid,1,2)1
  
• 11,131 select custid, city from customer
• where city CHICAGO

17
Encontrar las lecturas lógicas mas grandes

• select buffer_gets, sql_text
• from vsqlarea
• where buffer_gets gt 200000
• order by buffer_gets desc
• Buffer_gets SQL_TEXT
• 300,219 select order,cust_no, from
• orders where division 1

18
Encontrando el codigo PL/SQL

• select text
• from user_source
• where name PROCESS_DATE
• order by line
• TEXT___________________________
  
  
  
• procedure process_date is
• test_num number
• begin
• test_num 10
• if test_num 10 then
• update order_main
• set process_date sysdate
• where order_num 12345
• end if
• end

19
Encontrar USER que bloquean a otros.

• Select a.serial, a.sid, a.username, b.id1,
  c.sql_text
• from vsession a, vlock b, vsqltext c
• where b.id1 in
• (select distinct e.id1
• from vsession d, vlock e
• where d.lockwait e.kaddr)
• and a.sid b.sid
• and c.hash_value a.sql_hash_value
• and b.request 0

20
Mate al USER del problema

• SERIAL SID USERNAME ID1
• SQL_TEXT
• 18 11 JOHNSON 393242
• update authuser.emp set salary90000
• alter system kill session 11,18
• Session Killed.

21
Meta 4 Afinando problemas de Queries

• Lo que necesito saber para poner a punto mi
  sistema
• Cost-Based, Optimization and Analyze
• La regla 95/5
• Using HINTS (sugerencias)
• Uso de Index y Abusos
• La Driving Table
• Usando Parallel Query

22
Lo que necesito saber para poner a punto mi
sistema

• Datos Usted debe conocer sus datos DATOS!
• Metodos de Tuning Usted necesitara toda una
  lista.
• Donde el sistema es mas lento Los usuarios
  ofreceran esto.
• Otros Diseñadores

23
Algunos metodos

• El Optimizers
• Usando Hints (sugerencias)
• Usando Histograms
• Driving Tables
• Partitions
• Parallel Query

24
El Optimizers

• El Parámetro de Optimizer_Mode - los Valores
• Regla
• Escoja
• Optimizer_Goal - los Valores
• Regla (no tiene tiempo para poner a punto todos
  esto)
• All_Rows - Consigue todas las filas rápidamente
  (Informes)
• First_Row - Consigue la primera fila rápidamente
  (Formas)
• Escoja (Arregle áreas del problema)
• Alter Session set Optimizer_Goal ltmodegt

25
El comando ANALYZE

• En General
• Las estadísticas son generadas con el comando
  ANALYZE
• Deben generarse estadísticas de Cost Based
  Optimization
• Una vez que tabla es analizada usar Cost Based
  Optimization (a menos que se sobreescriba
  INIT.ora )
• Una tabla también se puede-ANALYZEd Usando el
  'Delete Statistics'

26
El comando ANALYZE

• PURPOSE
• To perform one of these functions on an index,
  table, or cluster
• To collect statistics about the object used by
  the optimizer and store them in the data
  dictionary
• To delete statistics about the object from the
  data dictionary
• To validate the structure of the object
• To identify migrated and chained rows of the
  table or cluster

27
ANALYZE Ejemplos

• SQLgt ANALYZE TABLE CUSTOMER
• ESTIMATE STATISTICS sample 5000 rows
• SQLgt ANALYZE TABLE CUSTOMER
• ESTIMATE STATISTICS sample 25 percent
• SQLgt ANALYZE TABLE CUSTOMER
• DELETE STATISTICS
• execute dbms_utility.analyze_schema(SCOTT,COMPU
  TE)

28
ANALYZE Ejemplos

• desc dba_tab_modifications
• SQLgt exec dbms_stats.gather_schema_stats( -
• gt ownname gt 'SCOTT', -
• gt options gt 'GATHER AUTO')
• There are several values for the options
  parameter that we need to know about
• gather re-analyzes the whole schema. 
• gather empty Only analyze tables that have no
  existing statistics. 
• gather stale Only re-analyze tables with more
  than 10 modifications (inserts, updates,
  deletes). 
• gather auto This will re-analyze objects which
  currently have no statistics and objects with
  stale statistics.   Using gather auto is like
  combining gather stale and gather empty.

29
Usando key sugerencias
30
Usando sugerencias ( Hints)

• La Sintaxis debe ser correcta o la sugerencia se
  ignorará, y ningún mensaje del error se emite.
• Las sugerencias sólo aplican a la declaración en
  la que ellos estan. Se tratan declaraciones
  anidadas como declaraciones totalmente diferentes
  y requieren sus propias sugerencias.
• Hay un limite de 255 caracteres para las
  sugerencias.
• Al usar un alias para una tabla en la
  declaración, el
• seudónimo necesita estar en la sugerencia.

31
Key Hints para Optimization

• Full Forzar un análisis completo de tablas
• select / FULL(table_name) / column1, column2
  ...
• Index Forzar una búsqueda indexada
• select / INDEX(table_name index_name1
  index_name2...) / column1, column2 ...
• Ordered Forzar el ordenamiento de una tabla con
  la cláusula FROM
• select / ORDERED / column1, column2 ...
• from table1, table2

32
La regla 95/5

• cuando "Optimizerencuentra un query para
  recuperar menos 4-7 de las filas, el optimizer
  escogerá manejar el query con un index si este
  existe.

33
Optimizer Modes

• There are two types of optimizer modes
• Rule-based
• Uses a ranking system
• Syntax and data dictionary driven
• Cost-based
• Chooses the path with lowest cost
• Statistics-driven

34
Setting the Optimizer Mode

• At the instance level
• Optimizer mode
• chooserulefirst_rowsfirst_rows_nall_rows
• At the session level
• Alter Session Set optimizer_mode
• chooserulefirst_rowsfirst_rows_nall_rows
• At the statement level
• Using hints

35
Using Hints in a SQL Statement

• CREATE INDEX st_idx ON CUSTOMER (STATE)
• SELECT / INDEX(CUSTOMER ST_IDX)/
• NAME, ADDRESS, CITY
• FROM CUSTOMER
• WHERE STATE 'CA'

36
Optimizer Plan Stability

• Users can stabilize execution plans, to force
  applications to use a desired SQL access path.
• A consistent execution path is thereby maintained
  through database changes.
• This is done by creating a stored outline
  consisting of hits.

37
Plan Equivalence

• SQL statement text must match the text in a
  stored outline.
• Plans are maintained through
• New Oracle versions
• New statistics on objects
• Initializacion parameter changes
• Database reorganization
• Schema changes

38
SQL Reports in Statspack

• The following reports on statements are provided
  by Statspack
• SQL ordered by gets
• SQL ordered by reads
• SQL ordered by executions
• SQL ordered by parse calls

39
Generate the Execution Plan

• Can be used without tracing
• Needs the plan_table table utlxplan.sql
• Create the explain plan
• EXPLAIN PLAN FOR
• SELECT last_name FROM hr.employees

40
Query the plan_table Table

• Use utlxpls.sql (hide parallel Query information)
• Use utlxplp.sql (show parallel Query information)
• Use the dbms_xplan package
• SELECT FROM TABLE(dbms_xplan.display)

41
Using SQL Trace and TKPROF

• Set the initialization parameters
• ALTER SESSION SET sql_trace True
• Run the application
• ALTER SESSION SET sql_trace False
• Format the trace file with TKPROF
• Interpret the output

42
Enable / Disabling SQL Trace

• At the instance level
• SQL_Trace True False
• At the session level
• Alter session set sql_trace TRUE FALSE
• EXECUTE dbms_session.set_sql_trace (TrueFalse)
• EXECUTE
• dbms_system.set_sql_trace_in_session
  (session_id, serial_id, TrueFalse)

43
Formatting the Trace File with TKPROF

• tkprof tracefile.trc output.txt options

Tracefile.trc
Output.txt
User_dump_dest
44
TKPROF Statistics

• CountNumber of execution calls
• CPU CPU seconds used
• Elapsed Total elapsed time
• Disk Physical reads
• Query Logical reads for consistent read
• Current Logical reads in current mode
• Rows Rows processed

45
SQLPlus Autotrace

• Create the plan_table Table
• Create and grant the plustrace role
• _at_oracle_home/sqlplus/admin/plustrce.sql
• Grant plustrace To scott
• SET AUTOTRACE offontraceonly
  ExplainStatistics

46
Normas básicas de optimización

1. Las condiciones (tanto de filtro como de join)
   deben ir siempre en el orden en que esté definido
   el índice. Si no hubiese índice por las columnas
   utilizadas, se puede estudiar la posibilidad de
   añadirlo, ya que tener índices extra sólo
   penaliza los tiempos de inserción, actualización
   y borrado, pero no de consulta.

47
Normas básicas de optimización

1. Al crear un restricción de tipo PRIMARY KEY o
   UNIQUE, se crea automáticamente un índice sobre
   esa columna.
2. Para chequeos, siempre es mejor crear
   restricciones (constraints) que disparadores
   (triggers).
3. Hay que optimizar dos tipos de instrucciones las
   que consumen mucho tiempo en ejecutarse, o
   aquellas que no consumen mucho tiempo, pero que
   son ejecutadas muchas veces.

48
Normas básicas de optimización

• 5. Generar un plan para todas las consultas de la
  aplicación, poniendo especial cuidado en los
  planes de las vistas, ya que estos serán
  incluidos en todas las consultas que hagan
  referencia a la vista
• Generar y optimizar al máximo el plan de las
  vistas. Esto es importante porque el SQL de una
  vista, no se ejecuta mientras que la vista no es
  utilizada en una consulta,
• así que todas las consultas de esa vista se ven
  afectadas por su plan. Hay que tener especial
  cuidado de hacer joins entre vistas.

49
Normas básicas de optimización

• 6. Si una aplicación que funcionaba rápido, se
  vuelve lenta, hay que parar y analizar los
  factores que han podido cambiar. Si el
  rendimiento se degrada con el tiempo, es posible
  que sea un problema de volumen de datos, y sean
  necesarios nuevos índices para acelerar las
  búsquedas. Cuantos más índices tenga una tabla,
  más se tardará en realizar inserciones y
  actualizaciones sobre la tabla, aunque más
  rápidas serán las consultas.
• Hay que buscar un equilibrio entre el número de
  índices y su efectividad, de tal modo que creemos
  el menos número posible, pero sean utilizados el
  mayor número de veces posible.

50
Normas básicas de optimización

• 7. Utilizar siempre que sea posible las mismas
  consultas. La segunda vez que se ejecuta una
  consulta, se ahorrará mucho tiempo de parsing y
  optimización, así que se debe intentar utilizar
  las mismas consultas repetidas veces.

51
Normas básicas de optimización

• 8. Las consultas más utilizadas deben
  encapsularse en procedimientos almacenados. Esto
  es debido a que el procedimiento almacenado se
  compila y analiza una sola vez, mientras que una
  consulta (o bloque PL/SQL) lanzado a la base de
  datos debe ser analizado, optimizado y compilado
  cada vez que se lanza.

52
Normas básicas de optimización

• 9. Los filtros de las consultas deben ser lo más
  específicos y concretos posibles. Es decir es
  mucho más específico poner WHERE campo 'a' que
  WHERE campo LIKE 'a'. Es muy recomendable
  utilizar siempre consultas que filtren por la
  clave primaria u otros campos indexados.

53
Normas básicas de optimización

• 10.Hay que tener cuidado con lanzar demasiadas
  consultas de forma repetida, como por ejemplo
  dentro de un bucle, cambiando una de las
  condiciones de filtrado. Siempre que sea posible,
  se debe consultar a la base de datos una sola
  vez, almacenar los resultados en la memoria del
  cliente, y procesar estos resultados después.

54
Normas básicas de optimización

• Evitar la condiciones IN ( SELECT)
  sustituyéndolas por joins cuando se utiliza un
  conjunto de valores en la clausula IN, se traduce
  por una condición compuesta con el operador OR.
  Esto es lento, ya que por cada fila debe
  comprobar cada una de las condiciones simples.
  Suele ser mucho más rápido mantener una tabla con
  los valores que están dentro del IN, y hacer un
  join normal. Por ejemplo, esta consulta
• SELECT FROM datos WHERE campo IN ('a', 'b',
  'c', 'd', ... , 'x', 'y', 'z')
• SELECT FROM datos d, letras l WHERE d.campo
  l.letra

55
Normas básicas de optimización

1. También hay que tener cuidado cuando se mete un
   SELECT dentro del IN, ya que esa consulta puede
   retornar muchas filas, y se estaría cayendo en el
   mismo error. Normalmente, una condición del tipo
   "WHERE campo IN (SELECT...)" se puede sustituir
   por una consulta con join.

56
Normas básicas de optimización

• 12.Cuando se hace una consulta multi-tabla con
  joins, el orden en que se ponen las tablas en el
  FROM influye en el plan de ejecución. Aquellas
  tablas que retornan más filas deben ir en las
  primeras posiciones, mientras que las tablas con
  pocas filas deben situarse al final de la lista
  de tablas.

57
Normas básicas de optimización

• 13. Si en la cláusula WHERE se utilizan campos
  indexados como argumentos de funciones, el índice
  quedará desactivado. Es decir, si tenemos un
  índice por un campo IMPORTE, y utilizamos una
  condición como WHERE ROUND(IMPORTE) gt 0, entonces
  el índice quedará desactivado y no se utilizará
  para la consulta.

58
Normas básicas de optimización

• 14. Siempre que sea posible se deben evitar las
  funciones de conversión de tipos de datos e
  intentar hacer siempre comparaciones con campos
  del mismo tipo. Si hay que hacer algún tipo de
  conversión, intenta evitar el uso del cast y
  aplica siempre la función de conversión sobre la
  constante, y no sobre la columna.

59
Normas básicas de optimización

• 15. Una condición negada con el operador NOT
  desactiva los índices
• 16. Una consulta calificada con la cláusula
  DISTINCT debe ser ordenada por el servidor aunque
  no se incluya la cláusula ORDER BY.

60
Normas básicas de optimización

• 17. Si vamos a realizar una operación de
  inserción, borrado o actualización masiva, es
  conveniente desactivar los índices, ya que por
  cada operación individual se actualizarán.

61
Optimizador basado en reglas (RULE)

• Se basa en ciertas reglas para realizar las
  consultas. Por ejemplo, si se filtra por un campo
  indexado, se utilizará el índice, si la consulta
  contiene un ORDER BY, la ordenación se hará al
  final, etc. No tiene en cuenta el estado actual
  de la base de datos, ni el número de usuarios
  conectados, ni la carga de datos de los objetos,
  etc. Es un sistema de optimización estático, no
  varía de un momento a otro.

62
Optimizador basado en costes (CHOOSE)

• Se basa en las reglas básicas, pero teniendo en
  cuenta el estado actual de la base de datos
  cantidad de memoria disponible, entradas/saludas,
  estado de la red, etc. Por ejemplo, si se hace
  una consulta utilizando un campo indexado, mirará
  primero el número de registros y si es
  suficientemente grande, entonces merecerá la pena
  acceder por el índice, si no, accederá
  directamente a la tabla.

63
Optimizador basado en costes (CHOOSE)

• Para averiguar el estado actual de la base de
  datos se basa en los datos del catálogo público,
  por lo que es recomendable que esté lo más
  actualizado posible (a través de la sentencia
  ANALYZE), ya que de no ser así, se pueden tomar
  decisiones a partir de datos desfasados (la tabla
  tenía 10 registros hace un mes pero ahora tiene
  10.000).
• ALTER SESSION SET OPTIMIZER_GOAL RULECHOOSE

64
Sugerencias o hints

• Un hint es un comentario dentro de una consulta
  SELECT que informa a Oracle del modo en que tiene
  que trazar el plan de ejecución. Los hint deben
  ir junto detrás de la palabra SELECT
• SELECT / HINT / . . .

65
Sugerencias o hints
Hint Descripción
/ CHOOSE / Pone la consulta a costes.
/ RULE / Pone la consulta a reglas.
/ ALL_ROWS / Pone la consulta a costes y la optimiza para que devuelva todas las filas en el menor tiempo posible. Es la opción por defecto del optimizador basado en costes. Esto es apropiado para procesos en masa, en los que son necesarias todas las filas para empezar a trabajar con ellas.
/ FIRST_ROWS / Pone la consulta a costes y la optimiza para conseguir que devuelva la primera fila en el menor tiempo posible. Esto es idóneo para procesos online, en los que podemos ir trabajando con las primeras filas mientras se recupera el resto de resultados. Este hint se desactivará si se utilizan funciones de grupo como SUM, AVG, etc.
/ INDEX( tabla índice ) / Fuerza la utilización del índice indicado para la tabla indicada. Se puede indicar el nombre de un índice (se utilizará ese índice), de varios índices (el optimizador elegirá uno entre todos ellos) o de una tabla (se utilizará cualquier índice de la tabla).
/ ORDERED / Hace que las combinaciones de las tablas se hagan en el mismo orden en que aparecen en el join.
66
Calcular el coste de una consulta

• Para calcular el coste de una consulta, el
  optimizador se basa en las estadísticas
  almacenadas en el catálogo de Oracle, a través de
  la instrucción
• ANALYZE TABLE,INDEX COMPUTE, ESTIMATE
  STATISTICS

67
Ejemplos

• EXPLAIN PLAN FOR
• SELECT ename, job, sal, dname
• FROM emp, dept
• WHERE emp.deptno dept.deptno
• AND NOT EXISTS
• (SELECT
• FROM salgrade
• WHERE emp.sal BETWEEN losal AND hisal)

68