Statspack : Average Active Sessions pour les versions antérieures à 10g

AAS = DB Time / Elapsed Time

Pour les versions antérieures à oracle 10g, le DB Time peut être calculé de la façon suivante ( Cf l'article de K. Hailey) :

En utilisant les tables Statspack stats$system_event et stats$sysstat (pour la version 9.2) :

SELECT snap_id,
  instance_number,
    to_char(start_snap_time,'DD/MM/YYYY_HH24:MI') start_snap_time,
    to_char(end_snap_time,'DD/MM/YYYY_HH24:MI') end_snap_time,
  TRUNC (dbtime / elapsed_time, 3) "AAS"
FROM
  (SELECT snap_id,
    instance_number,
    start_snap_time,
    end_snap_time,
    elapsed_time,
    SUM (VALUE) AS dbtime
  FROM
    (SELECT snap_id,
      instance_number,
    start_snap_time,
    end_snap_time,
      'Wait event' AS stat,
      TRUNC ( (end_snap_time - start_snap_time) * 24 * 60 * 60) elapsed_time,
      SUM (tw_delta) AS VALUE
    FROM
      (SELECT snap.snap_id,
        snap.instance_number,
        snap.startup_time,
        CASE
          WHEN snap.startup_time - LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time, a.event ORDER BY snap.snap_id) = 0
          THEN LAG ( snap.snap_time) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time, a.event ORDER BY snap.snap_id)
          WHEN (snap.startup_time                                                                                                               > LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id))
          OR (LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id) IS NULL)
          THEN snap.startup_time
        END start_snap_time,
        snap.snap_time AS end_snap_time,
        a.event,
        a.time_waited_micro,
        CASE
          WHEN snap.startup_time      - LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time, a.event ORDER BY snap.snap_id) = 0
          THEN ( (a.time_waited_micro - LAG ( a.time_waited_micro, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id))) / 1000000
          WHEN snap.startup_time                                                                                                               > LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id)
          OR LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id) IS NULL
          THEN a.time_waited_micro / 1000000
        END tw_delta
      FROM perfstat.stats$system_event a,
        perfstat.stats$snapshot snap
      WHERE a.snap_id  = snap.snap_id(+)
      AND a.event NOT IN
        (SELECT event FROM stats$idle_event
        )
      AND snap.snap_time >= SYSDATE - &NB_JOURS
      AND snap.snap_time   <= SYSDATE
      AND a.instance_number = snap.instance_number
      )
    GROUP BY snap_id,
      instance_number,
start_snap_time,
end_snap_time,
      'Wait event',
      TRUNC ( (end_snap_time - start_snap_time) * 24 * 60 * 60)
    UNION ALL
    SELECT snap_id,
      instance_number,
    start_snap_time,
    end_snap_time,
      'CPU Time' AS stat,
      TRUNC ( (end_snap_time - start_snap_time) * 24 * 60 * 60) elapsed_time,
      SUM (cpu_delta) AS VALUE
    FROM
      (SELECT snap.snap_id,
        snap.instance_number,
        snap.startup_time,
        CASE
          WHEN snap.startup_time - LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time ORDER BY snap.snap_id) = 0
          THEN LAG ( snap.snap_time) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time ORDER BY snap.snap_id)
          WHEN (snap.startup_time                                                                                                      > LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id))
          OR (LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id) IS NULL)
          THEN snap.startup_time
        END start_snap_time,
        snap.snap_time AS end_snap_time,
        a.name         AS stat,
        NULL           AS time_waited_micro,
        CASE
          WHEN snap.startup_time - LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time ORDER BY snap.snap_id) = 0
          THEN ( (a.VALUE        - LAG ( a.VALUE, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id))) / 100
          WHEN snap.startup_time                                                                                                      > LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id)
          OR LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id) IS NULL
          THEN a.VALUE / 100
        END cpu_delta
      FROM perfstat.stats$sysstat a,
        perfstat.stats$snapshot snap
      WHERE a.snap_id = snap.snap_id
      AND a.name      = 'CPU used by this session'
      AND snap.snap_time >= SYSDATE - &NB_JOURS
      AND snap.snap_time   <= SYSDATE
      AND a.instance_number = snap.instance_number
      )
    GROUP BY snap_id,
      instance_number,
start_snap_time,
end_snap_time,
      'CPU Time',
      TRUNC ( (end_snap_time - start_snap_time) * 24 * 60 * 60)
    )
  GROUP BY snap_id,
    instance_number,
    start_snap_time,
    end_snap_time,
    elapsed_time
  ORDER BY snap_id
  )
