Performance Tuning for new oracle DBA’s
In my experience and from what i normally hear from others about their experiences , performance tuning is the most challenging part of oracle database administration.
you are a new oracle dba and you want to know how to handle any performance tuning issues.
In this article we will look at the overall view of what you should know before you can start tuning your queries or troubleshoot any performance issues which your boss assigns to you.
Performance tuning terminologies that a new oracle dba should be aware of:
explain plan and how to use explain plan
interpreting explain plan
tracing a session and different ways to trace a session
optimizer and the role of the optimizer in handling the sql queries
execution plan and how to read a execution plan
data dictionary tables where you could find information about a sql and its performance statistics
optimizer hints and how you can use them to manipulate performance of your queries
trace events that you can use to trace the optimizer behavior when it is building an execution plan
optimizer session and instance parameters that can influence the behaviour of your sql queries performance
table and index statistics and the different type of statistics that influence the optimizer behaviour
awr report , addm report , awr sql report , ash report and how to use those reports to understand the performance of your database and sql queries
statspack and the difference between statspack and awr report
what is the use of flushing shared pool and buffer cache?
what are the various optimizer access paths and whats the use of the different access paths?
As you can see above , one should really have a grasp of the above concepts to tune the database queries to perform efficiently.
I will cover each of the above terminologies in separate articles.I have my own notes which i would have got from google or metalink during the past few years but which now really helps me as a fast reference. I will be posting that notes as separate articles in the performance tuning section of this blog albeit in an ordered way.
In real time , suddenly some sql queries that were performing very well in the past might suddenly behave quite badly. The users then raise an Incident ticket and when i get the ticket the process i normally follow is as below
1)user complains that the application is slow and an incident ticket is created.
2)the ticket comes to me.
3)i have to identify the query which is taking time. so i speak to the user to ask her/him which application module is giving a performance problem.
4)once the user confirms, then i normally speak to the application team who developed the application if they know which sql statements are called from that module.
5)if the applicaton team already knows the sql then i will create a awr report and try to identify the sql statement in the awr report.
6)if the application team does not know that, then i call the user and ask her/him to run the application process again and once she informs me i will enable a trace of the users session.
7)once the user confirms that her process finishes then i disable the trace for the users session.
8)then i take a awr report for this time duration.
9)i identify the query from the awr report that takes most of the time and then look at the underlying tables and whether the tables and indexes have up to date statistics.
10)if they dont have the latest statistics then the first thing i do is to gather the latest statistics for those tables and indexes.
11)then i flush the shared pool and try to run the query myself or again ask the user to try again.
12)if the problem still persists then i look into the dba_hist_sqlstat and v$sql_plan tables to identify if this particular sql had different execution plans in the past.
13)Then i compare the bad and the new execution plans and the access paths to the tables in the execution plan.
14)if necessary before running the query i will enable tracing events for the optimizer to understand why it is going ahead with the bad execution plan.
15)sometimes the query has to be rewritten , some optimizer hints have to be added , or simply a different bind variable value might have caused the optimizer to pickup a bad plan so just flushing the shared pool will help in this case.
16)if the query has to be rewritten or if optimizer hints have to be added then as it is a production system , i cant update the code on the fly..so i will forward the ticket to the development dba’s to test the code change and do a proper patch release.
as you can see , there might be different reasons for performance issues and i have not covered all the reasons as it is not practical . but i tried to give you an overview of the common things that i do and which help me in tuning the sql queries.
In the next articles i will cover each of the above sections in an indepth way . please post any comments if something seems wrong or not clear.
