Solved

Insert Tuning

Posted on 2013-01-23
9
330 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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 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.
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now