[ Pobierz całość w formacie PDF ]
.The first step in growth management is to be proactive.Allowroom for tables to grow from day one, within reason.Also plan to defragment thedatabase on a regular basis, even if doing so means developing a weekly routine.Hereare the basic conceptual steps involved in defragmenting tables and indexes in arelational database management system:1.Get a good backup of the table(s) and/or index(es).2.Drop the table(s) and/or index(es).3.Rebuild the table(s) and/or index(es) with new space allocation.4.Restore the data into the newly built table(s).5.Re-create the index(es) if necessary.6.Reestablish user/role permissions on the table if necessary.7.Save the backup of your table until you are absolutely sure that the newtable was built successfully.If you choose to discard the backup of the originaltable, you should first make a backup of the new table after the data has beenfully restored.WARNING: Never get rid of the backup of your table until you are surethat the new table was built successfully.The following example demonstrates a practical use of a mailing list table in an Oracledatabase environment.INPUT:CREATE TABLE MAILING_TBL_BKUP ASSELECT * FROM MAILING_TBL;OUTPUT:Table Created.INPUT/OUTPUT:drop table mailing_tbl;Table Dropped.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_NAMESTORAGE ( INITIAL NEW_SIZE,NEXT NEW_SIZE );Table created.INSERT INTO MAILING_TBLselect * from mailing_tbl_bkup;93,451 rows inserted.CREATE INDEX MAILING_IDX ON MAILING TABLE(INDIVIDUAL_ID)TABLESPACE TABLESPACE_NAMESTORAGE ( INITIAL NEW_SIZE,NEXT NEW_SIZE );Index Created.grant select on mailing_tbl to public;Grant Succeeded.drop table mailing_tbl_bkup;Table Dropped.ANALYSIS:Rebuilding tables and indexes that have grown enables you to optimize storage, whichimproves overall performance.Remember to drop the backup table only after you haveverified that the new table has been created successfully.Also keep in mind that youcan achieve the same results with other methods.Check the options that are availableto you in your database documentation.Tuning the DatabaseTuning a database is the process of fine-tuning the database server's performance.As anewcomer to SQL, you probably will not be exposed to database tuning unless you are anew DBA or a DBA moving into a relational database environment.Whether you will bemanaging a database or using SQL in applications or programming, you will benefit byknowing something about the database-tuning process.The key to the success of anydatabase is for all parties to work together.Some general tips for tuning a databasefollow.Minimize the overall size required for the database.It's good to allow room for growth when designing a database, but don't gooverboard.Don't tie up resources that you may need to accommodate databasegrowth.Experiment with the user process's time-slice variable.This variable controls the amount of time the database server's schedulerallocates to each user's process.Optimize the network packet size used by applications.The larger the amount of data sent over the network, the larger the networkpacket size should be.Consult your database and network documentation formore details.Store transaction logs on separate hard disks.For each transaction that takes place, the server must write the changes to thetransaction 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.)Stripe extremely large tables across multiple disks.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.)Store database sort area, system catalog area, and rollback areas on separatehard disks.These are all areas in the database that most users access frequently.Byspreading these areas over multiple disk drives, you are maximizing the use ofsystem resources.(See Figure 15.3.)Add CPUs [ Pobierz całość w formacie PDF ]