Home » RDBMS Server » Performance Tuning » help with coming up a test case!
help with coming up a test case! [message #192195] Mon, 11 September 2006 07:23 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hello,

There is one idea: " Instead of doing update, do delete and insert!" This idea is something basically wrong and I have to help to bring out that we would lose performance, by not using the UPDATE statement supplied by Oracle, and instead using delete and then insert.

For example in emp table, if ename of scott is to be updated to scotty then delete the row with ename of scott and insert a row with 'scotty' but don't use update statement becoz it is 'BETTER' this way-by delete/insert


I have to write a test case for that.

Can you please help.


Nirav

Re: help with coming up a test case! [message #192216 is a reply to message #192195] Mon, 11 September 2006 07:54 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
well, its quite simple.
There are a couple of things you can work on:

1) if we make a top-n list for DML operations with context to performance. The most expensive one is at the top.

a) DELETS (GENERATE a lot of UNDO and REDO DATA)
b) UPDATES (GENERATE lesser redo and undo, just the fields that are changed).
c) INSERTS (GENERATE very less UNDO data, just the owid of the inserted row).

As you know that deletes, the occupied space by the deleted records is not released. That can cause swere Space management issues later. Like Unnecessary Extra Space Usage. Tablespace Fragmentation Problems. Query Performance Problem due to scattered IO on the physical files.

Regards,

Liza

Re: help with coming up a test case! [message #192257 is a reply to message #192216] Mon, 11 September 2006 10:24 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Lize,

That is very helpful to me...specially your point on redo/undo did not strike me at all! Now here is a problem: I can not see any perceptible difference in performace when I run a tkprof report for either of the case! Also my developer friend wrote some methods in Java and he too does not see any differene in the response time! But i do see a great difference when I execute from sql plus with autotrace (not tkprof, but autotrace.)

so my difficulty here is: can we have a testcase with TKPROF that proves better response times and less resource use by update vs insert/delete?

again my sincere thanks,
Nirav
Re: help with coming up a test case! [message #192436 is a reply to message #192257] Tue, 12 September 2006 06:38 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
My bet is that your friend is performing single transaction delete and insert versus single transaction update, possibly within a loop on the Java client. Your tests are bulk processed on the server without any language switching.

The problem with this type of test is that much of the time is spent mucking about with networks and switching languages. If the SQL is (say) only 10% of the operation, then even if delete/insert is twice as slow, you are only going to see a very small reduction in overall performance.

If this is the "real-life" situation, then you would need to multi-task the database up to its breaking point to get a truer test. You would find that delete/insert breaks with fewer users.

Ross Leishman
Re: help with coming up a test case! [message #192475 is a reply to message #192436] Tue, 12 September 2006 09:32 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi Ross,

Thanks a lot! that gives me another insight on the 'why' of the java testing result. I hope that tomorrow I can upload a question I have on sql trace report. even when I use sql, and try getting a tkprof report from sql trace, the differences are not that obvious..but the results for the same sql are clear as daylight when i use autotrace!

thanks a lot!
Nirav
Re: help with coming up a test case! [message #192740 is a reply to message #192475] Wed, 13 September 2006 07:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Another point (which may not apply in your case): If the table you are doing this with has a primary key which is referenced as a foreign key, then the DELETE simply will not work.
Previous Topic: Merge Partition into 1 single partition
Next Topic: Analyze increases cost
Goto Forum:
  


Current Time: Fri Dec 02 22:46:17 CST 2016

Total time taken to generate the page: 0.09209 seconds