ch02.htm

Teach yourself Oracle8 in 21 day

or instruction that it thinks will be needed next. Sometimes this works, so the data
that is needed next is already in cache; sometimes the CPU has guessed incorrectly
and other data needs to be retrieved. This process of prereading the data is known
as <I>prefetching</I>.</P>
<P>Depending on the specific implementation of an SMP system, the memory bus might
be shared by all system processors; alternatively, each processor might have a private
bus to memory.</P>
<H3><FONT COLOR="#000077"><B>Virtual Memory System</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>In a virtual memory system,
the OS and hardware allow programs and users to use more memory than is actually
available in the system hardware. This memory, known as <I>virtual memory</I>, can
be mapped to physical memory. Code or data that is being run by the CPU must reside
in physical memory. If a program or data that is larger than physical memory is being
accessed, the parts of code and data that are not immediately needed by the program
can reside in virtual memory, not physical memory. As that bit of code or data is
needed, it can be copied into physical memory, and parts no longer needed can be
copied to disk. The process of mapping virtual memory onto physical memory by copying
the memory to and from disk is called <I>paging</I> or <I>swapping</I> (depending
on the OS architecture).</P>
<P>Both paging and swapping serve the same purpose, but each operates slightly differently
from the other. In a swapping system, an entire process is swapped out (moved from
memory to disk) or swapped in (moved from disk to memory). In a paging system, the
movement of data to and from the secondary storage occurs on a memory page basis;
when more memory is needed, one or more pages is paged out (moved from memory to
disk) to make room. A memory page is the smallest unit of memory that is used in
the operating system. A typical memory page size is 4KB. If data is requested from
virtual memory and is not in physical memory, that data is paged in (moved from disk
to memory) as needed. The rest of this section uses the term <I>paging</I> to describe
both paging and swapping.</P>
<P>Suppose you have a computer system with 16MB of physical memory. If you have a
program that needs to access 20MB of data, it obviously won't fit in physical memory.
In a virtual memory system, the data is read until little memory remains (the OS
reserves some for itself), then the OS copies some of the data pages to disk with
the paging mechanism. This is usually done using a least recently used algorithm
in which the oldest data is moved out. When some memory has been freed, the program
can read more data into memory. As far as the program is concerned, all the data
is still in memory; in fact, it is--in virtual memory. As the program begins to reread
some of the data and manipulate it, different pieces might be paged in (from disk
to physical memory) and paged out (from physical memory to disk).</P>
<P>As you can imagine, paging in or out can be time consuming and uses a lot of system
resources. This is why I warn you several times in this book to avoid using so much
memory that you cause paging or swapping. Access to disk is approximately 50 times
slower than access to memory.</P>
<H3><FONT COLOR="#000077"><B>Bus Design</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Simply put, <I>bus</I> is
a connection path used by the system to move data from one place to another. Buses
get complicated when you look at them from a performance perspective: Capacity, or
bandwidth, becomes an issue. Over the years, the term <I>bandwidth</I>, which was
originally used to describe the electronic characteristics of a circuit, has been
adopted by computer designers. In this case, <I>bandwidth </I>refers to the amount
of data that can be transmitted across a bus in a certain time.</P>
<P>Several bus designs have been introduced in the last few years, all with the same
goal: increased capacity. As processors, network hardware, disk controllers, and
disks become increasingly fast, buses must develop to support the load generated
by these devices. Thankfully, as computers have increased in performance, computer
designers have improved bus designs to accommodate these changes. The system bus
should not be a bottleneck in your system.</P>
<H3><FONT COLOR="#000077"><B>Oracle Resources</B></FONT></H3>
<P>The Oracle DBMS allocates different resources for various different functions,
including the allocation of system memory. The memory might be allocated for database
caching or for the data dictionary or library cache. The careful balance of this
precious resource is very important in tuning the Oracle RDBMS.</P>
<P>As much data as possible must be cached to avoid the additional cost of going
to disk. If you allocate a large Oracle data cache, a higher cache-hit rate can be
achieved. A high cache-hit rate indicates that a large percentage of requested data
is found in the Oracle cache rather than retrieved from disk.</P>
<H3><FONT SIZE="4" COLOR="#000077"><B>Application Design</B></FONT></H3>
<P>Application design can affect performance more than any other factor. In most
cases, performance can be severely degraded by an application that does not have
well-tuned SQL statements or does not use indexes. A good application design can
also significantly improve performance. The application is typically the first place
to look when you experience system performance problems.</P>
<P>If a database is built with indexes on a certain set of columns but those columns
are not specified in the <TT>WHERE</TT> clause of the SQL statement, the index probably
won't be used. It's not enough to create the correct index on tables; you must ensure
that the indexes are used.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>It's wise to create a specification
	identifying the tables and indexes in your database. That way, the application developers
	and the team that creates the database have a crystal-clear document that identifies
	which columns are indexed. This can help avoid confusion and allow the application
	code to fully exploit the indexes. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Oracle Features</B></FONT></H3>
