Oracle ADDM Report for last 24 hr .....

VARIABLE bid NUMBER

VARIABLE eid NUMBER

VARIABLE DBID NUMBER

VARIABLE inst_num number

exec select max(snap_id) -24 into :bid from dba_hist_snapshot ;

exec select max(snap_id) into :eid from dba_hist_snapshot ;

exec select DBID into :DBID from v$database;

exec select INSTANCE_NUMBER into :inst_num from v$instance ;

DECLARE

task_name VARCHAR2(30) := 'ADDM_ANUJ';

task_desc VARCHAR2(30) := 'ADDM ANUJ';

task_id NUMBER;

BEGIN

dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);

dbms_advisor.set_task_parameter('ADDM_ANUJ', 'START_SNAPSHOT', :bid);

dbms_advisor.set_task_parameter('ADDM_ANUJ', 'END_SNAPSHOT', :eid);

dbms_advisor.set_task_parameter('ADDM_ANUJ', 'INSTANCE', :inst_num);

dbms_advisor.set_task_parameter('ADDM_ANUJ', 'DB_ID', :DBID );

dbms_advisor.execute_task('ADDM_ANUJ');

END;

/

PL/SQL procedure successfully completed.

SET LONG 1000000

SET PAGES 0

SET LONGCHUNKSIZE 1000

COL get_clob FORMAT a80

SELECT DBMS_ADVISOR.GET_TASK_REPORT('ADDM_ANUJ','TEXT','TYPICAL') FROM dual;

DETAILED ADDM REPORT FOR TASK 'ADDM_ANUJ' WITH ID 3300

------------------------------------------------------

Analysis Period: from 02-FEB-2011 14:00 to 03-FEB-2011 14:00

Database ID/Instance: 1257031792/1

Database/Instance Names: ORCL/orcl

Host Name: apt-lnxtst-01

Database Version: 10.2.0.4.0

Snapshot Range: from 3052 to 3076

Database Time: 316 seconds

Average Database Load: 0 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FINDING 1: 36% impact (114 seconds)

-----------------------------------

SQL statements consuming significant database time were found.

RECOMMENDATION 1: SQL Tuning, 15% benefit (48 seconds)

ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for

possible performance improvements.

RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3

call dbms_stats.gather_database_stats_job_proc ( )

RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1

times and had an average elapsed time of 47 seconds.

