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
11th 2015. 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:
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.
Contributed by:
Paulo Ferreira
Portugal is a DBA with a decade of experience in IT and
has worked as a DBA for 8 years. He is an Oracle Certified Professional
(9i and 10g); IBM DB2 Certified (8 and 9 ?Viper?); and an Oracle 11i
Applications Database Administrator Certified Professional.
Currently, Paulo works as Senior Applications DBA for a company named
F2C Consultoria in Rio de Janeiro/Brazil. F2C serves one of Oracle
E-Business? largest clients in Brazil.