Conference ulysse::rdb_vms_competition

360.0. "More notes on Oracle" by DPDMAI::DAVISGB (Uh Oh...another Balloonist!) Tue May 23 1989 19:26

    Some notes on Oracle....from an Oracle experienced individual...
    
    
    
ORACLE Kernel

    75% of the ORACLE kernel (written in C) was rewritten for version 
    6.  They have incorporated a technology which makes use of 
    something called a Redolog Buffer.  Database blocks are read into 
    a large memory cache called the System Global Area (SGA).  This 
    area is a set of VMS Global Sections.  As database blocks in the 
    SGA are modified, the changed bytes are written to a Redolog 
    Buffer on disk.  The Redolog Buffer file is used to roll the 
    database forward in the event of a system failure.  Transaction 
    level rollbacks are implemented via special database blocks called 
    Rollback Segments.  The Redolog Buffers can be used to implement 
    AIJ by dedicating a tape drive to the Redolog Buffer archive 
    process.

    The main advantage of this architecture is a reduction of I/O for 
    implementing read consistency and a perceived reduction in I/O for 
    database commits.  There is no longer a separate Before Image (BI) 
    file.  Before Image data is now stored in the database as Rollback 
    Segments.  This means there is no longer a single file of before 
    image data and some of the before image data is cached in memory 
    in the form of Rollback Segments.  Commits are faster because they 
    can be deferred.  A database block can remain in memory across 
    several commits.  If the system fails, the block can be 
    reconstructed from the bytes stored in the Redolog Buffer.  
    Periodically the database writer must wake up and write "dirty" 
    blocks to disk.  This process is called syncpointing.  The idea 
    behind all of this is that during updates you write only the bytes 
    that have changed to the Redolog Buffer.  Writing of the actual 
    database blocks to disk can be deferred until a later time.

    Another advantage of this "fast commit" approach is that the 
    changed bytes for multiple update transactions can be written to 
    the Redolog with a single I/O (piggybacked).  

    There are several disadvantages to this approach however.  This 
    architecture works best when a relatively small number of database 
    blocks are being repeatedly updated.  These blocks can stay in 
    memory for an extended period of time while the smaller byte 
    packets associated with updates to those blocks can be buffered 
    and written to the Redolog Buffer.  Some of the payback comes home 
    however at syncpoint time.  Syncpointing is required because the 
    Redolog is a fixed size circular buffer.  Modified database blocks 
    must be written to disk before the Redolog Buffer wraps around 
    destroying the information required to recover changed blocks.  
    This brings up an interesting situation.  The bigger the buffer 
    is, the longer you can go without doing a syncpoint.  However, the 
    longer you go without a syncpoint, the longer that syncpoint will 
    take when you finally do it.  Also, large Redolog Buffers mean 
    increased recovery times should the system fail.  So you have this 
    big trade-off.  You can defer writing the database blocks, but the 
    cost is long syncpoint times and long recovery times. When the 
    database is large and the updates are randomly distributed most, 
    if not all, of the advantages of this architecture disappear.
 
Transaction Processing Option

    V6 also introduced the Transaction Processing System (now called 
    the Transaction Processing Option because of trademark problems).  
    The two major components of TPO are row level locking and PL/SQL. 

    Row level locking is promoted as a performance enhancement.  This 
    is debatable.  Row level locking does reduce contention by 
    increasing the granularity of locks, but at the cost of greater 
    locking overhead (we have known this for years).  As a result, V6 
    with TPO will use more CPU in order to manage the locking 
    activity.  The implementation details are kept under lock and key.  
    I do know that the Distributed Lock Manager is used to some extent 
    in managing locks in the cluster environment.  An enqueue is 
    generated when a required database block is in the SGA of another 
    machine in a cluster.  The database block must be written to disk 
    by the database writer before the requesting nodes lock request is 
    granted.

    Procedural Language SQL (PL/SQL), in my opinion, is a big joke.  
    First, it is not ANSI standard (as far as I know there is no 
    standard for procedural SQL).  As of late December, the product 
    was just moving into BETA.  The product has two components.  The 
    SQL*FORMS component comes with SQL*FORMS regardless of whether or 
    not you buy TPO.  The FORMS component allows you to perform simple 
    calculations and validation checks without having to access the 
    database.  For example, before the PL/SQL option became available 
    you had to execute a SQL statement to add two numbers and store 
    the result in a form field.  The ORACLE kernel had to parse and 
    execute this statement, returning the result to the forms 
    application.  This is very inefficient (especially in a networked 
    environment where the forms application runs on a PC or 
    workstation and the database kernel is on a remote system).  While 
    PL/SQL does improve this situation, it requires rewriting existing 
    applications and it is a nonstandard language syntax.  The second 
    component of PL/SQL allows an application to pass a block of 
    PL/SQL statements to the kernel for parsing and execution.  This 
    provides performance improvements over the submission of 
    individual statements (this is a real advantage in a networked 
    environment where the application front-end runs on one machine 
    and the database engine is on a remote machine).  This component 
    is supposed to be provided as part of the TPO.  Again, the last I 
    heard was that PL/SQL was far from ready (that does not bother 
    ORACLE, they will go ahead and sell it anyway).  This second 
    capability is not available through SQL*FORMS.  Parsing of PL/SQL 
    blocks is available through SQL code embedded in 3GLs or 
    interactive SQL procedures.

