ch15.htm

Learn SQL in an easy way...

As a newcomer to SQL, you probably will not be exposed to database tuning unless
you are a new DBA or a DBA moving into a relational database environment. Whether
you will be managing a database or using SQL in applications or programming, you
will benefit by knowing something about the database-tuning process. The key to the
success of any database is for all parties to work together. Some general tips for
tuning a database follow.

<UL>
	<LI>Minimize the overall size required for the database.
</UL>


<DL>
	<DD>It's good to allow room for growth when designing a database, but don't go overboard.
	Don't tie up resources that you may need to accommodate database growth.
</DL>


<UL>
	<LI>Experiment with the user process's time-slice variable.
</UL>


<DL>
	<DD>This variable controls the amount of time the database server's scheduler allocates
	to each user's process.
</DL>


<UL>
	<LI>Optimize the network packet size used by applications.
</UL>


<DL>
	<DD>The larger the amount of data sent over the network, the larger the network packet
	size should be. Consult your database and network documentation for more details.
</DL>


<UL>
	<LI>Store transaction logs on separate hard disks.
</UL>


<DL>
	<DD>For each transaction that takes place, the server must write the changes to the
	transaction logs. If you store these log files on the same disk as you store data,
	you could create a performance bottleneck. (See Figure 15.3.)
</DL>


<UL>
	<LI>Stripe extremely large tables across multiple disks.
</UL>


<DL>
	<DD>If concurrent users are accessing a large table that is spread over multiple
	disks, there is much less chance of having to wait for system resources. (See Figure
	15.3.)
</DL>


<UL>
	<LI>Store database sort area, system catalog area, and rollback areas on separate
	hard disks.
</UL>


<DL>
	<DD>These are all areas in the database that most users access frequently. By spreading
	these areas over multiple disk drives, you are maximizing the use of system resources.
	(See Figure 15.3.)
</DL>


<UL>
	<LI>Add CPUs.
</UL>


<DL>
	<DD>This system administrator function can drastically improve database performance.
	Adding CPUs can speed up data processing for obvious reasons. If you have multiple
	CPUs on a machine, then you may be able to implement parallel processing strategies.
	See your database documentation for more information on parallel processing, if it
	is available with your implementation.
</DL>


<UL>
	<LI>Add memory.
</UL>


<DL>
	<DD>Generally, the more the better.
</DL>


<UL>
	<LI>Store tables and indexes on separate hard disks.
</UL>


<DL>
	<DD>You should store indexes and their related tables on separate disk drives when-
	ever possible. This arrangement enables the table to be read at the same time the
	index is being referenced on another disk. The capability to store objects on multiple
	disks may depend on how many disks are connected to a controller. (See Figure 15.3.)
</DL>

<P>Figure 15.3 shows a simple example of how you might segregate the major areas
of your database.</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 15.3.</B></A><B><BR>
</B><I>Using available disks to enhance performance.</I></P>
<P>The scenario in Figure 15.3 uses four devices: disk01 through disk04. The objective
when spreading your heavy database areas and objects is to keep areas of high use
away from each another.

<UL>
	<LI>Disk01-- The system catalog stores information about tables, indexes, users,
	statistics, database files, sizing, growth information, and other pertinent data
	that is often accessed by a high percentage of transactions.<BR>
	<BR>
	
	<LI>Disk02--Transaction logs are updated every time a change is made to a table (insert,
	update, or delete). Transaction logs are a grand factor in an online transactional
	database. They are not of great concern in a read-only environment, such as a data
	warehouse or DSS.<BR>
	<BR>
	
	<LI>Disk03--Rollback segments are also significant in a transactional environment.
	However, if there is little transactional activity (insert, update, delete), rollback
	segments will not be heavily used.<BR>
	<BR>
	
	<LI>Disk04-- The database's sort area, on the other hand, is used as a temporary
	area for SQL statement processing when sorting data, as in a <TT>GROUP BY</TT> or
	<TT>ORDER BY</TT> clause. Sort areas are typically an issue in a data warehouse or
	DSS. However, the use of sort areas should also be considered in a transactional
	environment.
</UL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Also note how the application tables
	and indexes have been placed on each disk. Tables and indexes should be spread as
	much as possible. 
<HR>


</BLOCKQUOTE>

<P>Notice that in Figure 15.3 the tables and indexes are stored on different devices.
You can also see how a &quot;Big Table&quot; or index may be <I>striped</I> across
two or more devices. This technique splits the table into smaller segments that can
be accessed simultaneously. Striping a table or index across multiple devices is
a way to control fragmentation. In this scenario, tables may be read while their
corresponding indexes are being referenced, which increases the speed of overall
data access.</P>
<P>This example is really quite simple. Depending on the function, size, and system-related
issues of your database, you may find a similar method for optimizing system resources
that works better. In a perfect world where money is no obstacle, the best configuration
is to have a separate disk for each major database entity, including large tables
and indexes.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The DBA and system administrator
	should work together to balance database space allocation and optimize the memory
	that is available on the server. 
<HR>


</BLOCKQUOTE>

<P>Tuning a database very much depends on the specific database system you are using.
Obviously, tuning a database entails much more than just preparing queries and letting
them fly. On the other hand, you won't get much reward for tuning a database when
the application SQL is not fine-tuned itself. Professionals who tune databases for
a living often specialize on one database product and learn as much as they possibly
can about its features and idiosyncrasies. Although database tuning is often looked
upon as a painful task, it can provide very lucrative employment for the people who
truly understand it.
<H2><FONT COLOR="#000077"><B>Performance Obstacles</B></FONT></H2>
<P>We have already mentioned some of the countless possible pitfalls that can hinder
the general performance of a database. These are typically general bottlenecks that
involve system-level maintenance, database maintenance, and management of SQL statement
processing.</P>
<P>This section summarizes the most common obstacles in system performance and database
response time.

<UL>
	<LI>Not making use of available devices on the server--A company purchases multiple
	disk drives for a reason. If you do not use them accordingly by spreading apart the
	vital database components, you are limiting the performance capabilities. Maximizing
	the use of system resources is just as important as maximizing the use of the database
	server capabilities.<BR>
	<BR>
	
	<LI>Not performing frequent <TT>COMMIT</TT>s--Failing to use periodic <TT>COMMIT</TT>s
	or <TT>ROLLBACK</TT>s during heavy batch loads will ultimately result in database
	bottlenecks.<BR>
	<BR>
	
	<LI>Allowing batch loads to interfere with daily processing--Running batch loads
	during times when the database is ex