Oracle - Installation et configuration de StatsPack


1- Introduction

STATSPACK est un outil de diagnostic des performances, disponible depuis Oracle 8i. Il est le successeur du couple BSTAT/ESTAT avec de nouvelles fonctionnalités. STATSPACK est un outil de diagnostic pour les problèmes de performance liés au paramétrage de l'instance mais il supporte également les activités des applications en fournissant les commandes SQL qui chargent le plus le système, aussi STATSPACK peut être utilisé pour réagir sur des problèmes de performance comme pour monitorer les changements de charge sur un système.

Le paramètre timed_statistics doit impérativement être posistionné à true.

Les intervalles de temps entre les snapshots (points de collection) doivent être en général de l'ordre des minutes, jamais en heures ou en jours. L'intervalle de temps idéal entre deux snapshots est de 15 ou 30 minutes.

Une autre erreur commune avec STATSPACK est de réaliser des snapshots seulement lorsqu'il apparaît un problème, mais il est bon d'avoir des snapshots de manière continue dans le temps afin de déterminer si de nouvelles fonctionnalités sont apparues, lesquelles montrent des scans de table dramatiques ou encore un pourcentage de parse soft qui passe de 99% à 70% sur une tranche horaire, ce qui peut indiquer que cette nouvelle fonctionnalité n'utilise pas de variables ''bindées''.

Cet article présente l'architecture, l'installation et le paramétrage de STATSPACK. L'interprétation des résultat fait l'objet d'un autre article.

2- Architecture

L'utilitaire STATSPACK est une extension des utilitaires UTLBSTAT et 

UTLESTAT qui ont été utilisées depuis les premières versions d'Oracle.

2-1- UTLBSTAT-ULTESTAT

Les utilitaires BSTAT-ESTAT capturent les informations directement depuis les structures en mémoire d'Oracle (Vues dynamiques V$) et comparent alors les informations entre deux snapshots en vue de produire un rapport sur l'activité de la base de données durant le temps écoulé. Les fichiers utlbstat.sql et utlestat.sql montrent que les données sont prises principalement à partir de la vue V$SYSSTAT.

insert into stats$begin_stats select * from v$sysstat;
insert into stats$end_stats select * from v$sysstat;

2-2- STATSPACK

Avec STATSPACK, les données sont collectées à partir des données en mémoire (vues dynamiques V$) et ces données sont transférées dans des tables dédiées à l'utilitaire STATSPACK.

Ces valeurs sont alors disponibles pour comparaison avec d'autres snapshots.

Dans la plupart des cas, il existe une correspondance entre les vues V$ de la SGA et les tables correspondantes STATSPACK, comme par exemple V$SYSSTAT et STATS$sysstat.

SQL> desc v$sysstat;

Name                Null ?     Type
----                ------     ----
STATISTIC#                      NUMBER
NAME                            VARCHAR2(64)
CLASS                           NUMBER
VALUE                           NUMBER
STAT_ID                         NUMBER
SQL> desc stats$sysstat;
      
Name                Null ?     Type
----                ------     ----
SNAP_ID             NOT NULL   NUMBER
DBID                NOT NULL   NUMBER
INSTANCE_NUMBER     NOT NULL   NUMBER
STATISTIC#          NOT NULL   NUMBER
NAME                NOT NULL   VARCHAR2(64)
VALUE                          NUMBER

Il est important de comprendre que les données stockées dans les tables STATSPACK sont des valeurs cumulatives.

Les informations contenues dans les vues V$ sont collectées depuis le démarrage de l'instance. Pour obtenir un rapport significatif sur un intervalle de temps, il est nécessaire de réaliser un rapport sur deux snapshots valides.

Un rapport sera invalide si l'instance a été arrêtée entre deux snapshots car les valeurs cumulées dans les vues V$ sont remises à 0 au redémarrage de l'instance.

3- Installation et désinstallation

3-1- Création du tablespace tools

Dans la mesure du possible, il est fortement recommandé de ne pas installer STATSPACK sur le tablespace SYSTEM. Dans la pratique, STATSPACK s'installe dans le tablespace TOOLS qui éventuellement existe déjà. Une taille initiale de 250M pour le tablespace TOOLS est un bon départ.

SQL > create tablespace tools
            datafile '/sdata/oracle/v8/TSTT1ORA/data/tools_01.dbf' size 250M;

3-2- Création du package STATSPACK

3-2-1- Prérequis

Durant la création du package STATSPACK, le user PERFSTAT (mot de passe PERFSTAT) sera créé et l'utilisateur sera interrogé sur le tablespace par défaut (TOOLS) et le tablespace temporaire pour l'utilisateur PERFSTAT.

Pour l'installation, svrmgrl ne peut être utilisé, il est obligatoire d'utiliser SQL*Plus en se connectant en tant que sysdba (connect / as sysdba).

