Home » RDBMS Server » Performance Tuning » performance of delete/insert deteriorate over time (10.2)
performance of delete/insert deteriorate over time [message #377718] Wed, 24 December 2008 09:59 Go to next message
Orna
Messages: 62
Registered: November 2008
Member
Hi

I have a table that is going thru delete * ( complete ) and then insert every night.

the elapsed time is creeping up slowly
( overall timing started at 13 minutes a month ago, and now its 18 minutes ). The main offender is the delete statements, less so the inserts.


From ASH reports and looks like the problem is with the indexes on these tables. the db file sequential read event is creeping up . all indexes are in the same tablespace

Can you explain why this is happaning?
the amount of rows each delete/insert cycle is about the same ( about 4 milion rows each night ) .

Thanks
Orna
Re: performance of delete/insert deteriorate over time [message #377747 is a reply to message #377718] Wed, 24 December 2008 16:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I have a table that is going thru delete * ( complete ) and then insert every night
You mean, you are deleting all data and inserting again?
This has to do with HWM.
Search this forum for HWM (High water mark).
Long story short,
truncate the table instead of delete
and
Collect stats again (on both table and indexes) after purging the data.
Re: performance of delete/insert deteriorate over time [message #377749 is a reply to message #377747] Wed, 24 December 2008 16:55 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
Thanks

But as I mentioned above - I cannot truncate the table as part of the job. ( because of the implicit commit )
If I will have to do it ,it will be an application downtime.

I am just curious as to why HWM has anything to do with increased waits on the indexes data files and with db_sequential_reads on those files. I would understand if it was scatterred reads on the table itself - but why the indexes ? how are they affected with the HWM - especially since I am deleting the entire table every night .

Thanks again
Orna
Re: performance of delete/insert deteriorate over time [message #377750 is a reply to message #377749] Wed, 24 December 2008 17:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>But as I mentioned above - I cannot truncate the table as part of the job. ( because of the implicit commit )
Where?
This topic is been beaten down the death.
Did you care to search the forum?
Atleast collect the stats after Delete.

Re: performance of delete/insert deteriorate over time [message #377752 is a reply to message #377750] Wed, 24 December 2008 18:25 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
I apologize

I posted this question in another thread, that is where I explained about the truncate problem :

http://forums.oracle.com/forums/thread.jspa?forumID=61&threadID=841005

In any case , I am very familiar with HWM and the problems with continuing delete/inserts. However - the way it is presented in my case still puzzles me.
Why would indexes be affected if they are completely 'rebuilt' every might ( isnt a delete * and then re-insert = index rebuild, note - NOT table rebuild , only index rebuild ) ? indexes should not be effected with the HWM - they only have entries for actual rows - so even if the table consistes of 30% empty blocks - the indexes should still be the same size - if the number of rows remain unchanged.

thanks anyway
Orna
Re: performance of delete/insert deteriorate over time [message #377754 is a reply to message #377752] Wed, 24 December 2008 20:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You are presenting information in bit and pieces. Smile
Are you recollecting the stats for index/tables after every cycle?
Else
Try it and post the results.
Re: performance of delete/insert deteriorate over time [message #377807 is a reply to message #377754] Thu, 25 December 2008 05:31 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
ok will do
Re: performance of delete/insert deteriorate over time [message #377947 is a reply to message #377754] Fri, 26 December 2008 18:50 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
Collecting stats did not help.

Re: performance of delete/insert deteriorate over time [message #377948 is a reply to message #377947] Fri, 26 December 2008 18:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post what you did.
The exact command you used.
Re: performance of delete/insert deteriorate over time [message #377949 is a reply to message #377948] Fri, 26 December 2008 18:59 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
exec dbms_stats.gather_table_stats(ownname => 'AQR51', tabname => 'MATCHPATTERNS_SDB' cascade => true );

I would not expect new stats to change anything.

the command is 'delete * from < table_name >
it was a full table scan and it will remain a full table scan
the statistics did not change that much - as I said - the amount of rows always remains roughly the same.

Re: performance of delete/insert deteriorate over time [message #377950 is a reply to message #377949] Fri, 26 December 2008 19:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I am not worried about table.
Since you are rebuilding indexes, I was wondering whether you are recollecting stats after every cycle.
By cycle I mean,
delete, insert, rebuild index followed by recollection of stats.
Then delete again.
I have no access to database, Else i would have already created a test case.
Re: performance of delete/insert deteriorate over time [message #377951 is a reply to message #377950] Fri, 26 December 2008 19:11 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
I am NOT rebuilding the after each delete,insert

I suspect that rebuilding the indexes will do the trick here
but this is exactly what puzzled me : why ??
Isn't delete * and then insert all the rows again - is exactly equivalent to "rebuild " ?
why are the indexes 'deteriorating ' during these delete/insert cycles - if the delete is complete ?
Re: performance of delete/insert deteriorate over time [message #377952 is a reply to message #377951] Fri, 26 December 2008 19:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You previously stated
>>Why would indexes be affected if they are completely 'rebuilt' every might
followed by
>>NOT table rebuild , only index rebuild

and now stating
>>I am NOT rebuilding the after each delete,insert

Which one is true?

>>Isn't delete * and then insert all the rows again - is exactly equivalent to "rebuild " ?
No. Yes. May be.
Depends on how "rebuilding" is defined in our dictionary.
In oracle terminology, rebuilding index is dropping and creating the index again (online or offline).
In your case, it would be better to
drop the index,
truncate the table (preferably. Else just delete rows),
insert into table,
recreate the index
recollect stats.

>>why are the indexes 'deteriorating ' during these delete/insert cycles - if the delete is complete ?
It will. Over period of time.
Consider a text book.
If you completely change the contents of the book and just use the same index entries to goto a particular page, what happens?


Also, does your table have any lobs?




[Updated on: Fri, 26 December 2008 19:30]

Report message to a moderator

Re: performance of delete/insert deteriorate over time [message #377967 is a reply to message #377952] Sat, 27 December 2008 02:36 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When an index block is allocated, it is allocated for a particular range of values. If the number of rows with values in that range increases then the block can split (thereby DECREASING the range of values), but when the volumes decrease (rows are deleted), the blocks will never re-merge.

When you delete every row and then insert again, then these index blocks SHOULD fill back up - providing you insert the same values that you deleted.

However, if you delete rows with values that never get replaced, then some blocks will become sparse or even empty. A date index is a good example: if old values tend to "drop off" then the index blocks that contained the older dates will never re-fill.

Empty blocks should not present a problem, because it doesn't cost anything to ignore an empty block.

SPARSELY filled blocks are a different storey though. Say in the first cycle you had 1000 full blocks. Then in the second cycle you had a 10% change of data - such as a date or ever-increasing sequence number. The 10% that disappears might be evenly spread over (say) 200 blocks leaving them 50% full; the 10% that is newly created is densely packed in 100 new blocks.

Now you have 1100 blocks to delete from in the third cycle. Remember that Oracle does not read and write ROWS, it reads and writes BLOCKS, so that extra 100 blocks (with no increase in rows) is an extra 10% of works that needs to be done on the delete.

In the third cycle, the bottom 10% of values is deleted again, this time spread over the bottom 200 sparse blocks, plus the next 200 densely packed blocks. After the third cycle, you have 1200 blocks, 400 of them sparsely packed.

After many cycles, the entire index will be strung out over twice as many (or more!) blocks as it needs.


This should not happen with indexes on values that are randomly distributed. It should only occur when the values change predictably over time. You can test the theory by gathering statistics after each cycle and checking the LEAF_BLOCKS column - it should be increasing.

You could mitigate this by creating a REVERSE KEY index. This will turn the predictably changing values into randomly distributed values by doing a byte-wise reversal.

WARNING: If you use a Reverse Key index, it can only be used for EQUI-predicates (equals and IN), not for >, <, LIKE, or BETWEEN.

Ross Leishman
Re: performance of delete/insert deteriorate over time [message #377980 is a reply to message #377967] Sat, 27 December 2008 06:37 Go to previous messageGo to next message
Orna
Messages: 62
Registered: November 2008
Member
thank you !

One question though : does this explanation hold true also if the delete is COMPLETE every night ? ( delete * )
Re: performance of delete/insert deteriorate over time [message #377982 is a reply to message #377980] Sat, 27 December 2008 06:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Yes.
It does not even matter whether you delete all the records or selectively. Over time it will deteriorate.
Re: performance of delete/insert deteriorate over time [message #377987 is a reply to message #377718] Sat, 27 December 2008 08:18 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Can you implement:

ALTER TABLE ... SHRINK SPACE COMPACT;
?

It's supposed to reset HWM ( 10g and up).

HTH.
Re: performance of delete/insert deteriorate over time [message #378022 is a reply to message #377987] Sat, 27 December 2008 17:48 Go to previous message
Orna
Messages: 62
Registered: November 2008
Member
Hi guys

I got another very clear response from Jonathan Lewis in the OTN forum and wanted to attach it here if anyone is inserested.

http://forums.oracle.com/forums/thread.jspa?messageID=3190464&#3190464

thanks again
Orna
Previous Topic: Left outer join taking more time
Next Topic: One query with two where Conditions on same column
Goto Forum:
  


Current Time: Mon Dec 02 07:20:29 CST 2024