StatPacks

 Statspacks es un conjunto de utilidades que Oracle nos proporciona para 
 recojer estadísticas del estado de nuestra BBDD. Nos porporciona Estado 
 de Consultas, SGA, Lecturas Fisicas, lógicas, etc. 
 O sea, la bomba para el Tunning.
 

En este documento voy a describir de manera breve el funcionamiento de StatsPacks:

 La Instalación de StatsPacks Es muy simple: Basta la Ejecución del Script spcreate.sql, con un 
 usuario con derechos de DBA.
 
 $ORACLE_HOME/rdbms/admin/spcreate.sql
 

 $ORACLE_HOME/rdbms/admin/spdrop.sql
 

BEGIN
    statspack.snap(i_snap_level => 10, i_modify_parameter => 'true');
END;

Con la ejecución del Package anterior tendremos el nivel máximo de estadísticas.

 Para ver los resultados de StatPacks, ejecutaremos 

$ORACLE_HOME/rdbms/admin/spreport.sql

oraclMNG:hera2-New:/PKGHERA2/MNG/orabin/rdbms/admin>sqlplus perfstat/perfstat
 
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Oct 22 14:08:03 2007
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
 
SQL> start spreport
 
Current Instance
~~~~~~~~~~~~~~~~
 
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1328406229 SCOPE               1 SCOPE
 
 
 
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 1328406229        1 SCOPE        SCOPE        hera2
 
Using 1328406229 for database Id
Using          1 for instance number
 
Completed Snapshots
 
                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
SCOPE        SCOPE         6982 22 Oct 2007 14:00    10
 
 
 
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
 
Enter value for end_snap: 2
End   Snapshot Id specified: 2
 

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
 
Using the report name sp_1_2

$ORACLE_HOME/rdbms/admin/sppurge.sql

$ORACLE_HOME/rdbms/admin/sptrunc.sql

 
oraclMNG:hera2-New:/PKGHERA2/MNG/orabin/rdbms/admin>sqlplus perfstat/perfstat
 
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Oct 22 14:09:44 2007
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
 
SQL> start sptrunc
 
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables.  You may
wish to export the data before continuing.
 

About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you would like to continue, press <return>
 

Enter value for return:
Entered - starting truncate operation
 
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
Table truncated.
1 row deleted.
1 row deleted.
Commit complete.
Package altered.
Truncate operation complete

 Con la siguiente consulta podemos ver las Estadísticas que llevamos guardadas:

   SELECT name, snap_id, to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" 
   FROM stats$snapshot,v$database 
   ORDER BY 3;

 Para la recogida de estadísticas suele ser útil carear un JOB que nos recoja
 las estadísticas de forma peródica, para pacilitarnoslo, oracle nos proporciona el 
 siguiente script

 $ORACLE_HOME/rdbms/admin/spauto.sql

 Este script por defecto nos planifica una recogida cada hora.

oraclMNG:hera2-New:/PKGHERA2/MNG/orabin/rdbms/admin>sqlplus perfstat/perfstat
 
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Oct 22 14:10:46 2007
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
 
SQL> start spauto.sql
 
PL/SQL procedure successfully completed.
 

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
 
     JOBNO
----------
        26
 

Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
 

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
 
       JOB NEXT_DATE NEXT_SEC
---------- --------- --------
        26 22-OCT-07 15:00:00

• Purgar Statpacks
• Ver Statpacks

—-