<P>Another way to improve Oracle performance is to enable Oracle performance features.
Among the most important of these features (and my personal favorite) is the Oracle
Parallel Query option. Other Oracle performance features include partitioned tables
and the Oracle index-only table, both new in Oracle8.</P>
<H3><FONT COLOR="#000077"><B>The Oracle Parallel Query Option</B></FONT></H3>
<P>The Oracle Parallel Query option allows parallelism of many different operations,
which greatly enhances performance. The Oracle Parallel Query option consists of
several different components, including

<UL>
	<LI>Parallel query
	<P>
	<LI>Parallel index creation
	<P>
	<LI>Parallel recovery
	<P>
	<LI>Parallel table creation
	<P>
	<LI>Parallel index tables
</UL>

<H3><FONT COLOR="#000077"><B>Parallel Query</B></FONT></H3>
<P>The Oracle parallel query allows a single query to be divided into components
and run in parallel. Because a query spends much of its time waiting for I/O operations
to complete, parallelizing queries can greatly improve performance. In a well-tuned
system where I/O is not a problem, parallel queries can run many times faster than
normal queries. Statements that can be parallelized include

<UL>
	<LI>Table scans
	<P>
	<LI>Sorts
	<P>
	<LI>Joins
</UL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You might be wondering why parallelizing
	operations would help performance; after all, the work must still be done. In a typical
	Oracle operation (for example, a <TT>SELECT</TT> statement), the following steps
	occur:

</BLOCKQUOTE>


<DL>
	<DD><B>1.</B> Oracle performs some CPU processing to determine what data is needed.
	<P><B>2. </B>Oracle submits an I/O request to disk (assuming that the data is not
	already in the SGA) and then waits for that I/O to complete.</P>
	<P><B>3. </B>This operation is repeated until all data is retrieved.
</DL>



<BLOCKQUOTE>
	<P>In the case of a parallel query, these steps would be adjusted like so:

</BLOCKQUOTE>


<DL>
	<DD><B>1.</B> Oracle performs some CPU processing to determine the query operation.
	<P><B>2.</B> Different Oracle processes or threads receive their instructions on
	what data is needed.</P>
	<P><B>3. </B>Oracle thread 1 submits an I/O request to disk (if that data is not
	already in the SGA) and waits for that I/O to complete.</P>
	<P><B>4. </B>Oracle thread 2 submits an I/O request to disk (if that data is not
	already in the SGA) and waits for that I/O to complete.</P>
	<P><B>5. </B>Oracle thread 3 submits an I/O request to disk (if that data is not
	already in the SGA) and waits for that I/O to complete.
</DL>



<BLOCKQUOTE>
	<P>As shown here, that the time-consuming job of retrieving data from disk is duplicated,
	thus improving performance. This parallelism allows the CPU(s) to be utilized while
	other threads are waiting for I/Os. <BR>
	<BR>
	Retrieving data from disk is a slow process compared to the activity of the CPU,
	and your goal is to keep the CPUs busy. Because a significant part of any Oracle
	operation involves CPU processing and I/Os, it is possible and desirable to keep
	the CPUs busy while many I/Os are being processed simultaneously. This is the main
	goal of the Parallel Query option. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Parallel Index Creation</B></FONT></H3>
<P>Index creation involves reading from data tables and then writing to the index
tables. Because the parallel query allows reading of tables to be accelerated, the
index-creation process is sped up. Index creations can be quite time consuming, so
this can be a real advantage.</P>
<H3><FONT COLOR="#000077"><B>Parallel Recovery</B></FONT></H3>
<P>Recovery from a system failure can be quite time consuming. During recovery, users
must usually wait for the system to come back online, so any improvement in performance
is an advantage. Parallel recovery can speed the recovery process by parallelizing
the read from the redo log files, and the roll forward and rollback process.</P>
<H3><FONT COLOR="#000077"><B>Parallel Table Creation</B></FONT></H3>
<P>Although the Oracle Parallel Query option does not generally allow table creations
to occur, it is often the case when a table is created as a subset of other tables.
Data is often reduced from several large tables into a smaller subset, and this parallelism
can be beneficial. In such instances, the following statement allows for parallelism:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE table_name AS SELECT...
</FONT></PRE>
<H3><FONT COLOR="#000077"><B>Oracle Index Tables</B></FONT></H3>
<P>New to Oracle8, the index table allows indexes and tables to be stored together;
this saves space and improves performance by reducing disk I/O. If you reduce the
number of required disk I/Os, data can be accessed much faster.</P>
<H2><FONT COLOR="#000077"><B>OS Resources</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>In most systems, few resources
can be allocated in the operating system. Most OS parameters are changed only to
allocate sufficient resources to Oracle; additional resources usually do not improve
performance. A lack of resources, however, can decrease performance. <I>OS resources</I>
often refers to system memory or, in the case of UNIX, shared memory. Other OS resources
and tunables include network buffers and disk I/O tunables.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Windows NT is fairly self tunable,
	but there are a few things, relating primarily to configuration, to look out for:

</BLOCKQUOTE>


<UL>
	<LI>Remove unnecessary network protocols--Depending on how the system is configured,
	several network protocols that you do not use might be configured into your system.
	These extra protocols use CPU and memory resources.
	<P>