ORDER BY snap_id,start_snap_time,end_snap_time;

Pour les versions précédentes, la colonne time_waited_micro de la table stats$system_event n'existe pas. En version 8i la colonne est time_waited. Elle est alimentée uniquement si le paramètre timed_statistics est positionné à TRUE et les valeurs sont alors exprimées en centisecondes.

Statspack : Average Active Sessions

Dans la doc. oracle, l'Average Active Sessions est défini par :

AAS = DB time / elapsed time 

D'après la requête de l'article précédent définissant le DB Time nous pouvons calculer l'AAS ( pour version 10g ou supérieure) :

select
snap_id
,to_char(start_snap_time,'DD/MM/YYYY HH24:MI:SS') as start_snap_time
,to_char(end_snap_time,'DD/MM/YYYY HH24:MI:SS') as end_snap_time
,'AAS'
,trunc(value/elapsed_time,2)  as AAS
from (
select snap_id,start_snap_time,end_snap_time,trunc((end_snap_time-start_snap_time)*24*60*60) elapsed_time,stat_name,value
from (
select
     snap.snap_id
     ,snap.startup_time  as startup_time
     ,case
          when snap.startup_time - lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) = 0
          then lag(snap.snap_time) over (order by snap.dbid, snap.instance_number, snap.snap_id)
          when (snap.startup_time > lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id )) or
          (lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) is null)
          then snap.startup_time
          end start_snap_time
      , snap.snap_time AS end_snap_time
     ,sn.stat_name
     ,case
          when snap.startup_time - lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) = 0
           then round(((e.value - lag(e.value,1) over ( partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id)))/1000000,1)
          when snap.startup_time > lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id )
           then round(e.value/1000000,1)
          else round(e.value/1000000,1)
     end value
from stats$sys_time_model e
     , stats$time_model_statname sn
     , stats$snapshot snap
 where
  snap.snap_id = e.snap_id
  and snap.dbid = e.dbid
  and snap.instance_number = e.instance_number
  and e.stat_id                = sn.stat_id
  -- and snap.snap_time >= TO_DATE('&start_date','DD/MM/YYYY HH24:MI')
  -- and snap.snap_time <= TO_DATE('&end_date','DD/MM/YYYY HH24:MI')
  and sn.stat_name             = 'DB time'
  -- and snap.snap_id between 81 and 82
  and snap.snap_time >= sysdate - &NB_JOURS
  and snap.snap_time <= sysdate
  order by snap.snap_id
  ) )

Pour créer un graphique sous SQL Developper ( cf. article précédent), nous devons avoir uniquement 3 colonnes.
La requête est donc modifiée de la façon suivante :

select 
to_char(end_snap_time,'DD/MM/YYYY HH24:MI:SS') as end_snap_time
,'AAS'
,trunc(value/elapsed_time,2)  as AAS
from (
...

Les propriétés de l'axe Y ont été modifiées pour notre exemple :

Nous obtenons le graphique :

Statspack : suivre le DB Time au cours du temps

Ci-dessous une requête pour suivre le DB Time au cours du temps et déterminer ainsi les periodes où la base est la plus chargée ( pour version 10g ou supérieure)

select
snap_id
,to_char(start_snap_time,'DD/MM/YYYY HH24:MI:SS') as start_snap_time
to_char(end_snap_time,'DD/MM/YYYY HH24:MI:SS') as end_snap_time
,trunc((end_snap_time-start_snap_time)*24*60*60) elapsed_time
,stat_name
,value
from (
select
     snap.snap_id
     ,snap.startup_time  as startup_time
     ,case
          when snap.startup_time - lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) = 0
          then lag(snap.snap_time) over (order by snap.dbid, snap.instance_number, snap.snap_id)
          when (snap.startup_time > lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id )) or
          (lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) is null)
          then snap.startup_time
          end start_snap_time
      , snap.snap_time AS end_snap_time
     ,sn.stat_name
     ,case
          when snap.startup_time - lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) = 0
           then round(((e.value - lag(e.value,1) over ( partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id)))/1000000,1)
          when snap.startup_time > lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id )
           then round(e.value/1000000,1)
          else round(e.value/1000000,1)
     end value
from stats$sys_time_model e
     , stats$time_model_statname sn
     , stats$snapshot snap
 where
  snap.snap_id = e.snap_id
  and snap.dbid = e.dbid
  and snap.instance_number = e.instance_number
  and e.stat_id                = sn.stat_id
  -- and snap.snap_time >= TO_DATE('&start_date','DD/MM/YYYY HH24:MI')
  -- and snap.snap_time <= TO_DATE('&end_date','DD/MM/YYYY HH24:MI')
  and sn.stat_name             = 'DB time'
  -- and snap.snap_id between 1 and 82
  and snap.snap_time >= sysdate - 15
  and snap.snap_time <= sysdate -2
  order by snap.snap_id
  )

