3:50 AM
Deep Dey
Hi
We are facing some performance issues in few reports .This is making huge impacts on the servers going down. We have been recommended not to execute any SQL before having “Explain Plan” for the Query.
When we take the explain plan we will be getting “COST” and other parameters, from which we need to analyze the utilization of servers, temporary space occupied, numbers of rows that will return etc. I need to come up with some threshold values where above that it’s not feasible to run the query.
From Internet I understood that “If consistent gets or physical reads are high relative to the amount of data returned, it indicates that the query is expensive and needs to be reviewed for optimization. For example, if you are expecting less than 1,000 rows back and consistent gets is 1,000,000 and physical reads is 10,000, further optimization is needed.”
Is there any other rules of thumb? if yes can you please share with us. What all are the parameters that will tell us the query is bad/good? Can you please give your inputs?
Before we attempt to address any of the points in the question it must be stated that Oracle performance tuning is a big subject and one on which many books have been written so it's impossible to provide a full course on performance tuning in response to one question.
Having said that there is an overview of Oracle performance tuning on our site and you can follow this link for a whole series of articles onOracle performance tuning.
As far as using Oracle Explain Plan goes, you need to know that whilst this will give you all sorts of facts about a particular SQL statement what it doesn't do is tell you whether the query is good or bad. All you can use this for is to compare one version of a query with another and to ensure that the planned execution path generated by the Oracle optimizer matches your expectations.
For example the plan may show that a particular index is not being used (i.e. the Oracle optimizer believes that it is cheaper to not use the index than to use it). Your role then is to determine why the index is not being used. This could be because of how the query is written (thereby disabling the use of the index), because statistics are not up to date or because the query is so complicated that the optimizer cannot (in a very short space of time) generate a good plan or for a variety of other reasons such as skewed data which the Oracle optimizer doesn't know about.
In short, Oracle performance tuning is all about managing trade offs (more indexes = faster reads, slower writes) and Oracles' explain plan utility just gives you a window into the Oracle optimizer's "thinking". It's up to you to interpret the plan and say whether that's a "good" plan or a "bad" one. Also, there are no hard and fast rules that say if the consistent gets is ten times the number of rows or if the number of physical read is 10,000 then that's bad.
Unfortunately Oracle performance tuning can be a slow process. First you have to gather the evidence then you have to propose a solution, finally you have to test your solution and possibly repeat the whole process if the performance of your Oracle applications didn't improve sufficiently or if you need to tune other queries.
Specifically looking at the ratio of consistent gets/physical reads to the amount of data returned can be useful - you obviously want to minimise the amount of i/o that Oracle does but you need to know your data. Oracle reads data in blocks not rows, so if each row is several blocks in size this ratio will be high. These ratios could also be high if your queries join many tables together so you need to know your application and your data.
In conclusion, your best bet is to take a deep breath(!), read the articles on performance tuning (overview of Oracle performance tuning and Oracle performance tuning, determine where the problem is and then use explain plan to determine how the Oracle optimizer would run your queries. You can then create different version of those queries (perhaps using hints or restructuring them) or optimise the queries using other methods (such as creating extra indexes). Knowledge about the application and the data is essential. Finally also readOracle's own performance tuning guide.
We are facing some performance issues in few reports .This is making huge impacts on the servers going down. We have been recommended not to execute any SQL before having “Explain Plan” for the Query.
When we take the explain plan we will be getting “COST” and other parameters, from which we need to analyze the utilization of servers, temporary space occupied, numbers of rows that will return etc. I need to come up with some threshold values where above that it’s not feasible to run the query.
From Internet I understood that “If consistent gets or physical reads are high relative to the amount of data returned, it indicates that the query is expensive and needs to be reviewed for optimization. For example, if you are expecting less than 1,000 rows back and consistent gets is 1,000,000 and physical reads is 10,000, further optimization is needed.”
Is there any other rules of thumb? if yes can you please share with us. What all are the parameters that will tell us the query is bad/good? Can you please give your inputs?
Before we attempt to address any of the points in the question it must be stated that Oracle performance tuning is a big subject and one on which many books have been written so it's impossible to provide a full course on performance tuning in response to one question.
Having said that there is an overview of Oracle performance tuning on our site and you can follow this link for a whole series of articles onOracle performance tuning.
As far as using Oracle Explain Plan goes, you need to know that whilst this will give you all sorts of facts about a particular SQL statement what it doesn't do is tell you whether the query is good or bad. All you can use this for is to compare one version of a query with another and to ensure that the planned execution path generated by the Oracle optimizer matches your expectations.
For example the plan may show that a particular index is not being used (i.e. the Oracle optimizer believes that it is cheaper to not use the index than to use it). Your role then is to determine why the index is not being used. This could be because of how the query is written (thereby disabling the use of the index), because statistics are not up to date or because the query is so complicated that the optimizer cannot (in a very short space of time) generate a good plan or for a variety of other reasons such as skewed data which the Oracle optimizer doesn't know about.
In short, Oracle performance tuning is all about managing trade offs (more indexes = faster reads, slower writes) and Oracles' explain plan utility just gives you a window into the Oracle optimizer's "thinking". It's up to you to interpret the plan and say whether that's a "good" plan or a "bad" one. Also, there are no hard and fast rules that say if the consistent gets is ten times the number of rows or if the number of physical read is 10,000 then that's bad.
Unfortunately Oracle performance tuning can be a slow process. First you have to gather the evidence then you have to propose a solution, finally you have to test your solution and possibly repeat the whole process if the performance of your Oracle applications didn't improve sufficiently or if you need to tune other queries.
Specifically looking at the ratio of consistent gets/physical reads to the amount of data returned can be useful - you obviously want to minimise the amount of i/o that Oracle does but you need to know your data. Oracle reads data in blocks not rows, so if each row is several blocks in size this ratio will be high. These ratios could also be high if your queries join many tables together so you need to know your application and your data.
In conclusion, your best bet is to take a deep breath(!), read the articles on performance tuning (overview of Oracle performance tuning and Oracle performance tuning, determine where the problem is and then use explain plan to determine how the Oracle optimizer would run your queries. You can then create different version of those queries (perhaps using hints or restructuring them) or optimise the queries using other methods (such as creating extra indexes). Knowledge about the application and the data is essential. Finally also readOracle's own performance tuning guide.
0 comments:
Post a Comment