We have a new DBA in training and part of my job is to mentor and demonstrate good Oracle practices. Here’s a write up I made for him on how I solved a performance issue.
Issue:
The BI team complained about a large query in out production BI database that had been running slowly for the past few days.
Steps to resolve:
| Action | Result | Tool |
| Find the past three days SQL ids | Found three different SQL ids for the past three days. | @find_sql_awr (uses dba_hist_* views) |
| Review execution plan for past three days | The optimizer saw each day as separate sql because bind variables are not used but it used the same plan anyway. The implication is that stats are stable over past three days. SQL Monitor showed a mismatch between expected rows and actual rows returned each step (this was apparent to me while the SQL was running). | @dplan_awr (uses dbms_xplan.display_awr) |
| Determine the driving tables | Identified three tables – queried dba_tables to find owner | Eyeballs J |
| Check statistics variability over the past 10 days | Two tables showed no change in stats for past 10 days. | @diff_table_stats (uses dbms_stats.diff_table_stats_in_history) |
| Gather fresh stats on all three tables | dbms_stats.gather_table_stats | |
| Check stats variability again | One indexed column on one table changed from having 2 unique values to 70. | @diff_table_stats |
| Generate a new execution plan, force matching | New plan’s calculated cost is less than half the original plan. However the real performance improvement is a lot better because the original plan was calculated on old statistics and the original calculated cost was reported as much, much lower than the actual cost. | SQL Tuning Advisor |
Discussion:
The underlying data changed over the past 10 days. The SQL was being re-parsed every day because bind variables are not used. The automatic statistics gathering job did not recalculate statistics on two of the tables because it has a 10% threshold. The data changed less than 10% for the whole table even though one column changed significantly. When the optimizer saw the new statistics, it changed the indexes it uses. I added the force matching option so that the optimizer wouldn’t need to re-parse every day just because bind variables are not used. Quote from BI team: “It is now running in single digit minutes as opposed to hours.”
Resources:
The cardinality presentation by Maria Colgan and the scripts presentation by Kerry Osborne provide more background on this method.
Tags: cardinality, script, SQL Monitor, statistics, tuning
3 Comments
François G.
01/06/2012
Hi Steve, very interesting, one question though.
I often notice on forums or social networks people offering solutions with dba_hist view, awr or SQL Tuning Advisors.
Aren't they part of the tunning pack which mean you are already in enterprise edition and you've purchased the tunning packs?
I think there are a lot of people out there, including me of course, running standard editions, do you think these tools are the only way to get it done, at least so fast?
Thank you again.
Steve Harville
01/06/2012
Hi Francois,
You are right, there are other ways to get the same results. We have standardized on the native Oracle tools and the Quest Software tools. What do you use?
Thanks for the comment!
Francois
01/06/2012
If I have the required licencing, I love the native dbconsole OEM. Rig now I'm using quest's toad DBA suite for usual tasks, with all the non licenced options deactivated.
For tuning cases I love DB optimizer which I discovered thanks to kyle hailey.
I can also count on confio's Ignite software since a few month and I've found it very usefull so far.
And of course scripts, there are tons of very god scripts written by rzcognized experts like tom kyte, kyle hailey,jonathan lewis,randolf geist,cary millsap....
No trackbacks yet.