Oracle StatsPack

Universidad Latinoamericana de Ciencia y Tecnología

Escuela de Ciencias de la Computación

Codigo del Curso: 16-1004

Curso:  Administración de bases de datos relacionales II

Profesor: Luis Ruben Artavia

Integrantes:

Roy Cruz Polanco

Mario Solís Mercado

Oracle Statspack

Objetivo General

Investigar y aplicar las funcionabilidades y beneficios que provee Oracle Statspack, para evaluar el rendimiento de las consultas SQL en distintos escenarios.

Oracle Statspack

Objetivos especificos

  • Investigar sobre las distintas funcionabilidades que el Statspack provee.
  • Determinar los beneficios y las posibles desventajas que conlleva el Oracle Statspack.
  • Verificar la utilización del Oracle Statspack en distintos ambientes de pruebas.

Oracle Statspack

¿Qué es Oracle Statspack?

  • Un paquete para bases de datos Oracle, utilizado para gestionar el proceso de recopilación de estadísticas y datos de las tablas.

  • Aparece en la versión 8.1.6

  • Es una colección de scripts SQL, PL/SQL y SQL*Plus, que recopilan, almacenan y despliegan datos de rendimiento.

  • Se puede generar informes que proporcionan información sobre la salud, la instancia SQL que consume más recursos, wait events, y  parámetros de inicialización.

Oracle Statspack

¿Comó instalarLo?

Oracle Statspack

SQL> connect system/manager as SYSDBA

SQL> @spcreate
  • Este procedimiento almacenado creará un usuario llamado PERFSTAT, el cual le permitirá trabajar con las funcionalidades del statspack.
  • Se debe asignar al menos 100 MB para la creación inicial de los objetos de esquema PERFSTAT.

Ejecutar los Scripts en Windows

Oracle Statspack

SQL> @?/rdbms/admin/sprepsql
SQL> @?/rdbms/admin/spreport
SQL> @?/rdbms/admin/spcreate
SQL> @?/rdbms/admin/spdrop

Para ejecutar los procedimientos almacenados en windows se necesita poner @?/rdbms/admin/ antes del nombre de cada procedimiento.

esquema PERFSTAT

  • Posee los roles SELECT_CATALOG_ROLE, HG_ADMIN_ROLE, además  de tener los siguientes privilegios de sistema: CREATE/ALTER SESSION, CREATE TABLE, CREATE/DROP PUBLIC SYNONYM, CREATE SEQUENCE y CREATE PROCEDURE.
  • Tiene los privilegios otorgados a PUBLIC.

Oracle Statspack

SQL> @spdusr

Comando utilizado para eliminar el perfil

Ejecutar un Snapshot

Oracle Statspack

Execute STATPACK.SNAP;
  • Son una recolección de datos en un instante de tiempo  determinado

  • Obtienen las estadísticas y datos necesarios de las vistas “V$”.

Comando para ejecutar un snapshot

Optención de información de un Snap

Nivel Descripción
0 a 4 Estadísticas de rendimiento generales sobre todas las áreas de memoria, bloqueos internos, áreas y sucesos.
5 a 9 Las mismas estadísticas de los niveles anteriores, más las instrucciones SQL que más recursos demandan.
10 y posteriores Las mismas estadísticas de los niveles inferiores, más las intrucciones SQL que más recurssos requieren y los datos de bloques internos padre/hijo.

Oracle Statspack

SQL>  EXECUTE STATSPACK.SNAP(i_snap_level=>6);

comando para cambiar el nivel del snap

Modificar Valores de los Snaps

Nombre del parámetro Rango de valores Valor Predetermindado Descripción
i_snap_level 0-10 5 Nivel de snapshot
i_ucomment Cualquier Texto vacío Comentario para el snapshot
i_executions_th Entero >= 0 100 Umbral para el número de ejecuciones acumuladas
i_disk_reads_th Entero >= 0 1000 Umbral para el número de lecturas acumuladas
i_parse_calls_th Entero >= 0 1000 Umbral para el número de análisis acumuladas.
i_buffer_gets_th Entero >= 0 10000 Umbral para el número de extracciones de buffer acumuladdas.
i_sharable_mem_th Entero >= 0 100000 Umbral para la cantidad de memoria compartida asignada.
i_version_count_th Entero >= 0 20 Umbral para el número de version de la instrucción SQL.
i_session_id Entero >= 0 0 ID de sesión, si desea recoger estadísticas a nivel de sesión.
i_modify_parameter Boolean False Configurar a TRUE si se desea guardar los cambios para futuros snapshots.
STATSPACK.MODIFY_STATSPACK_PARAMETER -

 (i_snap_level => 5, i_buffer_gets_th => 100000);

Oracle Statspack

Gestión de los datos del Statspack

Oracle Statspack

El statspack permite analizar el espacio que va a ser utilizado en disco para almacenar esta información.  Así como eliminar estadisticas las cuales no van a ser necesarios para futuros reportes.

execute DBMS_UTILITY.ANALYZE_SCHEMA('PERFSTAT', 'COMPUTE');
SQL> sptrunc.sql.

comando para optener información schema y el espacio para almacenar en disco.

procedimiento para eliminar todo snaps antiguos

Configurar La recolección automatica

El paquete DBMS_JOB  son metodos para correr automaticamente, de manera periodica distitos scripts, con los cuales pueden ser ejecutados cada cierto tiempo.  Para ejecutar un snapshot se debe de correr el procedimiento auto.sql el cual nos mostrara el Job number para poder correr ejecutar los snapshots. 

 

En el ejemplo anterior de puede ver como se configurar el Job para ser ejecutado acada hora.

