Oracle Performance Tuning is not tough
People say Oracle performance tuning is the toughest part in database administration. My thought is different. If you are having deep knowledge of Oracle architecture, operating system basics, application awareness, and some basics of networking/storage then it won’t be tough for you. In short, you can be handled and ability to troubleshooting of performance issues.
Every version, Oracle adds some more parameters and changes behavior of old parameters. Always ask a question to yourself about performance issue that,“ why today and didn’t happen yesterday?”. If you are able to find out an answer to this question then you will realize immediately what is an actual problem and what will be the solution to the same issue.
“Do you think only experienced DBA can do Oracle Tuning?”
Do you understand my point? If not understood then I am explaining with an example.
Today we got complain from application team about the slowness of database. Their all applications were stuck and some of the applications failed with error ORA-0060: Resource busy. When I tried to login into Unix server then I observed terrific slowness because my login command was also stuck. I tried to execute some Unix command for investigating like Top, PRSTAT, VMSTAT, SAR but output was reflecting after 5-8 minutes. Means server was busy. Due to this reason, I asked the question to myself, “Why today and didn’t yesterday?”. This was a new server and we upgraded database before 20 days back. From last 20 days, all had been working very well. But from last 3 days, a new database was created and today full backup executed with the presence of new database what did not exist before. Root cause found and issue resolved immediately.
If you are able to understand the exact issue and circumstances of performance degrade then you will be able to address accurately. If the root cause of slowness doesn’t find out then every effort will be useless and you won’t be able to solve the performance issue. Due to these all reasons, performance tuning is art and science. You can be understood easily if you are having some basic and good knowledge of Oracle, operating system, storage, application trend, and networking about performance tuning. Only experienced DBAs are able to handle, it is absolutely wrong. Fresher Oracle DBA and intermediate DBA are also able to tune performance issue.
If you get the involvement of server performance issues even though it is not Oracle related then you will get a good idea and knowledge how to handle it. Sometimes, people are blaming each other for application team blames to DBA, DBA blames to System Admin, System Admin blames to storage, and storage blames to DBA. This is the wrong situation and nobody will be able to address performance issue of Oracle server accurately.
Don’t worry if you are fresher Oracle DBA or intermediate DBA, just try to involve in this kind of critical situation and try to investigate. You will get success.
Wish you all the best.
10 Comments. Leave new
Exclusive blog and nice article. This kind of article is boosting our confidence as working fresher Oracle DBA. So many times I tried to tune our server but everytime I found wrong root cause of issue. But in future I will get success. Dear Gitesh Trivedi sir, kindly keep blogging for us. I am fan of you. Thanks a ton.
Nice article and explanation about fresher will be able to resolve performance issues. But We are not getting chance to do this task because it is monopoly of senior guys and they didn’t provide chance. What should need to do?
Hi,
You can get opportunity anytime may be in presence or absent of senior DBA. Don’t loose chance to perform best. Meanwhile, try to involve in every tasks either of database or operating system specific. You would get exclusive knowledge during your involvement. If senior DBA didn’t respond then ask questions to system administrators. Wish you all the best. Keep in touch with my blog because I am going to publish more in same issue.
An ORA-00060 is a deadlock detected; an ORA-00054 is a resource busy with NOWAIT specified. The first is usually a programming error while the second indicates that an object is being accessed exclusively by another process. Mixing those error numbers and messages is confusing to any new DBA, which was not the intent of this post.
A bit more detail in the description of the process would have been helpful to any new DBA — a ‘checklist’, of sorts, would be most beneficial in helping new DBAs begin to confidently address database performance issues.
If new DBA knows about ORA-00060 and ORA-00054 then what would be happening? Junior DBA will be able to get more knowledge using blogs, online forums. Proper guidance is requiring that how to gain more. Best is investigation using own checklist. It will be useful to every junior Oracle DBA.
Perfectly explained monopoly of senior database administrators. I like this article. I am working as intermediate Oracle DBA in telecommunication at New York. I know tuning but never got chance in real time of database administration tasks. Every time Sr.Oracle DBA performs tuning tasks and never shares anything to us. Thanks for sharing.
Hi sir, could you tell me what is the basic knowlege needed to learn oracle dba as a fresher?
Nitesh,
Fresher means what? If anyone wants to learn Oracle DBA then he or she needs at least SQL basics with computer knowledge.
nice article sir. i have started following your blogs, such well explained articles.
Thanks. Sorry I was out of country and didn’t find free time to update this blog. But very soon I will start again sharing.