Insert Tuning

I have a delete on a tableA.

There is a row level trigger on the tableA, that inserts into history table tableB.

About a million rows are deleted and inserted into history table. In other environments this takes less than a minute in QA it takes 3 hours.

On checking i found that delete of tableA was fast, but insert into history tableB was slow.

Stats on history tableB are a week old.

How can i tune insert into history tableB?
Who is Participating?
Franck PachotConnect With a Mentor Commented:

The performance of the insert mainly depends on the number (and type) of indexes as they generate lot of redo. You should check if you have contention on redo log. Statspack or AWR report will help a lot to focus on the bottleneck.

Note that what you do is probably the worst way to do it: delete and insert, row by row.
Flashback Data Archive being the best one. In the middle, you can logically delete (flag rows as deleted) and have a batch job that insert/delete in bulk.

DavidSenior Oracle Database AdministratorCommented:
First guess, you're dealing with significantly different database definitions, devices, and so on.  Secondly, deletes require only for a bit to be flipped, whereas inserts require I/O to find a block with free space, write the row, and repeat for each index.  Then, a commit to flush rows from the undo buffer into the table.

Also, if you have not deferred index creation, you may be trying to create indexed rows one at a time, or without a preddefined index.

You should tune any data manipulation statement (such as insert) with an explain plan; this method is extremely well documented in Oracle and in the EE knowledge base, search key in top right corner.
gram77Author Commented:
Explain plan shows: Conventional Load COst= 1
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

DavidSenior Oracle Database AdministratorCommented:
1.  Please provide your Oracle version and storage (SAN, RAC, ASM);
2.  an example of your insert trigger;
3.  whether or not the table has any CLOB-type fields
4.  which rows in the HISTORY table are indexed, and type of index
5.  partitioned table or not?
6.  full explain plan result

Lastly, touch on the business case (reason) for doing row-based deletes and inserts with the volume you have.
gram77Author Commented:
1. Oracle 10g R2
2. It's a row level trigger.
3. No CLOB type fields present
5. Non partitioned table.
6. Explain Plan : conventional load : cost 1
Reason is to keep a log of what all rows were deleted.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
>> deletes require only for a bit to be flipped
Delete process needs to insert and manage original values in rollback segments and is very heaver than insert.

Trigger will definitely slow down the insert statement.

If you know your Sql_ID, then you can query DBA_HIST_SQLSTAT and see all DELTA column.
You can identify if there is contention.
Also you can generate ASH report to see any blocking locks activity during that time period.
gram77Author Commented:
I inserted in append mode and now it took less than 2 minutes to delete and maintain history of 1,50,000 rows.

append mode works for me since i am inserting into history table where the inserts are in bulk only once a day. and data is removed once a month.

doc on append mode.

I want to know since the append mode starts filling in data after the high water mark, without checking for availability of space below the high water mark will i get out of space issues often in this table
say if i remove bulk data every month. Will removing data set back the high water mark?

What are the disadvantages of using append mode? should i use it?
Franck PachotConnect With a Mentor Commented:
Hi, yes the deleted space will not be reused. You may plan to shrink after the bulk delele. It may be long but it's online.
Another disadvantage of append insert is thatit locks the table.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
This looks like another question to me.
Original question was for insert, this question is for delete operation.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.