Conference ulysse::rdb_vms_competition

82.0. "ORACLE V5.1.16 vs VAX Rbdb/VMS V2.3" by 32371::BERENSON (Rdb/VMS - Number ONE on VAX) Thu Mar 03 1988 16:23

A little while back a scanned the ORACLE documentation to compare it to
what we are doing for VAX Rdb/VMS V3.0.  I've edited my comparison to
represent Rdb/VMS V2.3, eliminating any V3.0 information (sorry).  As a
hint, you should know that most of the "*"s change to "-" and that quite
a few additional "-"s appear in the V3.0 comparison.

---------------------------------------------------------------------------
Notes from comparison of ORACLE V5.1.16 documentation to VAX Rdb/VMS V2.3:

- = ORACLE Functionality Inferior to VAX Rdb/VMS V2.3

* = ORACLE Functionality with some advantages over VAX Rdb/VMS V2.3

? = Should validate exact information

===========================================================================

* An ORACLE database is split into a number of partitions, each of which
is made up of 1 or more files.  A relation and its' indices are
associated with a partition by use of a space definition , which may
specify how many pages are initiall to be allocated for data and
indices, extension size, etc.  Multiple relations may use the same space
definition, and therefore may be stored in the same partition.  While
the ability to use multiple files within a partition allows a relation
to be very large, no facilities are provided to control the placement of
the relation or its' indices within the partition.  Thus, there is no
way for the DBA to prevent an individual disk drive from becoming the
limiting factor to application performance. VAX Rdb/VMS V2.3's
Multi-Disk Database feature allows database I/O to be spread over a VMS
Volume Set.  For many applications this will provide the same
performance benefit as the ORACLE multi-file capability, at the expense
of the complexity of VMS Volume-Set management.


* ORACLE permits records from different relations to be clustered
together based on a common key. All records that share a common key
value are stored together in a clustered block.  ORACLE automatically
creates a B-Tree index on the columns used for the cluster key.  This
scheme is slightly more general purpose than that provided by VAX
Rdb/VMS V3.0, since it does not rely on the suitability of the HASHED
access method.  On the other hand, without the ability to control the
placement of the clustered blocks, the ORACLE scheme will run into
unavoidable disk bottlenecks much earlier than the hashing-based scheme
used by VAX Rdb/VMS V3.0.  Also, the B-Tree index on the
cluster key is a likely source of contention problems at high
transaction rates.

- ORACLE has a fixed page size of 2048 bytes with 1972 bytes available
for data records.  Records are allowed to span pages, in which case the
portions of the record are chained together.  VAX Rdb/VMS V2.3 supports
variable page sizes, with an optimum size chosen at database definition
time. By adjusting the page size to match the data requirements, VAX
Rdb/VMS V2.3 can achieve superior performance.  For example, if an order
processing system typically requires 4KB to store the line item records
for a particular order then a page size of 8KB might be selected.  This
would allow the line items to be read or written with a single I/O.
Even with ORACLE Clusters, the 2KB pages size would result in several
I/Os as the chain of portions of the data cluster were followed. 

- ORACLE does not fully re-use space on deletions.  For example, if a record
is deleted from one page the space on that page will only be used when
modifying other records on that page.  Only when all records on the page have
been deleted will the space be available for storing new records.  With VAX
Rdb/VMS V3.0, the space for deleted records becomes available for re-use when
the user who deleted the record unbinds from the database. Depending on the
storage area type, this space may be available for new records within the same
relation, or for any new record which may be stored in that storage area.

- ORACLE does much of its work using background processes.  For example,
all writing of Before Images, After Images, and database pages, is
performed by background processes.  While this offers opportunities for
the grouping of I/Os (and we don't know how well ORACLE takes advantage
of the opportunity) it also presents the system with a set of potential
bottlenecks.  VAX Rdb/VMS V2.3 operates as a privileged set of
subroutines.  Each user process handles its own writing (and reading) of
database pages and recovery unit journals (before images).  At COMMIT
time, VAX Rdb/VMS V2.3 writes all modified database (and snapshot) pages
in the user's buffers in parallel, allowing the disk controllers to
optimize access to each drive's actuator and permitting individual
drives to complete their work simultaneously.  The VAX Rdb/VMS
After-Image Journal and Root File are written in a way that maximizes
the grouping of I/Os.  For example,  if multiple users on a system are
waiting for access to either file, all of their work is done
simultaneously by the first user to gain access to the file.  In
addition, access from different nodes in a VAXcluster may be done in
parallel, providing the disk controller the ability to turn separate
access into a single sequential I/O operation. 

