Background of Oracle Database

Oracle has a 3 decade history, outlasting many of its predecessors.  This brief summary traces the evolution of Oracle from its initial inception to its current status as the world moist flexible and robust database management system.

Founded in August 1977 by Larry Ellison, Bob Miner, Ed Oates and Bruce Scott, Oracle was initially named after "Project Oracle" a project for one of their clients, the C.I.A, and the company that developed Oracle was dubbed "Systems Development Labs", or SDL.   Although they may not have realized it at the time, these four men would change the history of database management forever.

In 1978 SDL was renamed Relational Software Inc (RSI) to market their new database.

1979 - Oracle release 2

The first commercial RDBMS was built using PDP-11 assembler language. Although they created a commercial version of RDBMS in 1977, it wasn't available for sale until 1979 with the launch of Oracle version 2. The company decided against starting with version 1 because they were afraid that the term "version 1" might be viewed negatively in the marketplace.  USA Air Force and then CIA were the first customers to use Oracle 2.

In 1982 there was another change of the company’s name, from RSI to Oracle Systems Corporation so as to match its popular database name.  The current company name comes from a CIA project that Larry Ellison had previously worked on code named “Oracle”.

1983 - Oracle release 3

The Oracle version 3 was developed in 1983. This version was assembled using C programming language and could run in mainframes, minicomputers, and PCs – or any hardware with a C compiler. It supported the execution of SQL statements and transactions. This version also included new options of pre-join data to increase Oracle optimization.

1984 - Oracle release 4

Despite the advances introduced in version 3, demand was so great that Oracle was compelled to improve the software even further with the release of version 4 in 1984. Oracle version 4 included support for reading consistency, which made it much faster than any previous version.  Oracle version 4 also brought us the introduction of the export/import utilities and the report writer, which allows one the ability to create a report based on a query.

1985 - Oracle release 5

With the introduction of version 5 in 1985, Oracle addressed the increasing use of the internet in business computing.  This version was equipped with the capability to connect clients’ software through a network to a database server.  The Clustering Technology was introduced in this version as well and Oracle became the pioneer using this new concept – which would later be known as Oracle Real Application Cluster in version 9i.  Oracle version 5 added some new security features such as auditing, which would help determine who and when someone accessed the database.

Oracle version 5.1 was launched in 1986 and allowed for supporting distributed queries. Later that same year Oracle released SQL*Plus, a tool that offers ad hoc data access and report writing.  1986 also brought the release of SQL*Forms, an application generator and runtime system with facilities for simple application deployment.

1988 - Oracle release 6

The PL/SQL language came with Oracle version 6 in 1988.  This version provided a host of new features including the support of OLTP high-speed systems, hot backup capability and row level locking – which locks only the row or rows being used during a writing operation, rather than locking an entire table.  Prior to the hot backup feature, database administrators were required to shutdown the database to back it up.  Once the hot backup feature was introduced, DBA’s could do a backup while the database was still online.

Oracle Parallel Server was introduced in Oracle version 6.2 and was used with DEC VAX Cluster. This new feature provided high availability because more than one node (server) could access the data in database. With the increased availability this feature also accelerated the performance of the system that was sharing users’ connections between nodes.

1992 - Oracle release 7

1992 was a memorable year for Oracle.  The company announced Oracle version 7, which was the culmination of four years of hard work and two years of customer testing before release to market.  This version of Oracle provided a vast array of new features  and capabilities in areas such as security, administration, development, and performance.  Oracle 7 also addressed security concerns by providing full control of who, when, and what users were doing in the database. Version 7 also allowed us to monitor every command, the use of privileges and the user’s access to a particular item. With Oracle 7 users could use stored procedures and had triggers to enforce business-rules. Roles were created at this version to make the security maintenance easier for users and privileges. The two-phase commit was added to support distributed transactions.

Oracle7 Release 7.1 introduced some good new capabilities for database administrators, such as parallel recovery and read-only tablespaces. For the application developments, Oracle inserted the dynamic SQL, user-defined SQL functions and multiple same-type triggers. The first 64-bit DBMS was introduced within this version as well as the VLM (Very Large Memory) option.  The feature Oracle Parallel Query could make some complex queries run 5 to 20 times faster.

In 1996 Oracle 7.3 was shipped, offering customers the ability to manage all kinds of data types; including video, color images, sounds and spatial data.  1996 also brought the release of Oracle's first biometric authentication for a commercially available database.  This technology could analyze human characteristics, both physical and behavioral, for purposes of authentication.

1997 - Oracle release 8

The Oracle 8 Database was launched in 1997 and was designed to work with Oracle's network computer (NC). This version supported Java, HTML and OLTP. 

1998 - Oracle release 8i

Just one year later Oracle released Oracle 8i which was the first database to support Web technologies such as Java and HTTP.  In 2000 Oracle 8i Parallel Server was working with Linux which eliminated costly downtime.

2001 - Oracle release 9i

Oracle Real Application Cluster came with Oracle 9i Database in 2001. This feature provides software for clustering and high availability in Oracle database environments.  Supporting native XML was also a new feature of Oracle 9i and this was the first relational database to have these characteristics. Version 9i release 2 enabled Oracle to integrate relational and multidimensional database. Despite the fact that hard disks were becoming  cheaper, data was increasing very quickly in databases and Oracle 9i came with a special technology named table compression that reduced  the size of tables by  3 to 10 times  and increased the performance  when accessing those tables.

2003 - Oracle release 10g

Although Oracle 9i had only been in the market for two years, Oracle launched version 10g in 2003.  The release of 10g brought us the introduction to Grid Computing technology.  Data centers could now share hardware resources, thus lowering the cost of computing infrastructure.  10g was also the first Oracle version to support 64-bit on Linux.  With Oracle Database 10g and Real Application Cluster it was now possible to move from very expensive SMP boxes and mainframes to an infrastructure that relies on low costs such as UNIX or Windows servers, which have high availability, scalability and performance.

Oracle has long strived to make their software products available through the internet; but this effort was only enhanced with the creation of the 10g Express Edition.  With the introduction of the 10g Express Edition in 2005, Oracle gave small business and startup corporations a viable option to integrate Oracle into the workplace at no cost. 

2007 - Oracle release 11g

The latest version of Oracle Database is 11g which was released on July 11^th 2007. This version introduced more features than any other in Oracle history.  This version includes:

• Oracle Database Replay, a tool that captures SQL statements and lets you replay them all in another database to test the changes before you actually apply then on a production database;
• Transaction Management using Log Miner and Flashback Data Archive to get DML statements from redo log files;
• Virtual Column Partitioning;
• Case sensitive passwords;
• Online Patching;
• Parallel Backups on same file using RMAN and many others.

Oracle is known for growth and change, which is why it is important to continually study its history and previous lessons learned while embracing new features and functionality. Throughout its history Oracle has acquired Database and Software Applications companies in order to provide more complete solutions to its customers and increase the credibility of its products.  Today Oracle has more than 320,000 customers and is present in 145 countries making it one of the elite companies in its field.

The History of Oracle tuning techniques

To fully appreciate the enhancements within Oracle 11g it’s important to take a historical perspective and understand the context of the enhancements. Performance tuning has not changed much since Oracle7, but there are a wealth of new tools and techniques with each new release.  (See Appendix A for a full list of tuning features by release)

Oracle 7 tuning tools

In Oracle 7, Oracle Education (later Oracle University) advocated “instance tuning”, an approach whereby the database is tuned as a whole by optimizing the critical init.ora parameters, CBO statistics and object parameters.  We also saw these specific tuning features:

•  Bitmap Indexes
• Partitioned Views
• Sequential prefetch for full table scans (asynchronous read ahead)
• alter index xx rebuild syntax
• Advanced replication

Oracle7 was in the days when a 300 megabyte SGA was considered large, and DBA’s tuned to very simple rules.  The Oracle Education DBA tuning course recommended simple ratio-based tuning, and almost all Oracle databases were I/O-bound, largely due to tiny RAM buffers.

Oracle 8 tuning tools

Oracle 8 was originally dubbed “Oracle Universal Server” (OUS) a relational database with object-oriented features.  Incidentally, despite the great quality of Oracle implementation of OODBMS, the object-oriented features never caught-on expect within Oracle Corporation itself.  Oracle 8 improved upon many of the Oracle 7 tuning features (some of which were too buggy for production) and gave us these meager new tuning features:

• Table and Index partitioning enhancements
•  reverse key indexes
• updatable views
• Oracle parallel query for DML (insert, update, delete)

In Oracle 8, the tuning professional received divide and conquer tools that allowed the DBA to perform maintenance activities in parallel.

Oracle 8i tuning tools

