Introduction
The intent of this article is to provide a general overview of the various performance enhancements contained in Oracle 8.04. It will focus on those enhancements that require some level of DBA or
developer interaction to initiate or configure. Those enhancements not requiring some form of interaction will not be discussed. Rather than describe a laundry list of performance enhancements
contained in Oracle 8.04, this article will focus on those techniques that have been proven to provide immediate performance gains. Because of space limitations, I am unable to provide in-depth
details on each of the performance enhancements that will be covered in this article.
VERSION 8 Enhancements
Partitioning
One of the new features of Oracle8 allows you to divide the storage of tables and indexes into smaller units called partitions. The partitioning of data into smaller subsets allows users to
overcome many of the problems that large tables and indexes create. Oracle8 allows administrators to store each partition in a separate tablespace. Partitioning of data into separate tablespaces
provides the following performance advantages:
- Load balancing – partitioning allows you to distribute data and balance the I/O load across several devices
- Parallel query – the Oracle8 optimizer is partition aware and will create query plans that access only those partitions needed to satisfy the query’s request
- Bulk data loads – SQL*Loader supports concurrent loading of individual partitions and entire partitioned tables
- Individual query performance – partition extended table names allow application programs to refer to individual partitions of a table as if they were individual tables. Accessing a single
partition (as opposed to the entire table) can dramatically reduce processing costs
When defining a partitioned table in Oracle8 you code a partition specification for the table that includes a key-based, table-level algorithm that is used to map rows to specific partitions and a
partition description that describes each partition. Oracle8 supports a single partitioning algorithm called range partitioning. Rows are placed in the separate partitions based on a partitioning
key. A partitioning key is a column or set of columns (up to 16) that determine how the data is divided among the partitions. With range partitioning, the best partition keys are dates, primary
keys or foreign key columns. Oracle8 does not allow users to update partitioning key columns if that update changes the partition the row is stored in. To change a value in one of these columns,
the application program must delete the row and then reinsert it with the new values. To define a partition, a non-inclusive upper boundary must be hard-coded into the table’s definition. These
upper boundaries should distribute the data (more or less) evenly among the different partitions.
Index Partitioning
Oracle8 also allows you to create partitioned indexes. Oracle uses the same range partitioning algorithm as it does for tables. Like partitioned tables, Oracle8 places rows in the separate index
partitions based on the index’s partitioning key. The partitioning key of an index must include one or more of the columns that define the index. This allows you to create an index that contains
multiple columns but partitions the index on a subset of those columns. An index partition is defined exactly like its partitioned table counterpart; a non-inclusive upper boundary must be
hard-coded into the index’s definition.
Equi-Partitioned Objects
An index and table having the same number of partitions are said to be equi-partitioned. Multiple tables and multiple indexes can be equi-partitioned if they meet Oracle8’s equi-partition
specifications. Please refer to the Oracle8 Server Concepts manual for a more complete listing of equi-partitioning specifications.
Equi-partitioned objects improve application performance by reducing the number of rows being sorted and joined. The Oracle8 optimizer is partition aware meaning that it is capable of creating
query plans that access a single index and table partition.
Local Indexes
You can ensure that a table and its associated index are equi-partitioned by specifying the LOCAL parameter during index creation. All keys in a local index partition point to rows stored in a
single table partition. When creating an index using the LOCAL parameter, range specifications and a maximum value do not need to be specified. Oracle8 automatically partitions the local index on
the same partitioning key columns as the table it references, creates the same number of partitions and gives each index partition the same partitioning key boundaries.
Global Indexes
Unlike their local index counterparts, index keys in a global index may point to rows in more than one table partition. To create a global index, you specify the GLOBAL parameter (default) during
index creation. Global indexes can be equi-partitioned with their tables that they reference, but Oracle8 will not take advantage of equi-partitioning when generating query plans or executing
maintenance operations. The entire index will be affected! To take advantage of Oracle8’s equi-partitioned performance improvements, the index must be created as LOCAL.
Unrecoverable Loads
Direct path loads can now be made unrecoverable by specifying the parameter UNRECOVERABLE in the load control file. Unrecoverable direct loads increase load performance by not recording loaded data
in the redo log files. Because the loaded data is not logged, media recovery is disabled for the loaded table. If media recovery does become necessary, the data blocks loaded by the unrecoverable
direct load are marked as logically corrupted. The data must be dropped and recreated making it advantageous to take a backup of the loaded data immediately after the execution of the unrecoverable
direct load.
Index-Only Tables
Oracle8 allows you to define an index-only table that keeps data sorted on the primary key. Index-only tables differ from a regular table in that Oracle maintains the table rows in a B-tree index
built on the primary key. The B-tree index contains both the encoded key value and the encoded key value’s corresponding row contents. The row contents are stored in place of the ROWID
that is stored in regular B-tree index configurations.
Index-only tables increase the performance of applications by providing faster key-based access to data for queries involving exact match and/or range search. Index-only tables eliminate the index
to table I/O associated with more conventional index/table storage structures. Storage requirements are also reduced as key columns are not duplicated in the table and its associated index.
Conventional B-tree index entries are quite small since they consist of a key value and ROWID. Index-only table’s index entries can become quite large since they consist of a key value
and the key value’s corresponding row data. The B-tree index leaf nodes (bottom layer of the index) of an index-only table may end up storing a single row which effectively destroys the
B-tree index’s dense clustering property. Oracle8 uses a row overflow area to overcome the problem of large B-tree index entries. You are able to define an overflow tablespace and a threshold
value (% of block size) during index-only table creation.
Direct Load Inserts
Oracle8 increases the performance of SQL INSERT statements by providing a direct load insert. The direct load insert bypasses the database buffer cache by inserting data directly into the datafiles
(much like the direct load utility). Direct load inserts are able to access both partitioned and non-partitioned tables and can execute either serially or in parallel. In addition, you have the
option to turn logging off to increase performance of the direct load insert statement. You activate no-logging mode by issuing an ALTER TABLE, ALTER INDEX or ALTER TABLESPACE command on the target
objects. You are required to issue a COMMIT or ROLLBACK statement immediately after executing a direct load insert. Direct load inserts require more space than conventional path inserts because
they ignore existing free space lists and append data beyond the high water mark. Conventional path inserts reuse free space in the current data segment below the high water mark. Direct
load inserts can not be used if there are any global indexes or referential constraints on the table.
Only the INSERT…SELECT syntax can process direct load inserts in parallel. The INSERT…..values variation of the insert statement can not execute in parallel. Conventional path inserts
are also unable to execute in parallel. You activate direct load insert by using a hint in the INSERT statement.
Parallel Insert, Update and Delete
Oracle Version 7 allows users to parallelize SQL SELECT statements to increase application performance. Parallelism increases query performance by splitting the work among multiple CPUs. Oracle8
takes parallelism one step further by allowing you to parallelize insert, update and delete statements. Parallel DML is particularly useful for decision support applications that contain large
amounts of data.
Update and delete statements can only be parallelized on partitioned tables. They can not access individual partitions or non-partitioned tables in parallel. A parallel update/delete process is
able to access multiple partitions during its execution but a single partition can only be updated or deleted by a single parallel process.
Unlike update and delete statements, insert statements can be parallelized on both non-partitioned and partitioned tables. Remember, only direct load INSERT…SELECT statements can process in
parallel. The INSERT…..values variation of the insert statement and conventional inserts can not execute in parallel.
There are many restrictions placed upon parallel DML. Space limitations prohibit me from explaining each of them in-depth. Please refer to the appropriate Oracle reference manuals for more
information.
You enable parallel DML processing by using the ALTER SESSION ENABLE PARALLEL DML statement. This statement is necessary because parallel DML has different locking, transaction and disk space
requirements than non parallel DML.
After the ALTER SESSION ENABLE PARALLEL DML statement is executed, Oracle8 will consider all further DML statements for parallel execution. This mode does not affect parallelization of queries or
the query portions of a DML statement. Altering the session to enable DML parallelism does not always ensure parallel DML operations will occur. You enable DML parallelism by using the PARALLEL
parameter to specify the default degree of parallelism for a particular object or by specifying hints in the DML statement.
Parallel Index Processing
Oracle8 provides a new hint called PARALLEL_INDEX to activate parallelism for statements that operate on indexes. The PARALLEL hint is still used for table access, but is ignored for full index
scans of partitioned and non-partitioned indexes, range scans of partitioned indexes and select operations of CREATE TABLE AS SELECT statements. You can use the explain statement to determine the
access path of a query to determine if it will perform any of the operations listed above. You can then add the PARALLEL_INDEX hint to the statement to allow it to process in parallel.
Large Pool
The shared pool region of the SGA stores a number of subareas. These subareas are sized by a single parameter called the shared_pool_size. It is impossible for the DBA to explicitly allocate
separate regions of memory for the components of the shared pool. The shared pool allocates memory for the library cache, the data dictionary cache and session information for systems that are
using Oracle’s multithreaded server. As a result, the shared pool is normally the second largest component of the SGA.
The multithreaded server also uses the shared pool for sorting operations on row data requested by CREATE INDEX, SQL ORDER BY, SQL GROUP BY and table join methods. If the instance does not use
multithreaded server, the amount of memory allocated for these operations is defined by the sort_area_size init.ora parameter. Problems begin to occur for multithreaded instances that require user
sort operations to be performed. The user sort operations begin to dominate the memory allocated to the shared pool region of the SGA.
Oracle8 introduces the large_pool_size parameter to offer administrators more control over memory allocation in multithreaded server environments. The large_pool_size parameter allows the
definition of a memory storage area for the exclusive use of user sort operations. This removes the conflicts that occur between user sort operations and library and data dictionary cache requests.
Object Oriented
Object REFs
In relational databases, ROWIDs are used to identify rows. A ROWID is the number of the physical database block and row displacement within that block. ROWIDs can not be used to reliably reference
rows in other tables. The row could be moved and the ROWID address changed.
In Oracle8, objects stored in an object table have a unique, system-generated identifier, called an object identifier (OID) that identifies each of the row objects. Administrators are able to embed
OIDs into columns, providing the ability to point to other rows in the database. You are able to reference an OID to get the values that an object contains. As a result, SQL joins are not required
to retrieve data from multiple objects.
OIDs create data relationships by using the embedded pointers to point to other objects. As a result, the addition of a new OID will require the DBA to scan each object affected in the database.
When the object is deleted, the programmers must know what objects have OIDs that contain the address of the deleted object.
You refer to the objects that appear as rows of object tables by using their object identifiers. Pointing to objects by using their object identifiers is called a reference or REF. Applications are
able to use references to navigate among the objects.
Oracle8 allows a column of a table or element of a collection to contain OIDs. An OID in a column could be used to create a one-to-one relationship and a varray (discussed later) could be used to
create a one-to-many relationship between objects. You can obtain a REF to a row object by selecting the object from its object table and applying the REF operator.
OID access is much faster than table joins. You are able to establish relationships between tables without using foreign keys. Like pointers in hierarchical databases (IMS, for example), the OIDs
point directly to the desired object(s). No searching, joining and sorting required!
Repeating Groups
Application developers have used arrays for some time. An array is an ordered set of objects, called elements. The array uses an index of numbers to point to specific elements. All of the elements
in an array must be of the same type. The array’s size depends on the number of elements it contains.
Oracle8 builds upon this concept through the use of varrays. Varrays are referred to as collections in Oracle8. Varrays do not allocate storage space and are variable in size. You define the
maximum size of the varray during creation. Oracle suggests that you use varrays for relatively small sets of objects. Varrays can be used as a datatype of a relational table, an object type
attribute or a PL/SQL variable, parameter or function return type.
An SQL SELECT statement will return the contents of the varray as it would any other column. Currently, in SQL you can manipulate whole varrays, but not their individual elements. SQL will be
mainly used to move entire collections of data into and out of database tables or between client side applications and stored subprograms. Within PL/SQL, varrays provide you with extended
procedural capabilities. To manipulate individual elements, your PL/SQL program can compute subscript values to access them by their index pointers. You are able to pass entire varrays as
parameters to PL/SQL subprograms. Oracle provides several new PL/SQL functions to determine the first and last, as well as prior and next values within a collection.
Repeating groups are available when a table row is fetched without having to retrieve them through a join operation. Repeating groups can improve performance when the number of repeating values is
small, each row has a relatively common number of repeating values and you want to avoid the overhead involved in joining tables. Repeating groups can also be used to create one-to-many
relationships when they are used to store OIDs.
Aggregate Objects
Oracle8 allows database designers to define both atomic objects and aggregate objects that are composed by grouping atomic objects together. Aggregate objects allow multiple objects to be
pre-assembled for fast retrieval. Aggregate objects provide performance improvements over objects that are stored at their atomic (smallest) level by removing the overhead involved with table
joining and sorting. The predefined object can be quickly assembled from the object IDs that comprise the aggregate object.
Summary
Hopefully, this article has provided readers with a high-level overview of some of the performance enhancements that are available in Oracle 8.04. It is important to thoroughly investigate each of
the performance enhancements in this article. The key to success is to gather more information, accumulate some valid test data, test, and implement!