VAXcluster Environment

    Everything I heard about V6 in the VAXcluster environment was bad.  
    It was very clear that IF they could ever get it working at all, 
    the performance in a cluster would probably NEVER be as good as V5 
    (even with TPO!).  We should be all over sites that have ORACLE 
    installed on VAXclusters. 

Distributed Capabilities

    ORACLE offers what they call location transparency through the use 
    of database links which can be used in SQL statements.  Database 
    links (or handles) can be prefixed to table names in SQL 
    statements.  This allows them to join tables from multiple remote 
    databases in a view.  This view allows read only access to the 
    joined tables.  

    Network connections are established through SQL*NET which sets on 
    top of various transport layers.  SQL*NET supports:

    	DECnet
    	ORACLE Async
    	TCP/IP
    	LU6.2
    	3270 Datastream
    	etc...

    In the near future SQL*NET will support Named Pipes, NETBIOS and 
    Novell's SPX/IPX.  There are also plans to support connection to 
    IBM systems through the SNA Gateway using LU6.2.  Current 
    connection is through the Interlink box.  

    True two phase commit is still a long way out (V7?).  The V6 
    effort has put them behind in this and other areas.  Overall they 
    have very good networking capabilities except for performance and 
    2PC.

Interoperability

    They have interoperability with DB2 through there SQL*CONNECT 
    product.  Did not see too many customers using this product (it is 
    extremely expensive when you configure SQL*CONNECT, SQL*NET and 
    the Interlink hardware/software).

    They will soon have connectivity with IMS through a CONNECT 
    product for IMS.  I believe it uses CICS as the monitor on the IBM 
    side.

    There was some talk of CONNECT products for VSAM and RMS.

Referential Integrity

    V6 allows you to define constraints, but they are not enforced 
    until V6.1.  Plans for implementation of referential actions are 
    for a much later release (maybe V7).

Backup and Recovery

    The primary unit for backup and recovery in V6 of ORACLE is the 
    tablespace.  Tablespaces were called partitions in V5.  A 
    tablespace can have many files associated with it.  Table spaces 
    can be taken off-line and backed up or recovered while the rest of 
    the database continues to run.  The ability to set tablespaces 
    on-line and off-line also allows them some degree of fault 
    tolerance in that a disk drive can go bad and the entire database 
    does not have to come down (unless the disk happens to contain 
    part of the system tablespace).  The DBA simply sets the 
    tablespace which is using the problem drive off-line.  
    Applications which do not use that tablespace can continue running 
    while the problem partition is restored and brought back on-line.  
    This capability is very nice.  On the other hand, as far as I know 
    they do not have the ability to do incremental backups.  If a 
    tablespace is very large, it can take a very long time to backup.  
    Also, a single backup process can not use more than one tape drive 
    at a time.

