Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
(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
)
);
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Maybe dbms_addm.get_report?
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65059
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65059
If you're just trying to dump the reports that are already available..
something like this?
SELECT DBMS_ADVISOR.get_task_repo
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;
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
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
(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
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.
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;
/
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.
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|
--------------------------
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.
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.
>>PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
Look at the original code that sdstuber posted. There is no "FROM DUAL" on the delete...
Look at the original code that sdstuber posted. There is no "FROM DUAL" on the delete...
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
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
something else to consider, the output of the query is a CLOB.
You may need to set LONG to something large, I reran my test above with LONG set higher and I did get results, the previous output was simply truncated.
So maybe try something like this to be more reliable
You may need to set LONG to something large, I reran my test above with LONG set higher and I did get results, the previous output was simply truncated.
So maybe try something like this to be more reliable
set long 10000000
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;
/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial