Optimizing Database Performance: The Example of Tuning an Oracle Database Tuning a database involves utilizing computing resources to process a...

Optimizing Database Performance:The Example of Tuning an Oracle DatabaseThe tuning process determines the amount and kind of database activity that will be generated by a given database. These activities will tend to be specific to each database type, and to a lesser extent, to each database. For example, a transaction-oriented database uses different computing resources from a database that supports data warehouse transactions. Tuning resources are broad and include the entire computing architecture; especially the network, application server and database server. CPU resources, memory, and disk devices must also be utilized efficiently. We use the Oracle DBMS as an example because it has many ‘knobs’ that may be adjusted to maximize operating system resources. The tuning process consists of collecting and analyzing performance statistics of the current database computing environment and then making any necessary changes to the database or the computing architecture. Historically, the tuning process has been a collection of manual and automated activity and has been mostly reactive in nature. Recent tuning support and products have enabled DBA’s to take a more proactive approach to performance tuning. Consider the following scenario:When a database application slows down, all parts of the application architecture must be reviewed to determine the cause of the slow down. Figure 1 contains an excerpt from a forty nine page STATSPACK report showing BUFFER POOL Statistics.Buffer Pool Statistics for DB: TEST-DB Instance: TEST-DB Snaps: 28 -29-> Standard block size Pools D: default, K: keep, R: recycle-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32kWrite BufferBusyWaits--- ---------- ----- ----------- ----------- ---------- ------- -------- ------1-------------------------------------------------------------Figure 1: Sample Buffer Pool StatisticsThrough analysis of the STATSPAK report the DBA determines if more memory buffers are needed or if a SQL statement needs to be tuned to reduce the number of I/O’s it is generating.Determining how many buffers to allocate or increase/decrease is an important part of tuning an Oracle instance. STATSPAK has a Buffer Pool Advisor metric that shows the relationship of the number of the buffers in the buffer cache to the estimated physical read factor. For example, the TEST-DB currently has 28,028 buffers allocated to it, making the buffer size 224 megabytes with a read factor of one and the number of physical reads at 4,844,540 (see Figure 2). The physical reads can be reduced by over a million if the number of buffers is increased from 28,028 to 36,036. This reduction of physical reads will require an additional sixty four megabytes of operating system memory, but may be well worth the investment of system resources.Buffer Pool Advisory for DB: TEST-DB Instance: TEST-DB End Snap: 29-> Only rows with estimated physical reads >0 are displayed-> ordered by Block Size, Buffers For Estimate (default block size first)EstimatedPhysical Reads--- ------------ ----- ---------------- ------------- ------------------24,033,16216,378,83613,438,40911,862,44510,866,85910,258,8079,903,5769,670,7019,133,8697,184,1556,026,5665,520,7845,179,8254,844,5404,577,3774,352,5784,085,6933,608,9773,422,8513,327,195-------------------------------------------------------------Figure 2: A Buffer Pool Advisory for the TEST-DBResponse time is defined as the total amount of time a transaction consumes:Response Time = Service Time + Wait Time, whereService time is the time a transaction consumes while executing its function, andWait time is the time the transaction spends waiting to execute.If the transaction originates from an application server, the operating resources of the application server must also be factored into the overall tuning equation. To give you an idea of current tuning assistance, the example of Oracle 10g is presented.With Oracle 10g the tuning process has become a “Performance Diagnosis” process. This process is based on the Oracle 10g Intelligent Self-Management Infrastructure, which is part of the database kernel. The goal of the Self-Management Infrastructure is to help the database learn about its operational environment and perform dynamic adjustments to the database to create an optimal database environment. The database takes corrective action on behalf of a slow running transaction and attempts to take corrective action. Oracle responds to current computing resources being used in order to decide about the best remedy for a slow database transaction. The key to the database being self-learning and self-managing is the Automatic Workload Repository (AWR). The 10g database takes a snapshot every hour (out of the box default) of the current work load and statistics and stores them in AWR. AWR keeps seven days worth of data, but the DBA can modify the default settings to retain more or less data and modify the snapshot interval. The Automatic Database Diagnostic Monitor (ADDM) is built into the database kernel and uses the data collected in the AWR to perform a diagnostic check on the database to ensure that it is running in an optimal fashion. ADDM identifies potential tuning issues and provides solutions based on the statistics gathered in AWR. This means that the corrective action will be customized to the problem that was defined in the AWR statistics without the DBA having to perform the analysis function of the tuning process. In summary, the DBA’s goal is to provide the end user with a database environment that meets or exceeds business processing requirements. The tuning process consists of both reactive and proactive elements and the DBA must know how to effectively use these tools to create an environment that allows the end user to accomplish the business activity in a timely manner. Current developments in DBMS packages are allowing DBAs to take more proactive tuning steps to keep their databases performing to expectations.Case Study Exercise:Article above talks about tuning an Oracle database. Summarize the given article in your words in detail explaining the following aspects of the paper:a.Different tuning tools and importance of their roles for Oracle 8i thru Oracle 10g.b.Role of the DBA in Oracle database tuning process.c.What is the key to database being self-learning and self-managing?