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
- A session memory over allocation trial – tiggers automatic addm.
- 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)
- 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
- 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;
/
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;
/
begin
update emp set join_date=sysdate where empno=1 ;
proc2;
end;
/
- 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>
- 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.