On peut utiliser cette requête sous SQL Developper afin de créer un graphique. Petit rappel, sous SQL Developper, pour créer un graphique, la requête doit retourner 3 colonnes. Par rapport à la requête ci-dessus, j'ai donc commenté les colonnes ci-dessous :

J'ai également ajouté une variable :

Il ne faut pas mettre de ";" ni de "/" à la fin de la requête.

On laisse les propriétés du graphique par défaut. Lors de l'exécution de la requête, le nombre de jours est demandé :

On obtient :

Statspack : générer une série de rapports automatiquement

Dernièrement j'ai dû générer tous les rapports statspack d'une journée. J'ai donc cherché sur le net une méthode pour automatiser cette tâche. J'ai alors trouvé ce post sur le forum "DBA Village".
J'ai complété la requête du post pour en faire la procédure ci-dessous. Celle-ci crée un fichier sql qu'il suffit ensuite de lancer sous sqlplus pour générer tous les rapports souhaités.
La procédure a 3 paramètres :

1. Le snap_id de début
2. Le snap_id de fin
3. Un directory oracle.

Il faut également copier sous $ORACLE_HOME/rdbms/admin le script spreport.sql en spreport_automatic.sql et ajouter les lignes ci-dessous :

-----
define begin_snap  = &1;
define end_snap    = &2;
define report_name = &3;

@@sprepins
-----

Exemple pour générer tous les rapports entre le snap 1 et le snap 21 :

SQL> exec gen_snap_sql(1,21,'SNAP') ;

Dans le directory "SNAP", le fichier snap.sql est créé avec les lignes suivantes :

@?/rdbms/admin/spreport_automatic  1  2  sp_1_2_2014-02-01-1023.txt
@?/rdbms/admin/spreport_automatic  2  3  sp_2_3_2014-02-01-1025.txt
@?/rdbms/admin/spreport_automatic  3  4  sp_3_4_2014-02-01-1034.txt
@?/rdbms/admin/spreport_automatic  4  11  sp_4_11_2014-02-01-1044.txt
@?/rdbms/admin/spreport_automatic  11  12  sp_11_12_2014-02-01-1049.txt
@?/rdbms/admin/spreport_automatic  12  21  sp_12_21_2014-02-01-1056.txt


Il suffit alors d’exécuter sous sqlplus le fichier snap.sql pour générer l'ensemble des rapports entre le snap 1 et 21.

La procédure :

CREATE OR REPLACE PROCEDURE gen_snap_sql(
    minSnapId IN NUMBER,
    maxSnapId IN NUMBER,
    directory IN VARCHAR2)
IS
  v_file UTL_FILE.FILE_TYPE;
  v_buffer VARCHAR2(1000) ;
  CURSOR myCur
  IS
    SELECT z.begin_snap,
      z.end_snap,
      TO_CHAR(z.begin_time,'MM')
      ||'_'
      ||TO_CHAR(z.begin_time,'YYYY') snap_month,
      'sp_'
      || TRIM(TO_CHAR(z.begin_snap)
      ||'_'
      ||TO_CHAR(z.end_snap)
      ||'_'
      ||TO_CHAR(z.begin_time,'YYYY-MM-DD-HH24MI')
      || '.txt') report_name
    FROM
      (SELECT LAG(s.snap_id,1) OVER(ORDER BY s.snap_id) begin_snap,
        s.snap_id end_snap,
        LAG(s.snap_time,1) OVER(ORDER BY s.snap_id) begin_time,
        TO_CHAR(s.snap_time,'YYYY-MM-DD-HH24MI') snap_time
      FROM stats$snapshot s
      WHERE s.snap_id BETWEEN minSnapId AND maxSnapId
      ) z
  WHERE z.begin_snap IS NOT NULL
  AND EXISTS
    (SELECT 1
    FROM stats$snapshot h1,
      stats$snapshot h2
    WHERE h1.dbid          = h2.dbid
    AND h1.instance_number = h2.instance_number
    AND h1.snap_id         = z.begin_snap
    AND h2.snap_id         = z.end_snap
    AND h1.startup_time    = h2.startup_time
    )
  ORDER BY 1,2;