Par ailleurs, avant l'installation de STATSPACK, il est impératif de s'assurer que les scripts listés ci-dessous ont été exécutés avec le user SYS :

3-2-2- Procédure d'installation

Les scripts sql d'installation sont dans le répertoire $ORACLE_HOME/rdbms/admin et diffèrent selon la version d'Oracle. Les procédures d'installation sont résumées dans le tableau qui suit :

Plateforme Installation Oracle 8.1.6 Installation Oracle 8.1.7 et versions supérieures
Unix
> connect / as sysdba
> @?/rdbms/admin/statscre
> connect / as sysdba
> @?/rdbms/admin/spcreate
NT
> connect / as sysdba
> @%ORACLE_HOME%\rdbms\admin\statscre
> connect / as sysdba
> @%ORACLE_HOME%\rdbms\admin\spcreate

3-2-3- Détail de l'installation pour Oracle 8.1.7 et versions supérieures

Avec les version 8.1.7 et supérieures, le script spcreate.sql dans le répertoire $ORACLE_HOME/rdbms/admin appelle 3 scripts dans ce même répertoire :

C'est le script spcusr.sql qui intéragit avec l'utilisateur pour demander le tablespace par défaut et le tablespace temporaire pour l'utilisateur perfstat.

Pour le tablespace par défaut :

Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the STATSPACK tables
and indexes.  This will also be the PERFSTAT user's default tablespace.
Using the SYSTEM tablespace to store statistical data is
NOT recommended.

TABLESPACE_NAME
------------------------------
RBS
CGC_DATA
TEMP
TOOLS
CGC_INDX
SCOTT_DATA01
SCOTT_INDX01
SCOTT_PART_DATA01
SCOTT_PART_INDX01
OEM_REPOSITORY

10 ligne(s) sélectionnée(s).


Specify PERFSTAT user's default   tablespace
Entrez une valeur pour default_tablespace : tools

Pour le tablespace temporaire :

Specify PERFSTAT user's temporary tablespace
Entrez une valeur pour temporary_tablespace : temp

3-2-4- Objets créés après l'installation

A l'issue de l'installation, le schéma perfstat comprend les objets ci-dessous :

Type d'objet Objet
TABLE STATS$BG_EVENT_SUMMARY
STATS$BUFFER_POOL_STATISTICS
STATS$DATABASE_INSTANCE
STATS$ENQUEUESTAT
STATS$FILESTATXS
STATS$IDLE_EVENT
STATS$LATCH
STATS$LATCH_CHILDREN
STATS$LATCH_MISSES_SUMMARY
STATS$LATCH_PARENT
STATS$LEVEL_DESCRIPTION
STATS$LIBRARYCACHE
STATS$PARAMETER
STATS$ROLLSTAT
STATS$ROWCACHE_SUMMARY
STATS$SESSION_EVENT
STATS$SESSTAT
STATS$SGA
STATS$SGASTAT
STATS$SNAPSHOT
STATS$SQLTEXT
STATS$SQL_STATISTICS
STATS$SQL_SUMMARY
STATS$STATSPACK_PARAMETER
STATS$SYSSTAT
STATS$SYSTEM_EVENT
STATS$TEMPSTATXS
STATS$WAITSTAT
SEQUENCE STATS$SNAPSHOT_ID
PACKAGE STATSPACK
  • procédure STAT_CHANGES
  • procédure SNAP
  • procédure SLARTI
  • procédure MODIFY_STATSPACK_PARAMETER
  • procédure QAM_STATSPACK_PARAMETER

3-3- Désinstallation du package STATSPACK

3-3-1- Procédure de désinstallation

Les scripts sql de désinstallation sont dans le répertoire $ORACLE_HOME/rdbms/admin et diffèrent selon la version d'Oracle. Les procédures de désinstallation sont résumées dans le tableau qui suit :

Plateforme Désinstallation Oracle 8.1.6 Désinstallation Oracle 8.1.7 et versions supérieures
Unix
> connect / as sysdba
> @?/rdbms/admin/statsdrp
> connect / as sysdba
> @?/rdbms/admin/spdrop
NT
> connect / as sysdba
> @%ORACLE_HOME%\rdbms\admin\statsdrp
> connect / as sysdba
> @%ORACLE_HOME%\rdbms\admin\spdrop

3-3-2- Détail de la désinstallation pour Oracle 8.1.7 et versions supérieures

Avec les versions 8.1.7 et supérieures, le script spdrop.sql dans le répertoire $ORACLE_HOME/rdbms/admin appelle 2 scripts dans ce même répertoire 

4- Configuration (niveaux et seuils)

Au sein de statspack, il existe des niveaux et des seuils pour déterminer les données à collectionner. Le niveau et les seuils de la configuration courante sont stockés dans la table STATS$STATSPACK_PARAMETER.

