Insert Tuning

Posted on 2013-01-23
Last Modified: 2013-01-27
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?
Question by:gram77
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 23

Expert Comment

ID: 38810538
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.

Author Comment

ID: 38810592
Explain plan shows: Conventional Load COst= 1
LVL 23

Expert Comment

ID: 38810903
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.
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.


Author Comment

ID: 38810973
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.
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 38811205
>> 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.
LVL 15

Accepted Solution

Franck Pachot earned 500 total points
ID: 38811668

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.


Author Comment

ID: 38814000
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?
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 500 total points
ID: 38814965
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.
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 38815198
This looks like another question to me.
Original question was for insert, this question is for delete operation.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question