Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 6 June 2012

Oracle 9i DBServer Tuning

Database Tuning is an optimization of database performance. It is necessary to effectively manage a growing database and also maintain the performance of applications. Application designer, Application developer, Database Administrator and System Administrator are involved to do this task. The role and responsibilities depends on the organization infrastructure. It may be some where a single person may be responsible for more than one task.
The database must be designed correctly to ensure that database performance does not deteriorate. A poor design database may require more time to tune the database. The main focus of application designer is on the logical structures and sizing involved with the applications as they are designed.
An Application Developer is responsible for an effective application design. An Application tuning can only achieved through effective application design and development.
A DBA is responsible to configure the database and monitor and document system activity (gather statistics) so that problems of system performance can be identified and corrected as quickly as possible. If problems develop, it is usually the database administrator (DBA) who makes the first attempt at resolving them. Thus, the DBA should have an accurate overview of all the applications and their interaction with each other on the database.
A System designer is responsible to configure the system in which the database and application are located such as Network connectivity, System specification and other devices.
Common Tuning Problem
1. Bad Session Management: The Bad session Management is related to the middleware (log on procedure). It slows down the system.
2. Bad cursor Management: It is usually results from Programmer sides.
For Example:
Create or replace procedure prco1
Select * from HRMS. Pay_Payment_Master where Employee_number=5205;
Select * from HRMS. Pay_Payment_Master where Employee_number=8402;
Here in above example we do not make use of bind variable in where clause so if the cursor sharing is SIMILAR or FORCE then both statement will parse to a single cursor even the SQL Text is different.
3. Bad Relational Design: Bad relational designs usually result from over normalization.
For Example:
a)     Wrong Column into Table: This requires many table joins to produce the output that could have been obtained from a single table.
b)     Unnecessary Table Join: Sometimes unnecessary join operation can also impact on database as a Bad Relational Design.
Overall Tuning Goal: When you start performing tuning you goal or motive should be such that
Reducing response time (SQL Tuning)
Providing high throughput as per response time.
Optimizing the utilization of CPU and Memory.
Minimizing contention.
Goal of SQL Tuning:
One of the goals of tuning an Oracle9i server is to ensure that SQL statements access the smallest possible number of Oracle blocks needed to complete their work. This speeds up the response time for the statement and reduces the impact on other queries and Data Manipulation Language (DML) statements being executed in the database.
Select * from HRMS. Pay_Payment_Master
Where payslip_date > ‘01-JAN-99’ and Employee_number = 5205;
Goal of Memory utilization:
Another goal of tuning is improved memory utilization. Frequently used data blocks cached in memory can be readily accessed. The availability of needed data in memory reduces the number of overall physical reads. Memory utilization impacts database and operating system performance, and instance hit percentages. You can increase memory utilization by reducing paging and swapping. An alternative way to improve memory utilization is to ensure that users share code, such as frequently used SQL statements, within memory areas. This helps to avoid reparsing and duplicate storage of such SQL statements.
Goal of Minimizing Contention:
A further goal of tuning the Oracle9i server is to ensure that reading and writing are performed as rapidly as possible. For example, separating different segment types across tablespaces can minimize write contention. Maximizing the number of blocks that are obtained during a read operation minimizes the number of read operations required. Good application tuning and database design help ensure that users have access to data when required thus minimizing contention problem on database.
Backup Procedures:
Finally, the database should be tuned to optimize the speed in which backup procedures and housekeeping tasks are performed. This protects data and minimizes the impact of those tasks on database performance.
Note: If any problem came across the DBA who has to make the first attempt at performing a diagnosis and providing a solution.
Tuning Steps:
As a DBA it is your responsibility to ensure server performance does not negatively affect the availability and usability of database. To ensure effective performance of DBserver you must follow certain steps to tune them.
There is six steps involve in tuning Oracle 9i database:
1. Tune architecture and design
2. Tune Applications (Tune long running Queries, Transactions)
3. Tune Memory: (Tuning the System Global Area (SGA) and user process memory)
4. Tune I/O: (configuring the distribution of data files, segment types, and tablespaces)
It also involves monitoring the interaction between data files and memory during reads and writes. The interaction between files can be tuned by separating the most frequently accessed data into different tablespaces and separating tables from their related indexes. You can also separate rollback segments from tables and data dictionary objects from all other objects.
5. Reduce Contention: (shared pools, lock, latches)       
Reducing contention between actions in the database. Since an Oracle database is normally used by multiple users, tuning is needed to minimize the time that processes have to wait until a resource is available. Resources that can involve contention include blocks, shared pools, locks, and latches.
6. Tune the OS
Note: The rationale for this approach is that by making improvements early in the sequence you can avoid problems in the later steps. For example, if your application uses many full table scans, this may show up as excessive I/O. However, resizing the buffer cache or redistributing disk files is not necessary if you can rewrite the queries so that they access only four blocks instead of 4,000.
Your first steps as DBA after getting problem:
You collect the data from users regarding the scope of the problem. You must focus on obtaining factual information. This may include information such as application tables, time and frequency of operation, and actual error codes or screen displays.

After you have defined the problem, you collect a full set of operating system and database statistics. Compare these statistics with your baseline statistics. Examine the differences to determine what has changed in the system. This is likely to be the location of the problem.
The next step is to look for common performance errors. Compare the list of differences obtained from the collected statistics with common performance errors. Determine whether one of these errors has occurred in your system.
The fourth step is to build a conceptual model or hypothesis with the above information. The purpose of this hypothesis is it assists you while working with database.
After you have developed the hypothesis, make the change that was identified. You should implement only one change at a time because multiple changes can make it difficult to determine the effect of each change.
Finally check that the bottleneck has been resolved. Collect all the statistics again and compare between the current and the baseline sets. If your hypothesis is correct, then the current set of statistics will be a better match with the baseline set than the previous set.
Proactive Task Performed by DBA
1. Check the logs:
A DBA must regularly check alert. log file as the error message help you detecting the nature of problem.
2. Check the initialization Parameter:
You can change the settings of initialization parameter depending on the nature of applications. For example, for an online transaction processing (OLTP) environment, you should set CURSOR_SHARING to Similar, whereas for a data warehouse database, it should be set to Exact.
3. Ensure that CPU and Network are not overloaded:
A DBA should regularly check for CPU and disk queuing, disk utilization, memory swapping, and network overload. If the application is reasonably well tuned, the DBA can add more hardware to improve the performance. CPU utilization is the most important operating system statistic in the tuning process. Monitor the CPU utilization for the entire system and for each individual CPU on multiprocessor environments. Utilization for each CPU can indicate single-threading and scalability issues.
4. Avoid disk queuing and memory swapping:
Check the current response time and the length of the disk queues. Use these statistics to determine whether the disk is performing optimally or if it is overworked.
If current response time > 20 milliseconds
Then the disk is performing badly or is overloaded
If Length of disk queue > 2
Then the disk can be a potential bottleneck for the system
Check the virtual memory statistics. You must ensure very little paging and swapping activity in the system. The performance of the system degrades rapidly and unpredictably when paging or swapping occurs. Also, you must use network statistics to determine whether a network or network interface is overloaded or not performing optimally.
5. Tune response Time:
To tune the response time, analyze system performance in terms of work done and the time spent waiting for work. The Oracle server provides a set of “Wait Events” to record the activity of processes that are idle or waiting.

Hope it will help to understand basic of Performance Tuning. Next we will come with detailed discussion on different aspect of Tuning in oracle 9i database.


Post a Comment