The Nova Equity Clearing and Settlements System, that is currently in operation at SG runs on an Oracle V7 Relational Database Management System (RDBMS). The structure of the Nova database was originally designed to be database platform independent. This has resulted in producing a database, which does not use many of Oracle's key and best features and has resulted in a database that is not as efficient as it should be.

The following document will compare the current Nova Oracle database design, with the design of the Swan Oracle database.

Oracle Versions

Both the Nova and Swan databases currently run on the same major version of Oracle - version 7.3.4. The Nova database is currently being developed to run on Oracle version 8.1.7

Oracle CHAR and VARCHAR2 Data Types

One of the major flaws in the Nova database structure is its use of the CHAR data type, I believe this occurred due to its original design specification of being database platform independent. The CHAR data type definition has been used throughout the database where it would have been far more efficient to use Oracle's VARCHAR2 data type.

The difference between CHAR and VARCHAR2, is that, a 40 character long CHAR field, uses 40 bytes to store the data in the database, and a VARCHAR2 uses a maximum of 40 bytes to store a field. All CHAR fields are space filled up to the specified size, therefore any variable length fields, which use the CHAR data type are inefficient.

To highlight the inefficiency of using a CHAR data type instead of a VARCHAR2 within the Nova database here is one example of wasted space in a Nova Crest table.

Table = T_EVENT

Column Name = TEXT

Column Data Type = char(100) or 100 Bytes

Typical Data = 18/12/96 17:26:33 Cancelled, Deleted

Data Size = 35 Bytes

Wasted Bytes = 65 Bytes

Total Rows = 16,911,167

Total Bytes = 2,621,440,000

Total Wasted Bytes = 1,099,225,855 (65 Bytes * 16911167 Total Rows)

As you can see in this one table there is over 1GB of wasted space, throughout the database I would guestimate that a total of 10% or 2GB's of the database is wasted space.

When analysing the Swan database, it was immediately evident that the database was designed specifically for Oracle. The Swan database only uses the CHAR field for 1 CHAR not null fields, and all other character fields are VARCHAR2.

Table Space Separation

Both Nova and Swan physically store their tables in specific table spaces. Nova has divided their tables into separate table spaces by functionality, in other words all the trade related tables are in the TRDTBLSPC table space and all the settlement related tables are in STTTBLSPC table space, etc. This approach does not have any detrimental effect on the performance of the database but does give the DBA additional administration monitoring and re-sizing all the table spaces continuously. Swan have taken a more practical approach to this and have separated all the tables into static and transactional table spaces. This means that only two table spaces have to be monitored the transactional data and transactional index.

Users, Roles and Security

Both systems use oracle roles granted to users to provide system and database privileges. The Nova system only supplied one role, the normal user role. I had to develop and implement a read only role for read only users. When checking Swan I identified that there are already two separate roles, one for updating and for read only, this is a good start but would need further investigation to ensure they are maintained correctly.

The Nova system also suffers with a security issue, in that, due to the way the Nexus and the trade API processes operate, they require matching user-ids and passwords. This is potentially a major security loophole. Initial investigation on the Swan system also highlighted a couple of security problems, the main one, being able to get into the database with DBA privileges and therefore being able to see and update anything and everything. This maybe because the database that we have been given for evaluation has not been fully configured for security.

Oracle Data Block Size

Nova is currently configured to run using a 4K block size, this is currently sufficient but the database would benefit by using an 8K block, especially when the increased workload hits the database with the agency business. Unfortunately this is not an easy task and involves a complete database rebuild, which maybe possible as part of the Oracle 8 migration.

Swan is already configured using the optimal 8K block size.

Indexes, Constraints and Referential Integrity, primary and foreign keys.

Indexes, constraints and referential integrity initially on Nova was very poor. The Nova database was supplied with very few and inadequate indexes. Throughout the last six months both Bernard Gabriel and I have had to constantly monitor the existing indexes and build new indexes to enhance performance. Many of the Nexus processes, which perform the majority of the processing within the database, were totally un-tuned. After a full scale analysis of Nexus by myself, this has now been rectified.

The Nova database does not use the concept of foreign key constraints to maintain integrity within the database. The integrity is controlled primarily by the application, which although does not cause a problem, only provides one level of integrity.

Database integrity within Swan on the other hand, is excellent. Primary and foreign key constraints exist on the majority of tables and virtually every field on each table has a specific constraint. This implies that all the integrity is performed at the database level, meaning updated code can not inadvertently corrupt the database.

Rollback Segments, Redo Logs and Archive Logs

All the rollback segments and redo logs are specifically configured for each database, in the case of Nova we have configured the rollback segments and redo logs to perform optimally with the current number of users and transactions being performed. This task would have to be specifically performed for the Swan database when it is initially configured. For test purposes the current configuration of the Swan rollback segments and redo logs is perfectly sufficient.

Object Storage Parameters

Storage parameters in an Oracle database are very important, as they determine how the database objects grow, and therefore how often the database has to be optimised (a very lengthy and risky task). The designers of the Nova database had very little concern for the storage and growth of objects within the database. The Nova designers did not work to any standard when creating database objects, which lead to a very un-structured database. Over the last year the majority of these design faults have been rectified by Bernie and I, we have worked to the Oracle Flexible Architecture (OFA) recommended standards, and organised nearly all the objects storage parameters.

