Sp statspack custom pkg 9i

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

Sp statspack custom pkg 9i est la variante Oracle 9i du package statspack_custom : mêmes procédures de snapshot et de planification, mais la procédure purge est réécrite car la fonction native statspack.purge n'existe pas encore sous 9i.

Ici, purge se contente de calculer les bornes (min/max snap_id) des snapshots plus vieux que le nombre de jours donné — la suppression effective des lignes (identique à celle détaillée dans Sp purge 30days 9i) doit être complétée dans le corps de la procédure ou déléguée à ce script.

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); — identifier les snapshots de plus de 30 jours
• statspack_custom.purge_schedule_midnight(30); — planifier cette purge quotidiennement à minuit

Script

-- Package statspack_custom : encapsule snapshot, planification et purge
-- Statspack derrière des procédures simples. Variante Oracle 9i
-- (pas de statspack.purge natif : la purge calcule la plage de snap_id
-- a supprimer, la suppression proprement dite suit la logique détaillée
-- dans le script "Sp purge 30days 9i").

CREATE OR REPLACE PACKAGE statspack_custom
IS

  PROCEDURE snap;
  PROCEDURE snap_schedule(in_start_date IN DATE, in_interval IN VARCHAR2);
  PROCEDURE snap_schedule_5;
  PROCEDURE snap_schedule_15;
  PROCEDURE snap_schedule_30;
  PROCEDURE purge(in_days_older_than IN INTEGER);
  PROCEDURE purge_schedule(
      in_days_older_than IN INTEGER
    , in_start_date      IN DATE
    , in_interval        IN VARCHAR2
  );
  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;

  -- Sous Oracle 9i, statspack.purge n'existe pas encore : on calcule ici
  -- uniquement les bornes des snapshots a purger. La suppression effective
  -- (stats$snapshot, stats$sqltext, stats$undostat, ...) suit la meme
  -- logique table par table que le script "Sp purge 30days 9i".
  PROCEDURE purge(in_days_older_than IN INTEGER)
  IS
    v_LowSnapId  NUMBER;
    v_HiSnapId   NUMBER;
  BEGIN
    SELECT NVL(MIN(snap_id),0), NVL(MAX(snap_id),0)
    INTO   v_LowSnapId, v_HiSnapId
    FROM   stats$snapshot
    WHERE  snap_time < (sysdate - in_days_older_than);

    DBMS_OUTPUT.PUT_LINE('Snapshots a purger : ' || v_LowSnapId || ' a ' || v_HiSnapId);

    -- TODO : brancher ici les DELETE détaillés dans "Sp purge 30days 9i"
    -- (stats$snapshot, stats$sqltext, stats$seg_stat_obj, stats$undostat,
    -- stats$database_instance, stats$statspack_parameter).

    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 10g — variante 10g avec purge native (statspack.purge)
• Sp purge 30days 9i — détail complet de la logique de suppression 9i
• Sp snap — équivalent script simple pour un snapshot manuel