Has anyone noticed severe performance issues with bulk updates on tables with compound triggers, caused by ever increasing amounts of Allocated PGA memory . I can't believe it's just me !? I've seen this effect on both 11.2.0.4 and 12.1.0.2 on Oracle Linux and AIX. This can easily be re-recreated by creating the following table and index:
CREATE TABLE large_table (
large_id NUMBER NOT NULL
,ref_id NUMBER NOT NULL
,large_txt VARCHAR2(100)
,large_date DATE ) ;
CREATE UNIQUE INDEX large_pk ON large_table (large_id) ;
Then using the following script as the basis for inserting and updating the data:
DECLARE
TYPE large_tab_type IS TABLE OF large_table%ROWTYPE;
largeTab large_tab_type := large_tab_type();
lv_allocated NUMBER;
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..10000 LOOP
largeTab.EXTEND;
largeTab(j).large_id := ((i - 1) * 10000) + j;
largeTab(j).ref_id := j;
largeTab(j).large_txt := RPAD('0',99,'1');
largeTab(j).large_date := SYSDATE;
END LOOP;
FORALL k IN 1..largeTab.COUNT
INSERT INTO large_table VALUES largeTab(k);
FORALL k IN 1..largeTab.COUNT
UPDATE large_table
SET large_date = SYSDATE
WHERE large_id = largeTab(k).large_id;
largeTab.DELETE;
SELECT pm.allocated INTO lv_allocated
FROM v$session s , v$process p , v$process_memory pm
WHERE s.paddr = p.addr AND p.pid = pm.pid
AND pm.category = 'PL/SQL'
AND s.osuser = 'xxxxxx' AND s.program LIKE 'sqlplus%';
dbms_output.put_line('Memory Alloc : ' || TRUNC(lv_allocated/1024/1024,1) || 'MB');
END LOOP;
END;
/
Firstly, comment out the bulk FORALL UPDATE and run it to insert 100,000 records. Commit and then comment out the insert and add the FORALL UPDATE back in and run it with SERVEROUTPUT ON. It should display:
USER@ORACLE> @bulkupd
Memory Alloc : 3.4MB
Memory Alloc : 3.4MB
etc..
Then rollback, create a simply compound trigger as follows and run it again.
CREATE OR REPLACE TRIGGER large_compound
FOR INSERT OR UPDATE OR DELETE ON large_table
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
NULL;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
NULL;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
NULL;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
NULL;
END AFTER STATEMENT;
END large_compound;
/
The output is now:
USER@ORACLE> @bulkupd
Memory Alloc : 23.3MB
Memory Alloc : 43.2MB
Memory Alloc : 63.2MB
Memory Alloc : 83.1MB
Memory Alloc : 103MB
Memory Alloc : 122.9MB
Memory Alloc : 142.8MB
Memory Alloc : 162.7MB
Memory Alloc : 182.6MB
Memory Alloc : 202.5MB
Seems strange ..? This was causing us a major headache as when we hit the PGA limit, the job grinded to a halt. What's interesting is that changing the BULK UPDATE to an iterative update in a loop, or changing the compound trigger to a normal DML trigger reverts back to the normal memory behaviour.
I've gone into a lot more detail on my blog http://www.angelicoit.co.uk