Oracle Database 12c Real-Time ADDM

Oracle 12c has a new automatic performance analysis/troubleshooting feature, known as the Real-Time ADDM.  This feature analyzes performance problems as soon as they are detected, and attempts to find solutions and saves its analysis as a Real-time ADDM analysis report.

The Oracle Database looks out for these performance problems, and tries to analyze them in rea-time whereas previously, they would get analyzed and the snapshot collection time only.

• High Load
• IO bound instance
• CPU bound instance
• Over allocated memory
• Interconnect bound instance (RAC)
• Session limit approaching 100%
• Process limit approaching
• Hung sessions
• Deadlock detected

As per the Oracle documentation, the MMON process analyses the instance performance every 3 seconds and triggers a Real-time ADDM if any of the conditions above are detected.

So what does a real-time addm report look like? Below are two trials where an attempt was made to trigger the auto addm by deliberately triggering two of the events mentioned above

1. A session memory over allocation trial – tiggers automatic addm.
2. A deadlock – no automatic addm.

Memory over allocation trial – results in real-time addm.

In a test database, ensure that the init.ora parameter OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to FALSE, and execute a script that creates a large enough pl/sql collection until the pga consumed is bigger than the instance PGA_AGGREGATE_LIMIT value. Here is the script:

create or replace type  t_city is object ( city_name varchar(100));
/
create or replace type t_city_coll is table of t_city;
/

declare
  v_city      varchar(100) := 'testtesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttest';
  v_city_coll t_city_coll := t_city_coll();
begin
  for i in 1 .. 10000000
    loop
    v_city_coll.extend();
    v_city_coll(v_city_coll.count()) := t_city(v_city);
  end loop;
end;

It fails after 6 minutes with ORA-4036 (PGA memory used exceeds PGA_AGGREGATE_LIMIT).

But it is interesting to see if it will trigger an real-time addm also.

Going to the Performance Hub and clicking on the ADDM tab, we can see a Real-Time ADDM report was generated indeed, below it is seen that it has a Report_id=121.

The screen grab below shows the content of the Real-Time ADDM report ADDM findings tab, which shows the findings and offers a couple of recommendations, which are to lower instance memory or kill the session consuming extra PGA.

Trying out real-time addm by triggering a deadlock

Trigger a deadlock error by using an autonomous transaction as follows. (This deadlock trialwas tried out on a NONCDB with compatible=12.1.0.0.0)

1. Consider a table called emp with these 3 records.

SQL> select * from emp;

     EMPNO NAME                 JOIN_DATE END_DATE

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

         1 emp1                 03-DEC-14

         2 emp2                 03-DEC-14

         3 emp3                 03-DEC-14

2. Create a procedure name proc2 and proc1 as shown. Proc2 is called by Proc1 and proc2 executes as an autonomous transaction and tries to modify the same record as proc1, triggering the deadlock.

create or replace procedure proc2 is
pragma autonomous_transaction;
begin
delete from emp where empno=1;
commit;
end;
/

create or replace procedure proc1 is
begin
 update emp set join_date=sysdate where empno=1 ;
  proc2;
end;
/

3. When called, proc2 will immediately trigger the deadlock condition and Oracle reports the error as shown.

SQL>

SQL> begin

  2    -- Call the procedure

  3    proc1;

  4  end;

  5  /

begin

  -- Call the procedure

  proc1;

end;

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at "SCOTT.PROC2", line 4

ORA-06512: at "SCOTT.PROC1", line 4

ORA-06512: at line 3

SQL>

4. But upon checking the Performance Tab in EM Express, no Real-time ADDM was triggered. After repeatedly invoking the procedure the Current ADDM findings only mentioned that the DELETE statement in the procedure was consuming a lot of db time.