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.
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

632 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