Avatar of bkreynolds48
bkreynolds48
 asked on

addm report - run in a script

I have this that runs the awr report - how do I convert it to run the addm report?

SELECT output
   FROM (SELECT MIN(snap_id) minid, MAX(snap_id) maxid
           FROM dba_hist_snapshot
          WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE)),
        TABLE(
            DBMS_WORKLOAD_REPOSITORY.awr_report_text(
                (SELECT dbid
                   FROM v$database),
                (SELECT instance_number
                   FROM v$instance),
                minid,
                maxid
            )
        );
Oracle Database

Avatar of undefined
Last Comment
bkreynolds48

8/22/2022 - Mon
slightwv (䄆 Netminder)

Sean Stuber

If you're just trying to dump the reports that are already available..

something like this?

SELECT DBMS_ADVISOR.get_task_report(
           task_name,
           'TEXT',
           'TYPICAL',
           'ALL',
           owner
       )
           AS report
  FROM dba_addm_tasks;
bkreynolds48

ASKER
what I am trying to do is get yesterday's addm report - so need to get the start/end snap_id and then run the addm report - like the above for the awr report
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Sean Stuber

ADDM isn't likely to already have a report for the previous day.  Instead, it will have many small-interval reports.

To get a full-day report, you'll have to request it and then retrieve it.

Something like this should do it

DECLARE
    v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
    v_min_id    NUMBER;
    v_max_id    NUMBER;
    v_dbid      NUMBER;
BEGIN
    SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
      INTO v_min_id, v_max_id, v_dbid
      FROM dba_hist_snapshot
     WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);

    DBMS_ADDM.analyze_db(
        task_name        => v_task_name,
        begin_snapshot   => v_min_id,
        end_snapshot     => v_max_id,
        db_id            => v_dbid
    );
END;

SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;


BEGIN
    DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
END;

Open in new window



Do note though, just because there are snapshots for the beginning and ending of a day doesn't mean ADDM has the necessary information to generate the report.  You may need to narrow the range
bkreynolds48

ASKER
thanks - I will give this a try - but given this is Friday it may take me a couple of days to get back to this

Thanks so much for your help
awking00

You might consider including the l_options parameter set to 8, which will display the ADDM specific portions of the report.
DBMS_WORKLOAD_REPOSITORY.awr_report_text(
                 (SELECT dbid
                    FROM v$database),
                 (SELECT instance_number
                    FROM v$instance),
                 minid,
                 maxid,
                  8
             )

You may also consider the dbms_advisor package (see the following link) to create the addm report.
http://oracle-base.com/articles/10g/automatic-database-diagnostic-monitor-10g.php
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bkreynolds48

ASKER
I get an error on line
SELECT DBMS.get_report

ora 6550
pls 103
slightwv (䄆 Netminder)

dbms.get_report doesn't exist...

Do you mean dbms_addm.get_report?
bkreynolds48

ASKER
yes sorry - type

this line from the script above

SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sean Stuber

which did you try?


dbms.get_report --- this should fail

or

dbms_addm.get_report -- this worked for me.


if you get  "does not exist"  it may be because you don't have access, not because it doesn't exist
slightwv (䄆 Netminder)

Make sure you have permission to execute DBMS_ADDM.

Try from sqlplus and post the entire stack trace.  There should be more lines to go with the ora-06550.
bkreynolds48

ASKER
I ran as sqlplus / as sysdba
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bkreynolds48

ASKER
let me check for more on the error
bkreynolds48

ASKER
error at line 20:
ORA-06550: line 20, column 1;
PLS-00103: Encountered the symbol "SELECT"
bkreynolds48

ASKER
if I take that line out - it runs without error
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
slightwv (䄆 Netminder)

If you just copied and pasted the script as-is, you need a '/' after the END; in a pl/sql block.

For example:

...
END;
/

SELECT
...
END;
/
bkreynolds48

ASKER
yes I figured that out
can you tell me what this  does? - little scared about deletes in production

BEGIN
    DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
END

also I need to spool this to a file so I can email the output to myself

one more thing I will need to try to add is that I have 3 databases on this server and would like this to run for all three once this is working
Is that Possible?
slightwv (䄆 Netminder)

The online docs tell you:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65049

Look at the code above:
v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');

Then it calls DBMS_ADDM.analyze_db with that task_name.

The delete is just cleaning up after itself...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Sean Stuber

It deletes the ADDM task the previous pl/sql block created.

The 3 steps above do this...

