[ Pobierz całość w formacie PDF ]
.Adding CPUs can speed up data processing for obvious reasons.If you have multipleCPUs on a machine, then you may be able to implement parallel processingstrategies.See your database documentation for more information on parallelprocessing, if it is available with your implementation.Add memory.Generally, the more the better.Store tables and indexes on separate hard disks.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 theindex is being referenced on another disk.The capability to store objects onmultiple disks may depend on how many disks are connected to a controller.(SeeFigure 15.3.)Figure 15.3 shows a simple example of how you might segregate the major areas of yourdatabase.Figure 15.3.Using available disks to enhance performance.The scenario in Figure 15.3 uses four devices: disk01 through disk04.The objective whenspreading your heavy database areas and objects is to keep areas of high use away fromeach another.Disk01-- The system catalog stores information about tables, indexes, users,statistics, database files, sizing, growth information, and other pertinent datathat is often accessed by a high percentage of transactions.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 onlinetransactional database.They are not of great concern in a read-onlyenvironment, such as a data warehouse or DSS.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.Disk04-- The database's sort area, on the other hand, is used as a temporary areafor SQL statement processing when sorting data, as in a GROUP BY or ORDER BYclause.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. TIP: Also note how the application tables and indexes have been placed oneach disk.Tables and indexes should be spread as much as possible.Notice that in Figure 15.3 the tables and indexes are stored on different devices.Youcan also see how a "Big Table" or index may be striped across two or more devices.Thistechnique 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.Inthis scenario, tables may be read while their corresponding indexes are being referenced,which increases the speed of overall data access.This example is really quite simple.Depending on the function, size, and system-relatedissues of your database, you may find a similar method for optimizing system resourcesthat works better.In a perfect world where money is no obstacle, the bestconfiguration is to have a separate disk for each major database entity, including largetables and indexes.NOTE: The DBA and system administrator should work together to balancedatabase space allocation and optimize the memory that is available on theserver.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 lettingthem fly.On the other hand, you won't get much reward for tuning a database whenthe application SQL is not fine-tuned itself.Professionals who tune databases for aliving often specialize on one database product and learn as much as they possibly canabout its features and idiosyncrasies.Although database tuning is often looked upon asa painful task, it can provide very lucrative employment for the people who trulyunderstand it.Performance ObstaclesWe have already mentioned some of the countless possible pitfalls that can hinder thegeneral performance of a database.These are typically general bottlenecks thatinvolve system-level maintenance, database maintenance, and management of SQLstatement processing.This section summarizes the most common obstacles in system performance and databaseresponse time
[ Pobierz całość w formacie PDF ]