ADDM

DECLARE
   L_FROM_TIME                   TIMESTAMP;
   L_TO_TIME                     TIMESTAMP;
   L_MIN_ID                      DBA_HIST_SNAPSHOT.SNAP_ID%TYPE;
   L_MAX_ID                      DBA_HIST_SNAPSHOT.SNAP_ID%TYPE;
   L_START_NODE                  DBA_HIST_SNAPSHOT.INSTANCE_NUMBER%TYPE;
   L_END_NODE                    DBA_HIST_SNAPSHOT.INSTANCE_NUMBER%TYPE;
   L_DB_NAME                     V$DATABASE.NAME%TYPE;
   L_DB_ID                       V$DATABASE.DBID%TYPE;
   L_START_TIME                  DBA_HIST_SNAPSHOT.BEGIN_INTERVAL_TIME%TYPE;
   L_INTERVAL_MIN                NUMBER := 0;
   L_INPUT_INTERVAL              NUMBER := 0;
   L_COUNT                       NUMBER := 0;
   L_SNAP_DIFF                   NUMBER := 0;
   L_RPT_FORMAT					 VARCHAR2(10);
   L_START_SNAP_ID               NUMBER;
   L_END_SNAP_ID                 NUMBER;
   L_TASK_NAME                   VARCHAR2(50);   
   L_TASK_ID                     NUMBER; 
