| help with coming up a test case! [message #192195] |
Mon, 11 September 2006 07:23  |
orausern
Messages: 826
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  |
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 |
orausern
Messages: 826
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 |
rleishman
Messages: 3728
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 |
orausern
Messages: 826
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
