Oracle Overview
The Oracle Instance
An Oracle instance consists of:
A) five processes: system monitor, process monitor, db writer, checkpoint, and log writer.
B) data files which contain the tables and other data objects, control files which contain configuration information, redo log files for transaction processing, and archive files for recovery purposes.
C) configuration files which contains the instance attributes, and external security information
Memory Model
The Oracle memory model consists of:
A) the SGA, which contains executable program code (min 8 meg);
B) the shared pool, which is split between the library cache and data dictionary cache, optimized queries are stored here;
C) the redo log buffer, which is where transactions are stored before they are written to the redo logs;
D) the db buffer cache, which is where database operations are stored before they are written to the data files.
The Oracle dataserver runs as a multiple processes within the operating system; the number of users connected to the database is reflected in the number of processes managed by the OS. Each Oracle user connection requires 1 meg of server memory.
Transaction Processing
Transactions are written to the redo log buffer, where they advance to the redo logs, data file buffer, and data files. When a rollback occurs, block images are discarded from the redo log buffer; as the previous block data is held in the rollback segment blocks. Committed transactions are promoted to the archive logs. Archive logs are used to restore the data in the event of a hardware failure. A checkpoint operation flushes all updated (committed) memory blocks from the log buffer and database buffer pool.
Note that transaction logging is optional, on a table by table basis, and archive logging is also optional.
During an update transaction, record locking provides prevents data block collisions from occurring. Access to the "before" image of the record(s) is made available during this time, which reduces contention. This is a patented record locking mechanism.
Backup Procedures
Previous to Oracle 8, the only way to back up the database was through a "cold" backup. This involved shutting down the Oracle instance, backing up the data files, and restarting upon completion.
Oracle 8 has the Recovery Manager package which facilitates backing up data files on-line.
Recovery Procedures
Recovery is achieved by restoring the data files, and verifying the control files are synchronized properly.
Security and Account Setup
Oracle is shipped with several built-in accounts: system, internal, and sys. Operating system authentication is required in order for a login to be created with similar privileges. After a login is created, access is then granted to the tables within schemas as needed.
Database Creation
Databases are initialized with the "create database" command. In most (99.9%) cases the database name is the same as the instance name, and there is only one database per instance.
Within an Oracle instance, schemas are created which contain the tables for an application. Tables are referenced by schema_name.tablename. Each user is assigned a default schema upon creation; this schema name is the same as the user name. In order for tables to be referenced without the schema name, they must be owned by the user, or in the "system" schema. "Synonyms" can be created to bypass the prefix requirement. Typically, an "internal" or "system" level login will be used to access the database.
A typical Oracle instance will have 12 data files, 6 redo log members, 6 archive files, and 4 control files, all spread across various disk subsystems.
Data Types
Supported data types include number, char, varchar2, date, long raw, clob, and blob.
Blob and clob (Oracle 8 only) datatypes are implemented via pointers within the physical record structure ; the field contents are stored in dedicated blocks. As a result, each blob or clob field requires at least 2K of storage (depending on the database block size). Long raw datatypes are stored in-line, and are discouraged.
For string data, the varchar2 type can be used for lengths up to 2000; the clob type can be used for longer field data.
( ** blob = binary large object, clob = character large object)
Date fields are represented as a number of days, along with a decimals fraction (accurate to the minute). Additional accuracy can be obtained by purchasing a separate module.
Sequences provide a means of autoincrementing an ID column - selecting sequence_name.NEXTVAL automatically increments the sequence and returns the new value.
Storage Concepts
Tables are stored in tablespaces; a tablespace is made up of one or more data files. Although it is possible to use raw devices within Oracle, it is not recommended. Control files, rollback segments, and redo logs are all stored in separate files within the operating system.
PL-SQL
PL-SQL is a robust programming language in which stored procedures can be written. The procedures are stored in a compiled format, which allows for faster execution of code. Cursors are supported for row by row processing. Arrays are supported (using the table datatype), as well as structures (the record type). Since PL-SQL procedures cannot return a result set, returning rows to a calling application requires implementing arrays as "out" variables.
One powerful feature of PL-SQL is the ability to create custom functions which can be used within SQL statements.
Performance and scalability
Oracle has always been known for its speed and performance. Oracle 8 supports over 15,000 active user connections. The patented record locking scheme made it an attractive candidate for canned applications marketed by Peoplesoft and the like. The ability to turn transaction logging on and off at will allows Oracle squeak by competitors during benchmark analysis runs.
Coming soon: benchmarks on Solaris and Linux machines.
Price and Support
Price per seat is relatively high, compared to other vendors.
Support is achieved by opening "TAR" cases with the support team. Response is usually within 48 hours.
Management and Development Tools (for Windows)
SQL-Plus is the interactive query tool used with Oracle; it is useful for entering queries and stored procedures. Complex reporting capabilities are available for those willing to learn SQL-Plus extensions.
Oracle Enterprise ships with Enterprise Manager, a full featured front end to the intricacies of Oracle. The main areas of the system (security, storage, schemas) are managed by totally separate applications, which is cumbersome at times.
The "best of breed" product in this category is DB-Artisan by Embarcadero Technologies.
Other Oracle system management tools (like the GUI manager for Context) have a lot to be desired.
Oracle gained a reputation for poor quality development tools with the release of Oracle Forms for windows. Renamed Developer 2000, it is gaining acceptance within pure Oracle shops.
Performance Tests
Back to Main page