Sp statspack custom pkg 10g

Fiche express
Type	Script SQL*Plus de diagnostic (Statspack)
Voir aussi	Sp statspack custom pkg 9i · Sp snap · Sp purge 30days 10g

Sp statspack custom pkg 10g crée un package PL/SQL statspack_custom qui encapsule toutes les opérations courantes de gestion Statspack (snapshot manuel, planification à 5/15/30 minutes, purge par ancienneté) derrière des procédures simples à appeler, plutôt que de jongler avec un script SQL différent pour chaque action.

Compatible Oracle 10g et suivants : la procédure purge s'appuie sur la fonction native statspack.purge. Pour Oracle 9i, dépourvu de cette fonction, voir la variante Sp statspack custom pkg 9i qui recalcule et supprime manuellement la plage de snapshots.

Exemples d'utilisation une fois le package compilé :

• statspack_custom.snap; — snapshot manuel immédiat
• statspack_custom.snap_schedule_5; / snap_schedule_15; / snap_schedule_30; — planifier un snapshot récurrent
• statspack_custom.purge(30); — purger les snapshots de plus de 30 jours
• statspack_custom.purge_schedule_midnight(30); — planifier une purge quotidienne à minuit

Script

-- Package statspack_custom : encapsule snapshot, planification et purge
-- Statspack derrière des procédures simples. Compatible Oracle 10g+
-- (la purge s'appuie sur statspack.purge).

CREATE OR REPLACE PACKAGE statspack_custom
IS

  -- Snapshot Statspack immédiat (wrapper de statspack.snap).
  PROCEDURE snap;

  -- Planifie un job générique exécutant snap() à intervalle donné.
  PROCEDURE snap_schedule(in_start_date IN DATE, in_interval IN VARCHAR2);

  -- Raccourcis pour les fréquences les plus courantes.
  PROCEDURE snap_schedule_5;
  PROCEDURE snap_schedule_15;
  PROCEDURE snap_schedule_30;

  -- Purge les snapshots plus vieux que in_days_older_than jours.
  PROCEDURE purge(in_days_older_than IN INTEGER);

  -- Planifie un job générique exécutant purge() à intervalle donné.
  PROCEDURE purge_schedule(
      in_days_older_than IN INTEGER
    , in_start_date      IN DATE
    , in_interval        IN VARCHAR2
  );

  -- Raccourci : purge quotidienne à minuit.
  PROCEDURE purge_schedule_midnight(in_days_older_than IN INTEGER);

END statspack_custom;
/

CREATE OR REPLACE PACKAGE BODY statspack_custom
IS

  PROCEDURE snap
  IS
  BEGIN
    statspack.snap;
    COMMIT;
  END;

  PROCEDURE snap_schedule(in_start_date IN DATE, in_interval IN VARCHAR2)
  IS
    v_JobNumber      NUMBER;
    v_InstanceNumber NUMBER;
  BEGIN
    SELECT instance_number INTO v_InstanceNumber FROM v$instance;

    DBMS_JOB.SUBMIT(
        job       => v_JobNumber
      , what      => 'statspack_custom.snap;'
      , next_date => in_start_date
      , interval  => in_interval
      , no_parse  => TRUE
      , instance  => v_InstanceNumber
    );
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20000, 'snap_schedule: ' || SQLERRM);
  END;

  PROCEDURE snap_schedule_5
  IS
  BEGIN
    statspack_custom.snap_schedule(
        TRUNC(sysdate,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,'MI'))/5)+1)*5)/(24*60)
      , 'TRUNC(sysdate,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,''MI''))/5)+1)*5)/(24*60)'
    );
  END;

  PROCEDURE snap_schedule_15
  IS
  BEGIN
    statspack_custom.snap_schedule(
        TRUNC(sysdate,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,'MI'))/15)+1)*15)/(24*60)
      , 'TRUNC(sysdate,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,''MI''))/15)+1)*15)/(24*60)'
    );
  END;

  PROCEDURE snap_schedule_30
  IS
  BEGIN
    statspack_custom.snap_schedule(
        TRUNC(sysdate,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,'MI'))/30)+1)*30)/(24*60)
      , 'TRUNC(sysdate,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,''MI''))/30)+1)*30)/(24*60)'
    );
  END;

  PROCEDURE purge(in_days_older_than IN INTEGER)
  IS
    v_DbId             v$database.dbid%TYPE;
    v_DbName           v$database.name%TYPE;
    v_InstanceNumber   v$instance.instance_number%TYPE;
    v_InstanceName     v$instance.instance_name%TYPE;
    v_snapshots_purged NUMBER;
  BEGIN
    SELECT d.dbid, d.name, i.instance_number, i.instance_name
    INTO   v_DbId, v_DbName, v_InstanceNumber, v_InstanceName
    FROM   v$database d, v$instance i;

    v_snapshots_purged := statspack.purge(
        i_num_days        => in_days_older_than
      , i_extended_purge  => true
      , i_dbid            => v_DbId
      , i_instance_number => v_InstanceNumber
    );

    DBMS_OUTPUT.PUT_LINE('Snapshots supprimés (> ' || in_days_older_than
                          || ' jours) : ' || v_snapshots_purged);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20000, 'purge: ' || SQLERRM);
  END;

  PROCEDURE purge_schedule(
      in_days_older_than IN INTEGER
    , in_start_date      IN DATE
    , in_interval        IN VARCHAR2
  )
  IS
    v_JobNumber      NUMBER;
    v_InstanceNumber NUMBER;
  BEGIN
    SELECT instance_number INTO v_InstanceNumber FROM v$instance;

    DBMS_JOB.SUBMIT(
        job       => v_JobNumber
      , what      => 'statspack_custom.purge(' || in_days_older_than || ');'
      , next_date => in_start_date
      , interval  => in_interval
      , no_parse  => TRUE
      , instance  => v_InstanceNumber
    );
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20000, 'purge_schedule: ' || SQLERRM);
  END;

  PROCEDURE purge_schedule_midnight(in_days_older_than IN INTEGER)
  IS
  BEGIN
    statspack_custom.purge_schedule(in_days_older_than, TRUNC(sysdate+1), 'SYSDATE+1');
  END;

END statspack_custom;
/

show errors

Voir aussi

• Sp statspack custom pkg 9i — variante Oracle 9i (purge manuelle, sans statspack.purge)
• Sp snap — équivalent script simple pour un snapshot manuel
• Sp auto 30 · Sp auto 5 — équivalents scripts simples pour la planification
• Sp purge 30days 10g — équivalent script simple pour la purge