?
Solved

Insert Tuning

Posted on 2013-01-23
9
Medium Priority
?
339 Views
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?
0
Comment
Question by:gram77
[X]
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
9 Comments
 
LVL 23

Expert Comment

by:David
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.
0
 

Author Comment

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

Expert Comment

by:David
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:gram77
ID: 38810973
1. Oracle 10g R2
2. It's a row level trigger.
3. No CLOB type fields present
4.
5. Non partitioned table.
6. Explain Plan : conventional load : cost 1
Reason is to keep a log of what all rows were deleted.
0
 
LVL 15

Expert Comment

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

Accepted Solution

by:
Franck Pachot earned 2000 total points
ID: 38811668
Hi,

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.

Regards,
Franck.
0
 

Author Comment

by:gram77
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.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484


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?
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 2000 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.
Regards,
Franck.
0
 
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.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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
This video shows how to recover a database from a user managed backup

718 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