for the sort area but less space for the rollback area.</P><P>Most transactions in an OLAP system take place as part of a batch process. Insteadof having several rollback areas for user input, you may resort to one large rollbackarea for the loads, which can be taken offline during daily activity to reduce overhead.<H2><FONT COLOR="#000077"><B>Batch Loads Versus Transactional Processing</B></FONT></H2><P>A major factor in the performance of a database and SQL statements is the typeof processing that takes place within a database. One type of processing is OLTP,discussed earlier today. When we talk about transactional processing, we are goingto refer to two types: user input and batch loads.</P><P>Regular user input usually consists of SQL statements such as <TT>INSERT</TT>,<TT>UPDATE</TT>, and <TT>DELETE</TT>. These types of transactions are often performedby the end user, or the customer. End users are normally using a front-end applicationsuch as PowerBuilder to interface with the database, and therefore they seldom issuevisible SQL statements. Nevertheless, the SQL code has already been generated forthe user by the front-end application.</P><P>Your main focus when optimizing the performance of a database should be the end-usertransactions. After all, &quot;no customer&quot; equates to &quot;no database,&quot;which in turn means that you are out of a job. Always try to keep your customershappy, even though their expectations of system/database performance may sometimesbe unreasonable. One consideration with end-user input is the number of concurrentusers. The more concurrent database users you have, the greater the possibilitiesof performance degradation.</P><P>What is a batch load? A <I>batch load</I> performs heaps of transactions againstthe database at once. For example, suppose you are archiving last year's data intoa massive history table. You may need to insert thousands, or even millions, of rowsof data into your history table. You probably wouldn't want to do this task manually,so you are likely to create a batch job or script to automate the process. (Numeroustechniques are available for loading data in a batch.) Batch loads are notoriousfor taxing system and database resources. These database resources may include tableaccess, system catalog access, the database rollback segment, and sort area space;system resources may include available CPU and shared memory. Many other factorsare involved, depending on your operating system and database server.</P><P>Both end-user transactions and batch loads are necessary for most databases tobe successful, but your system could experience serious performance problems if thesetwo types of processing lock horns. Therefore, you should know the difference betweenthem and keep them segregated as much as possible. For example, you would not wantto load massive amounts of data into the database when user activity is high. Thedatabase response may already be slow because of the number of concurrent users.Always try to run batch loads when user activity is at a minimum. Many shops reservetimes in the evenings or early morning to load data in batch to avoid interferingwith daily processing.</P><P>You should always plan the timing for massive batch loads, being careful to avoidscheduling them when the database is expected to be available for normal use. Figure15.1 depicts heavy batch updates running concurrently with several user processes,all contending for system resources.</P><P><A NAME="01"></A><A HREF="01.htm"><B>Figure 15.1.</B></A><B><BR></B><I>System resource contention.</I></P><P>As you can see, many processes are contending for system resources. The heavybatch updates that are being done throw a monkey wrench into the equation. Insteadof the system resources being dispersed somewhat evenly among the users, the batchupdates appear to be hogging them. This situation is just the beginning of resourcecontention. As the batch transactions proceed, the user processes may eventuallybe forced out of the picture. This condition is not a good way of doing business.Even if the system has only one user, significant contention for that user couldoccur.</P><P>Another problem with batch processes is that the process may hold locks on a tablethat a user is trying to access. If there is a lock on a table, the user will berefused access until the lock is freed by the batch process, which could be hours.Batch processes should take place when system resources are at their best if possible.Don't make the users' transactions compete with batch. Nobody wins that game.<H2><FONT COLOR="#000077"><B>Optimizing Data Loads by Dropping Indexes</B></FONT></H2><P>One way to expedite batch updates is by dropping indexes. Imagine the historytable with many thousands of rows. That history table is also likely to have oneor more indexes. When you think of an index, you normally think of faster table access,but in the case of batch loads, you can benefit by dropping the index(es).</P><P>When you load data into a table with an index, you can usually expect a greatdeal of index use, especially if you are updating a high percentage of rows in thetable. Look at it this way. If you are studying a book and highlighting key pointsfor future reference, you may find it quicker to browse through the book from beginningto end rather than using the index to locate your key points. (Using the index wouldbe efficient if you were highlighting only a small portion of the book.)</P><P>To maximize the efficiency of batch loads/updates that affect a high percentageof rows in a table, you can take these three basic steps to disable an index:<DL>	<DD><B>1. </B>Drop the appropriate index(es).<BR>	<BR>	<B>2. </B>Load/update the table's data.<BR>	<BR>	<B>3.</B> Rebuild the table's index.</DL><H2><FONT COLOR="#000077"><B>A Frequent </B><TT>COMMIT</TT><B> Keeps the DBA Away</B></FONT></H2><P>When performing batch transactions, you must know how often to perform a &quot;commit.&quot;As you learned on Day 11, &quot;Controlling Transactions,&quot; a <TT>COMMIT</TT>statement finalizes a transaction. A <TT>COMMIT</TT> saves a transaction or writesany changes to the applicable table(s). Behind the scenes, however, much more isgoing on. Some areas in the database are reserved to store completed transactionsbefore the changes are actually written to the target table. Oracle calls these areas<I>rollback segments</I>. When you issue a <TT>COMMIT</TT> statement, transactionsassociated with your SQL session in the rollback segment are updated in the targettable. After the update takes place, the contents of the rollback segment are removed.A <TT>ROLLBACK</TT> command, on the other hand, clears the contents of the rollbacksegment without updating the target table.</P><P>As you can guess, if you never issue a <TT>COMMIT</TT> or <TT>ROLLBACK</TT> command,transactions keep building within the rollback segments. Subsequently, if the datayou are loading is greater in size than the available space in the rollback segments,the database will essentially come to a halt and ban further transactional activity.Not issuing <TT>COMMIT</TT> commands is a common programming pitfall; regular <TT>COMMIT</TT>shelp to ensure stable performance of the entire database system.</P><P>The management of rollback segments is a complex and vital database administrator(DBA) responsibility because transactions dynamically affect the rollback segments,and in turn, affect the overall performance of the database as well as individualSQL statements. So when you are loading large amounts of data, be sure to issue the<TT>COMMIT</TT> command on a regular basis. Check with your DBA for advice on howoften to commit during batch transactions. (See Figure 15.2.)</P><P><A NAME="02"></A><A HREF="02.htm"><B>Figure 15.2.</B></A><B><BR></B><I>The rollback area.</I></P><P>As you can see in Figure 15.2, when a user performs a transaction, the changesare retained in the rollback area.<H2><FONT COLOR="#000077"><B>Rebuilding Tables and Indexes in a Dynamic Environment</B></FONT></H2><P>The term <I>dynamic database environment </I>refers to a large database that isin a constant state of change. The changes that we are referring to are frequentbatch updates and continual daily transactional processing. Dynamic databases usuallyentail heavy OLTP systems, but can also refer to DSSs or data warehouses, dependingupon the volume and frequency of data loads.</P><P>The result of constant high-volume changes to a database is growth, which in turnyields fragmentation. Fragmentation can easily get out of hand if growth is not managedproperly. Oracle allocates an initial extent to tables when they are created. Whendata is loaded and fills the table's initial extent, a next extent, which is alsoallocated when the table is created, is taken.</P><P>Sizing tables and indexes is essentially a DBA function and can drastically affectSQL 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 defragmentthe 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 indexesin 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 inan 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_TBLselect * 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 afteryou have verified that the new table has been created successfully. Also keep inmind that you can achieve the same results with other methods. Check the optionsthat 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.