ch15.htm

21精通SQL

in a constant state of change. The changes that we are referring to are frequent
batch updates and continual daily transactional processing. Dynamic databases usually
entail heavy OLTP systems, but can also refer to DSSs or data warehouses, depending
upon the volume and frequency of data loads.</P>
<P>The result of constant high-volume changes to a database is growth, which in turn
yields fragmentation. Fragmentation can easily get out of hand if growth is not managed
properly. Oracle allocates an initial extent to tables when they are created. When
data is loaded and fills the table's initial extent, a next extent, which is also
allocated when the table is created, is taken.</P>
<P>Sizing tables and indexes is essentially a DBA function and can drastically affect
SQL statement performance. The first step in growth management is to be proactive.
Allow room for tables to grow from day one, within reason. Also plan to defragment
the database on a regular basis, even if doing so means developing a weekly routine.
Here are the basic conceptual steps involved in defragmenting tables and indexes
in a relational database management system:

<DL>
	<DD><B>1. </B>Get a good backup of the table(s) and/or index(es).<BR>
	<BR>
	<B>2. </B>Drop the table(s) and/or index(es).<BR>
	<BR>
	<B>3. </B>Rebuild the table(s) and/or index(es) with new space allocation.<BR>
	<BR>
	<B>4. </B>Restore the data into the newly built table(s).<BR>
	<BR>
	<B>5.</B> Re-create the index(es) if necessary.<BR>
	<BR>
	<B>6. </B>Reestablish user/role permissions on the table if necessary.<BR>
	<BR>
	<B>7. </B>Save the backup of your table until you are absolutely sure that the new
	table was built successfully. If you choose to discard the backup of the original
	table, you should first make a backup of the new table after the data has been fully
	restored.
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Never get rid of the backup of
	your table until you are sure that the new table was built successfully. 
<HR>


</BLOCKQUOTE>

<P>The following example demonstrates a practical use of a mailing list table in
an Oracle database environment.
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">CREATE TABLE MAILING_TBL_BKUP AS
<B>SELECT * FROM MAILING_TBL;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Table Created.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"><B>drop table mailing_tbl;</B>

Table Dropped.

<B>CREATE TABLE MAILING_TBL
    (
    INDIVIDUAL_ID        VARCHAR2(12)     NOT NULL,
    INDIVIDUAL_NAME    VARCHAR2(30)    NOT NULL,
    ADDRESS            VARCHAR(40)    NOT NULL,
    CITY            VARCHAR(25)    NOT NULL,
    STATE            VARCHAR(2)    NOT NULL,
    ZIP_CODE        VARCHAR(9)    NOT NULL,
    )
    TABLESPACE TABLESPACE_NAME
    STORAGE    (    INITIAL        NEW_SIZE,
            NEXT        NEW_SIZE    );</B>

Table created.

<B>INSERT INTO MAILING_TBL
select * from mailing_tbl_bkup;</B>

93,451 rows inserted.

<B>CREATE INDEX MAILING_IDX ON MAILING TABLE
    (
    INDIVIDUAL_ID
    )
    TABLESPACE TABLESPACE_NAME
    STORAGE    (    INITIAL        NEW_SIZE,
            NEXT        NEW_SIZE    );
</B>
Index Created.

<B>grant select on mailing_tbl to public;
</B>
Grant Succeeded.

<B>drop table mailing_tbl_bkup;</B>

Table Dropped.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Rebuilding tables and indexes that have grown enables you to optimize storage,
which improves overall performance. Remember to drop the backup table only after
you have verified that the new table has been created successfully. Also keep in
mind that you can achieve the same results with other methods. Check the options
that are available to you in your database documentation.
<H2><FONT COLOR="#000077"><B>Tuning the Database</B></FONT></H2>
<P>Tuning a database is the process of fine-tuning the database server's performance.
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-2.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/03-2.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