* ORACLE supports a global cache of database pages.  Under some
circumstances this can result in applications performing less I/O and
using less memory than the per-user cache scheme used by VAX Rdb/VMS
V2.3.  In many applications, particularly those where different users
access different portions of the database, the global vs local cache
approaches will make little difference in performance.  In a very small
number of cases, the global cache approach will result in lower
performance because the data access pattern of one user application can
interfere with the access pattern of another application.  This is
particularly true when the size of the global cache is not set
appropriately.

- ORACLE uses a single Before Image File to recover aborted transactions
AND to provide read-consistency.  In a VAXcluster, the BI file is broken
into separate ranges of pages for each processor. Apparently, if there
is insufficient space in the BI file transactions will fail.  The BI
file must then be manually expanded.  The use of the BI file to provide
read-consistency complicates the situation.  VAX Rdb/VMS V2.3 separates
the BI File, or Recovery Unit Journal (RUJ) and any requirements for
read_consistency.  The RUJ files are kept on a per user basis and are
independently assignable to different disk drives. Since they are only
uses for recovery purposes, the associated space can be reused as soon
as the transaction commits.  An additional mechanism, SNAPSHOTS, are
available for those installations that wish to provide consistent
read_only transactions.  The snapshot, or.SNP, file may be independently
assigned to a disk to eliminate any disk bottlenecks. 

- ORACLE is unable to use an index for queries which include predicates
of the form IS NULL or IS NOT NULL.  VAX Rdb/VMS V2.3 is able to use an
index to satisfy these queries.

* ORACLE is capable of using multiple indices to satisfy queries in which
the predicates are equalities and the predicates are on the same table.
In this case, the information returned from each of up to 5 indices is
merged to determine which records should be retrieved.  VAX Rdb/VMS V2.3
picks a single index, reads those records that match the index and
performs the rest of the predicate checking.

- By default ORACLE compresses B-Tree indices, using both forward and
rear compression.  However when this is done, Index-Only retrieval (the
use of the information in the index as data, thereby bypassing actually
reading the data records under some circumstances) can not be used by
ORACLE to reduce I/O.  Oracle allows indices to be defined as
uncompressed, causing them to take substantially more space but permitting
Index-Only retrievals.   VAX Rdb/VMS V2.3 B-Tree indices are always
forward, or pre-fix, compressed AND allow Index-Only retrieval.

* ORACLE provides a limited auditing facility.  VAX Rdb/VMS V2.3 does
not currently provide such a facility, although much of this information
can be obtained by dumping the After-Image Journal with RMU/DUMP.

- ORACLE does not support After Image Journaling in a VAXcluster
environment.  VAX Rdb/VMS V2.3 fully supports AIJ in a VAXcluster.

< Note 82.0 by 32371::BERENSON "Rdb/VMS - Number ONE on VAX" >
                    -< ORACLE V5.1.16 vs VAX Rbdb/VMS V2.3 >-


Thank you, Hal, for an excellent comparison!

>>* ORACLE supports a global cache of database pages.  Under some
>>circumstances this can result in applications performing less I/O and
>>using less memory than the per-user cache scheme used by VAX Rdb/VMS
>>V2.3.

Just as an afterthought to global cache - if one uses VAX ACMS, one gets a kind
of global cache due to the limited number of processes doing the work for
many users, i.e., often as not, the data required by one user will still
be in the server for the next, and so on.  The tuning of an application
to take advantage of this fact is not very hard, given the ease with which
one can define servers and the routines to be run therein.


Cheers,
   John

    I'll join the chorus of thanks.
    
    And hoping that you will update this VERY interesting note
    when V3.0 is shipped.
    
    Lars