Le niveau (level) contrôle le type de données collectées alors que le paramètre seuil (threshold) joue comme un filtre sur la collection des ordres SQL dans la table STATS$SQL_SUMMARY.

4-1- Niveaux (levels)

Les niveaux disponibles pour une version d'Oracle sont stockés dans la table STATS$LEVEL_DESCRIPTION.

> select snap_level, description from PERFSTAT.STATS$LEVEL_DESCRIPTION;
SNAP_LEVEL DESCRIPTION

0          This level captures general statistics, including rollback segment,
           row cache, SGA, system events, background events, session events,
           system statistics, wait statistics, lock statistics, and Latch information

5          This level includes capturing high resource usage SQL Statements,
           along with all data captured by lower levels
           
6          This level includes capturing SQL plan and SQL plan usage information
           for high resource usage SQL Statements, along
           with all data captured by lower levels
           
7          This level captures segment level statistics, including logical
           and physical reads, row lock, itl and buffer busy waits,
           along with all data captured by lower levels
          
10         This level includes capturing Child Latch statistics, along with
           all data captured by lower levels

Le niveau 6 n'est disponible qu'à partir d'Oracle 9i

Le niveau 7 n'est disponible qu'à partir d'Oracle 10g

Le niveau 5 est le niveau par défaut appliqué.

Le niveau 10 est très consommateur en ressources, aussi ce dernier ne doit être lancé qu'à la demande du support Oracle.

La fonction statspack.snap permet d'exécuter et de changer le niveau d'un snapshot :

> exec statspack.snap(i_snap_level => <level : 0,5,6,7,10>)

Le paramètre i_modify_parameter positionné à true permet de changer de niveau pour tous les futurs snapshots :

> exec statspack.snap(i_snap_level => <level : 0,5,6,7,10>, i_modify_parameter => 'true')

Pour visualiser le niveau par défaut appliqué :

> select snap_level from PERFSTAT.STATS$STATSPACK_PARAMETER;
SNAP_LEVEL
----------
5

4-2- Seuils (thresholds)

Les niveaux 5 et 10 capturent par défaut les commandes SQL les plus consommatrices qui dépassent un des quatre seuils ci-dessous :

Ces seuils peuvent facilement être retrouvés dans la table PERFSTAT.STATS$STATSPACK_PARAMETER.

SQL> desc perfstat.STATS$STATSPACK_PARAMETER;
DBID             NOT NULL NUMBER
INSTANCE_NUMBER  NOT NULL NUMBER
SESSION_ID       NOT NULL NUMBER
SNAP_LEVEL       NOT NULL NUMBER Niveau du snapshot
NUM_SQL          NOT NULL NUMBER Nombre de commandes SQL (50)
EXECUTIONS_TH    NOT NULL NUMBER Nombre d'exécutions (1000)
PARSE_CALLS_TH   NOT NULL NUMBER Nombre d'appels de parse (1000)
DISK_READS_TH    NOT NULL NUMBER Nombre de lectures physiques (1000)
BUFFER_GETS_TH   NOT NULL NUMBER Nombre de lectures logiques (10000)
SHARABLE_MEM_TH  NOT NULL NUMBER
VERSION_COUNT_TH NOT NULL NUMBER
PIN_STATSPACK    NOT NULL VARCHAR2(10)
ALL_INIT         NOT NULL VARCHAR2(5)
LAST_MODIFIED             DATE
UCOMMENT                  VARCHAR2(160)
JOB                       NUMBER

SQL> select snap_level, num_sql, executions_th,
 parse_calls_th, disk_reads_th, buffer_gets_th
 from perfstat.STATS$STATSPACK_PARAMETER;

snap_level num_sql executions_th parse_calls_th disk_reads_th buffer_gets_th
---------- ------- ------------- -------------- ------------- --------------
5               50           100           1000          1000          10000

Il est possible de modifier ces valeurs par défaut avec ou sans prise de snapshot. Les valeurs qui sont modifiables sont listées dans le tableau qui suit :

Paramètre Intervalle de valeurs Valeur par défaut Description
I_snap_level 0,5,10 5 Niveau de snapshot
I_ucomment Text Blanc Commentaire stocké pour le snapshot
I_executions_th Integer >=0 100 SQL, # times executed
I_disk_reads_th Integer >=0 1000 SQL, # disk read statements
I_parse_calls_th Integer >=0 1000 SQL, # parse calls
I_buffer_gets_th Integer >=0 10000 SQL, # buffer gets for statement
I_session_id Valide sid -v$session 0 (toutes les sessions) Capture pour un SID en particulier
I_modify_parameter True/False False si true, ces paramètres sont sauvegardés pour tous les snapshots.

La procédure modify_statspack_parameter du package sera utilisée pour modifier un seuil sans prise de snashot :

> exec statspack.modify_statspack_parameter(i_executions_th => 100);

5- Créer, visualiser et supprimer des snapshots

