Achieve Goal of Oracle Performance Tuning of Oracle Database Server

by / February, 2011 / Published in Oracle DBA Tips

oracle performance tuning

Everybody presume Oracle tuning is toughest part of Oracle database administration. Do you think same? Off course Oracle tuning is difficult and vital task for Oracle DBA.

If you are concentrating following tasks of Oracle database then tuning becomes easier and more effective. If you are working on largest Oracle database which is running 24/7 then tuning mission is more critical and obscure. Oracle DBA doesn't have any kind of magic stick that he or she can use to improve performance of Oracle server quickly. This is more complex and important task. Tuning database level for only one report or query is no way to tune Oracle database. For improving performance of single report or query, the whole database will be affecting after tuning database level.

Off course, as remote dba expert you need to have mastery in Oracle server tuning. Following basic concepts are more important and playing major role in Oracle database server tuning.

Constant Database Monitoring:

Large and critical database needs to be monitored constantly. Database trends and wait event analysis is more important to check daily and constant. Remote database monitoring is more effective because databases are residing in data center and Oracle DBA is managing more databases. Constant checking wait events, latch contentions, disk io, memory contentions, growth of database, growth of system resources, increase the demand of space, increase the demand of memory utilization, locking activities are monitoring of critical and 24/7 running databases. Here we are not discussing about important of database because once data is stored in database then important of database is increasing because it is collection of all data.

Operating system Monitoring:

Operating system monitoring is equally important in compare of database monitoring. Memory utilization, swapping, physical disk io & disk usages, operating system logs, space usage are important part of Oracle database administration. These tasks are also responsible to degrading performance of Oracle server. If any other applications are running on Oracle database server then it should be needed to monitor for effecting database performance. I have seen lots of Oracle DBAs, who are not looking this area and trying to tune Oracle database without any knowledge of operating system issues. Your all efforts might be wasting if you are not looking into operating system issues.

Oracle DBA Interview Questions

Network Monitoring:

Network monitoring is also important. If network is becoming slow and TNS packet failure are increasing then performance of Oracle database server is degrading. This is the important factor because all transactions, queries and reports are depending on network connectivity and using network protocol for satisfying data request.

Storage Monitoring:

Nowadays large databases are residing on storage server. Storage becomes common requirement for Oracle database server. Oracle database is located on either SAN or NAS. Basic concept of storage is necessary for Oracle DBA. As Oracle DBA you should at least know that where my databases are locating. I don't say that we have knowledge of storage but some knowledge is necessary.

Strong knowledge of Oracle Architecture:

As Oracle DBA we should need strong knowledge of Oracle database architecture and concept. If we don't have this knowledge or we have average knowledge then you won't get success in Oracle performance tuning. Strong and deep knowledge of Oracle fundamental is key requirement. Nowadays Oracle provides so many performance monitoring tools like AWR, STATSPACK reports, Data Dictionary views etc. All things depend on your way of understand those tools. If you check AWR report and see only top 5 wait events, then it is not sufficient. Otherwise Oracle doesn't provide other statistics in same report. Means we should need to consider all wait events statistics.

Good Application knowledge:

Oracle DBA has little bit application knowledge too. If you have some concept about your application, especially application flow then Oracle tuning is becoming more effective and easier. Without application knowledge sometime we are searching needle in darkest room without torch. Sometime for improving performance of single report, other report is affecting adversely because we do not know application flow.

Awareness of Oracle Secrets:

Secret of Oracle is the hidden parameters and default setting of those parameters. If you have good knowledge of some hidden parameters then you are able to judge root cause of performance bottleneck. Without investigating root cause of performance degradation, our tuning task not becomes more effective.

If we need to concentrate above all components then our Oracle database server performance tuning techniques will be effective and useful. If we don't consider above parts during our performance tuning goal achievement then may be our tuning task fail to improving Oracle database performance or it will be temporary solution. Therefore, we can say that performance tuning of Oracle is the art and science both. There are so many factors affecting on Oracle database server. For tuning Oracle database server we need consideration of each and every component which are directly or indirectly involve with Oracle database server. If we consider these all parts and keep all things in our mind then and only Oracle tuning is becoming more effective. Otherwise our all efforts are becoming useless. Sometime we got success but it is not final result of improving Oracle performance.

You need high expertise for managing different versions of Oracle database on various operating systems for Oracle server tuning. Expert remote dba team offers uninterrupted database operations execution smoothly. Dbametrix offers professional remote dba services for Oracle databases with strong service level agreement with lowest cost. You can get experienced senior level database services 24x7 with strong response time. Dbametrix is having expertise in all kinds of Oracle database support.

Dbametrix is world wide leader in remote dba support. Expert remote DBA team of Dbametrix is offering high quality professional Oracle DBA support with strong response time to fulfill your SLA. Contact our sales department for more information.