Oracle Statspack

EXECUTE DBMS_JOB.INTERVAL(job_number,'SYSDATE+(1/24)');

Generar reportes StatsPack

El @spreporte genera un conjunto de repotes relacionados a los snapshots que fueron seleccionados para generar el reporte, y despliega datos de utilización de la cache, de la utilización del share pool, la exploración de las tablas, cache buffer por area y actividad de bloques así como un informe de consultas sql.

SQL> @spreport
SQL> @sprepsql

El @sprepsql muestra las estadísticas, el texto completo de SQL, y (si se ha tomado un snapshot de nivel 6), información sobre cualquier plan de SQL asociado con esa sentencia.

Oracle Statspack

Información general Reporte

Oracle Statspack

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          1371108442 orcl                1 09-Ago-14 12:24 11.2.0.1.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     W7-PC            Microsoft Windows x86      2     2       1          1.8

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:         15 09-Ago-14 13:54:26       33       4.0
  End Snap:         16 09-Ago-14 13:54:57       35       3.7
   Elapsed:       0.52 (mins) Av Act Sess:       0.0
   DB time:       0.02 (mins)      DB CPU:       0.01 (mins)

Información de Caché y perfil de carga

Oracle Statspack


Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:       320M              Std Block Size:         8K
     Shared Pool:       416M                  Log Buffer:     8,392K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.0                0.3        0.01        0.00
       DB CPU(s):                0.0                0.2        0.00        0.00
       Redo size:           34,114.2          211,508.0
   Logical reads:              319.4            1,980.2
   Block changes:               67.1              416.2
  Physical reads:                0.0                0.0
 Physical writes:                0.1                0.4
      User calls:                8.9               55.0
          Parses:                5.7               35.2
     Hard parses:                0.0                0.0
W/A MB processed:                0.9                5.4
          Logons:                0.1                0.6
        Executes:                8.4               51.8
       Rollbacks:                0.0                0.0
    Transactions:                0.2

INdicadores de Eficiencia y Estadisticas del Share Pool

Oracle Statspack

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:  100.00  Optimal W/A Exec %:  100.00
            Library Hit   %:  100.00        Soft Parse %:  100.00
         Execute to Parse %:   32.05         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:             % Non-Parse CPU:   97.22

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   71.93   71.96
    % SQL with executions>1:   62.77   62.84
  % Memory for SQL w/exec>1:   64.91   65.02

Estadisticas de la instancia

Oracle Statspack

Instance Activity Stats  DB/Inst: ORCL/orcl  Snaps: 15-16

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
session pga memory max                     5,117,032      165,065.6  1,023,406.4
session uga memory                     4,295,641,400  138,569,077.4 ############
session uga memory max                     3,370,368      108,721.6    674,073.6
shared hash latch upgrades - no w                171            5.5         34.2
SMON posted for undo segment shri                  0            0.0          0.0
sorts (memory)                                   158            5.1         31.6
sorts (rows)                                   5,001          161.3      1,000.2
sql area evicted                                   0            0.0          0.0
sql area purged                                    0            0.0          0.0
SQL*Net roundtrips to/from client                260            8.4         52.0
switch current to new buffer                       1            0.0          0.2
table fetch by rowid                              31            1.0          6.2
table fetch continued row                          0            0.0          0.0
table scan blocks gotten                       2,647           85.4        529.4
table scan rows gotten                        86,452        2,788.8     17,290.4
table scans (direct read)                          0            0.0          0.0
table scans (long tables)                          0            0.0          0.0
table scans (short tables)                        28            0.9          5.6
TBS Extension: bytes extended                      0            0.0          0.0
TBS Extension: files extended                      0            0.0          0.0
TBS Extension: tasks created                       0            0.0          0.0
TBS Extension: tasks executed                      0            0.0          0.0
temp space allocated (bytes)                       0            0.0          0.0
total cf enq hold time                             0            0.0          0.0
total number of cf enq holders                     0            0.0          0.0
total number of times SMON posted                  0            0.0          0.0
transaction rollbacks                              0            0.0          0.0
undo change vector size                      386,884       12,480.1     77,376.8
          -------------------------------------------------------------

Reporte de SQL

Oracle Statspack


DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
ORCL          1371108442 orcl                1 11.2.0.1.0  NO  W7-PC

 Start Id     Start Time         End Id      End Time       Duration(mins)
--------- ------------------- --------- ------------------- --------------
       21 18-Ago-14 23:13:21         22 19-Ago-14 18:47:56        1,174.58

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
   milliseconds (ms) for Per Execute
                                                       % Snap
                     Statement Total      Per Execute   Total
                     ---------------  ---------------  ------
        Buffer Gets:       1,558,549         62,342.0   25.97
         Disk Reads:          83,630          3,345.2   44.55
     Rows processed:      41,943,038      1,677,721.5
     CPU Time(s/ms):              41          1,621.3
 Elapsed Time(s/ms):             268         10,706.1
              Sorts:               0               .0
        Parse Calls:              25              1.0
      Invalidations:               2
      Version count:               1
    Sharable Mem(K):              18
         Executions:              25

SQL Text
~~~~~~~~
insert into test select * from test

Desinstalar StatsPAck

El archivo spdrop.sql llama a scripts que borran las tablas, el paquete, los sinónimos públicos y el usuario PERFSTAT.  Para poder ejecutar este procedimiento se necesitan tener permisos de SYSDBA.

Oracle Statspack

SQL> @spdrop

Video

Oracle Statspack

Statspack

By Roy Cruz

Statspack

Introduction to Oracle statspacks. Made for the relational data base course in ulacit college.

  • 445