Pour générer un snapshot :

sqlplus perfstat/<mot_de_passe_perfstat>
SQL > exec statspack.snap ;

Pour visualiser les snapshots disponibles :

sqlplus perfstat/<mot_de_passe_perfstat>
SQL > select name, snap_id,
to_char(snap_time,'dd/mm/yyyy hh24:mi:ss')
from STATS$SNAPSHOT, v$database;

Pour purger les snapshots (Environnement Unix, version 8.1.7 et supérieures) :

SQL > ?/rdbms/admin/sppurge.sql

Le script SQL interagit avec l'utilisateur pour demander l'id de snapshot de départ et l'id de snapshot de fin pour la purge :

6- Génération des rapports STATSPACK

Pour générer un rapport STATSPACK entre deux snapshots :

Plateforme Désinstallation Oracle 8.1.6 Désinstallation Oracle 8.1.7 et versions supérieures
Unix
> connect perfstat/<mot_de_passe>
> @?/rdbms/admin/statsrep
> connect perfstat/<mot_de_passe>
> @?/rdbms/admin/spreport
NT
> connect perfstat/<mot_de_passe>
> @%ORACLE_HOME%\rdbms\admin\statsrep
> connect perfstat/<mot_de_passe>
> @%ORACLE_HOME%\rdbms\admin\spreport

Le script SQL interagit avec l'utilisateur pour demander l'id de snapshot de départ et l'id de snapshot de fin pour la génération du rapport, ainsi que le nom du fichier pour générer le rapport. Ce rapport sera créé dans le répertoire courant d'exécution de SQL * Plus.

Exemple pratique :

SQL> @?/rdbms/admin/spreport.sql


   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1424898654 CGC                 1 tstt1ora


Completed Snapshots

                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
tstt1ora     CGC              1 28 Jan 2005 17:26     5
                              2 28 Jan 2005 17:49     5
                              3 28 Jan 2005 17:50    10
                              4 28 Jan 2005 17:51    10


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entrez une valeur pour begin_snap : 1
Begin Snapshot Id specified: 1

Entrez une valeur pour 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.
Entrez une valeur pour report_name : report_statspack_1_2.txt



STATSPACK report for

DB Name         DB Id    Instance     Inst Num Release     OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
CGC           1424898654 tstt1ora            1 8.1.7.0.0   NO  CGC

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
 Begin Snap:          1 28-Jan-05 17:26:14        7
   End Snap:          2 28-Jan-05 17:49:37        7
    Elapsed:                  23.38 (mins)

Cache Sizes
~~~~~~~~~~~
           db_block_buffers:       2048          log_buffer:      32768
              db_block_size:       8192    shared_pool_size:   10240000

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:                196.66            275,912.00
              Logical reads:                  2.64              3,703.00
              Block changes:                  0.30                423.00
             Physical reads:                  0.10                138.00
            Physical writes:                  0.08                115.00
                 User calls:                  0.03                 46.00
                     Parses:                  0.08                108.00
                Hard parses:                  0.01                 19.00
                      Sorts:                  0.05                 70.00
                     Logons:                  0.00                  0.00
                   Executes:                  0.12                169.00
               Transactions:                  0.00

  % Blocks changed per Read:   11.42    Recursive Call %:   97.62
 Rollback per transaction %:    0.00       Rows per Sort:  106.13

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   96.27    In-memory Sort %:   94.29
            Library Hit   %:   76.12        Soft Parse %:   82.41
         Execute to Parse %:   36.09         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:             % Non-Parse CPU:

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   94.90   94.21
    % SQL with executions>1:   69.11   76.45
  % Memory for SQL w/exec>1:   37.30   53.25

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                             Wait     % Total
Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
control file parallel write                           451            0     .00
direct path read                                       62            0     .00
direct path write                                      55            0     .00
control file sequential read                           43            0     .00
db file sequential read                                23            0     .00
-------------------------------------------------------------

Wait Events for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> cs - centisecond -  100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

                                                                    Avg
                                                     Total Wait    wait  Waits
Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
---------------------------- ------------ ---------- ----------- ------ ------
control file parallel write           451          0           0      0  451.0
direct path read                       62          0           0      0   62.0
direct path write                      55          0           0      0   55.0
control file sequential read           43          0           0      0   43.0
db file sequential read                23          0           0      0   23.0
log file parallel write                18          0           0      0   18.0
refresh controlfile command             6          0           0      0    6.0
SQL*Net break/reset to clien            2          0           0      0    2.0
db file parallel write                  1          0           0      0    1.0
db file scattered read                  1          0           0      0    1.0
file open                               1          0           0      0    1.0
log file sync                           1          0           0      0    1.0
virtual circuit status                 46         46           0      0   46.0
SQL*Net message from client            39          0           0      0   39.0
SQL*Net message to client              39          0           0      0   39.0
-------------------------------------------------------------

