Want to be Expert in Oracle Performance Tuning?

Practical Hands on of Oracle Performance Tuning:

I am always advising to my students to prepare your accurate check list for investigating of performance troubleshooting. But some of students are implementing and some of are not. Some of students are not preparing their own check list and they are relying on my check list. But my check list is general not deployed with proper environment.

Here I am explaining you live scenario incident reported before 10 days back from live Oracle database server. This incident is only for learning something from others failure. Scenario was taken from one of my student's server. We are not disclosing any scenario of our client and this scenario doesn't belong to any client's environment or incident.

Live Scenario:

Recently one of my candidates is getting performance issue in live database. Client was having solid performance issue of Oracle database and their all works were badly impacting.

Oracle Database Version: Oracle 11.2.0.2
Operating system: Cent OS 5 Linux 64 bit
Server RAM: 8 GB
Server Virtual Memory: 8 GB

Oracle DBA tried to investigate issue and find out SGA memory bottleneck. He investigated issue using AWR reports and statistics of Oracle 11g database. He got very low buffer cache hit ratio and found Cache buffer chain wait event in TOP 5 wait event statistics. Due to this reason, Oracle DBA increased buffer cache size.

After changes affecting, again Oracle 11g database is badly impacting with poor performance. Now again Oracle DBA checked AWR reports and found again low buffer cache hit ratio. He increased buffer cache size. After 2 - 3 hours, again database reacted with bad performance. Now Oracle DBA confused because he got poor buffer cache hit ratio and heavy wait in "free buffer inspected" wait event.

As per above scenario, Oracle DBA failed to find out root cause and failed to optimize performance of Oracle 11g database. The reason of failure was not he is not having knowledge of Oracle performance tuning but he hasn't prepare his own check list for investigating root cause of performance troubleshooting as per his technical environment. When you are getting low buffer cache hit ratio then you should need to check other impacts too like full table scan. Without checking other area, if you are increasing buffer cache size that is not solution. Off course, in some scenario, you need to increase buffer cache but for deploying this increment you need to check proper sizing of your server's RAM and paging/swapping activities. Without knowing of these areas, if you will be increasing SGA size of instance then it might give you reverse impact means your database will be badly impacting of slowness due to heavy paging and swapping.

Every time I am suggesting for preparing proper check list for performance tuning troubleshooting investigation and disaster recovery to my every pupil. But unfortunately, some of are forgetting to deploy because of lot of workload and not getting chance to face critical issue of Oracle database server slowness. Without proper check list, you won't be able to find out actual root cause of problem and troubleshoot same. If you want to be master in performance optimization then your one of task is creating proper check list for issue analysis and examination. For deploying this, you need to consider your technical environment and requirement. Without having accurate check list, you won't be able to troubleshoot or find out root cause.

Solid performance tuning skills are requiring in remote dba services and you need to get expertise and mastery in this techniques. During remote dba support and Oracle support in live environment, you need to develop these kind of expertise because it needs in every moment of your life. Dbametrix is expert remote dba team of remote Oracle DBA support services and provides professional high quality services. For more detail kindly contact our sale department.