Instalar la utilidad STATSPACK. Crear snapshots y generar informe.
Crear tablespace statspack_tsp con 100M en /u05/oradata/fabiancrea (crear dir.
/u05/oradata/fabiancrea).
Crear tablespace statspack_temp con 2M en /u05/oradata/fabiancrea .
Instalar statspack con $ORACLE_HOME/rdbms/admin/spcreate.sql, indicando los datos que se piden: clave de usuario perfstat, tablespace por defecto, y tablespace temporal por defecto.
$ mkdir /u05/oradata/fabiancrea
$ chmod Rg+w /u05/oradata/fabiancrea
SQL> create tablespace statpack_tsp datafile '/u05/oradata/fabiancrea /statpack_tsp01.dbf' size 100M
autoextend on next 10M maxsize 200M
extent management local autoallocate
segment space management auto;
Tablespace created.
SQL> create temporary tablespace statpack_temp
tempfile '/u05/oradata/fabiancrea/statpack_temp01.dbf' size 2M
autoextend on next 1M maxsize 10M;
Tablespace created.
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
... Installing Required Packages
...
... Creating PERFSTAT user ...
Choose the PERFSTAT user's password.
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password
Enter value for perfstat_password: perfcursoXY
...
Choose the Default tablespace for the PERFSTAT user
...
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for performance data is not supported.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
SEGAUTO
PERMANENT
STATPACK_TSP PERMANENT
SYSAUX PERMANENT *
TSP4K PERMANENT
USERS PERMANENT
Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: STATPACK_TSP
...
Choose the PERFSTAT user's temporary tablespace.
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.
...
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: STATPACK_TEMP
© Juan Luis Serradilla Amarilla y Francisco Fernández Martínez 11
Administración Avanzada de Oracle10g
...
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Una vez instalado STATSACK, comprobamos si ha habido errores (en los ficheros *.lis):
SQL> !grep i
err *.lis
spcpkg.lis:No errors.
spcpkg.lis:No errors.
spcpkg.lis:SPCPKG complete. Please check spcpkg.lis for any errors.
spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.
spcusr.lis:SPCUSR complete. Please check spcusr.lis for any errors.
Ahora podemos utilizar el paquete STATSPACK.
Primero compruebo si está activada la recolección de estadísticas:
SQL> show parameter timed_statistics
NAME TYPE VALUE
timed_statistics boolean TRUE
Ahora me conecto como PERFSTAT (o usuario con acceso al paquete STATSPACK) y genero el primer snapshot (baseline) con nivel 10 (i_snap_level). Después, espero al menos 5 minutos para generar otro snapshot, pues para sacar un informe necesito al menos dos:
SQL> connect perfstat
Enter password:
Connected.
SQL> exec STATSPACK.SNAP(i_snap_level=>10)
PL/SQL procedure successfully completed.
(... LANZAR ALGUNAS CONSULTAS, COMO LAS DOS SIGUIENTES ...)
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
5924
SQL> SELECT COUNT(*) FROM DBA_EXTENTS;
COUNT(*)
2647
(... ESPERAR 5 MINUTOS DESDE QUE HICIMOS EL SNAPSHOT...)
SQL> exec STATSPACK.SNAP
PL/SQL procedure successfully completed.
Ahora ya podemos generar el informe:
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
...
Enter value for begin_snap: 1
...
Enter value for end_snap: 2
...
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ Begin
Snap: 1 29Jan07
13:15:56 15 5.4
End Snap: 2 29Jan07
13:17:40 15 6.8
Elapsed: 1.73 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ Buffer
Cache: 28M Std Block Size: 2K
Shared Pool Size: 52M
Log Buffer: 2,819K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ Redo
size: 17,111.12 1,779,556.00
Logical reads: 352.43 36,653.00
Block changes: 64.20 6,677.00
Physical reads: 35.24 3,665.00
Physical writes: 31.09 3,233.00
User calls: 0.10 10.00
Parses: 13.85 1,440.00
Hard parses: 1.34 139.00
Sorts: 6.13 637.00
Logons: 0.00 0.00
Executes: 29.43 3,061.00
Transactions: 0.01
% Blocks changed per Read: 18.22 Recursive Call %: 99.98
Rollback per transaction %: 0.00 Rows per Sort: 12.03
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.95
Buffer Hit %: 89.99 Inmemory
Sort %: 100.00
Library Hit %: 83.66 Soft Parse %: 90.35
Execute to Parse %: 52.96 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 50.98 % NonParse
CPU: 61.48
Shared Pool Statistics Begin End
Memory
Usage %: 83.22 84.28
% SQL with executions>1: 72.27 87.15
% Memory for SQL w/exec>1: 66.21 78.80
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
dbfile sequential read 1,387 6 4 74.5
CPU time 1 16.4
db file scattered read 107 0 2 3.1
log file parallel write 9 0 24 2.6
log file switch completion 2 0 55 1.3
...
End of Report ( sp_1_2.lst )
/u05/oradata/fabiancrea).
Crear tablespace statspack_temp con 2M en /u05/oradata/fabiancrea .
Instalar statspack con $ORACLE_HOME/rdbms/admin/spcreate.sql, indicando los datos que se piden: clave de usuario perfstat, tablespace por defecto, y tablespace temporal por defecto.
$ mkdir /u05/oradata/fabiancrea
$ chmod Rg+w /u05/oradata/fabiancrea
SQL> create tablespace statpack_tsp datafile '/u05/oradata/fabiancrea /statpack_tsp01.dbf' size 100M
autoextend on next 10M maxsize 200M
extent management local autoallocate
segment space management auto;
Tablespace created.
SQL> create temporary tablespace statpack_temp
tempfile '/u05/oradata/fabiancrea/statpack_temp01.dbf' size 2M
autoextend on next 1M maxsize 10M;
Tablespace created.
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
... Installing Required Packages
...
... Creating PERFSTAT user ...
Choose the PERFSTAT user's password.
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password
Enter value for perfstat_password: perfcursoXY
...
Choose the Default tablespace for the PERFSTAT user
...
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for performance data is not supported.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
SEGAUTO
PERMANENT
STATPACK_TSP PERMANENT
SYSAUX PERMANENT *
TSP4K PERMANENT
USERS PERMANENT
Pressing
tablespace (identified by *) being used.
Enter value for default_tablespace: STATPACK_TSP
...
Choose the PERFSTAT user's temporary tablespace.
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.
...
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: STATPACK_TEMP
© Juan Luis Serradilla Amarilla y Francisco Fernández Martínez 11
Administración Avanzada de Oracle10g
...
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Una vez instalado STATSACK, comprobamos si ha habido errores (en los ficheros *.lis):
SQL> !grep i
err *.lis
spcpkg.lis:No errors.
spcpkg.lis:No errors.
spcpkg.lis:SPCPKG complete. Please check spcpkg.lis for any errors.
spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.
spcusr.lis:SPCUSR complete. Please check spcusr.lis for any errors.
Ahora podemos utilizar el paquete STATSPACK.
Primero compruebo si está activada la recolección de estadísticas:
SQL> show parameter timed_statistics
NAME TYPE VALUE
timed_statistics boolean TRUE
Ahora me conecto como PERFSTAT (o usuario con acceso al paquete STATSPACK) y genero el primer snapshot (baseline) con nivel 10 (i_snap_level). Después, espero al menos 5 minutos para generar otro snapshot, pues para sacar un informe necesito al menos dos:
SQL> connect perfstat
Enter password:
Connected.
SQL> exec STATSPACK.SNAP(i_snap_level=>10)
PL/SQL procedure successfully completed.
(... LANZAR ALGUNAS CONSULTAS, COMO LAS DOS SIGUIENTES ...)
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
5924
SQL> SELECT COUNT(*) FROM DBA_EXTENTS;
COUNT(*)
2647
(... ESPERAR 5 MINUTOS DESDE QUE HICIMOS EL SNAPSHOT...)
SQL> exec STATSPACK.SNAP
PL/SQL procedure successfully completed.
Ahora ya podemos generar el informe:
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
...
Enter value for begin_snap: 1
...
Enter value for end_snap: 2
...
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ Begin
Snap: 1 29Jan07
13:15:56 15 5.4
End Snap: 2 29Jan07
13:17:40 15 6.8
Elapsed: 1.73 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ Buffer
Cache: 28M Std Block Size: 2K
Shared Pool Size: 52M
Log Buffer: 2,819K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ Redo
size: 17,111.12 1,779,556.00
Logical reads: 352.43 36,653.00
Block changes: 64.20 6,677.00
Physical reads: 35.24 3,665.00
Physical writes: 31.09 3,233.00
User calls: 0.10 10.00
Parses: 13.85 1,440.00
Hard parses: 1.34 139.00
Sorts: 6.13 637.00
Logons: 0.00 0.00
Executes: 29.43 3,061.00
Transactions: 0.01
% Blocks changed per Read: 18.22 Recursive Call %: 99.98
Rollback per transaction %: 0.00 Rows per Sort: 12.03
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.95
Buffer Hit %: 89.99 Inmemory
Sort %: 100.00
Library Hit %: 83.66 Soft Parse %: 90.35
Execute to Parse %: 52.96 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 50.98 % NonParse
CPU: 61.48
Shared Pool Statistics Begin End
Memory
Usage %: 83.22 84.28
% SQL with executions>1: 72.27 87.15
% Memory for SQL w/exec>1: 66.21 78.80
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
dbfile sequential read 1,387 6 4 74.5
CPU time 1 16.4
db file scattered read 107 0 2 3.1
log file parallel write 9 0 24 2.6
log file switch completion 2 0 55 1.3
...
End of Report ( sp_1_2.lst )
Comentarios