ch02.htm

Teach yourself Oracle8 in 21 day

	<LI>Configure the protocols you use in order from most-often used to least-often
	used--This reduces some of the overhead associated with traversing the infrequently
	used protocols.
	<P>
	<LI>Keep a close eye on paging--Windows NT treats all memory as virtual. The best
	way to determine whether your system is paging is to watch Pages/Sec in NT's perfmon.
	If paging occurs, lower the amount of memory allocated to Oracle. 
<HR>

</UL>

<H3><FONT COLOR="#000077"><B>Hardware</B></FONT></H3>
<P>Several hardware factors can affect your system's performance. These factors include

<UL>
	<LI>Memory capacity
	<P>
	<LI>Number of CPUs
	<P>
	<LI>CPU cache
	<P>
	<LI>Memory-bus bandwidth
	<P>
	<LI>I/O capacity
</UL>

<H4><FONT COLOR="#000077"><B>Memory Capacity</B></FONT></H4>
<P>Earlier today you saw an overview of how the system hardware operates. Clearly,
any operation that must access slower components, such as a disk or network, will
slow down processing. Therefore, it is important that you have sufficient memory
in your system.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Most hardware architectures
are limited to 4GB of physical memory, but some architectures on the market support
much more. These architectures are said to support a <I>VLM</I>, or <I>Very Large
Memory</I>, architecture. Soon it will be possible to support hundreds of gigabytes
of physical memory in a system, allowing for very fast RDBMS operations.</P>
<P>System memory is allocated to Oracle and used for database caching, user memory,
and the shared pool, which is used for both the data dictionary and the library cache.
You must have enough memory for the shared pool because an insufficient shared pool
can hurt performance. When the shared pool is satisfied, the more database buffers
you can allocate to the DBMS the better. Be careful, though, to avoid starving the
PGA memory needed by your processes, and avoid paging at all costs. You can never
have too much memory in your system. Anything that can be cached will reduce system
I/O, improving performance.</P>
<P><FONT COLOR="#000077"><B>Number of CPUs</B></FONT></P>
<P>Oracle typically scales well with additional CPUs. By adding CPUs you can see
significant performance improvement with little additional cost. Some factors that
determine how much improvement you will see by adding more processors are the CPU
cache and memory- bus bandwidth.</P>
<P><FONT COLOR="#000077"><B>CPU Cache</B></FONT></P>
<P>A large CPU cache allows more data and executable code to be stored on the local
processor than in memory. This reduces the number of times the CPU must access main
memory. Whenever the CPU accesses memory, a slowdown occurs while the CPU waits for
that data or code to be retrieved. It is especially bad when the memory bus is busy;
the CPU waits even longer until the bus becomes free.</P>
<P><FONT COLOR="#000077"><B>Memory-Bus Bandwidth</B></FONT></P>
<P>The memory-bus bandwidth determines how quickly data can be transferred between
CPU to memory. If the memory bus is busy when data or code is needed, a CPU stalls
waiting for the bus to free. This can severely degrade performance in a multiprocessor
computer. A fast memory bus can reduce this problem. A large CPU cache can also reduce
this problem by allowing more data and code to be cached.</P>
<P><FONT COLOR="#000077"><B>I/O Capacity</B></FONT></P>
<P>I/O is typically one of the biggest factors limiting system performance. Because
most DBMS operations involve retrieving data from disk, I/O can be a limiting factor
if you do not have adequate capacity for your system load. Fortunately, you can usually
solve this problem by carefully configuring your system for proper I/O distribution
and by having sufficient I/O capacity. Simply having adequate disk space is insufficient;
you must also have enough disk drives to support the number of disk I/Os that the
system requires.</P>
<H2><FONT COLOR="#000077"><B>Oracle8 New Features</B></FONT></H2>
<P>Oracle8 has introduced many new features, and I would like to focus on a few key
features for the Oracle8 DBA:

<UL>
	<LI>Partitioned objects
	<P>
	<LI>Improved parallelism
	<P>
	<LI>New index types
	<P>
	<LI>Enhanced recovery features
</UL>

<H3><FONT COLOR="#000077"><B>Partitioned Objects</B></FONT></H3>
<P>Partitioned objects allow Oracle objects, such as tables and indexes, to be broken
into smaller, more manageable pieces. Partitioning these objects allows many operations
that could normally be performed on only a table or an index to be divided into operations
on a partition. By dividing these operations, you can often increase the parallelism
of those operations, thus improving performance and minimizing system downtime.</P>
<P>Partitions are enabled via the <TT>PARTITION BY RANGE</TT> parameter of the <TT>CREATE
TABLE</TT> statement. In this manner, ranges of data are assigned to each individual
partition like so:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE emp 
(
name CHAR(30),
address CHAR(40),
region INTEGER
)
PARTITION BY RANGE ( region)
(
PARTITION VALUES LESS THAN (10) TABLESPACE tbl0,
PARTITION VALUES LESS THAN (20) TABLESPACE tbl1,
PARTITION VALUES LESS THAN (30) TABLESPACE tbl2
);
</FONT></PRE>
<P>This creates a table with partitioning, as shown in Figure 2.5.</P>
<P><A NAME="05"></A><A HREF="05.htm"><B>Figure 2.5.</B></A></P>
<P><I>Table partitioning.</I></P>
<P>Partitioning is recommended for large tables because it makes them much more manageable.
Oracle does not currently support partitioning of clusters. By partitioning a table,
you can break that large table into several much smaller pieces. A partitioned table
can take advantage of some of the following features:

