| Performance tuning of Oracle Database |
Tuning an Oracle database is extremely important for a database administrator for monitoring the performance of the Oracle database and to achieve good results.
The tuning of a database involves a number of tools like documentation, training support, non-licensed tools and separate other consultants as well. Some of these tools are free while others might involves some costs as well.
It is said that the experienced you are the better you are while dealing with the database tuning and monitoring the performance .
When a DBA starts getting into the core of tuning a database he might need several kinds of information like :-
1. When did the problem actually start?
2. Is the problem occurring only to you or to others as well?
3. What is the impact that the problem is causing?
4. Were any changes made in the environment recently?
5. Do any workaround exists for the problem while it was searched by the researchers?
If one gets the above information then the pinpointing of the solution can be made much easier and the work can be simplified and can be prioritized much easily.
The problem that exists with the database can be due to multiple reasons We need to understand and then arrive at the possible solution to the problem.
The causes of the not tuned database can be as follows:-
• High CPU consumption
• High IO consumption
• Poorly performing SQL commands
• Database design issues
• Hardware problems
• Application problems
• Software bug
The tuning of the database can be classified into the following 3 types:-
1. Tuning the database before something goes wrong
2. Tuning after the problem arises
3. Tuning SQL
We will study and analyse each of them one by one and try to understand how each of them works:-
Tuning the database before something goes wrong
This is the tuning in case where no installation is done and we are preparing the database in advance so that nothing goes wrong in the future.
Planning the installation of your database software or a new database is an important step in performance tuning. Poor planning can lead to problems that can’t be surmounted easily by the tools at your disposal.
One should also be careful while selecting the software as the software we choose will decide whether it will result in proper performance or not.
It is not always recommended to use the latest and the most recent version of the Oracle softwares as it might involve some glitches or errors .Therefore it is important that there should be atleast one or two patch updates after which the version should be used to ensure the reliability.
It is recommended that an approved or certified version of the software should be used if it is integrated with third party software.
After one settles on a version of the Oracle software to use, one need to make sure that one has the latest maintenance pack or processes are applied.
It is also important that the hardware integrated should also be considered before integration with Oracle that they are well within budget and the cost is also incurred within the same.
Tuning a database after the problem arises
The database light incur problem even after a lot of precautions are taken as no matter how much preparation or configuration we do someday or somehow a problem will arise.
We will now consider that process that needs to be followed in the above case:-
For this we need certain information from the users who are facing the issues:-
When did this problem actually start?
Is the problem being faced by only the users or a number of them?
Can we replicate the problem?
The impact of the problem on the business.
The above information will provide a lot of help in verifying the problem and resolving it.
If not then we can engage a team for that matter to solve the problem ,a team of system admins to check the server architecture to help resolve the problem.
TUNING SQL
One of the best methods to tune SQL is explaining a plan which helps us to do what it says.
Explain Plan just does what it says. It exactly shows you the execution of the SQL statement and explains what each step of the plan is doing.
The Explain Plan is generated on a command line by using the SQL command EXPLAIN PLAN FOR as shown in Fig 9.1 .
- Fig 9.1
- Fig 9.2
The above fig 9.2 shows what all steps are being executed in running the command and what all costs are incurred in the same.
By just looking at the cost incurred in each step, one can easily determine what stage of the query execution is the most expensive (resource-wise, not money-wise).
One can then focus in lowering the costs of those particular steps and thus tuning SQL in turn.