Temporary table use and the redo log

> > By the way, who tells you that the inserts are causing the redo
> > generation? There might other DML/DDL going on that generates redo.

Good call :-) There was a delete in there which I missed.
I now use

EXECUTE IMMEDIATE 'TRUNCATE TABLE TP_TEMP';

and it works, i.e. no redo information being generated.

So summary for future reference of all the steps needed

- you have a stored procedure which has an insert into a temporary
table and 2 selects working
on that temporary table

(1) split the procedure into 2, one half with the insert, the other
half with the selects which calls the first
(2) in the insert stored proc:

(i) add the below just before the first BEGIN
PRAGMA AUTONOMOUS_TRANSACTION;

(ii) if you need to delete from the temp table before you begin then:
EXECUTE IMMEDIATE 'TRUNCATE TABLE TP_TEMP';

(iii) add the below at the end of the insert, just before the END
COMMIT;

(iv) Change your insert to look like the below

INSERT /*+APPEND */ INTO

Thats it. Now of course it needs proper testing and I can't say if it
will increase or slow performance or have any
other side effects. Also, its an ugly hack so as a last resort

Na Wed, 09 Sep 2009 13:20:18 -0700, Randolf Geist napisao:

> Please check above mentioned point: You need to verify that the inserts
> are actually direct-path inserts in your "actual problem stored
> procedure". You can do so by using above technique of querying/
> accessing the table after inserting before committing. If this *doesn't*
> fail with an ORA-12838, then you've performed a conventional insert. The
> "APPEND" hint will be ignored if any of the restrictions apply for
> direct-path inserts.

Interesting method for establishing success or failure of the direct-path
insert. I usually trace the session and see if there are direct file
writes in the wait events.



--
http://mgogala.freehostia.com