Cost Based Optimizer - Let your optimizer do it for you!!!(Easier said than done)
Easier said than done ! I have a 9i RAC environment (SLES; Suse Linux) - its been up for a while with no statistics updated except when the first roll out happened. I come onboard and start crying we need statistics updated (its a COST Based Optimizer OLTP system); some queries are suffering because of bad execution plans which in turn is because stats are not updated.
All right, so convinced management that we should go ahead and get our stats updated and keep it updated for a COST Based Optimizer system. And guess what while 80% of the queries do better - 20% suffered and started taking a very long time after statistics were updated (In a PreProduction environment).
So the process has not been simple (considering we have n number of Applications on the system) - we have had to go through many test cycles to identify bad sql and tune them. I have not found an easier way to get past this problem ; have not heard of a better solution so far from my friends in the Oracle community as well. I do know that AWR on 10g will make this effort a little easy - are there any solutions for 9i????
All right, so convinced management that we should go ahead and get our stats updated and keep it updated for a COST Based Optimizer system. And guess what while 80% of the queries do better - 20% suffered and started taking a very long time after statistics were updated (In a PreProduction environment).
So the process has not been simple (considering we have n number of Applications on the system) - we have had to go through many test cycles to identify bad sql and tune them. I have not found an easier way to get past this problem ; have not heard of a better solution so far from my friends in the Oracle community as well. I do know that AWR on 10g will make this effort a little easy - are there any solutions for 9i????
posted by Vidya Balasubramanian at
10:56 AM
4 Comments:
I'm interested to see how you were generated statistics in this case. Can you please do a cut-and-paste?
Also, can you give and example of how you identified the bad SQL as well as what type of SQL issues you encountered?
How did you go about tuning these? What steps did you take?
Regards,
By
mathewbutler, at 8:18 AM
sorry abt the late response - have been busy and trying to get back to blogging
ok the first thing is to get the support of your test team , they can be of immense help to create baseline reports
1)clone prod to preprod. create a baseline report. Nothing fancy, an excel spreadsheet that records reponse times
for different parts of an Application based on a specific load test on preprod. We used this approach for one application
at a time.
2)Update stats for the Application you are running your baseline Reports for.
The below sql was used:
exec dbms_stats.gather_schema_stats('schema name',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt =>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=>TRUE);
3)Rerun your baseline Report, you should be able to compare this report with your Report in step1 and immediately
identify specific parts of the Application that degraded in performance signifiacntly after the stats were updated.
4)For instance ; we had one batch job that did horribly after the stats were updated.
5) So I ask the test team to rerun the batch job in preprod. While the job is running; I capture the sql that waits for a long time to complete.
6) So , now you have the sql. Tune the sql :) Easier said than done!!!
- in my experience it is very rarely that the sql does badly because of a FTS. Most of the developers are aware that they need to avoid FTS and
use indexes. The case I have run into so far
(i) the developer did not realize that with all teh numerous joins in the query ; a cartesian join was happeneing ; resulting in way more records
being processed than waht was needed.
(ii)IN replaced with a join has helped in some queries.
(iii)In some cases adding more indexes have helped
(iv)a 30million row table and not Partitioned ...you start crying Partitioning Licecnce then (unfortunately the licence is not a part of Std edition).
(v)see if you rewrite the query better
(vi)if users start complaining about instantaneous response times - then you need to start considering baselines;if you are on a NAS as opposed to a SAN what kind of performance can you
expect ; we all would love instantaneous response time ; in reality given your current infrastructure / limitation what can you expect and what best you can do has to be
clearly communicated.
By
Vidya Balasubramanian, at 4:25 PM
>>- are there any solutions for 9i????
In 9i, can you not run statspack?
By
Rahul, at 8:56 AM
Likewise apologies for the tardy response.
From my perspective, I agree that you need to have an understanding of the performance profile of your system based on business process. I think this is your spreadsheet. Preferably, your application logs the start and end times for these business processes as this greatly simplifies things. If not you have a deal of testing to carry out.
As rahul (above) suggested. Using STATSPACK will provide you the history of what has changed ( along with SQL plans if configured correctly ).
Pair the above with some specific testing for the business process that has the most business impact and then you can start identifying the problem SQL, how it was being executed before your change, how it is being executed with the new statistics and next why it is slow ( based on the stats ). From there you can decide whether there is something wrong with how you are generating the stats ( maybe a missing hiostogram ) , the data in the sample you are gathering for, or just a poorly written query for which you were previously lucky.
I think that tuning needs to be an iterative and proactive process. The more information that is logged by the application about it's runtime performance the easier it is to be proactive. Otherwise all you are left with are trending reports using STATSPACK - which can be very useful, but only if you can pull out the detail from the aggregetared data.
Good luck with the blog.
Regards,
By
mathewbutler, at 4:22 AM
Post a Comment
<< Home