BEGIN
   BEGIN
      L_FROM_TIME := TO_TIMESTAMP ('&&1' ||lpad('&&2',4,0), 'YYYYMMDDHH24MI');
      L_TO_TIME := TO_TIMESTAMP ('&&1' || lpad('&&3',4,0),  'YYYYMMDDHH24MI');
      L_INPUT_INTERVAL := TO_NUMBER ('&&4');    
      L_RPT_FORMAT := UPPER('&&5');  
      IF L_RPT_FORMAT <> 'TEXT' THEN
         L_RPT_FORMAT := 'TEXT';
         DBMS_OUTPUT.PUT_LINE('--Error Unknown Report Format - Both HTML and TEXT formatted reports will be generated');         
      END IF;     

   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE (SQLERRM);
         DBMS_OUTPUT.PUT_LINE('--Error input format: Date ('||'&&1'||') should in YYYYMMDD, From time (&&2) and to time (&&3) must be hour and minutes');
         GOTO EXIT_LOOP;
   END;

   if (l_to_time<l_from_time) then
      	 DBMS_OUTPUT.PUT_LINE('--Error time range: From time '||to_char(l_from_time,'YYYYMMDD HH24MI')||' later than to time '||to_char(l_to_time,'YYYYMMDD HH24MI'));
         GOTO EXIT_LOOP;
   end if;

   SELECT DBID, NAME
   INTO   L_DB_ID, L_DB_NAME
   FROM   V$DATABASE;

   SELECT DISTINCT
          MIN (SNAP_ID) - 1,
          MAX (SNAP_ID),
          MIN (INSTANCE_NUMBER),
          MAX (INSTANCE_NUMBER),
          MIN (BEGIN_INTERVAL_TIME),
          ROUND(MIN(TO_DATE (TO_CHAR (END_INTERVAL_TIME, 'YYYYMMDDHH24MI'),
                               'YYYYMMDDHH24MI')
                      - TO_DATE (
                           TO_CHAR (BEGIN_INTERVAL_TIME, 'YYYYMMDDHH24MI'),
                           'YYYYMMDDHH24MI'
                        ))*1440
                )
   INTO   L_MIN_ID,
          L_MAX_ID,
          L_START_NODE,
          L_END_NODE,
          L_START_TIME,
          L_INTERVAL_MIN
   FROM   DBA_HIST_SNAPSHOT
   WHERE  BEGIN_INTERVAL_TIME >= L_FROM_TIME
      AND END_INTERVAL_TIME <= L_TO_TIME;

   IF (L_INPUT_INTERVAL / L_INTERVAL_MIN < 1)
   THEN
      L_SNAP_DIFF := 1;
      L_INPUT_INTERVAL:=L_INTERVAL_MIN;
   ELSE
      L_SNAP_DIFF := ROUND (L_INPUT_INTERVAL / L_INTERVAL_MIN);
   END IF;

   DBMS_OUTPUT.PUT_LINE('set feedback off echo off serveroutput off wrap on pagesize 0 linesize 1000 trimspool on ');

   FOR X IN 1 .. (L_MAX_ID - L_MIN_ID) / L_SNAP_DIFF
   LOOP
      FOR Y IN L_START_NODE .. L_END_NODE
      LOOP

         L_START_SNAP_ID :=  L_MIN_ID + L_COUNT * L_SNAP_DIFF;
         L_END_SNAP_ID := L_MIN_ID + (L_COUNT + 1) * L_SNAP_DIFF;

         -- Create ADDM Reports
         dbms_advisor.create_task('ADDM',L_TASK_ID,L_TASK_NAME,'run_addm('||L_START_SNAP_ID||','|| L_END_SNAP_ID||''')');
         dbms_advisor.set_task_parameter(L_TASK_NAME,'START_SNAPSHOT',L_START_SNAP_ID);
         dbms_advisor.set_task_parameter(L_TASK_NAME,'END_SNAPSHOT',L_END_SNAP_ID);
         dbms_advisor.set_task_parameter(L_TASK_NAME,'INSTANCE',Y);
         dbms_advisor.execute_task(L_TASK_NAME);
         dbms_output.put_line('--Task#' || L_TASK_ID || ' (' || L_TASK_NAME || ')');

         -- text version ONLY
      	 DBMS_OUTPUT.PUT_LINE(   'spool c:\temp\online_60\'
                              || L_DB_NAME
                              || Y
                              || '_&&1'
                              || '_'
                              || TO_CHAR (
                                    L_START_TIME
                                    +   L_COUNT                                     
                                      * L_INPUT_INTERVAL
                                      / 1440,
                                    'HH24MI'
                                 )
                              || '_to_'
                              || TO_CHAR (
                                    L_START_TIME
                                    +   (L_COUNT + 1)                                     
                                      * L_INPUT_INTERVAL
                                      / 1440,
                                    'HH24MI'
                                 )
                              || '_ADDM.txt');
         DBMS_OUTPUT.PUT_LINE('SET LONG 1000000');
         DBMS_OUTPUT.PUT_LINE('SET PAGESIZE 50000'); 
         DBMS_OUTPUT.PUT_LINE('COLUMN GET_CLOB FORMAT A80');
         DBMS_OUTPUT.PUT_LINE('SELECT dbms_advisor.get_task_report('        	                              	                      
                              || ''''||L_TASK_NAME||''','        	                      
                              || ''''||L_RPT_FORMAT||''','
                              || '''ALL'','        	                      
                              || '''ALL'','        	                      
                              || 'NULL'   
                              || ') as addm_rep from dba_advisor_tasks where task_id = '||L_TASK_ID||';'
                              );
         DBMS_OUTPUT.PUT_LINE ('spool off;');                

         L_TASK_NAME := NULL;

      END LOOP;

      L_COUNT := L_COUNT + 1;
   END LOOP;


  <<exit_loop>>
   NULL;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE(   '--NO DATA FOUND BETWEEN '
                           || TO_CHAR (L_FROM_TIME, 'YYYYMMDD HH24MI')
                           || ' and  '
                           || TO_CHAR (L_TO_TIME, 'YYYYMMDD HH24MI'));
WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(   '--Error:');
      DBMS_OUTPUT.PUT_LINE(  SQLERRM);
END;
/

 

-- A simple version:

DECLARE
    task_name VARCHAR2(30) := 'DEMO_ADDM01';
    task_desc VARCHAR2(30) := 'ADDM Feature Test';
    task_id NUMBER;
BEGIN
    dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
    dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 65);
    dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 66);
    dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
    dbms_advisor.set_task_parameter(task_name, 'DB_ID', 1712582900);
    dbms_advisor.execute_task(task_name);
END;
/