Re: What is a good blocksize to use.

The database has been develloped with both (Oracle and SQL-server) in mind. Constructions specific to SQL-server or specific to Oracle have been avoided. (Or only if unavoidable been implemented twice, for example identity keys and sequence numbers have seperate coding).

Although both database systems work different there are a great number of similarities.
We have a lot of experience with the data in the databases from a legacy database. (A not Relational Dbms). From that database we know were bottlenecks were.

Now designing for Oracle and for SQL-server we want to know what to do and what not to do and specifically why. Developing is going towards object orientation, using the RUP (Rational Unified Process) method. Here the influence and the used tools are not always geared towards the database. To our developers advise is not enough, even a good advise has to have a solid understanding why and how.
In the OO world some advises go directly against the advises given in the RDBMS world. So it is not only the difference between the RDBMS'ses we are trying to cope with but also with different points of view from the OO world.
(OO does sometimes go to lots of roundtrips only gathering smal pieces of information, were in database terms it would have been far better to do one large query.)

Up to now we have had some 'misunderstandings' about for example : Using artificial, surrogate and intelligent Primairy Keys. Using identity (MS) and sequences (Oracle). Using Isolation levels. (Do we handle this in the database, the application,or a combination. Surprisingly after taking some expert advise we ended up with using application code to ensure a correct isolation. It took us long to get to this decision. But we came mainly to this decision because Oracle does not provide what we needed. We had to find this out from inside out. Documentation on this did not provide the correct answer).

==> Yes, I have to buy the Thomas Kyte book. (Mental not: do arrange that today).

And to come to better decisions, also in applications I do try to understand the 'internal' workings of the different RDBMSses. Hence the blocksize questions.

ben

>Hi Ben,
>
>you mention that you database is a 'port' from SQLServer, and that
>Oracle performs worse that SQLServer. Fair point, but ....
>
>did you actually convert the system to make best use of Oracle features
>or simply 'copy' it all over and expect similar results ?

No we designed for both.

>have you taken into account the fact that both database systems are
>totally different in working etc ?
>have you converted any of the SQL used (I'm presuming you have an app
>that accesses the db) to change it from the SQLServer way of things to
>the Oracle way ?
>do you use 'temporary' tables in your processing - because this is
>required by SQLServer but Oracle doesn't need them because of 'read
>consitency' etc ?

No we do not use temporary tables.
MS SQL-server isolation level serializable offers real serializability and this does offer read consistency and more. It offers serializability as described in the Iso standard. But we do not use this because Oracle does not offer real serializability as described in the "(Second Informal Review Draft) ISO/IEC 9075:1992"

>
>I suggest a good look at Tom Kyte's book Exprert one-on-one Oracle -
>especially the first few chapters where he discusses the differences
>between different databases.
>
>
>Regards,
>Norman (being constructive and not starting any religous wars !)
>
>-------------------------------------
>Norman Dunbar
>Database/Unix administrator
>Lynx Financial Systems Ltd.
>mailto:Norman.Dunbar_at_LFS.co.uk
>Tel: 0113 289 6265
>Fax: 0113 289 3146
>URL: http://www.Lynx-FS.com
>-------------------------------------
>

Ben Brugman Received on Thu Sep 26 2002 - 07:01:45 CDT