You create a task named for yourself
Generate a report for your own task
Then delete your own task.
bkreynolds48

ASKER
I just logged on from where the script is running
here is what I ran

SQL> DECLARE
    v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
    v_min_id    NUMBER;
    v_max_id    NUMBER;
    v_dbid      NUMBER;
BEGIN
    SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
      INTO v_min_id, v_max_id, v_dbid
      FROM dba_hist_snapshot
     WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);

    DBMS_ADDM.analyze_db(
        task_name        => v_task_name,
        begin_snapshot   => v_min_id,
        end_snapshot     => v_max_id,
        db_id            => v_dbid
    );
END;
/

SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;

BEGIN
DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
END;
/


here is the output

ERROR at line 1:
ORA-13627: Setting of parameter START_SNAPSHOT is disallowed until the task is
reset.
ORA-06512: at "SYS.PRVT_ADVISOR", line 4082
ORA-06512: at "SYS.PRVT_ADVISOR", line 4094
ORA-06512: at "SYS.DBMS_ADVISOR", line 363
ORA-06512: at "SYS.DBMS_ADDM", line 65
ORA-06512: at line 12


SQL> SQL>
DBMS_ADDM.GET_REPORT(USER||'_'||TO_CHAR(SYSDATE-1,'YYYY-MM-DD'))
--------------------------------------------------------------------------------
          ADDM Report for Task 'BEV_2015-06-07'
          -------------------


SQL> SQL>   2    3    4  DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
                                                                    *
ERROR at line 2:
ORA-06550: line 2, column 69:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
:= . ( % ;
The symbol ":= was inserted before "FROM" to continue.
Sean Stuber

you had previously created the task, without deleting it.

run the delete by itself.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
bkreynolds48

ASKER
SQL> BEGIN
DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
END;
/  2    3    4  
DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
                                                                    *
ERROR at line 2:
ORA-06550: line 2, column 69:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
:= . ( % ;
The symbol ":= was inserted before "FROM" to continue.
bkreynolds48

ASKER
I have tried running the delete statement several times but still get the above error
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bkreynolds48

ASKER
thanks - it runs with no errors - sorry about the pilot error
I don't get any output
can I spool to a file?  I tried but nothing was in it so must be missing something besides just
spool filename
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Sean Stuber

no, the filename is pretty much it,
here's an example

spool testfile.txt
DECLARE
    v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
    v_min_id    NUMBER;
    v_max_id    NUMBER;
    v_dbid      NUMBER;
BEGIN
    SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
      INTO v_min_id, v_max_id, v_dbid
      FROM dba_hist_snapshot
     WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);

    DBMS_ADDM.analyze_db(
        task_name        => v_task_name,
        begin_snapshot   => v_min_id,
        end_snapshot     => v_max_id,
        db_id            => v_dbid
    );
END;
/
SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
BEGIN
    DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
END;
/

Open in new window


the script above produced this for me...

SQL> DECLARE
  2      v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
  3      v_min_id    NUMBER;
  4      v_max_id    NUMBER;
  5      v_dbid      NUMBER;
  6  BEGIN
  7      SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
  8        INTO v_min_id, v_max_id, v_dbid
  9        FROM dba_hist_snapshot
 10       WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);
 11  
 12      DBMS_ADDM.analyze_db(
 13          task_name        => v_task_name,
 14          begin_snapshot   => v_min_id,
 15          end_snapshot     => v_max_id,
 16          db_id            => v_dbid
 17      );
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;

DBMS_ADDM.GET_REPORT(USER||'_'||TO_CHAR(SYSDATE-1,'YYYY-MM-DD'))                
--------------------------------------------------------------------------------
          ADDM Report for Task 'SYSTEM_2015-06-07'                              
          -------------------                                                   
                                                                                

SQL> BEGIN
  2      DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> exit

Open in new window


note, there isn't anything useful in it, because their isn't any ADDM data for that time period on the database I used.
So, the script ran, but that doesn't guarantee useful output

That's what I was trying to warn about in my second post
bkreynolds48

ASKER
I have Cygwin on my laptop and connected to the same database that I was running on the AIX system.  Ran these same commands and data was returned.
weird
oh - Cygwin runs a Linux env on my laptop
ASKER CERTIFIED SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bkreynolds48

ASKER
that seems to have worked
LAST QUESTION
is there a way to run this on more than one database?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Sean Stuber

simply connect to each database in turn and run the script again



if you have database links then you could invoke the procedures and queries across the links
same code just add @your_link
bkreynolds48

ASKER
Thanks for all your help