The Swan database was obviously designed using the OFA standards. All their objects are created using initial and next extents size dividable by 8K blocks and the percent increase value is set to zero, for every object. The maximum extents size is only set to 99 which could be set a lot higher in version 7.3.4, although a well maintained database would never have objects which have extended 99 times.

Functions, Stored Procedures and Packages

Nova has only recently started to make use, of one of oracles most useful features stored procedures and packages. Most of Nova's database code is stored in functions. This is not a real problem, but does produce a slight security risk in that any user with sql*plus access can probe the database and see what each of the functions perform. Also most of the processing performed by the Nova system is embedded in the Nova GUI, in some cases this processing could be performed more efficiently using oracle's stored procedures and packages (groups of related stored procedures stored together in a package).

The Swan system appears to use stored procedures and packages for the majority of its processing. If this is the case I would expect the system to perform very efficiently. Further investigation is required to prove this theory.

Oracle Sequences

Nova does not use Oracle sequences, instead it uses a reference table which contains a sequence number for all the corresponding processes. This is a quite a major concern as the number of transactions increase the table (tsysnxtid) which maintains these sequences will become a bottleneck on the system.

Swan uses a number of Oracle sequences, to maintain counters and unique sequence ids. This is the most efficient method within Oracle.

Oracle Locking

Nova does not rely on Oracle to maintain table and record locking on the database. Instead it uses a set of tables. Each time a record is being modified within the database an entry is put on one of the locking tables. The record can not be modified by any other users, until the record is deleted from the locking table. This is a very unreliable and inefficient method of controlling locking within an Oracle database. This approach was probably adopted because of the initial design being database independent, but could become another major bottleneck in the database as transactions increase.

From initial analysis of Swan, it appears that Swan uses only Oracle's standard locking functionality. Oracle has obviously a tried and tested locking mechanism, which can handle extremely large quantities of transactions.

Conclusion

Nova's major flaw was that it was initially designed to be database platform independent. This has resulted in a database that does not use a number of Oracle's key features and bypasses them with an inefficient equivalent, see Oracle Locking for example. The Nova database that is currently in production at Societe Genrale, has been tuned immensely, from the database that was supplied by ACT. It is currently performing adequately, but would benefit from a number of improvements. ACT over the last year have been made aware of the majority of the problems which currently exist within the database, but due to time and effort constraints have been unable to, as yet, rectify these problems. Nova currently contains several potential major design faults, which could become, real issues as the volume of transactions increase. Over the next few weeks, we will be benchmarking the Nova system with the anticipated volumes required for agency business (approx. 20,000 trades a day). This will be a very useful and interesting exercise and will prove Nova's capabilities.

From the initial analysis of the Swan database, it is obvious, that it was designed specifically for Oracle and uses most of Oracle's best features. It has been designed using the Oracle Flexible Architecture Standard and is technically well structured. The database in its current state would only need a small amount of fine tuning on the redo logs, rollback segments and archive logs, to run in our environment. A full benchmarking exercise should be performed to ensure it can handle the volumes we expect to be running with in production, but I would be surprised if it did not perform well.

Appendix D - Client Satisfaction Policy

Client satisfaction with the quality of our Professional Services is very important to us. Satisfied clients are key to the future of our business.

If at any time you are not satisfied with the services provided, or you have a suggestion for improvement, please let us know and we will follow up on it. You should refer these items in writing to the Services Manager. We undertake to respond to you within two working days either with a response or an acknowledgment telling you how long our follow up is likely to take and who has been assigned responsibility for it.

The Services Manager

VPIS Ltd

18 Golding Thoroughfare

Chelmsford

Essex

CM2 6TU

EMail: services@vpisltd.co.uk

The Services Manager will acknowledge your letter and initiate the necessary action to resolve the matter.

Appendix E - Client Survey Form

CLIENT SURVEY FORM
Date:		Client:
Project Code:		Completed By:

VPIS Ltd is constantly striving to improve the quality of service to its clients. Your opinion is of great value to us so we should be grateful if you would take the time to complete this survey form and return it to us in the pre-paid envelope provided.

For each question below please indicate the response that best matches your assessment of our service using the following key. There is space at the end for comments.

E	Exceeded your expectations	M	Met your expectations
F	Failed to meet your expectations	NA	Not applicable to this review

Did the results of this review meet your needs?
E M F NA

How accurately did our proposal set your expectations for duration and deliverables?
E M F NA

Do you consider that we had the appropriate skills and background for this review?
E M F NA

What is your assessment of the quality of our deliverables?
E M F NA

Did we meet your expectations in terms of time and budget?
E M F NA

Use the following space for any comments you wish to make

Would you use VPIS Ltd services again for future engagements?
YES NO

Would you recommend VPIS Ltd services to others?
YES NO

Would you be willing to act as a reference for VPIS Ltd services?
YES NO

What is the most important thing VPIS Ltd services can do to improve the quality of its professional services?

We appreciate the opportunity to be of service to your organisation and hope to work with you again in the future. Thank you for helping VPIS Ltd services to improve the quality of its services. Please return this form in the pre-paid envelope provided.