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: , , , ,