Tools and Applications

    SQL*FORMS

    	SQL*FORMS is ORACLE's flagship application development 
        product.  It is a nice product, but it is difficult to learn 
        and awkward to use.  SQL*FORMS is targeted for application 
        developers.  They refer to it as a 4th generation environment 
        (4GE) to distinguish it from a 4th generation language.  It is 
        used to develop complete forms based applications.  The 
        SQL*FORMS development environment is itself a SQL*FORMS 
        application and is menu driven.  The first thing you notice is 
        that the product is very awkward to use.  It is function key 
        driven and many of the function key assignments are strange.  
        This is a problem with ORACLE products in general.  They must 
        adapt their tools and the database to multiple hardware 
        environments causing them to stick to a generic set of 
        capabilities.  Another example of this kind of limitation is 
        that they make use of only a subset of video attributes for 
        fields on a form.

    	Default applications are easy to create with SQL*FORMS.  A 
        default application consists of a form which has one or more 
        pages with one or more blocks.  The blocks are mapped to 
        database tables.  A default form has rudimentary capabilities 
        which allow an operator (using a completely different set of 
        function key assignments) to query and update the database.  A 
        default form with two blocks (a master/detail screen which 
        maps to two tables for example) can be created in less than 5 
        minutes - provided that the tables have already been defined 
        in the database.  

    	This ability to rapidly build default form applications is 
        very impressive, but creating fully functional forms based 
        applications is much more difficult.  Movement between blocks 
        and fields within blocks (block coordination) is done through 
        SQL*FORMS triggers which call SQL*FORMS macros.  Block 
        coordination is not generated as part of the default form 
        creation process.  In addition to SQL*FORMS MACROS, triggers 
        can contain SQL (or PL/SQL) statements.  These statements are 
        used to perform calculations, validations of input against 
        database tables, etc... Learning to code and coordinate the 
        execution of these triggers is a non-trivial task!  Triggers 
        can be at the form level, at the block level and at the field 
        level.  There are also several types of triggers including 
        key-triggers, pre-field triggers, post-field triggers, 
        post-change triggers, pre-form triggers, etc...  Firing order 
        for triggers depends on the type of trigger, at what level the 
        trigger is coded, etc... Furthermore, the current product does 
        not allow you to store triggers in a library for indirect 
        reference by other forms.  This limitation also applies to 
        forms in general.  It is very difficult for forms developers 
        to share form definitions.  It currently has no way to create 
        popup windows or pulldown menus.  A future version of this 
        product will eliminate most of these problems.  Also note that 
        it does not currently have bitmapped graphics support.

    SQL*REPORTWRITER

    	Very nice menu/form based report writer product.  Can create 
        almost any type of report.  The biggest problem with it seems 
        to be performance, but that should improve.  We need something 
        like this badly.  It combines SQL requests with report layout 
        and template information.  The actual report definitions are 
        stored in the database.  One problem I see is that template 
        information and queries are not shareable between application 
        developers (ie. it would be nice to share common information 
        like standard report headers).

    SQL*QMX

    	Nice end user tool based on IBM's Query By Forms (QBF).  Menu 
        driven and simple to use.  People who like QBF will like this 
        product, but there are better products on the market.  I think 
        some very nice front-ends to SQL Services could make this 
        product look sick.

    EASY*SQL

    	A dead or dying product - at least on the PC.  The VAX product 
        sells quite well.  The product manager for EASY*SQL is also 
        the product manager for SQL*CALC, SQL*GRAPH and 
        SQL*REPORTWRITER.  Only SQL*REPORTWRITER has significant 
        funding.  The others are dying on the vine.

    SQL*LOADER

    	Used to bulk load data into the database.  Can understand 
        IBM's bulk loader syntax.  Can load multiple relations at the 
        same time.  We need a facility like this.  It is very useful 
        for conversions and mass updates from external sources.

    DBXL/QUICKSILVER

    	Provides DBASE programming interface to an ORACLE database in 
        the PC environment.

    ORACLE MAIL

    	ORACLE MAIL is a very strategic product for ORACLE.  It uses 
        the database for storing electronic mail messages.  Because 
        messages are stored in the database and the database runs on 
        so many platforms, ORACLE has a good message.  They say they 
        can integrate corporate data via the ORACLE database and now 
        they can integrate mail using that same database.

    ORACLE FINANCIALS

    	There are eight software packages that comprise ORACLE 
        FINANCIALS.  They talk to customers as if all eight packages 
        are ready to go.  As of Jan. 1 only two of the eight were 
        production (I believe they were General Ledger and Accounts 
        Payable).  The software is based on ORACLE's internal 
        accounting software.  The software development effort was 
        headed by Jeff Walker (ORACLE's chief financial officer 
        formerly with Walker International).

    	The biggest problem with this software is its cost and its 
        competition.  If you configure all eight pieces on a MicroVAX 
        the cost can approach 250K without the ORACLE kernel.  A group 
        of financial application software specialists and salesman are 
        being trained to market the software.  I think the whole thing 
        is a big ego trip for Jeff Walker and is headed for trouble.  
        Sales of the package have been slow in coming.

    ADD-IN for LOTUS 123

    	This is a nice interface to the database for users who are 
        familiar with Lotus 123.  The problem with this and all of 
        ORACLE's PC based products is price and PC resource 
        requirements.  Most of the PC based products are not very 
        useful unless you have 1.5-2.0 MB of memory.


    
    

    The last time I checked with my V6 customer, Oracle V6/TPO had not
    been shipped with cluster support.  How do you know that V6/TPO
    does enqueues ???  Have you seen V6/TPO run in a VAXcluster where
    the Oracle database is mounted in SHARED mode ??
    
    Another important point is that IF the rollback segment fills, the
    application programs will most likely abort and the database will
    shutdown.  At least that was my experience last December during
    our benchmarks.  Also, depending on the application and the size
    of the rollback segment and/or redo log files, recovery after a
    system (or database) crash can take a lot of time.  For example,
    during our benchmark, a rollback segment filled, the database shutdown,
    four batch programs aborted.  When we tried to restart the database,
    recovery took TWO (2) hours !!!
    
    Re: Oracle's connect product for IMS/DB.  It is my understanding
    that this product is quite far out with nothing close till the end
    of this calendar year.