Tuning Insert.

create or replace package body myPKG
as
begin

delete
from tableA
where idate = '21-Jan-13';

end;

create or replace
TRIGGER myTrig
AFTER UPDATE OR DELETE OR INSERT
ON tableA OLD AS OLD NEW AS NEW
FOR EACH ROW
declare
begin
   if deleting
   then
      insert /*+ append */ into tableA_hist
         (col1,
          col2,
          col3,
          col4,
          col5)
      values
         (:old.col1,
         :old.col2,
         :old.col3,
         :old.col4,
         :old.col5);
end;

ORA-04088: error during execution of trigger 'PBSDDEV.USR_OVRD_ELMNT_SPEC_AIUR'
ORA-06512: at "PBSDDEV.MRGN_OVRD_POSTLOAD", line 1336
ORA-06512: at line 9
12838. 00000 -  "cannot read/modify an object after modifying it in parallel"

I've mentioned this before, you really should post your code in the question, not in a reply to yourself.  Doing what you do hides the question by making it look like someone has already responded.

Will take care

Depending on the requirement it might be advantageous to populate the history table with a trigger on insert/update of tableA.
Presumably the history is for archival purposes rather than a whole data set.

take off the append hint to avoid the 12838 error

I added the append hint to tune the insert. How else do I tune the insert

your code is generating an error, "tuning" it isn't particularly important if it simply fails.
removing append should allow it to complete.

but, for tuning - don't do it row-by-row
 this will be much more complicated, but the insert itself is fine, the problem is doing it again and again and again

also, since the insert is part of a delete trigger, are you sure the problem is in the insert and not the delete?

what are the wait events?

If I disable the trigger, the delete itself is fast. However, if I enable the trigger it is held up in the insert

I have inserted into the history table before deleting. and commented out the trigger

create or replace package body myPKG
as
begin

insert into tableA
values (col1, col2)
select col1, col2
from tableA
where idate = '21-Jan-13';

delete
from tableA
where idate = '21-Jan-13';

end;

--disabled the trigger
create or replace
TRIGGER myTrig
AFTER UPDATE OR DELETE OR INSERT
ON tableA OLD AS OLD NEW AS NEW
FOR EACH ROW
declare
begin
   if deleting
   then
      insert /*+ append */ into tableA_hist
         (col1,
          col2,
          col3,
          col4,
          col5)
      values
         (:old.col1,
         :old.col2,
         :old.col3,
         :old.col4,
         :old.col5);
end;

that should be a lot faster

you can put the append into the insert now that it's not part of the delete trigger


you might want to adjust isolation level of your session if you're concerned about someone committing a transaction between the time the insert starts and the delete starts.

We are using dbms_lock package.
Is there any other way to define isolation level

alter session set isolation_level ....


dbms_lock doesn't do that

It is still taking the same time after i commented out the insert into history table in the trigger, and shifted it out in the proc.
I want to try out insert in APPEND mode, and change the history table to nologging.

alter table tableA_hist nologging;

create or replace package body myPKG
as
begin

insert /*+ APPEND */ into tableA_hist
values (col1, col2)
select col1, col2
from tableA
where idate = '21-Jan-13';

delete
from tableA
where idate = '21-Jan-13';

end;

Also, the log_mode in QA environment is NOARCHIVELOG and in production it is ARCHIVELOG.
Will this slow down the insert in the production environment?

Is there any other way i can tune the insert without choosing NOLOGGING as NOLOGGING has it's own pitfalls?

yes, archivelog will be slower but not much.

do put append back in

your insert doesn't have any complexity to lend itself to tuning the syntax.
Do you have an index on idate so the select will run effeciently?

Are their indexes or triggers on tableA_hist?  If so, those will slow down inserts

The same proc in UAT, DEV and other environments is not long running, the bottleneck is only in QA.
And in UAT and in other env. where the proc finishes fast, the index was analyzed more recently then in QA - this may be one of the reasons.
There is clearly an environmental difference.

Index on all the environments are valid.

Also, don't understand how can index rebuild or index analysis tune an insert. As insert is not like select where a recent analyzed table is faster than the other one.

In my mind the only way insert can benifit is by disabling the index and enabling it later, or by no archive way. Not by rebuilding an index or reanalyzing it.

I have analyzed the tables and used append hint, and also created index on large tables, still the insert takes 2 hours to complete.

The insert is trying to add 1 million rows at once. Is this an issue.

Will it help if i insert smaller chunks say 1,00,000 rows at once and then commit?

would it help to use NOLOGGING on the history table?

I see Long Ops: Sort Output taking time

I can bulk collect into a pl/sql variable using a single fetch, and insert into history table 10 times each time inserting 1,00,000 rows, along with NOLOGGIN on the history table and the index it uses.
Will this help?

sdstuber
"nologging can make the insert faster, make sure you take a backup afterward though"
How do i ensure a backup?