oracle ibÉrica, srl · infv5_jda_awr&statspack_rdbms_v1.2.doc, ver. 1.2 20 de marzo de 2017...
TRANSCRIPT
ORACLE IBÉRICA, S.R.L. ADVANCED CUSTOMER SUPPORT
JUNTA DE ANDALUCIA
INFORME
USO DE AWR Y/O STATSPACK PARA
RECOGIDA DE ESTADÍSTICAS DE
RENDIMIENTO DE BASES DE DATOS
SOLUTION SUPPORT CENTER
Referencia documento: InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc
Fecha: 20 de marzo de 2017
Versión: 1.1
Copyright(c) 2.017 ORACLE IBÉRICA
Todos los derechos reservados
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 2 / 13 Nº: 20845/G
Registro de Cambios
Fecha Autor Versión Notas
05/05/2006 Pablo Fernández Riejos
1.0 Primera versión
20/03/2017 Jonathan Ortiz 1.2 Actualización AWR
Revisiones
Nombre Role
Grupo de ASEs de Oracle Sevilla Oracle ACS Service Engineers
Jose María Gomez Technical Account Manager
Agustin Calvo Technical Account Manager
Distribución
Copia Nombre Empresa
1 Dirección Técnica JdA Junta de Andalucia
2 José María Gómez Oracle ACS Service Manager
3 Agustin Calvo Oracle ACS Service Manager
4 Grupo de ASEs de Oracle Sevilla Oracle ACS Service Engineer
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 3 / 13 Nº: 20845/G
Índice de Contenidos
INTRODUCCIÓN .................................................................................................................................... 4 CLARIFICACIÓN DE REQUISITOS DE LICENCIAMIENTO ........................................................................ 5 PREREQUISITOS DE STATSPACK ............................................................................................................ 6 INSTALACIÓN Y CONFIGURACIÓN DE STATSPACK .............................................................................. 7
Instalación ......................................................................................................................................... 7 Configuración ................................................................................................................................... 7
GENERACIÓN DE INFORMES DE STATSPACK ........................................................................................ 8 DEPURADO DE INFORMACIÓN HISTÓRICA DE STATSPACK .................................................................. 9
Instalación y ubicación.................................................................................................................... 10 INSTALACIÓN Y CONFIGURACIÓN DE AWR ..................................................................................... 11
Instalación ....................................................................................................................................... 11 Configuración ................................................................................................................................. 11
GENERACIÓN DE INFORMES DE AWR ............................................................................................... 12 DEPURADO DE INFORMACIÓN HISTÓRICA DE AWR ......................................................................... 13
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 4 / 13 Nº: 20845/G
Introducción
Los entornos productivos sufren a menudo problemas de diversa índole. Entre ellos, uno de los más frecuentes es la pérdida de rendimiento (medida a menudo como un retraso en el tiempo de respuesta de las aplicaciones). Habitualmente, el tiempo tomado para la detección y (posible) corrección del problema supera la duración del problema en sí. A menos que el problema se reproduzca de forma intermitente o sea fácilmente reproducible, las posibilidades de analizar las posibles causas son escasas. En la capa de Base de Datos, Oracle incorpora un paquete de estadísticas que automatiza la recogida periódica de información del sistema. La filosofía de la herramienta consiste en obtener muestras periódicas de diferentes vistas dinámicas de la base de datos (vistas V$) y obtener una foto (snapshot) de dichas vistas. Este paquete de estadísticas que automatiza la recogida periódica de información del sistema es conocido como Statspack en versiones de bases de datos Oracle 8i en adelante. A partir de la versión Rdbms 10g Oracle genero un nuevo paquete de recogida de estadísticas llamado AWR ( Automatic Workload Repository ). El objetivo de este documento es facilitar el despliegue y ejecución permanente de este paquete en cualquier base de datos. La recogida periódica de este tipo de información tiene claras ventajas:
Permite un análisis bastante detallado de un problema de rendimiento una vez que éste ha desaparecido.
La información puede analizarse en períodos grandes (semanas, meses) con lo que pueden establecerse tendencias y avanzarse a problemas de falta de capacidada (Capacity Planing)
Facilita la generación de informes de diversa índole para el análisis de diferentes indicadores de rendimiento (SQL optimizable, consumo de recursos, rendimiento de I/O, …)
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 5 / 13 Nº: 20845/G
Clarificación de requisitos de licenciamiento
STATSPACK y AWR son herramientas independientes, cada una con su propio repositorio: AWR es parte de la instalación de 10g y posterior en Oracle Rdbms Enterprise Edition, STATSPACK se instala mediante script que creará el esquema PERFSTAT con sus tablas/paquetes necesarios (se recomienda tener un espacio de tabla dedicado para ello). No hay incompatibilidad entre esas herramientas, pero se aconseja no tomar instantáneas en el mismo tiempo para evitar la congestión potencial en la extracción de datos. A partir de Rdbms EE 10g, Oracle recomienda hacer uso de AWR en lugar de usar Statspack, teniendo en cuenta los siguientes puntos:
El acceso directo a las vistas de AWR y los informes de AWR requieren licencia de Diagnostics Pack, si se dispone de licencia de Diagnostics Pack lo recomendable es usar AWR.
Si no se dispone de licencia de Diagnostics Pack, lo recomendable es dejar instalado AWR porque muchas funcionalidades de la base de datos como el asesor automático de segmentos, el asesor de segmentos de Undo necesitan información recopilada por AWR. El uso de estas funcionalidades, que implicitamente tienen acceso a algunas vistas de AWR, NO requiere ninguna licencia de Pack. Y realizar la instalación de Statspack tal y como se recoge en el siguiente apartado del documento.
Si el cliente decide usar SOLO Statspack a partir de 10g es necesario realizar la instalación de Statspack tal y como se recoge en el siguiente apartado del documento y deshabilitar la instalación que viene por defecto de AWR en Oracle Enterprise Edition usando las siguientes referencias:
o Para 10g usar la Nota 1909073.1, disabling and Uninstalling AWR. o Para 11g y superior usar la Nota 436386.1, Package for disabling AWR without a
Diagnostic Pack license in Oracle. Los servicios ACS para el análisis de incidencias en ocasiones solicitan al cliente los datos de estadísticas de rendimiento de la base de datos y es responsabilidad del cliente cumplir con los requisitos de licencia y no violar los acuerdos de licencia. Para ello el Cliente debe ser conocedor de los packs que tiene en su contrato de licencias y proporcionara a ACS los datos de Statspack o AWR de acuerdo a dicho contrato. Comprobación del uso de AWR: Para comprobar el uso de varias características relacionadas con AWR en la vista dba_feature_usage_statistics, por ejemplo, para comprobar la creación de Informes podría utilizar la siguiente consulta:
column name format a30
SQL> SELECT name,detected_usages,currently_used,
TO_CHAR(last_sample_date,'DD-MON-YYYY:HH24:MI') last_sample
FROM dba_feature_usage_statistics
WHERE name = 'AWR Report';
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 6 / 13 Nº: 20845/G
Prerequisitos de Statspack
Los requisitos previos para poder hacer uso de los procedimientos descritos más adelante son los siguientes:
Espacio necesario en disco
Por experiencia en varios clientes, 500 MB de espacio en disco para el Tablespace de StatsPack suelen ser suficientes. No obstante, el espacio ocupado depende en gran medida de la actividad de la base de datos, por lo que se recomienda su monitorización y ajuste durante los primeros meses.
Usuario DBA
La instalación y configuración del paquete deberá realizarla un usuario con privilegios especiales (típicamente el administrador de base de datos – DBA).
Otros
Para la ejecución automática de los trabajos (jobs) de Statspack, es necesario definir el parámetro
job_queue_processes a un valor superior a 0 (preferiblemente 2 ó 3).
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 7 / 13 Nº: 20845/G
Instalación y Configuración de StatsPack
Instalación
La información recogida por Statspack es almacenada en un repositorio específico (habitualmente del usuario PERFSTAT).
La instalación se suele realizar ejecutando un script preparado para tal fin. Este script es
$ORACLE_HOME/rdbms/admin/spcreate.sql y hay que ejecutarlo desde SQL*Plus con un usuario DBA ( / as sysdba).
El script internamente invocará a los siguientes scripts:
1. spcusr -> crea el usuario (perfstat) y otorga privilegios
2. spctab -> crea las tablas
3. spcpkg -> crea el paquete (statspack)
Existe más información sobre la instalación de Statspack en la Nota 149113.1 de MyOracleSupport
Configuración
Se recomienda activar una toma de muestras de forma automática con una hora de diferencia.
Para ello es necesario ejecutar el script $ORACLE_HOME/rdbms/admin/spauto.sql, desde la utilidad SQL*Plus y conectado como el usuario propietario del repositorio de Statspack (habitualmente, PERFSTAT).
El script lo que hace es crear un nuevo Job de base de datos y va a generar un snapshot cada hora. Para
ello, lo que hace el job es ejecutar el procedimiento STATSPACK.SNAP cada hora.
Para mayor información al respecto, consultar la Nota 228913.1 de Metalink.
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 8 / 13 Nº: 20845/G
Generación de Informes de Statspack
El paquete StatsPack aporta una función para la generación de informes: SPREPORT.SQL.
Esta utilidad se ejecuta desde SQL*Plus:
$ORACLE_HOME/rdbms/admin/spreport.sql
Este script muestra una lista de los snapshots existentes (snap_id) y solicita la entrada del snapshot inicial y final. También requiere el nombre del fichero de salida que, por defecto, es sp_<snap_inicial>_<snap_final>.lst
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 9 / 13 Nº: 20845/G
Depurado de información histórica de Statspack
Uno de los mayores inconvenientes de la ejecución permanente de StatsPack es que requiere de un depurado periódico de la información que se va almacenando. Para facilitar esta labor se adjunta (a modo de ejemplo) un shell script que automatiza esta actividad. Este shell script realiza las siguientes actividades:
Exporta los datos de StatsPack a disco
Comprime el fichero de export
Trunca el tablespace de StatsPack, liberando el espacio ocupado para las nuevas estadísticas
Este trabajo debería ejecutarse una vez al mes (por ejemplo, cada día 1).
# Carga de variables de ejecucion
V_DIR_ACT=`dirname $0`
export NLS_LANG=american_america.we8iso8859p15
export fecha=`date '+%Y%m%d'`
export ruta=$ORACLE_BASE/$V_DB_NAME/app/statspack
export sid=$ORACLE_SID
# Export de los datos almacenados en la BD
exp perfstat/perfstat file=$ruta/exp_statspack_$sid$fecha.dmp
log=$ruta/exp_statspack_$sid$fecha.log compress=n statistics=none owner=perfstat
# Creacion de un fichero tar con el export y compresion del mismo
tar cvf $ruta/exp_statspack_$sid$fecha.tar $ruta/exp_statspack_$sid$fecha.dmp
compress $ruta/exp_statspack_$sid$fecha.tar
# Borrado del dmp
rm $ruta/exp_statspack_$sid$fecha.dmp
# Truncado del tablespace STATSPACK. Almacenado de la informacion en el log correspondiente
$ORACLE_HOME/bin/sqlplus perfstat/perfstat >> $ruta/truncate_statspack_$sid.log << _EOF_
@?/rdbms/admin/sptrunc.sql
exit
_EOF_
# Borrado de los posibles archivos antiguos que existan y del log de spool
rm $V_DIR_ACT/sptrunc.lis
cd $ruta
find . -mtime +80 -name "exp_statspack*" -exec rm {} \;
Debería observarse el crecimiento del tablespace de StatsPack durante los primeros meses para ajustar su tamaño a un valor óptimo. Para reducir el espacio utilizado en disco se recomienda realizar un “resize” del fichero al tamaño deseado. En el caso de que al hacer el resize se encuentren problemas (debido por ejemplo a la existencia de algún “extent” en la parte alta del tablespace) se recomienda recrear el tablespace ejecutando las acciones siguientes:
Ejecutar $ORACLE_HOME/rdbms/admin/spdrop.sql para eliminarlo
Ejecutar $ORACLE_HOME/rdbms/admin/spcreate.sql para volverlo a crear
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 10 / 13 Nº: 20845/G
Instalación y ubicación
El script debería configurarse con permisos 755 y ser propiedad del usuario oracle. Hay que personalizar internamente sistema para que pueda encontrar el entorno. Tiene que crearse otro directorio donde irá el resultado de los exports de los statspack. El directorio statspack, debe crearse propiedad de oracle y del grupo dba y además debe tener permisos ‘drwxrwxr-x’, es decir, además del usuario oracle, deben poder escribir en él los miembros del grupo dba.
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 11 / 13 Nº: 20845/G
Instalación y Configuración de AWR
Instalación
La información recogida por AWR es almacenada en un repositorio específico dentro de las tablas de AWR incluidas en el tablespace SYSAUX.
La instalación se realiza por defecto en Oracle Rdbms Enterprise Edition. Por lo que no es necesario la ejecución de ningún script. En caso de que se desee realizar una reinstalación de AWR es necesario seguir los pasos de la siguiente guía de borrado y recreación de AWR:
Nota#782974.1 - How to Recreate The AWR ( AUTOMATIC WORKLOAD )
Repository ?
Configuración
De forma predeterminada, las instantáneas de AWR se toman cada hora y se conservan durante 7 días. Los valores predeterminados para estos ajustes se pueden modificar siguiendo el procedimiento siguiente.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- (30 dias).
interval => 30); -- 30 Minutos.
END;
/
Los cambios en la configuración se reflejan en la vista DBA_HIST_WR_CONTROL. Normalmente, el período de retención debe capturar al menos un ciclo completo de carga de trabajo. Si su sistema tiene un periodo mensual un tiempo de retención de al menos un mes sería más beneficioso que el predeterminado de 7 días. Un intervalo de "0" desactiva la recolección de instantáneas, lo que a su vez detiene gran parte de la funcionalidad de autoajuste, por lo que no se recomienda. La recopilación automática sólo es posible si el parámetro STATISTICS_LEVEL está establecido en TYPICAL o ALL. Si el valor está establecido en BASIC se pueden tomar instantáneas manuales, pero faltarán algunas estadísticas.
Se recomienda activar una toma de muestras de forma automática con una hora de diferencia y una retención de al menos 30 dias. Para ello es necesario ejecutar el script
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- (30 dias).
interval => 60); -- 60 Minutos.
END;
/
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 12 / 13 Nº: 20845/G
Generación de Informes de AWR
Oracle proporciona dos scripts para producir informes de AWR (awrrpt.sql y awrrpti.sql). Son similares en formato a los informes de statspack y ofrecen la opción de HTML o formatos de texto sin formato. Los dos informes dan esencial la misma salida, pero el awrrpti.sql le permite seleccionar una sola instancia. Los informes se pueden generar de la siguiente manera.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql @$ORACLE_HOME/rdbms/admin/awrrpti.sql Las secuencias de comandos le indican que introduzca el formato de informe (html o texto), el ID de snapshot de inicio, el ID de snapshot final y el nombre de archivo de informe. El informe resultante puede ser abierto en un navegador o editor de texto.
InfV5_JdA_AWR&Statspack_Rdbms_V1.2.doc, Ver. 1.2
20 de marzo de 2017
Certificado ISO-9001:2000 Pág. 13 / 13 Nº: 20845/G
Depurado de información histórica de AWR
El purgado de información de AWR se gestiona de forma automática a través de la ventana de retención de todas formas se puede realizar de forma manual el borrado de snapshots a través de la ejecución del siguiente script:
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => <snap_inicial>,
high_snap_id => <snap_final>);
END;
/