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.a wr_report_ text(
(SELECT dbid
FROM v$database),
(SELECT instance_number
FROM v$instance),
minid,
maxid
)
);
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.a
(SELECT dbid
FROM v$database),
(SELECT instance_number
FROM v$instance),
minid,
maxid
)
);
If you're just trying to dump the reports that are already available..
something like this?
SELECT DBMS_ADVISOR.get_task_repo rt(
task_name,
'TEXT',
'TYPICAL',
'ALL',
owner
)
AS report
FROM dba_addm_tasks;
something like this?
SELECT DBMS_ADVISOR.get_task_repo
task_name,
'TEXT',
'TYPICAL',
'ALL',
owner
)
AS report
FROM dba_addm_tasks;
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
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
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
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;
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
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
Thanks so much for your help
You might consider including the l_options parameter set to 8, which will display the ADDM specific portions of the report.
DBMS_WORKLOAD_REPOSITORY.a wr_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
DBMS_WORKLOAD_REPOSITORY.a
(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
ASKER
I get an error on line
SELECT DBMS.get_report
ora 6550
pls 103
SELECT DBMS.get_report
ora 6550
pls 103
dbms.get_report doesn't exist...
Do you mean dbms_addm.get_report?
Do you mean dbms_addm.get_report?
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;
this line from the script above
SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
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
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
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.
Try from sqlplus and post the entire stack trace. There should be more lines to go with the ora-06550.
ASKER
I ran as sqlplus / as sysdba
ASKER
let me check for more on the error
ASKER
error at line 20:
ORA-06550: line 20, column 1;
PLS-00103: Encountered the symbol "SELECT"
ORA-06550: line 20, column 1;
PLS-00103: Encountered the symbol "SELECT"
ASKER
if I take that line out - it runs without error
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;
/
For example:
...
END;
/
SELECT
...
END;
/
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?
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?
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...
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...
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.
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.
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_C HAR(SYSDAT E-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.
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|
--------------------------
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.
you had previously created the task, without deleting it.
run the delete by itself.
run the delete by itself.
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.
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.
ASKER
I have tried running the delete statement several times but still get the above error
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
no, the filename is pretty much it,
here's an example
the script above produced this for me...
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
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;
/
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
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
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
weird
oh - Cygwin runs a Linux env on my laptop
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that seems to have worked
LAST QUESTION
is there a way to run this on more than one database?
LAST QUESTION
is there a way to run this on more than one database?
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
if you have database links then you could invoke the procedures and queries across the links
same code just add @your_link
ASKER
Thanks for all your help
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65059