Sybase to Oracle
October 29, 2009 - 8am Central time zone
Reviewer: Leonid Gvirtz from Israel
Sybase ASE versions 15.0.* don't support snapshot isolation. Writers will block readers, with some
exceptions though. There are features in DOL tables that help to avoid blocking in some cases.
I have some past experience with conversion of Transact-SQL code to PL/SQL - and I completely agree
with Tom on this topic. Even if you use any tool for automatic code migration (commercial or free),
you still will need to review the code. Sometimes, while the generated code was workable, it was
either ugly-looking or less than optimal.
Hope it helps
multiversioning works pretty well in MS SQL Server
October 29, 2009 - 1pm Central time zone
Reviewer: A reader from concord, ca
Hi Tom, we have been using multiversioning in SQL Server 2005 for over three years and have found
that the database is not using significantly more resources than with the default mode. Do you
have any test cases or articles to show that it is more "resource expensive"? I know it uses
tempdb for the multiple row versions, but we haven't found contention for tempdb to be a problem.
Followup October 29, 2009 - 3pm Central time zone:
well, turning it on by default generates more work than not having it on.
In Oracle, we use undo (we all have to generate that to support rollback) to provide multiversioning.
In Sqlserver - turning it on will increase the amount of work done during all insert/update/delete activity - you have to put the before image somewhere - and generate the undo/redo stream.
quote:
... The database administrator must make sure that tempdb is optimized for increased I/O bandwidth that is based on the version store workload. ...
The size of the tempdb database must also be monitored (especially if the application has long running transactions).
http://msdn.microsoft.com/en-us/library/ms345124%28SQL.90%29.aspx
Now, I cannot vouch for the technical accuracy of that note - because they did get some Oracle stuff wrong, but I hope they get the sqlserver stuff right. (In fact most ALL of the oracle stuff is actually wrong there, but that isn't what I was after on that page..)
It versions the entire row - most updates don't update entire rows
Your mileage will vary - based on load. If you were smallish to begin with with extra capacity - turning it on probably won't do much to you. If you are are big on the other hand...
code
October 29, 2009 - 3pm Central time zone
Reviewer: sam
Tom:
I like your comment about the 20/80 rule for code converters.
There are many vendors out there that claim they will convert your power builder application code
or cobol, etc to pure JAVA/J2EE code.
In your epxerience, do those tools make life easier or you would prefer writing code from scratch
in the JAVA or whatever other language.
Basically are they worth it. some of those things are pertty expensive.
Followup October 29, 2009 - 3pm Central time zone:
the only answer (you should be getting bored of me saying this already) is of course
"it depends"
is your goal to get your product on new programming paradigm fast - even if it is not very elegant?
do you have millions of lines of legacy code that you could not pay anyone to look at?
then maybe the converters and consultants are the way to go.
Do you have an in house developed application that is 10 years old and you want to update to some current way of doing things? Perhaps a re-engineer is called for (throw it out, start over in effect).
So, it depends.
October 29, 2009 - 4pm Central time zone
Reviewer: A reader
quote:
... The database administrator must make sure that tempdb is optimized for increased I/O bandwidth
that is based on the version store workload. ...
The size of the tempdb database must also be monitored (especially if the application has long
running transactions).
Even in Oracle , either UNDO or Rollback segments has to be sized accordingly . Otherwise long
running transaction will lead to ORA-01555 . Why monitoring tempdb is a bag thing ?
Followup October 30, 2009 - 9am Central time zone:
correct - but - in sqlserver you have to size your transaction log (where they store redo and undo) correctly as well.
My point was: to enable this feature requires sqlserver to not only record undo/redo - but also do extra work (eg: turn this on and you are right away increasing the workload on the server)
In Oracle, this is fundamental to our core - it is the basic way we do it, everything is built up from this - there is no "to use this, be prepared to increase your workload". Do you see what I'm saying? Turn on multi-versioning and do more work in sqlserver.
I'm not saying that monitoring tempdb for IO issues is 'a bad thing', I'm saying "this is not a no-cost freebie". You have to monitor your transaction log (where undo and redo goes) as well as tempdb now in addition to have this work.
sqlserver without multiversioning is less resource intensive than sqlserver with it - that was my point.
|