Re: Grab a series of AWR reports

From: Robert Hanuschke <robert.hanuschke_at_googlemail.com>
Date: Fri, 12 Aug 2011 20:04:22 +0200
Message-ID: <CAL6WDh1Tp6=7PTOs0re0T14C8Eb18COuYNGktk3dap3LmDW=mA_at_mail.gmail.com>

Hi Stephan,

here's a pl/sql script - so, OS independent - I wrote (in fact, the first I ever wrote back then) for that kind of purpose. It creates a snapshot and an awr report between the one before that and the newly created and puts it into a database directory which you can define as a parameter. As a bonus, it also deletes ones older than a number of days - which is also specified as parameter. To complete the list of parameters, you can choose between html or text format for your reports.
You could let that run as a job in the frequency you desire or of course manually like this:

CREATE OR REPLACE PROCEDURE latest_awr (

• Author: Robert Hanuschke <robert.hanuschke_at_googlemail.com>
• Purpose: Creates a new awr snapshot and awr report on disk and deletes old ones
• Choose either 'html' or 'text' format var_report_type in varchar2 default 'html',
• Directory the reports are placed into. Has to exist in DBA_DIRECTORIES var_awr_dir in varchar2,
• Number of days the old reports are kept on disk var_keep_days in number default '3') IS type rc is ref cursor; var_line varchar2(2000); c1 rc; var_filehandle utl_file.file_type; var_begin_snap number(10); var_end_snap number(10); var_inst_num number(2); var_report_name varchar2(70); var_dbid number(10);

    var_oldest_keep number(10);
    var_counter     number(10);
    var_ex          boolean;
    var_flen        number;
    var_bsize       binary_integer;

BEGIN     SELECT dbid
    INTO var_dbid
    FROM SYS.V_$DATABASE;     dbms_workload_repository.create_snapshot;

    SELECT max(snap_id)
    INTO var_end_snap
    FROM SYS.DBA_HIST_SNAPSHOT;     SELECT instance_number
    INTO var_inst_num
    FROM SYS.V_$INSTANCE;     var_begin_snap := var_end_snap-1;
    var_report_name := 'awrrpt_' || var_inst_num || '_' || var_begin_snap || '_' || var_end_snap || '.' || var_report_type;

    var_filehandle := utl_file.fopen(var_awr_dir,var_report_name,'w',32767);     open c1 for 'select output from
table(dbms_workload_repository.awr_report_'||var_report_type||'(' || var_dbid || ',' || var_inst_num || ',' || var_begin_snap || ',' || var_end_snap || '))';

    loop

        fetch c1 into var_line;
        exit when c1%notfound;
        utl_file.put_line(var_filehandle,var_line,FALSE);

    end loop;
    utl_file.fclose(var_filehandle);

    SELECT MAX(snap_id)
    INTO var_oldest_keep
    FROM SYS.DBA_HIST_SNAPSHOT
    WHERE end_interval_time < SYSDATE-var_keep_days;

    var_counter := var_oldest_keep;

    loop

        utl_file.fgetattr(var_awr_dir,'awrrpt_'||var_inst_num||'_'||to_char(var_counter-2)||'_'||to_char(var_counter-1)||'.'||var_report_type,var_ex,var_flen,var_bsize);
        if var_ex then
            utl_file.fremove(var_awr_dir,'awrrpt_'||var_inst_num||'_'||to_char(var_counter-2)||'_'||to_char(var_counter-1)||'.'||var_report_type);
        else
            exit;
        end if;
        var_counter := var_counter-1;

    end loop;

END;
/

Best regards,
Robert

On Fri, Aug 12, 2011 at 7:25 PM, Uzzell, Stephan <SUzzell_at_micros.com> wrote:
> Hi Listers,
>
>
>
> Asking here before I try to re-invent the wheel. Does anyone (using Windows
> – so no cron or sh scripts please) have a script that will allow the
> generation of a sequence of AWR reports? I want to collect a series of
> snapshots every morning.
>
>
>
> 10.2.0.3 on Windows if that matters.
>
>
>
> Thanks,
>
> stephan
>
>
>
> Stephan Uzzell
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 12 2011 - 13:04:22 CDT