Background Wait Events for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last)

                                                                    Avg
                                                     Total Wait    wait  Waits
Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
---------------------------- ------------ ---------- ----------- ------ ------
control file parallel write           451          0           0      0  451.0
control file sequential read           24          0           0      0   24.0
log file parallel write                17          0           0      0   17.0
db file sequential read                 9          0           0      0    9.0
db file parallel write                  1          0           0      0    1.0
db file scattered read                  1          0           0      0    1.0
rdbms ipc message                   1,401      1,344           0      0 ######
pmon timer                            445        445           0      0  445.0
smon timer                              5          5           0      0    5.0
-------------------------------------------------------------

SQL ordered by Gets for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

  Buffer Gets    Executions  Gets per Exec  % Total  Hash Value
--------------- ------------ -------------- ------- ------------
            156           38            4.1     4.2   2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1

             89            1           89.0     2.4   2249281901
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
 and file#=:2 and block#=:3

 ...

SQL ordered by Gets for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

  Buffer Gets    Executions  Gets per Exec  % Total  Hash Value
--------------- ------------ -------------- ------- ------------
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1 from obj$ where owner#=:1 and name=:2 and namespace=:3 an
d(remoteowner=:4 or remoteowner is null and :4 is null)and(linkn
ame=:5 or linkname is null and :5 is null)and(subname=:6 or subn
ame is null and :6 is null)

              4            1            4.0     0.1   1004464078
select grantee#,privilege#,nvl(col#,0),max(nvl(option$,0)) from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0)
order by grantee#

              4            1            4.0     0.1   4049165760
 ...
-------------------------------------------------------------

SQL ordered by Reads for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> End Disk Reads Threshold:    1000

 Physical Reads  Executions  Reads per Exec % Total  Hash Value
--------------- ------------ -------------- ------- ------------
              0            5            0.0     0.0    114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1

              0            3            0.0     0.0    199702406
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i
 .distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa
mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt
,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n

 ...

SQL ordered by Reads for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> End Disk Reads Threshold:    1000

 Physical Reads  Executions  Reads per Exec % Total  Hash Value
--------------- ------------ -------------- ------- ------------

              0            1            0.0     0.0   1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3

              0            1            0.0     0.0   1966425544
select text from view$ where rowid=:1

-------------------------------------------------------------

SQL ordered by Executions for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> End Executions Threshold:     100

 Executions   Rows Processed    Rows per Exec   Hash Value
------------ ---------------- ---------------- ------------
          38               40              1.1   2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1

           6               38              6.3   1536916657
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1

           5                0              0.0    114078687


SQL ordered by Executions for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> End Executions Threshold:     100

 Executions   Rows Processed    Rows per Exec   Hash Value
------------ ---------------- ---------------- ------------
obj#, d_owner#, nvl(property,0) from dependency$,obj$ where d_ob
j#=:1 and p_obj#=obj#(+) order by order#

           1                0              0.0   1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4

           1                1              1.0   1877781575

-------------------------------------------------------------

Instance Activity Stats for DB: CGC  Instance: tstt1ora  Snaps: 1 -2

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
background checkpoints completed                 0          0.0          0.0
background checkpoints started                   0          0.0          0.0
background timeouts                          1,344          1.0      1,344.0
buffer is not pinned count                   1,328          1.0      1,328.0
buffer is pinned count                          18          0.0         18.0
bytes received via SQL*Net from c            4,655          3.3      4,655.0
bytes sent via SQL*Net to client            45,926         32.7     45,926.0
calls to get snapshot scn: kcmgss              209          0.2        209.0
calls to kcmgas                                  7          0.0          7.0
calls to kcmgcs                                 17          0.0         17.0
cleanouts and rollbacks - consist                0          0.0          0.0
cleanouts only - consistent read                 0          0.0          0.0
cluster key scan block gets                    244          0.2        244.0
cluster key scans                               61          0.0         61.0
commit cleanout failures: block l                0          0.0          0.0
commit cleanout failures: buffer                 0          0.0          0.0
commit cleanout failures: callbac                3          0.0          3.0
commit cleanouts                                55          0.0         55.0
commit cleanouts successfully com               52          0.0         52.0
consistent changes                               4          0.0          4.0
consistent gets                              2,291          1.6      2,291.0
CR blocks created                                4          0.0          4.0
cursor authentications                          11          0.0         11.0
data blocks consistent reads - un                4          0.0          4.0
db block changes                               423          0.3        423.0
db block gets                                1,412          1.0      1,412.0
DBWR buffers scanned                             0          0.0          0.0
DBWR checkpoint buffers written                  1          0.0          1.0
DBWR checkpoints                                 0          0.0          0.0
DBWR free buffers found                          0          0.0          0.0
DBWR lru scans                                   0          0.0          0.0
DBWR make free requests                          0          0.0          0.0
DBWR summed scan depth                           0          0.0          0.0
DBWR transaction table writes                    1          0.0          1.0
DBWR undo block writes                           0          0.0          0.0
deferred (CURRENT) block cleanout               23          0.0         23.0
dirty buffers inspected                          0          0.0          0.0
enqueue conversions                              0          0.0          0.0
enqueue releases                               168          0.1        168.0
enqueue requests                               168          0.1        168.0
enqueue timeouts                                 0          0.0          0.0
enqueue waits                                    0          0.0          0.0
execute count                                  169          0.1        169.0
free buffer inspected                            0          0.0          0.0
free buffer requested                           73          0.1         73.0
hot buffers moved to head of LRU                67          0.1         67.0
immediate (CR) block cleanout app                0          0.0          0.0
immediate (CURRENT) block cleanou               10          0.0         10.0
index fast full scans (full)                     0          0.0          0.0
leaf node splits                                 5          0.0          5.0
logons cumulative                                0          0.0          0.0
logons current
messages received                               26          0.0         26.0
messages sent                                   26          0.0         26.0
no buffer to keep pinned count               1,056          0.8      1,056.0
no work - consistent read gets               1,003          0.7      1,003.0