Dubbed “The Internet enabled database”, Oracle 8i concentrated on enhancements to existing tuning features:

• Functional based indexes (FBI)
• KEEP pool replaces “alter table xxx cache” syntax.
• Hash and Composite partitioning
• SQL*Loader direct load API (direct=y)
• Online index rebuilds
• Cache Fusion  added to Oracle Parallel Server (OPS)
• The Oracle HTTP server allows faster Apache extensions
• Oracle JVM Accelerator

In Oracle 8i, the DBA received STATSPACK, and the whole landscape of Oracle tuning changed.  Now able to perform historical analysis, the DBA was able to apply scientific methods to analyze historical performance information.

Oracle 9i tuning tools

Oracle 9i saw major improvements in performance tuning tools, most notably STATSPACK, an improvement on the old BSTAT-ESAT utility with allowed for elapsed-time reports to be stored within the database.  Oracle also introduced “advisory” utilities, tools which showed the marginal benefits for different SGA pool sizes.  We also saw these important new tuning features:

• Multiple blocksize support
• 64-bit Oracle allows very large SGA sizes
• Separate data buffers allows for segregation of critical tables & indexes
• Oracle upsert statement improves warehouse loads
• SQL case statement replaces decode syntax
• Oracle external tables allow SQL access to flat files
• Real Application Clusters (RAC) replaces OPS
• List Partitioning and multi-level list-hash partitioning

In Oracle 9i, the major tuning advancements death with the management of very-large data buffers (from 64-bit Oracle releases) and the ability to segregate critical tables and indexes into separate data buffers.

Oracle 10g tuning tools

Oracle 10g was a major improvement in all areas of Oracle performance.  The optimizer undertook a major overhaul and the entire kernel was improved and PL/SQL performance was improved by over 20%.  As the “Grid” database, Oracle re-defined the traditional definition of grid computing, allowing for blade servers to be genned-in as needed to accommodate changes in the workload.  We also saw several important tuning features, most importantly the Automated Workload Repository (AWR) and the Automatic Session History (ASH) tables:

• Oracle 10g Grid – RAC is enhanced for dynamic scalability with server blades
• Completely reworked 10g Enterprise Manager (OEM)
• AWR and ASH tables incorporated into OEM Performance Pack and Diagnostic Pack options
• Automated Session History (ASH) materializes the Oracle Wait Interface over time
• Automatic Database Diagnostic Monitor (ADDM)
• Automatic Workload Repository (AWR) enhances STATSPACK
• SQLTuning Advisor
• SQLAccess Advisor

Oracle 10g is the age of AWR and ASH, Oracle’s codification of their tuning approaches, and a goldmine for the Oracle professional who wanted to analyze performance information. 

In Oracle 10g release 2, we saw another major tuning improvement, mutexes.  To improve cursor execution and also hard parsing, a new memory serialization mechanism has been created in 10gR2. For certain shared-cursor related operations, mutexes are used as a replacement for library cache latches and library cache pins. Using mutexes is faster, uses less CPU and also allows significantly improved concurrency over the existing latch mechanism. The use of mutexes for cursor pins can be enabled by setting the init.ora parameter _use_kks_mutex to TRUE.

Oracle 11g tuning tools

Oracle 11g was not a revolutionary release for Oracle tuning, but it concentrated mostly upon improvements to existing features and the introduction of semi-automated tools to replicate the steps taken by a human tuning expert.  Here is a list of the major performance tuning new features of Oracle 11g:

• SQL Performance Analyzer – This is the codification of Oracle’s “holistic” approach, an empirical technique whereby SQL is tuned in a real-world environment.  Designed to testing silver bullet impact of global changes (init.ora parameters, new indexes and materialized views, &c), SPA provides real-world evidence of the performance impact of major changes.
• 11g Compression – with a late start out of the gate (other DBMS tools have had compression for decades), Oracle’s compression promises to improve the speed of full-scans operations (important to batch jobs and data warehouses).
• SQL optimization improvements – The cost-based optimizer (CBO) is continuously evolving, and we now see bind peeking fixed and extended optimizer statistics.
• Automatic Memory Tuning - Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes.
• AWR Baselines - The AWR baselines of 10g have been extended to allow automatic creation of baselines for use in other features. 
• Adaptive Metric Baselines - Notification thresholds in 10g were based on a fixed point. In 11g, notification thresholds can be associated with a baseline, so the notification thresholds vary throughout the day in line with the baseline.