<UL>
	<LI>Partitioned DML
	<P>
	<LI>Exporting/importing by partition
	<P>
	<LI>Range partitioning
	<P>
	<LI>Local and global indexing
	<P>
	<LI>Parallel loading by partition
</UL>

<H3><FONT COLOR="#000077"><B>Partitioned DML</B></FONT></H3>
<P>Parallel <TT>INSERT</TT>, <TT>DELETE</TT>, and <TT>UPDATE</TT> operations can
occur on a partition basis. Using partitions allows these operations to be conducted
either globally or locally within a partition.</P>
<H3><FONT COLOR="#000077"><B>Exporting/Importing by Partition</B></FONT></H3>
<P>Partitioning allows operations such as exports and imports to be performed on
a partition basis. This can reduce the time required by some maintenance operations,
such as reorganization of data or reclustering. This also allows you to change the
physical layout of your database on a partition basis. If you limit the scope of
export and import operations, they can benefit from a large degree of parallelism.</P>
<H3><FONT COLOR="#000077"><B>Range Partitioning</B></FONT></H3>
<P>Range partitioning is a method whereby the partitioning of data is done based
on the value of the data itself. This allows for tremendous flexibility in distributing
data based on ranges of data values. Range partitioning allows you to partition high-volume
data separately from low-volume data or to separate current from old data.</P>
<H3><FONT COLOR="#000077"><B>Local and Global Indexing</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>local index</I> indexes
data that resides in only one partition. A <I>global index</I> indexes data that
resides on more than one partition. This allows for great flexibility in terms of
adding new indexes, reducing index sizes, and allowing for partition independence.</P>
<P>An example of where local indexing might be beneficial is a table where sales
records are stored. Using table and index partitioning, you can store data and indexes
separately based on calendar months; doing this allows reduced index size and faster
index lookups for entries of a particular month. If you partition these entries you
can add new months and delete outdated entries without reindexing the entire table.
You could keep 12 months of partitions and indexes online in this manner.</P>
<H3><FONT COLOR="#000077"><B>Parallel Loading by Partition</B></FONT></H3>
<P>With a partitioned table, SQL*Loader can either load an entire table in parallel
by partition or simply load a single partition. Either method provides great flexibility.</P>
<P>If you use the conventional path load, the loader automatically distributes the
data to the correct partition and updates the local and global indexes. You can also
use the loader to load a partitioned table or a partition of a table. Again, indexes
are built automatically. It is also possible to direct-load a partition in parallel
provided that no global indexes exist, but you must rebuild the local indexes yourself.</P>
<H3><FONT COLOR="#000077"><B>Improved Parallelism</B></FONT></H3>
<P>The arrival of Oracle8 has heralded tremendous improvement in the area of parallelization.
In addition to the new parallel features listed previously, some existing parallel
operations have been extended.</P>
<P>Parallel recovery has been improved by allowing rollbacks of parallel DML operations
that have failed to be performed in parallel. This parallel transaction recovery
is supported on transaction and process failures but not during instance recovery.</P>
<P>New parallel hints have been added for parallel insert operations. The <TT>APPEND</TT>
hint tells the optimizer to append the insert data beyond the high water mark of
the segment.</P>
<H3><FONT COLOR="#000077"><B>New Index Types</B></FONT></H3>
<P>The index-only table is new in Oracle8. With traditional indexes and tables, data
and indexes are stored separately. With an index-only table, the data to which the
index refers is stored in the leaf block or lowest level block of the index, so the
data and indexes are stored together. Depending on your application, this can be
an advantage.</P>
<P>Applications that access data primarily via a key value can see an advantage from
the use of index-only tables. Because the data is stored within the index, the data
is immediately available when the index has reached its lowest level. This can speed
data retrieval.</P>
<P>Applications that do not access data primarily via a key value will see no improvement;
indeed, performance will likely be degraded in these applications. Any application
that involves table scans or requires multiple indexes will not benefit from the
index table. The index table is covered in much more detail on Day 13.</P>
<H3><FONT COLOR="#000077"><B>Enhanced Recovery Features</B></FONT></H3>
<P>Oracle has made tremendous improvements in the areas of backup and recovery. Most
of these new features revolve around the Recovery Manager. Another recovery feature
in Oracle8 is the image copy backup, which can improve recovery time in the event
of a failure.</P>
<H3><FONT COLOR="#000077"><B>Recovery Manager</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Recovery Manager is an online
utility designed to assist the DBA with all backup and recovery operations. Not only
does it perform the backup and recovery, it maintains a database called the <I>recovery</I>
<I>catalog</I> that stores information about these operations.</P>
<H3><FONT COLOR="#000077"><B>Image Copy Backup</B></FONT></H3>
<P>An image copy backup essentially allows you to copy a datafile to another place
on disk or to another disk on your system. In the event of a failure, no recovery
is necessary from the image copy; you must simply switch to that backup copy. You
must, however, perform a recovery to make that copy current. In the event of a failure,
this might be the fastest way to recover.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Days 16-18 cover backup and recovery
	techniques in greater detail. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077"><B>Oracle Products</B></FONT></H2>
<P>As part of the overview of the Oracle system, I would like to briefly cover the
optional available Oracle products. Although many of these products are covered elsewhere
in this book, you should at least aware of their existence. The Oracle product line
is divided into three areas:

<UL>
	<LI>The Oracle server
	<P>
	<LI>Development tools
	<P>
	<LI>Applications
</UL>

<H3><FONT COLOR="#000077"><B>The Oracle Server</B></FONT></H3>
<P>The Oracle server is the DBMS itself, and includes many options