Instance Activity Stats for DB: CGC  Instance: tstt1ora  Snaps: 1 -2

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
opened cursors cumulative                      103          0.1        103.0
opened cursors current
parse count (hard)                              19          0.0         19.0
parse count (total)                            108          0.1        108.0
physical reads                                 138          0.1        138.0
physical reads direct                          114          0.1        114.0
physical writes                                115          0.1        115.0
physical writes direct                         114          0.1        114.0
physical writes non checkpoint                 114          0.1        114.0
pinned buffers inspected                         0          0.0          0.0
prefetched blocks                                2          0.0          2.0
PX local messages recv'd                         0          0.0          0.0
PX local messages sent                           0          0.0          0.0
recovery array reads                             0          0.0          0.0
recovery blocks read                             0          0.0          0.0
recursive calls                              1,883          1.3      1,883.0
redo blocks written                            509          0.4        509.0
redo buffer allocation retries                   0          0.0          0.0
redo entries                                   234          0.2        234.0
redo log space requests                          0          0.0          0.0
redo size                                  275,912        196.7    275,912.0
redo synch writes                                1          0.0          1.0
redo wastage                                 5,196          3.7      5,196.0
redo writes                                     17          0.0         17.0
rollback changes - undo records a                0          0.0          0.0
rollbacks only - consistent read                 4          0.0          4.0
rows fetched via callback                      254          0.2        254.0
session logical reads                        3,703          2.6      3,703.0
session pga memory                               0          0.0          0.0
session pga memory max                           0          0.0          0.0
session uga memory                           4,564          3.3      4,564.0
session uga memory max                      15,696         11.2     15,696.0
sorts (disk)                                     4          0.0          4.0
sorts (memory)                                  66          0.1         66.0
sorts (rows)                                 7,429          5.3      7,429.0
SQL*Net roundtrips to/from client               39          0.0         39.0
summed dirty queue length                        0          0.0          0.0
switch current to new buffer
table fetch by rowid                           351          0.3        351.0
table fetch continued row                        0          0.0          0.0
table scan blocks gotten                       332          0.2        332.0
table scan rows gotten                         799          0.6        799.0
table scans (long tables)                        1          0.0          1.0
table scans (short tables)                      12          0.0         12.0
total file opens                                 1          0.0          1.0
transaction rollbacks                            0          0.0          0.0
user calls                                      46          0.0         46.0
user commits                                     1          0.0          1.0
write clones created in backgroun                0          0.0          0.0
write clones created in foregroun                0          0.0          0.0
-------------------------------------------------------------

Tablespace IO Stats for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TEMP
            86       0    0.0     1.3          114        0          0    0.0
TOOLS
            21       0    0.0     1.0            0        0          0    0.0
SYSTEM
            10       0    0.0     1.2            0        0          0    0.0
RBS
             0       0    0.0                    1        0          0    0.0
-------------------------------------------------------------

File IO Stats for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
->ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
RBS                      /SDATA/ORACLE/V8/TSTT1ORA/DATA/RBS01.DBF
             0       0                           1        0          0

SYSTEM                   /SDATA/ORACLE/V8/TSTT1ORA/DATA/SYSTEM01.DBF
            10       0    0.0     1.2            0        0          0

TEMP                     /SDATA/ORACLE/V8/TSTT1ORA/DATA/TEMP01.DBF
            86       0    0.0     1.3          114        0          0

TOOLS                    /SDATA/ORACLE/V8/TSTT1ORA/DATA/TOOLS01.DBF
            21       0    0.0     1.0            0        0          0

-------------------------------------------------------------

