Friends & Experts,
Examine the below trace file, which generated after running
the application for 7 mins. The person who give me this trace
file told me to tune this query, because his application takes
around 7 mins to complete one task which suppose to takes only
few second.
FROM
SOME_TABLE WHERE SOME_COLUMNE = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ---------- --------- --------
Parse 41 0.00 0.00 0 0 0 0
Execute 10846 3.99 4.17 0 0 0 0
Fetch 10846 23.04 24.39 0 264858 0 10646
------- ------ -------- ---------- ------- ---------- --------- --------
total 21733 27.03 28.57 0 244858 0 10646
Lets, first try to locate the problem.
Execution count=10846
Elapsed Time=28.57, So on avg one execution takes around 3ms....So this is good
Consistent read block=264858, So on avg 24 blocks/execution....So this seams OK..
Than, where the problem is?? Problem is in Application designing(coding).
Out of 7min of tracing only 29 second used by this query, So where the remaining
400 seconds spent by application. One should need to find out this, for this it is
require to look at detailed application tracing and/or statspack/AWR report.
One more thing I like to raise here: Why application executes this query 25 times
per second? If there are multiple session going on simultaneously then this is ok,
Otherwise we need to look at this also.(Actually I saw one application which executes sql
50 times per second because of poor application coding only.)
Showing posts with label Performance tuning. Show all posts
Showing posts with label Performance tuning. Show all posts
Wednesday, December 22, 2010
tkprof trace file analysis.....
Friday, November 12, 2010
How to start database tuning and how to know, what to tune?
How to start database tuning and how to know, what to tune?
This question is frequently asked by many people online as well as offline.
Actually there is no rule of thumb for performance tuning. Before starting tuning process, one should ask him self, why he want to tune? Is database really require to tune?
Sometime people read article from web regarding "Best practice for database PT" and start to implementing same on their production database. Don't do this. Always take some precautionary steps to avoid bottleneck but this is not mean that you should implement each "Best Practice" on your database.
Performance tuning practice is like medical practice. When you visit your doctor, he/she first ask you few questions to find out what is problem and what is CAUSE of problem, before suggesting any medicine. Just imagine what happen if he/she give you medicine for all general disease without diagnosing any thing. In that case do we really need doctor? No.
So, the important thing is diagnosis of problem. Without knowing actual cause of problem you can't resolve it for a long time. :(though, you can resolve it for a short time :). Suppose you have fever and because of that you have headache and body-ache. In this case root cause of problem is fever, so take medicine for fever, not for headache.
What I am trying to convey is, first identify symptom, root cause of problem and then take necessary steps to solve it. This will solve your problem for long time without any side effect.
Most of the time you have message like application is running slow from end user. After this, it is your task to find out why it is running slow. Most of the time you will found that slow response is because of poorly written sql query. So, try to rewrite it before upgrading hardware or software for this :)
This question is frequently asked by many people online as well as offline.
Actually there is no rule of thumb for performance tuning. Before starting tuning process, one should ask him self, why he want to tune? Is database really require to tune?
Sometime people read article from web regarding "Best practice for database PT" and start to implementing same on their production database. Don't do this. Always take some precautionary steps to avoid bottleneck but this is not mean that you should implement each "Best Practice" on your database.
Performance tuning practice is like medical practice. When you visit your doctor, he/she first ask you few questions to find out what is problem and what is CAUSE of problem, before suggesting any medicine. Just imagine what happen if he/she give you medicine for all general disease without diagnosing any thing. In that case do we really need doctor? No.
So, the important thing is diagnosis of problem. Without knowing actual cause of problem you can't resolve it for a long time. :(though, you can resolve it for a short time :). Suppose you have fever and because of that you have headache and body-ache. In this case root cause of problem is fever, so take medicine for fever, not for headache.
What I am trying to convey is, first identify symptom, root cause of problem and then take necessary steps to solve it. This will solve your problem for long time without any side effect.
Most of the time you have message like application is running slow from end user. After this, it is your task to find out why it is running slow. Most of the time you will found that slow response is because of poorly written sql query. So, try to rewrite it before upgrading hardware or software for this :)
And remember that the person suffering from last stage of cancer do not need medicine for headache.Share your thoughts for the same. Have a happy weekend..
Subscribe to:
Posts (Atom)