Hybrid Approach

Some applications, such as those marketed by Peoplesoft, use a hybrid of the previous two approaches. They store the password to a generic application ID in an encrypted application table. End user IDs are used only to connect and have read access only to the table with the encrypted password. The application then decrypts the password and uses the generic ID to service the application.

The advantage to this method is that it is harder for end users to directly manipulate application tables through SQL*Plus or other tools. For most users, it means that application data must be manipulated through the application. It also places faith in the encryption mechanism used by the application because the password is essentially being stored somewhere.

I usually recommend using either DBMS-enforced security or a hybrid approach. Application-enforced security is usually too lax for comfort.

Oracle8 Auditing Features

For sensitive OLTP applications, end users may have auditing requirements. Oracle provides some auditing features, but often Oracle’s audit trail does not contain what end users want it to contain. Using Oracle’s auditing features, we can track which SQL statements end users issue and which type of operation was attempted (insert, update, and so on). However, Oracle auditing does not record which rows were affected.

If there is a question about somebody’s actions, often there are questions about which rows and tables were affected. Oracle8 auditing features cannot provide this level of detail. Most developers will custom-write auditing capabilities for OLTP applications for this reason.

If you do use Oracle’s auditing capabilities, target the auditing to selected operations and tables. Also remember to purge audit data after a while.

OLTP Administration and Maintenance Considerations

This section will address various administration and maintenance considerations for large OLTP applications, such as backup and recovery, batch processing, and performance problems to expect over time.

Backups and Recovery Considerations

Take hot backups when batch processing isn’t running. For many OLTP systems, this means taking the hot backup during the day. The primary reason is that updates, inserts, and deletes are logged. Frequently, the number of writes, and thus the number of redos, is higher during batch processing than at any other time. During hot backups, Oracle logs the entire block before each change is made, and the number of archive files is greatly inflated. So you should look for a time during the day where writes are minimized.

I always recommend that recoveries be periodically tested. Data loss for large OLTP applications tends to be expensive to replace manually due to labor costs. In fact, if recoveries are periodically tested, you can provide effective time estimates in the event that a recovery of the production environment is needed. If the database is large, you may need to tune the backup and recovery process as well as the application.

Even though cold backups are not theoretically necessary, I usually recommend doing one every week. The database can be opened and used without any archive files if need be. In applications with high availability requirements, however, it may be difficult to find time to do a cold backup.

Batch Data Loading

If possible, remove indexes before performing loads on tables. It is more efficient to load a table without indexes and then build that table afterwards than it is to load tables with indexes in place. Also, use SQL*Loader whenever possible. SQL*Loader’s ability to bypass the database engine makes it faster than conventional path loads. If for some reason you must use a custom-written insert program, use array processing with a 3rd- generation language like C or COBOL.

Object Fragmentation

You may recall that objects (tables, indexes, and clusters) have INITIAL and NEXT extent specifications in their storage clause. Objects that have many extents (or are fragmented) are less efficient than objects that are contained within contiguous space because there’s more head contention. Furthermore, when managing fragmented objects, Oracle often has to issue its own SQL statements (also known as recursive calls) against system tables to keep track of where all the fragments are. Recursive calls of this sort contribute to CPU overhead, which we don’t want in large OLTP applications.

Fragmentation Detection

Objects that are fragmented can be listed by issuing the following SQL statement:

Select owner, segment_name, segment_type, extents

From SYS.DBA_SEGMENTS

Where owner not in (‘SYS’,’SYSTEM’) and extents >= 2

Order by 4 desc;

The only way to correct fragmentation is to drop and re-create the fragmented objects with more space allocated in their first extents.

Periodic Maintenance

If you are using cost-based optimization, you must gather statistics periodically by using the ANALYZE command. ANALYZE should be run periodically for all tables and indexes not owned by SYS and SYSTEM. An example of gathering statistics for a table and index is presented in Listing 31.3.

Listing 31.3. Illustration of the analyze command.

   Analyze table fred estimate statistics;

   Analyze index fred_pk estimate statistics;

With ANALYZE, you have a choice of estimating statistics or computing them. Estimating statistics means projecting them using a sample of pages from the object being analyzed. Computing statistics means literally calculating them with information obtained from every block in the object. On large objects, this will take a long time and a lot of temporary tablespace. Usually, estimating statistics is good enough.

Because inserts and deletes often degrade performance of indexes over time, I periodically rebuild all indexes. Oracle7.3 and later make rebuilding indexes very easy with the alter index rebuild syntax. Listing 31.4 illustrates the rebuilding of an index with the rebuild syntax. It is interesting to note that with this syntax, you don’t need to be concerned about referential integrity issues. It used to be that if an index was used for a primary key, you had to disable the primary key and all related foreign keys to rebuild the index. The rebuild syntax doesn’t require this.

Listing 31.4. Illustration of the alter index rebuild command.

   Alter index fred_pk rebuild;

OLTP Implementation Issues

Large OLTP applications have a variety of implementation issues that are not like implementation issues associated with other types of applications.