Get ADDM Finding and Recommendation in SQLPlus

Get ADDM Finding and Recommendation in SQLPlus

Get snapshots

set linesize 190

select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from DBA_HIST_SNAPSHOT; 

Current Running ADDM Task

SELECT
'TASK_ID: ' ||TASK_ID || CHR(10) ||
'TASK_NAME: ' ||TASK_NAME || CHR(10) ||
'DESCRIPTION: ' || SUBSTR(DESCRIPTION,1,60) || CHR(10) ||
'STATUS: ' ||STATUS || CHR(10) ||
'ACTIVITY_COUNTER: ' ||ACTIVITY_COUNTER || CHR(10) ||
'RECOMMENDATION_COUNT: ' ||RECOMMENDATION_COUNT AS INFO
FROM DBA_ADDM_TASKS
ORDER BY TASK_ID DESC FETCH FIRST 1 ROWS ONLY;

Genrate ADD Report

set long 1000000 longchunksize 1000000
set linesize 1000 pagesize 0
set trim on trimspool on
set echo off feedback off
spool addm_report.txt
SELECT DBMS_ADVISOR.GET_TASK_REPORT('&V_TASK_NAME')
FROM DBA_ADVISOR_TASKS
WHERE TASK_ID= &V_TASK_ID;
spool off

Current Running ADDM Task

SELECT
'TASK_ID: ' ||TASK_ID || CHR(10) ||
'TASK_NAME: ' ||TASK_NAME || CHR(10) ||
'DESCRIPTION: ' || SUBSTR(DESCRIPTION,1,60) || CHR(10) ||
'STATUS: ' ||STATUS || CHR(10) ||
'ACTIVITY_COUNTER: ' ||ACTIVITY_COUNTER || CHR(10) ||
'RECOMMENDATION_COUNT: ' ||RECOMMENDATION_COUNT AS INFO
FROM DBA_ADDM_TASKS
ORDER BY TASK_ID DESC FETCH FIRST 1 ROWS ONLY;

Finding of Task 

SELECT
'FINDING_ID: ' ||FINDING_ID || CHR(10) ||
'FINDING_NAME: ' ||FINDING_NAME || CHR(10) ||
'TYPE: ' ||TYPE || CHR(10) ||
'IMPACT_TYPE: ' ||IMPACT_TYPE || CHR(10) ||
'IMPACT: ' ||IMPACT || CHR(10) ||
'MESSAGE: ' ||MESSAGE || CHR(10) ||
'MORE_INFO: ' ||MORE_INFO || CHR(10) ||
'FILTERED: ' ||FILTERED || CHR(10) ||
'FLAGS: ' ||FLAGS AS INFO
FROM DBA_ADDM_FINDINGS F
WHERE F.TASK_ID = &V_TASK_ID;

Recommendation of Task

SELECT
'REC_ID: ' ||REC_ID || CHR(10) ||
'FINDING_ID: ' ||FINDING_ID || CHR(10) ||
'TYPE: ' ||TYPE || CHR(10) ||
'RANK: ' ||RANK || CHR(10) ||
'PARENT_REC_IDS: ' ||PARENT_REC_IDS || CHR(10) ||
'BENEFIT_TYPE: ' ||BENEFIT_TYPE || CHR(10) ||
'BENEFIT: ' ||BENEFIT || CHR(10) ||
'ANNOTATION_STATUS: ' ||ANNOTATION_STATUS || CHR(10) ||
'FLAGS: ' ||FLAGS || CHR(10) ||
'FILTERED: ' ||FILTERED || CHR(10) ||
'REC_TYPE_ID: ' ||REC_TYPE_ID AS INFO
FROM DBA_ADVISOR_RECOMMENDATIONS
WHERE TASK_ID = &V_TASK_ID
ORDER BY FINDING_ID, RANK;

SELECT

'TASK_NAME: ' ||TASK_NAME || CHR(10) ||
'REC_ID: ' ||REC_ID || CHR(10) ||
'ACTION_ID: ' ||ACTION_ID || CHR(10) ||
'OBJECT_ID: ' ||OBJECT_ID || CHR(10) ||
'COMMAND: ' ||COMMAND || CHR(10) ||
'COMMAND_ID: ' ||COMMAND_ID || CHR(10) ||
'MESSAGE: ' ||MESSAGE || CHR(10) ||
'FLAGS: ' ||FLAGS || CHR(10) ||
'ATTR1: ' ||ATTR1 || CHR(10) ||
'ATTR2: ' ||ATTR2 || CHR(10) ||
'ATTR3: ' ||ATTR3 || CHR(10) ||
'ATTR4: ' ||ATTR4 || CHR(10) ||
'ATTR5: ' ||TO_CHAR(ATTR5) || CHR(10) ||
'ATTR6: ' ||TO_CHAR(ATTR6) || CHR(10) ||
'NUM_ATTR1: ' ||NUM_ATTR1 || CHR(10) ||
'NUM_ATTR2: ' ||NUM_ATTR2 || CHR(10) ||
'NUM_ATTR3: ' ||NUM_ATTR3 || CHR(10) ||
'NUM_ATTR4: ' ||NUM_ATTR4 || CHR(10) ||
'NUM_ATTR5: ' ||NUM_ATTR5 || CHR(10) ||
'FILTERED: ' ||FILTERED AS INFO
FROM DBA_ADVISOR_ACTIONS
WHERE TASK_ID = &V_TASK_ID
ORDER BY REC_ID;