Buffer Pool Statistics for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
-> Pools   D: default pool,  K: keep pool,  R: recycle pool

                                                      Free    Write     Buffer
       Buffer    Consistent    Physical   Physical  Buffer Complete       Busy
P        Gets          Gets       Reads     Writes   Waits    Waits      Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D          80             0          31          1       0        0          0
          -------------------------------------------------------------

Rollback Segment Stats for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
->A high value for "Pct Waits" suggests more rollback segments may be required

       Trans Table      Pct   Undo Bytes
RBS No     Gets       Waits     Written        Wraps  Shrinks  Extends
------ ------------ ------- --------------- -------- -------- --------
     0          6.0    0.00               0        0        0        0
     1          6.0    0.00               0        0        0        0
     2          6.0    0.00               0        0        0        0
     3         29.0    0.00          74,290        0        0        0
     4          7.0    0.00           1,712        0        0        0
     5          6.0    0.00               0        0        0        0
     6         26.0    0.00               0        0        0        0
     7          6.0    0.00               0        0        0        0
          -------------------------------------------------------------

Rollback Segment Storage for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
->Optimal Size should be larger than Avg Active

RBS No    Segment Size      Avg Active    Optimal Size    Maximum Size
------ --------------- --------------- --------------- ---------------
     0         401,408               0                         401,408
     1       4,186,112         142,081       4,194,304       4,186,112
     2       4,186,112          51,609       4,194,304      12,574,720
     3       4,186,112         142,081       4,194,304       4,186,112
     4       4,186,112          52,428       4,194,304       4,186,112
     5       4,186,112         141,262       4,194,304       4,186,112
     6       4,186,112         141,262       4,194,304       7,331,840
     7       4,186,112          99,614       4,194,304       4,186,112
-------------------------------------------------------------

Latch Activity for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
  willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

                                                Pct    Avg                 Pct
                                   Get          Get   Slps       NoWait NoWait
Latch Name                       Requests      Miss  /Miss     Requests   Miss
----------------------------- -------------- ------ ------ ------------ ------
active checkpoint queue latch            447    0.0                   0
cache buffers chains                   7,242    0.0                  77    0.0
cache buffers lru chain                   88    0.0                   0
checkpoint queue latch                 2,301    0.0                   0
dml lock allocation                       48    0.0                   0
enqueue hash chains                      326    0.0                   0
enqueues                                 606    0.0                   0
job_queue_processes parameter             21    0.0                   0
ktm global data                            5    0.0                   0
library cache                          3,830    0.0                   4    0.0
library cache load lock                   86    0.0                   0
list of block allocation                  20    0.0                   0
loader state object freelist              16    0.0                   0
messages                               2,817    0.0                   0
multiblock read objects                   16    0.0                   0
ncodef allocation latch                   21    0.0                   0
redo allocation                          722    0.0                   0
redo writing                           1,831    0.0                   0
row cache objects                      1,051    0.0                   5    0.0
sequence cache                             3    0.0                   0
session allocation                       929    0.0                   0
session idle bit                         110    0.0                   0
session switching                         21    0.0                   0
session timer                            445    0.0                   0
shared pool                            1,178    0.0                   0
sort extent pool                          42    0.0                   0
Token Manager                              1    0.0                   5    0.0
transaction allocation                    33    0.0                   0
transaction branch allocation             21    0.0                   0
undo global data                         180    0.0                   0
virtual circuit queues                    69    0.0                   0
-------------------------------------------------------------

Dictionary Cache Stats for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
->"Pct Misses"  should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA"     is the ratio of usage to allocated size for that cache

                           Get         Pct     Scan   Pct      Mod  Final  Pct
Cache                    Requests     Miss Requests  Miss      Req  Usage  SGA
---------------------- ------------ ------ -------- ----- -------- ------ ----
dc_constraints                    0               0              0    182   99
dc_database_links                 0               0              0      0    0
dc_files                          0               0              0      6   29
dc_free_extents                 105    0.0        0              0     34   51
dc_global_oids                    0               0              0      0    0
dc_histogram_data                 0               0              0      0    0
dc_histogram_data_valu            0               0              0      0    0
dc_histogram_defs                 0               0              0     56   46
dc_object_ids                    38    0.0        0              0    530  100
dc_objects                      110    1.8        0              0  1,233  100
dc_outlines                       0               0              0      0    0
dc_profiles                       0               0              0      1   20
dc_rollback_segments             80    0.0        0              0      9   75
dc_segments                      47    0.0        0              0    219   37
dc_sequence_grants                0               0              0     14   15
dc_sequences                      0               0              0     14   74
dc_synonyms                      14   78.6        0              0     83   99
dc_tablespace_quotas              0               0              0      4   17
dc_tablespaces                    8    0.0        0              0     12   75
dc_used_extents                   0               0              0     85   94
dc_user_grants                    0               0              0     23   37
dc_usernames                     35    0.0        0              0     14   67
dc_users                         30    0.0        0              0     25   86
ifs_acl_cache_entries             0               0              0      0    0
          -------------------------------------------------------------


Library Cache Activity for DB: CGC  Instance: tstt1ora  Snaps: 1 -2
->"Pct Misses"  should be very low

                    Get       Pct        Pin        Pct               Invali-
Namespace         Requests    Miss     Requests     Miss     Reloads  dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY                       0                     0                 0        0
CLUSTER                    6    0.0              8    0.0          0        0
INDEX                      0                     0                 0        0
OBJECT                     0                     0                 0        0
PIPE                       0                     0                 0        0
SQL AREA                 181    2.8            628    6.7          0        0
TABLE/PROCEDURE          204   18.6            256   66.8         67        0
TRIGGER                    0                     0                 0        0
          -------------------------------------------------------------

SGA Memory Summary for DB: CGC  Instance: tstt1ora  Snaps: 1 -2

SGA regions                       Size in Bytes
------------------------------ ----------------
Database Buffers                     16,777,216
Fixed Size                               75,804
Redo Buffers                             77,824
Variable Size                        18,141,184
                               ----------------
sum                                  35,072,028
          -------------------------------------------------------------


SGA breakdown difference for DB: CGC  Instance: tstt1ora  Snaps: 1 -2

Pool        Name                        Begin value      End value  Difference
----------- ------------------------ -------------- -------------- -----------
java pool   free memory                   4,861,952      4,861,952           0
java pool   memory in use                   380,928        380,928           0
large pool  free memory                     614,400        614,400           0
shared pool Checkpoint queue                 73,764         73,764           0
shared pool db_block_buffers                278,528        278,528           0
shared pool db_block_hash_buckets            98,328         98,328           0
shared pool db_files                        370,988        370,988           0
shared pool dictionary cache              1,259,524      1,262,688       3,164
shared pool enqueue_resources                94,176         94,176           0
shared pool event statistics per ses        206,400        206,400           0
shared pool file # translation table         65,572         65,572           0
shared pool fixed allocation callbac            320            320           0
shared pool free memory                     626,068        710,396      84,328
shared pool joxs heap init                    4,248          4,248           0
shared pool KGFF heap                         9,812          9,812           0
shared pool KGK heap                         17,556         17,556           0
shared pool KQLS heap                       996,132      1,106,756     110,624
shared pool library cache                 2,364,984      2,385,952      20,968
shared pool message pool freequeue          124,552        124,552           0
shared pool miscellaneous                   875,388        875,388           0
shared pool PLS non-lib hp                    2,096          2,096           0
shared pool PL/SQL DIANA                    629,536        629,536           0
shared pool PL/SQL MPCODE                   126,804        126,804           0
shared pool PX msg pool                      12,080         12,080           0
shared pool PX subheap                        5,012          5,012           0
shared pool sessions                        129,360        129,360           0
shared pool sql area                      3,641,152      3,420,652    -220,500
shared pool State objects                   132,960        132,960           0
shared pool table columns                    19,364         19,980         616
shared pool table definiti                      960          1,760         800
shared pool trigger defini                      228            228           0
shared pool trigger inform                      396            396           0
shared pool trigger source                      188            188           0
shared pool VIRTUAL CIRCUITS                 97,992         97,992           0
            db_block_buffers             16,777,216     16,777,216           0
            fixed_sga                        75,804         75,804           0
            log_buffer                       66,560         66,560           0
          -------------------------------------------------------------

init.ora Parameters for DB: CGC  Instance: tstt1ora  Snaps: 1 -2

                                                                  End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
background_dump_dest          /sdata/oracle/v8/TSTT1ORA/bdump
compatible                    8.1.0
control_file_record_keep_time 1
control_files                 /sdata/oracle/v8/TSTT1ORA/contr
db_block_buffers              2048
db_block_size                 8192
db_file_multiblock_read_count 8
db_files                      1024
db_name                       CGC
distributed_transactions      10
global_names                  TRUE
instance_name                 TSTT1ORA
java_pool_size                5242880
large_pool_size               614400
log_buffer                    32768
log_checkpoint_interval       10000
log_checkpoint_timeout        1800
max_dump_file_size            10240
max_enabled_roles             30
mts_dispatchers               (PROTOCOL=TCP)(PRE=oracle.aurora.
open_cursors                  300
oracle_trace_collection_name
os_authent_prefix
processes                     50
remote_login_passwordfile     EXCLUSIVE
service_names                 CGC
shared_pool_size              10240000
sort_area_retained_size       65536
sort_area_size                65536
user_dump_dest                /sdata/oracle/v8/TSTT1ORA/udump
          -------------------------------------------------------------

End of Report

Annexe

Historique

Version Date Commentaires
1.0 01/2005 Version initiale

Liens

Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2), Using Statspack
Books OnLine Oracle 9i R2
Oracle