RECOMMENDATION 2: SQL Tuning, 7.2% benefit (23 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "cb75rw3w1tt0s". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID cb75rw3w1tt0s

begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;

RATIONALE: SQL statement with SQL_ID "cb75rw3w1tt0s" was executed 51753

times and had an average elapsed time of 0.00072 seconds.

RECOMMENDATION 3: SQL Tuning, 7.1% benefit (22 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID 6gvch1xu9ca3g

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;

broken BOOLEAN := FALSE; BEGIN

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF

broken THEN :b := 1; ELSE :b := 0; END IF; END;

RATIONALE: SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 1441

times and had an average elapsed time of 0.019 seconds.

RECOMMENDATION 4: SQL Tuning, 4.6% benefit (14 seconds)

ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID

"bunssq950snhf".

RELEVANT OBJECT: SQL statement with SQL_ID bunssq950snhf and

PLAN_HASH 2694099131

insert into wrh$_sga_target_advice (snap_id, dbid, instance_number,

SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select

:snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR,

ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice

RATIONALE: SQL statement with SQL_ID "bunssq950snhf" was executed 24

times and had an average elapsed time of 0.6 seconds.

RECOMMENDATION 5: SQL Tuning, 4.5% benefit (14 seconds)

ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID

"8hk7xvhua40va".

RELEVANT OBJECT: SQL statement with SQL_ID 8hk7xvhua40va

INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_VALUE,

METRIC_GUID, STRING_VALUE, TARGET_GUID, VALUE) VALUES ( :1, NVL(:2, '

'), :3, :4, :5, :6)

RATIONALE: SQL statement with SQL_ID "8hk7xvhua40va" was executed 2452

times and had an average elapsed time of 0.0057 seconds.

FINDING 2: 17% impact (55 seconds)

----------------------------------

Waits on event "log file sync" while performing COMMIT and ROLLBACK operations

were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 17% benefit (55 seconds)

ACTION: Investigate application logic for possible reduction in the

number of COMMIT operations by increasing the size of transactions.

RATIONALE: The application was performing 11 transactions per minute

with an average redo size of 8087 bytes per transaction.

RECOMMENDATION 2: Host Configuration, 17% benefit (55 seconds)

ACTION: Investigate the possibility of improving the performance of I/O

to the online redo log files.

RATIONALE: The average size of writes to the online redo log files was 6

K and the average time per write was 6 milliseconds.

SYMPTOMS THAT LED TO THE FINDING:

SYMPTOM: Wait class "Commit" was consuming significant database time.

(17% impact [55 seconds])

FINDING 3: 16% impact (50 seconds)

----------------------------------

PL/SQL execution consumed significant database time.

RECOMMENDATION 1: SQL Tuning, 15% benefit (48 seconds)

ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for

possible performance improvements.

RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3

call dbms_stats.gather_database_stats_job_proc ( )

RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1

times and had an average elapsed time of 47 seconds.

RATIONALE: Average time spent in PL/SQL execution was 6.5 seconds.

RECOMMENDATION 2: SQL Tuning, 4.8% benefit (15 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "cb75rw3w1tt0s". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID cb75rw3w1tt0s

begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;

RATIONALE: SQL statement with SQL_ID "cb75rw3w1tt0s" was executed 51753

times and had an average elapsed time of 0.00072 seconds.

RATIONALE: Average time spent in PL/SQL execution was 0.00029 seconds.

RECOMMENDATION 3: SQL Tuning, 3.4% benefit (11 seconds)

ACTION: Investigate the SQL statement with SQL_ID "6mcpb06rctk0x" for

possible performance improvements.

RELEVANT OBJECT: SQL statement with SQL_ID 6mcpb06rctk0x

call dbms_space.auto_space_advisor_job_proc ( )

RATIONALE: SQL statement with SQL_ID "6mcpb06rctk0x" was executed 1

times and had an average elapsed time of 10 seconds.

RATIONALE: Average time spent in PL/SQL execution was 9.3 seconds.

RECOMMENDATION 4: SQL Tuning, 3.3% benefit (10 seconds)

ACTION: Tune the PL/SQL block with SQL_ID "2b064ybzkwf1y". Refer to the

"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide

and Reference"

RELEVANT OBJECT: SQL statement with SQL_ID 2b064ybzkwf1y

BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

RATIONALE: SQL statement with SQL_ID "2b064ybzkwf1y" was executed 2869

times and had an average elapsed time of 0.0037 seconds.

RATIONALE: Average time spent in PL/SQL execution was 0.0035 seconds.

RECOMMENDATION 5: SQL Tuning, 3.1% benefit (10 seconds)

ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID

"8szmwam7fysa3".

RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and

PLAN_HASH 2976124318

insert into wri$_adv_objspace_trend_data select timepoint,

space_usage, space_alloc, quality from

table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,

NULL, 'FALSE', :5, 'FALSE'))

RATIONALE: SQL statement with SQL_ID "8szmwam7fysa3" was executed 13

times and had an average elapsed time of 0.73 seconds.

RATIONALE: Average time spent in PL/SQL execution was 0.71 seconds.

FINDING 4: 6.7% impact (21 seconds)

-----------------------------------

The SGA was inadequately sized, causing additional I/O or hard parses.

RECOMMENDATION 1: DB Configuration, 4.4% benefit (14 seconds)

ACTION: Increase the size of the SGA by setting the parameter

"sga_target" to 640 M.

ADDITIONAL INFORMATION:

The value of parameter "sga_target" was "512 M" during the analysis

period.

SYMPTOMS THAT LED TO THE FINDING:

SYMPTOM: Wait class "User I/O" was consuming significant database time.

(8% impact [25 seconds])

FINDING 5: 3.3% impact (10 seconds)

-----------------------------------

Soft parsing of SQL statements was consuming significant database time.

RECOMMENDATION 1: Application Analysis, 3.3% benefit (10 seconds)

ACTION: Investigate application logic to keep open the frequently used

cursors. Note that cursors are closed by both cursor close calls and

session disconnects.

RECOMMENDATION 2: DB Configuration, 3.3% benefit (10 seconds)

ACTION: Consider increasing the maximum number of open cursors a session

can have by increasing the value of parameter "open_cursors".

ACTION: Consider increasing the session cursor cache size by increasing

the value of parameter "session_cached_cursors".

RATIONALE: The value of parameter "open_cursors" was "300" during the

analysis period.

RATIONALE: The value of parameter "session_cached_cursors" was "20"

during the analysis period.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ADDITIONAL INFORMATION

----------------------

Wait class "Application" was not consuming significant database time.

Wait class "Concurrency" was not consuming significant database time.

Wait class "Configuration" was not consuming significant database time.

CPU was not a bottleneck for the instance.

Wait class "Network" was not consuming significant database time.

Session connect and disconnect calls were not consuming significant database

time.

Hard parsing of SQL statements was not consuming significant database time.

The database's maintenance windows were active during 33% of the analysis

period.

====

for PDB

alter session set container=ANUJ;

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '5bbqkc6pmfhwy',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '5bbqkc6pmfhwy_tuning_task',

description => 'Tuning task for statement 5bbqkc6pmfhwy');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

2. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5bbqkc6pmfhwy_tuning_task');

3. Get the Tuning advisor report.

set long 50000 longchunksize 5000 linesize 300

col REPORT_TUNING_TASK for a100 wrap

select dbms_sqltune.report_tuning_task('5bbqkc6pmfhwy_tuning_task')  REPORT_TUNING_TASK from dual;

====================

--- Finding All Snapshot List

SQL> 

SELECT SNAP_ID,

  SNAP_LEVEL,BEGIN_INTERVAL_TIME,

  TO_CHAR(BEGIN_INTERVAL_TIME, 'dd/mm/yy hh24:mi:ss') BEGIN

FROM

   DBA_HIST_SNAPSHOT

ORDER BY SNAP_ID desc;  

   SNAP_ID SNAP_LEVEL BEGIN_INTERVAL_TIME                                                         BEGIN

---------- ---------- --------------------------------------------------------------------------- -----------------

     39004          2 18-JAN-22 02.00.23.142 AM                                                   18/01/22 02:00:23

     39004          2 18-JAN-22 02.00.23.175 AM                                                   18/01/22 02:00:23

     39003          2 18-JAN-22 01.00.13.555 AM                                                   18/01/22 01:00:13

     39003          2 18-JAN-22 01.00.13.591 AM                                                   18/01/22 01:00:13

     39002          2 18-JAN-22 12.00.04.937 AM                                                   18/01/22 00:00:04

     39002          2 18-JAN-22 12.00.04.996 AM                                                   18/01/22 00:00:04

     39001          2 17-JAN-22 11.00.07.027 PM                                                   17/01/22 23:00:07

     39001          2 17-JAN-22 11.00.06.984 PM                                                   17/01/22 23:00:06

     39000          2 17-JAN-22 10.00.48.335 PM                                                   17/01/22 22:00:48

     39000          2 17-JAN-22 10.00.48.366 PM                                                   17/01/22 22:00:48

--- Executing Oracle Package DBMS_ADVISOR to Generate advisory task.

SQL> 

BEGIN

  -- Create an ADDM task.

  DBMS_ADVISOR.create_task (

    advisor_name      => 'ADDM',

        task_name         => '39001_39003_AWR_SNAP',

        task_desc         => 'Advisor for snapshots 39001 to 39003');

  

      -- Set the start and end snapshots.

      DBMS_ADVISOR.set_task_parameter (

       task_name => '39001_39003_AWR_SNAP',

       parameter => 'START_SNAPSHOT',

       value     => 39001);

      DBMS_ADVISOR.set_task_parameter (

       task_name => '39001_39003_AWR_SNAP',

       parameter => 'END_SNAPSHOT',

       value     => 39004);

      -- Execute the task.

     DBMS_ADVISOR.execute_task(task_name => '39001_39003_AWR_SNAP');

   END;

PL/SQL procedure successfully completed.

------ Showing ADDM Report data.

SQL> 

SET LONG 100000

SET PAGESIZE 50000

SELECT DBMS_ADVISOR.get_task_report('39001_39003_AWR_SNAP') AS report  FROM   dual;

====

col SQL_ID form a16

col Benefit form 9999999999999

select * from (

select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 

from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 

where a.REC_ID = b.OBJECT_ID

and a.TASK_ID = b.TASK_ID

and a.TASK_ID in (select distinct b.task_id

from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l

where a.begin_interval_time > sysdate - 7 

and  a.dbid = (select dbid from v$database) 

and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 

and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 

and b.advisor_name = 'ADDM' 

and b.task_id = l.task_id 

and l.status = 'COMPLETED') 

and length(b.ATTR4) > 1 

group by b.ATTR1

order by max(a.BENEFIT) desc) where rownum < 6;

/ 

or 

-- with task name

col SQL_ID form a16

col Benefit form 9999999999999

select * from (

select b.ATTR1 as SQL_ID,a.TASK_NAME, max(a.BENEFIT) as "Benefit" 

from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 

where a.REC_ID = b.OBJECT_ID

and a.TASK_ID = b.TASK_ID

and a.TASK_ID in (select distinct b.task_id

from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l

where a.begin_interval_time > sysdate - 7 

and  a.dbid = (select dbid from v$database) 

and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 

and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 

and b.advisor_name = 'ADDM' 

and b.task_id = l.task_id 

and l.status = 'COMPLETED') 

and length(b.ATTR4) > 1 

group by b.ATTR1,a.TASK_NAME

order by max(a.BENEFIT) desc) where rownum < 6;

SQL_ID           TASK_NAME                             Benefit

---------------- ------------------------------ --------------

g8ayhu2g4wh1f    ADDM:3316548098_1_571783          14128696816

1rn43zb7tm2jg    ADDM:3316548098_1_571783          14128696816

1rn43zb7tm2jg    ADDM:3316548098_1_571785          13952920387

9783wcf3s3634    ADDM:3316548098_1_571646          13662000000

87a333ujtm7q3    ADDM:3316548098_1_571862          13178202405

set linesize 300 pagesize 300

col TASK_NAME  for a30

select task_name, execution_end ,task_id from dba_advisor_tasks  

where advisor_name='ADDM' 

and status='COMPLETED' 

and owner='SYS' 

--and instr(TASK_NAME,'_3_',1,1)=xx

order by execution_end desc;

/

TASK_NAME                      EXECUTION    TASK_ID

------------------------------ --------- ----------

ADDM:1222414252_2_40551        23-MAR-14     164533

from above task name !!!!

SET linesize 300 LONG 999999 pages 1000 longchunksize 999999

col MESSAGE for a50 wrap

col IMPACT_TYPE for a30

SELECT

 r.type,

 r.Rank,

 r.benefit,

 f.impact_type,

 f.impact,

 f.message

FROM

 dba_advisor_recommendations r,

 dba_advisor_findings f

WHERE 1=1

   and   r.task_name = 'ADDM:1222414252_2_40551'

  AND r.finding_id = f.finding_id

  AND r.task_id = f.task_id

ORDER BY r.rank ASC, r.benefit DESC;

/

SET linesize 300 LONG 999999 pages 1000 longchunksize 999999

select DBMS_ADVISOR.GET_TASK_REPORT('ADDM:1222414252_2_40551', 'TEXT',  'TYPICAL', 'ALL', 'SYS') from dual;

/

====

ADDM 

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);

exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

DECLARE

  task_name VARCHAR2(30) := 'SYSTEM_ADDM';

  task_desc VARCHAR2(30) := 'ADDM Feature Test';

  task_id   NUMBER;

BEGIN

  select count(*)

    into task_id

    from dba_advisor_tasks

   where task_name = 'SYSTEM_ADDM';

  if task_id = 0 then

    dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);

  else

    dbms_advisor.reset_task(task_name => 'SCOTT_ADDM');

  end if;

  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'START_SNAPSHOT', :BgnSnap);

  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'END_SNAPSHOT', :EndSnap);

  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'INSTANCE', :INST_NUMBER);

  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'DB_ID', :DID);

  dbms_advisor.execute_task('SYSTEM_ADDM');

END;

/

select dbms_advisor.get_task_report('SYSTEM_ADDM', 'TEXT', 'ALL') from dual;