BEGIN
  v_file := UTL_FILE.FOPEN (directory, 'snap.sql', 'W');
  FOR myCur_record IN myCur
  LOOP
    v_buffer := '@?/rdbms/admin/spreport_automatic'||'  '|| myCur_record.begin_snap ||'  '|| myCur_record.end_snap||'  '|| myCur_record.report_name;
    UTL_FILE.PUT_LINE (v_file,v_buffer);
  END LOOP ;
  UTL_FILE.FCLOSE (v_file);
EXCEPTION
WHEN utl_file.invalid_path THEN
  dbms_output.put_line('Directory non valide');
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Erreur : '||sqlerrm);
END ;
/

Blogger : insérer une coloration syntaxique pour le code SQL

Afin d'améliorer la mise en page du code SQL nous allons utiliser SyntaxHighlighter  de Alex Gorbatchev.
Il faut insérer dans le modèle du blog le code ci-dessous :

<!-- SyntaxHighlighter-->

<link href='http://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/>

<link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeDefault.css' rel='stylesheet' type='text/css'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushBash.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCss.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPerl.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPhp.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushVb.js' type='text/javascript'/>

<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'/>



<script language='javascript' type='text/javascript'>

SyntaxHighlighter.config.bloggerMode = true;

SyntaxHighlighter.all();

</script>

<!-- SyntaxHighlighter-->

Pour cela dans la page "Mes Blogs" cliquer sur "Modèle" puis sur "Modifier le code HTML" :

Au-dessus de la balise "</Head>" ajouter le code :

Il existe différent style css, le style par défaut est : shThemeDefault.css. Vous pouvez le remplacer par un des thèmes ci-dessous :

Pour utiliser SyntaxHighlighter il faut positionner ensuite dans son code HTML la syntaxe cidessous :

<pre class="brush: sql">
select sysdate from dual ;
</pre>

Exemple avec le thème par défaut :

select sysdate from dual ;

Oracle SQL Developper : créer un rapport enfant

A partir du rapport du précédent article, nous allons créer un rapport enfant. Ce rapport enfant nous permettra d'afficher l'évolution du temps d'exécution d'une requête retournée par le rapport parent.

Au final nous aurons ceci :

Editons le rapport créer dans l'article précédent "Top 10 expensive SQL in the workload repository" :

Sous "Master Report", cliquer sur "Child Reports" et en bas de la fenêtre sur "Add Child" :

Donner un nom au rapport, par exemple 'SQL_ID Elapsed_time" :

Dans "SQL Query" entrez la requête avec en bind variable le champ SQL_ID comme indiqué dans la capture d'écran :

Après avoir enregistré le rapport, cliquer sur le rapport pour exécuter la requête. En cliquant sur une ligne, le rapport enfant s'exécute :

Afin d'afficher le rapport enfant sous forme de graphique, éditer le rapport et choisissez "Chart" dans le menu "Style" du rapport enfant :

Afin d'améliorer l'affichage, configurez les axes dans le menu "Property". Modifier le nombre de décimales affichées sur l'axe Y :

Et modifier l'échelle de l'axe Y :

Et nous obtenons le rapport ci-dessous :

Pour créer des graphiques dans les rapports SQL Developper, la requête doit retourner obligatoirement 3 champs. Ci-dessus nous avions dans le rapport enfant les champs :

1. snap_time
2. sql_id
3. avg_elapsed

Si votre requête ne concerne que deux champs, vous pouvez doublez un des deux champs :

1. col1
2. col2
3. col2

Autre point à noter, la variable utilisée dans la requête du rapport enfant doit être renseignée en majuscules.

Oracle SQL Developper : créer un rapport

Nous allons créer un rapport personnalisé sous SQL Developper afin de rechercher par exemple les 10 requêtes les plus coûteuses en terme d'elapsed time dans le référentiel AWR.

Dans un premier nous créons un répertoire nommé AWR sous "User Defined Reports" dans la fenêtre "Reports". Si la fenêtre "Reports" n'est pas présente, il faut aller dans "View" et sélectionner "Reports".

Le répertoire AWR est créé sous "User Defined Reports" :

Faire un clic droit sur le répertoire AWR est sélectionner "New Report".
La requête utilisée est celle du script oracle sqltrpt.sql que l'on trouve sous $ORACLE_HOME/rdbms/admin :

Cliquer sur "Apply".

Le rapport apparaît dans le répertoire "AWR". En cliquant dessus la requête s’exécute :

Oracle SQL Developper : changer la langue

Afin de modifier la langue par défaut de SQL Developper, il faut ajouter dans le fichier sqldeveloper.conf
qui se trouve dans le répertoire ...sqldeveloper\bin, la ligne ci-dessous :

En anglais :

AddVMOption  -Duser.language=en