Strategies for Minimising SQL Execution Plan Instability

Execution Plan Instability – What is the problem?

The Oracle Optimizer is a complex piece of software and with every release it becomes more complex.

RBO

In the beginning, the Optimizer was rule-based.

The Optimizer had a ranked list of heuristics used to optimize a query, picking the lowest ranked rule available.

This rule-based mode, whilst still in use with some internal Oracle dictionary queries, has been unsupported since version 10.1.

This means that no code changes have been officially made to the RBO and no bug fixes are provided. There are many features that the RBO is unaware of.

Applications should not still be using the rule-based optimizer.

CBO

The Cost-Based Optimizer is designed to evaluate a number of execution plans and pick the one estimated to be the fastest.

Many of the CBO features are designed to combat common optimization problems and many of these problems occur where development teams are not necessarily aware of the full implications of the features that they are using.

These built-in default behaviours roughly conform to the infamous 80%:20% rule in that most of the time they do a good job but they are not infallible.

Bind Variables, Literals and Histograms

Most of the features which are deliberately designed such that plan instability is difficult to avoid stem from the decision to use bind variables or literals (NOT that they should be mutually exclusive) and the interaction of the former with histograms.

In article 1 of his Philosophy series, Jonathan Lewis neatly sums this up:

Histograms and bind variables exist for diametrically opposed reasons – they won’t work well together without help

Fundamentally, bind variables exist to provide shareable plans.

Bind variables should be used where we are not interested in getting specific optimizations for differing parameters.

Literals should be used where we want the Optimizer to pay particular attention to data distribution and skew for the specific parameters supplied.

A SQL statement will/should often have some parameters which should be literals and some which should be binds.

From this point onwards, there has been a whole raft of features designed to treat the misuse of one or the other.

In Oracle 8.1.6, histograms were introduced.

Histograms exist to provide specific data distribution information, particularly relevant to specific parameters.

Also, in that version, we got CURSOR_SHARING – targeted at applications using literals instead of binds such that SQL which was identical part from the use of binds was rewritten to use sytem-generated bind variables.

Then in 9.2, we got bind variable peeking.

This feature was introduced so that the optimizer could peek at the values supplied at parse time and use data distribution information specific to these parse-time values to generate an execution plan which suited those values.

In addition and at the same time, through these various versions to present day, we have had the default behaviour of DBMS_STATS statistic gathering to let the database decide which columns it will create histograms on, based on the SQL which has been running.

This means that new histograms can suddenly spring up – or existing histograms unexpectedly disappear – on all sorts of columns. This can be problematic on columns with large numbers of distinct values AND particularly so on join cardinalities where there may be a mismatch of histograms on both sides of the join.

Ever since this point, we have had a conflict of interest in feature usage and an ever increasing number of additional functionality to battle against this conflict – adaptive cursor sharing, cardinality feedback, etc, etc

Finally, the education message got blurred or lost somewhere along the line to the extent that a lot of systems blindly overuse bind variables because of the perceived performance impact of using literals.

This situation is not helped by the fact PL/SQL is designed to encourage bind variables.

Using supplied parameters as literals means using some construct of dynamic SQL, not difficult but nevertheless an added complexity and also another feature which is often blindly discouraged.

SQL Execution Plan Instability – Is SPM a viable approach?

SPM Overview

In Oracle 11g, Oracle introduced SQL Plan Baselines as a SQL Plan Management feature.

The core attraction of this functionality is that you can “lock in” a particular plan or plans for a SQL statement. This stores a set of outline hints and a specific plan hash value in relation to a specific SQL signature.

The Optimizer then uses that set of hints to try to reproduce the desired plan. If it can it will, if it can’t it will reject the hintset.

Additionally, the Optimizer completes its best-cost execution plan optimization anyway so that it can provide the ability to do a controlled evolution of baselined plans in the event that the lower-cost plan that it would have used is different and better performing than the baselined plan.

To use this the database parameters (session or system) just need to be configured to capture plans into a baseline and then use them.

There is flexibility to this capture/use. You can capture everything as it is generated; you could capture everything from memory now and/or at regular intervals and import into a baseline; you could capture everything from AWR into a SQL Tuning Set and then import into a baseline; or you could capture everything from another environment and export/import it into another.

And at any point, you can turn off capture and continue to use those which you currently have – this usage continues to capture any lower cost plans that the optimizer would have generated for any existing baselined plans

For a more detailed look at SPM and a comparison with SQL Profiles, see documentation.

Sounds good – why isn’t it the silver bullet for system-wide stability?

This approach might be good enough, but it is not a cast-iron guarantee of system-wide stability.

There are a number of reasons why not.

New & Changed SQL

Firstly, you need to have captured all your plans already. If you get brand new SQL, then there will be nothing in SPM.

Depending on your application, this may be a significant concern.

For example, consider an application making heavy usage of Hibernate which generates SQL statements.

A minor change in the domain model can mean a change of system-generated table alias in many statements.

As a result, you may suddenly get a lot of brand new SQL and significant numbers of baselined statements which you will now never see again.

What are the baselined plans based on? Are they the best plans? The only plans ever used?

If you suddenly baseline the plans for a large number of SQL statements, you are dictating which plan is to be used.

The plans will be based on the parsed plans in memory or in AWR at the time.
Are these the best plans?

Does/should this SQL statement always use this plan?

Are there normally multiple plans for different bindsets?

What if you normally get benefit from adapative cursor sharing?

ACS and baselines

What if your application benefits from adapative cursor sharing?

Sure, you can baseline multiple plans but these plans have no ACS information.

As soon as that ACS information is no longer in memory (as happens), there is no shortcut in a baseline to regain that, you still have to have the multiple executions required for the optimizer to recognize that which plans to use for which bindsets.

Parsing overhead

Widespread usage of baselines might, depending on your system performance profile, have a significant impact on the parsing resources.

This is because it always generates a best-cost plan anyway.

Then if that is not the baselined plan, it will use the baselined hintset to try to generate the specific plan hash.

In addition, it that is not possible, it will use just the optimizer_features_enable hint to try to generate the required plan.

So, you might in a heavily-baselined system to be doing 2x the parse work of a non-baselined system.

This might well be easily accommodated but there are systems where this would cause a problem.

Integration with development and testing processes

A SQL Plan Baseline is tied to a SQL statement based on the statement’s EXACT_MATCHING_SIGNATURE – a hash of the SQL statement which has been case and space normalized.

If a SQL statement materially changes, the baseline no longer applies.

How aware are developers of the presence of baselines?

And how to integrate with the development process?

How will our release process deal with baselines?

And if baselining large numbers of plans is being considered, then we have to think about where these will be generated.

The natural implication (of widespread baseline usage) is that new functionality being promoted to Prod would have a set of tested, baselined plans accompanying it and these would presumably have to be generated in a Prod-like environment which included Prod-like volumes.

SQL Execution Plan Instability – Decision Time

There is a decision to be made.

(And/or perhaps there is often a conceptual over-simplification by senior management to combat? Or at least a lack of deep understanding of the beast that we’re dealing with here?)

Do you want the Optimizer to try to get a better execution plan sometimes?

If the answer is yes, then you have to accept that it will get it wrong from time to time.

In particular, the various feedback and adaptive mechanisms are designed to recognize that they have got it wrong.

BUT they need that problematic execution in the the first place – sometimes more than one – to recognize that fact.

That one problematic execution could be your next Priority 1 incident.

In addition, the feedback mechanism is not perfect and it still can make subsequent executions worse in some circumstances.

SQL Execution Plan Instability – Turn it off?

IF your primary goal is plan stability – and I think many teams would claim this is this their goal but they do not embrace the implications of this – then perhaps a better decision is to turn off the various features which cause or combine to cause most of the problems of instability.

Appropriate usage of binds, appropriate usage of literals

Choose whether to use a bind variable or a literal as is appropriate for the value / column / SQL statement.

A SQL statement might have a mix of both.

DBMS_STATS defaults

A METHOD_OPT of FOR ALL INDEXED COLUMNS SIZE AUTO is an immediate red flag. This is never a good setting.

FOR ALL COLUMNS SIZE AUTO without table-level preferences (SET_TABLE_PREFS) is another red flag.

As an interim step, consider use FOR ALL COLUMNS SIZE REPEAT to lock in the current histogram usage.

The end goal should be to have table level preferences set for all tables.

This relies on knowing your data, your data distribution, your code, and knowing which histograms make sense (i.e. for skewed columns) – it will be far fewer than gathered by default.

For columns with significant numbers of distinct skew, it may be necessary to manually craft the histograms.

Volatile tables

Volatile tables should have stats set to an appropriate setting to generate appropriate plans for all situations and then those stats should be locked.

Stats which are gathered at any point during the volatility cycle may be good or may be problematic.

Similarly dynamic sampling can only see the data at the time of hard parse – you might be lucky and this is better than stats which say 0 rows but it can be a time bomb.

Turn off optimizer features

Turning off optimizer features might be best done via a LOGON trigger and turning such off for a subset of application users. These features include:

  • Bind Variable Peeking – off via _optim_peek_user_binds = false
  • Cardinality Feedback and ACS – should be disabled by turning off bind variable peeking but off via _optimizer_use_feedback = false, _optimizer_adaptive_cursor_sharing = false, _optimizer_extended_cursor_sharing_rel = “none”
  • Dynamic Sampling – optimizer_dynamic_sampling to 0
  • 12c additional adaptive features – e.g. adaptive execution plans

Additionally it probably makes sense to turn off the adaptive direct path read behaviour or anything with the word adaptive or dynamic in it or associated to it

This functionality decides on whether to do full segment scans via the buffer cache or not and the behaviour is a runtime decision depending on the size of the object relative to the buffer cache AND depending on how much of the segment is currently in the cache.

  • Adaptive direct path reads – _serial_direct_read = always

All too often I’ve seen a concurrently executed SQL statement switch to a “bad” plan involving a full table scan delivered via direct path reads stress out the IO subsystem because of the number of concurrent executions of that query which then affects performance across the DB.

Caveat

The actions above are still not sufficient to guarantee plan stability but, for this goal above all else, this is likely to be the most appropriate action.

However, to further guarantee stability it is still likely that some degree of hinting – whether via manual hints, sql profiles or baselines – might be necessary for small numbers of SQL statements where the intial cost-based plan is not appropriate e.g. GTTs and other situations but it should be small number of statements.

SQL Execution Plan Instability – Summary & Opinion

The actions discussed above are made on the basis that we want to minimise the chance of execution plan instability at all costs.

By making this decision, we are prioritizing stability over all the features within Oracle designed to generate better plans for specific situations, sets of binds, etc.

Personally, I always recommend going with the default behaviour until such time as it causes a significant problem.

I also always recommend matching the scope of a solution to the scope of a problem.

For example, if we have a problem with one or two SQL statements, the potential solutions should be limited to those SQL statements.

We should never be making changes with a potential system-wide impact for the sake of a couple of SQL statements.

And even parameter changes can be injected to specific SQL statements either via a SQL Patch or via manual hints.

In my opinion, having multiple plans for a SQL statement is expected.

But what is under-appreciated is the extent to which this is normal.

These are normally only noticed when they cause a problem and the significant number of plans which regularly change plans without impact tend to go unnoticed.

It is also my opinion that SQL execution issues occur mostly when the SQL is badly written or when incompatible features are combined – e.g. peeked binds together with histograms – and I prefer to tackle that root cause rather than a generic concept of plan instability being inherently problematic.

A future path?

Might it be a good idea for there to be a built-in choice of optimizer “umbrella” modes – maximum performance vs maximum stability which would turn on/off some of the features above and/or adjust their sensitivity/aggressiveness?

Regexp hungry for CPU? Real time sql monitoring shows just how

Not exactly a snappy blog post title…

First up, an execution plan showing the old problem of how the costs of a scalar subquery are not properly accounted for, even in the latest 11gR2 releases.
Read more of this post

Poll: SQL Plan Management

I’m interested in what SQL Plan Management features people are actively using.
Read more of this post

Plan Problem with Partition Top N

Yesterday I was having issues with a poor choice of plan by the optimizer when trying to do a top N of a partitioned table.

SQL> CREATE TABLE t1
  2  (col1  varchar2(1)  not null
  3  ,col2  number       not null
  4  ,col3  varchar2(50) not null)
  5  PARTITION BY LIST (col1)
  6  (  
  7   PARTITION P1 VALUES ('A'),
  8   PARTITION P2 VALUES ('B'),
  9   PARTITION P3 VALUES ('C'),
 10   PARTITION P4 VALUES ('D')
 11  );

Table created.

SQL> INSERT 
  2  INTO   t1
  3  SELECT DECODE(MOD(ROWNUM,4),1,'A',2,'B',3,'C',0,'D') 
  4  ,      100000000+ROWNUM
  5  ,      LPAD('X',50,'X')
  6  FROM   DUAL
  7  CONNECT BY ROWNUM <= 1000000;

1000000 rows created.

SQL> CREATE UNIQUE INDEX i1 ON T1 (col2,col1) LOCAL;

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T1');

PL/SQL procedure successfully completed.

Now COL2 is unique but to be a unique index on a partitioned table, I have to include the partition key.

I want the top N of COL2 in descending order.

SQL> select /*+ gather_plan_statistics */
  2        *
  3  from (select col2 from t1 t order by col2 desc) 
  4  where rownum <=10;

      COL2
----------
 101000000
 100999999
 100999998
 100999997
 100999996
 100999995
 100999994
 100999993
 100999992
 100999991

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  cwt7s3cmmw3vb, child number 0
-------------------------------------
select /*+ gather_plan_statistics */       * from (select col2 from t1
t order by col2 desc) where rownum <=10

Plan hash value: 738905059

-------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |     10 |00:00:00.49 |    2698 |
|*  1 |  COUNT STOPKEY           |      |      1 |        |     10 |00:00:00.49 |    2698 |
|   2 |   VIEW                   |      |      1 |   1000K|     10 |00:00:00.49 |    2698 |
|*  3 |    SORT ORDER BY STOPKEY |      |      1 |   1000K|     10 |00:00:00.49 |    2698 |
|   4 |     PARTITION LIST ALL   |      |      1 |   1000K|   1000K|00:00:00.65 |    2698 |
|   5 |      INDEX FAST FULL SCAN| I1   |      4 |   1000K|   1000K|00:00:00.27 |    2698 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)

Seems a strange option to me to do a FAST FULL SCAN and in the process visiting 2698 buffers.

Wouldn’t we much rather this did a descending index scan?

SQL> select /*+ gather_plan_statistics */
  2        *
  3  from (select /*+ index_desc (t i1) */ col2 from t1 t order by col2 desc) 
  4  where rownum <=10;

      COL2
----------
 101000000
 100999999
 100999998
 100999997
 100999996
 100999995
 100999994
 100999993
 100999992
 100999991

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  f1ytxtp8bdgx6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */       * from (select /*+
index_desc (t i1) */ col2 from t1 t order by col2 desc) where rownum
<=10

Plan hash value: 2521435439
-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |      1 |        |     10 |00:00:00.01 |      12 |
|*  1 |  COUNT STOPKEY                  |      |      1 |        |     10 |00:00:00.01 |      12 |
|   2 |   VIEW                          |      |      1 |   1000K|     10 |00:00:00.01 |      12 |
|*  3 |    SORT ORDER BY STOPKEY        |      |      1 |   1000K|     10 |00:00:00.01 |      12 |
|   4 |     PARTITION LIST ALL          |      |      1 |   1000K|     40 |00:00:00.01 |      12 |
|*  5 |      COUNT STOPKEY              |      |      4 |        |     40 |00:00:00.01 |      12 |
|   6 |       INDEX FULL SCAN DESCENDING| I1   |      4 |   1000K|     40 |00:00:00.01 |      12 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - filter(ROWNUM<=10)

I’m glad that actually kicked in there as I wanted because on the real-world example that this relates to, it was being distinctly stubborn and at one point I thought I might have to resort to something distinctly ugly like:

SQL> select /*+ gather_plan_statistics */
  2         *
  3  from (select col2
  4        from   (select col2
  5                from   t1 partition (p1) t
  6                order by col2 desc) 
  7        where rownum <=10
  8        union all
  9        select col2
 10        from   (select col2
 11                from   t1 partition (p2) t
 12                order by col2 desc) 
 13        where rownum <=10
 14        union all
 15        select col2
 16        from   (select col2
 17                from   t1 partition (p3) t
 18                order by col2 desc) 
 19        where rownum <=10
 20        union all
 21        select col2
 22        from   (select col2
 23                from   t1 partition (p4) t
 24                order by col2 desc) 
 25        where rownum <=10
 26        order by col2 desc)
 27  where rownum <=10;

      COL2
----------
 101000000
 100999999
 100999998
 100999997
 100999996
 100999995
 100999994
 100999993
 100999992
 100999991

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  8y7d958mpah49, child number 0
-------------------------------------
select /*+ gather_plan_statistics */        * from (select col2
from   (select col2               from   t1 partition (p1) t
   order by col2 desc)       where rownum <=10       union all
select col2       from   (select col2               from   t1 partition
(p2) t               order by col2 desc)       where rownum <=10
union all       select col2       from   (select col2
from   t1 partition (p3) t               order by col2 desc)
where rownum <=10       union all       select col2       from
(select col2               from   t1 partition (p4) t
order by col2 desc)       where rownum <=10       order by col2 desc)
where rownum <=10

Plan hash value: 1726521473
---------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |      1 |        |     10 |00:00:00.01 |      12 |
|*  1 |  COUNT STOPKEY                    |      |      1 |        |     10 |00:00:00.01 |      12 |
|   2 |   VIEW                            |      |      1 |     40 |     10 |00:00:00.01 |      12 |
|*  3 |    SORT ORDER BY STOPKEY          |      |      1 |     40 |     10 |00:00:00.01 |      12 |
|   4 |     UNION-ALL                     |      |      1 |        |     40 |00:00:00.01 |      12 |
|*  5 |      COUNT STOPKEY                |      |      1 |        |     10 |00:00:00.01 |       3 |
|   6 |       VIEW                        |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|   7 |        PARTITION LIST SINGLE      |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|   8 |         INDEX FULL SCAN DESCENDING| I1   |      1 |    250K|     10 |00:00:00.01 |       3 |
|*  9 |      COUNT STOPKEY                |      |      1 |        |     10 |00:00:00.01 |       3 |
|  10 |       VIEW                        |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  11 |        PARTITION LIST SINGLE      |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  12 |         INDEX FULL SCAN DESCENDING| I1   |      1 |    250K|     10 |00:00:00.01 |       3 |
|* 13 |      COUNT STOPKEY                |      |      1 |        |     10 |00:00:00.01 |       3 |
|  14 |       VIEW                        |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  15 |        PARTITION LIST SINGLE      |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  16 |         INDEX FULL SCAN DESCENDING| I1   |      1 |    250K|     10 |00:00:00.01 |       3 |
|* 17 |      COUNT STOPKEY                |      |      1 |        |     10 |00:00:00.01 |       3 |
|  18 |       VIEW                        |      |      1 |     10 |     10 |00:00:00.01 |       3 |
|  19 |        PARTITION LIST SINGLE      |      |      1 |    250K|     10 |00:00:00.01 |       3 |
|  20 |         INDEX FULL SCAN DESCENDING| I1   |      1 |    250K|     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - filter(ROWNUM<=10)
   9 - filter(ROWNUM<=10)
  13 - filter(ROWNUM<=10)
  17 - filter(ROWNUM<=10)

But then the stubbornness disappeared and I couldn’t reproduce.
Conclusion – I must have been doing something stupid.

On a related note, whilst I was messing about, I noticed this.
Spot the trivial difference (not that it matters):

1
SQL> select *
  2  from (select col2 from t1 t order by col2 desc)
  3  where rownum <= 10;

........

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  8uy80z5da45ct, child number 0
-------------------------------------
select * from (select col2 from t1 t order by col2 desc) where rownum
<= 10

Plan hash value: 3155368986

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |
|*  1 |  COUNT STOPKEY               |      |       |       |            |          |
|   2 |   VIEW                       |      |    10 |   130 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN DESCENDING| I1   |  1000K|  6835K|     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)


21 rows selected.

SQL> select *
  2  from (select /*+ index_desc(t i1) */ col2 from t1 t order by col2 desc)
  3  where rownum <= 10;

........

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  3asrnmxg6bqsj, child number 0
-------------------------------------
select * from (select /*+ index_desc(t i1) */ col2 from t1 t order by
col2 desc) where rownum <= 10

Plan hash value: 3155368986

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |
|*  1 |  COUNT STOPKEY               |      |       |       |            |          |
|   2 |   VIEW                       |      |    10 |   130 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN DESCENDING| I1   |    10 |    70 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)


21 rows selected.

SQL> 

Sql tuning request

Without knowing anything about the problem in advance, I thought it would be good to do a walkthrough post of a sql tuning request.

But now that I’m done I’m unconvinced as it’s probably too long, the query too meaningless and the real time sql monitoring text output too unreadable in a blog post.

I’m always reluctant to post real, specific application issues because I’m never sure how well they translate and illustrate the desired points unless you convert them to a standalone test case.

Let’s see how it goes.

  • The idea is to touch on the broad strategies that I’m going through.
  • The scope of the solution should match the scope of the problem – so for a single problem query, table design and current indexes, stats and histograms should be considered as set in stone.
  • Ideally we want to avoid hinting as much as possible or at least stick to acceptable hints.
  • And if we’re going to manually intervene, we’re not particularly interested in what the current production plan is nor the usage of any plan stability features to preserve it (unless it’s better than we can do ourselves).

This is from an 11gR2 testing environment, 11.2.0.3 to be specific.

Having done the latest merge of a production code release (9.2.0.8) into our 11gR2 environment (upgrade ETA March), a report has been reported as slow, taking about 1.5 minutes in production and some 20 minutes in the 11gR2 environment.

So, having traced the report and found that the driving query is the prime suspect, let’s get some feedback on the performance of the SQL statement.

Here’s the original sql statement to give some context – I’ve commented out most of the columns because they don’t add much other than just length to the post.

SELECT ... some columns ...,
       ... a function call ...,
       ... some more columns ...
FROM   isbk,
       inst,
       bsta,
       isco,
       sdol,
       borg,
       book,
       isdm,
       rule
WHERE  book.book_num = rule.book_num
AND    rule.rule_type_code = 'IBK'
AND    isbk.inst_num = inst.inst_num
AND    inst.inst_num = bsta.inst_num
AND    inst.inst_num = isco.inst_num
AND    isco.inst_num = isdm.inst_num
AND    isco.xcod_code = 'SEDL'
AND    isco.inst_num = sdol.inst_num
AND    sdol.xcod_code = 'ISIN'
AND    borg.borg_num = inst.issuer_num
AND    book.book_num = isbk.book_num
AND    bsta.current_mat_date >= TO_DATE(v_bus_date, 'yyyymmdd')
UNION ALL
SELECT ... some columns ...
FROM   inst, 
       bsta,
       isco,
       sdol,
       borg,
       isdm,
       inix
WHERE  inst.inst_num = bsta.inst_num
AND    inst.inst_num = isco.inst_num
AND    isco.inst_num = isdm.inst_num
AND    isco.xcod_code = 'SEDL'
AND    isco.inst_num = sdol.inst_num
AND    sdol.xcod_code = 'ISIN'
AND    borg.borg_num = inst.issuer_num
AND    bsta.current_mat_date >= TO_DATE(v_bus_date, 'yyyymmdd')
AND    isco.inst_num = inix.inst_num
AND    inix.xcod_code = 'NIX'
AND    NOT EXISTS (SELECT 1
                   FROM   isbk,
                          rule,
                          book,
                          bsta
                   WHERE  rule.rule_type_code = 'IBK'
                   AND    book.book_num = rule.book_num
                   AND    book.book_num = isbk.book_num
                   AND    inst.inst_num = isbk.inst_num
                   AND    inst.inst_num = bsta.inst_num
                   AND    bsta.current_mat_date >= TO_DATE(v_bus_date, 'yyyymmdd'));

Looking at the query and the repetition of tables and joins between the two UNION ALL parts, it looks like a classic case of two sets of disparate driving data that then need to be joined to the same additional tables.

Ironically, expanding ORs out into UNIONs is a common initial tuning step for performance problems with OR predicates.

Here’s what little extra schema knowledge might be useful:

  • INST has a pk of INST_NUM.
  • ISCO has an n:1 relationship with INST.
  • ISDM has an n:1 relationship with INST.
  • BORG has a 1:1 relationship with INST.ISSUER_NUM.
  • BSTA has a 1:1 relationship with INST.
  • ISBK has a n:1 relationship with INST and a n:1 relationship with BOOK.
  • BOOK has a pk of BOOK_NUM.
  • RULE is a table used for generic filtering rules, in this case related to BOOK.BOOK_NUM.

Let’s use Real-Time SQL Monitoring (usual license caveats apply) to see what the current performance story is.

SELECT dbms_sqltune.report_sql_monitor(<sql_id>) FROM DUAL;

If there’s one downside to RTSM, it’s a bit too wide for these blog posts :(
Maybe the RTSM pictures would have been better?

Anyway, I’ve sacrificed some of the columns in the report and tried to shrink the font (there’s a scrollbar at the bottom of the plan window).


Global Stats
===========================================================
| Elapsed |   Cpu   | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
===========================================================
|    1094 |    1094 |    0.29 |     0.20 |    60 |   257K |
===========================================================

SQL Plan Monitoring Details (Plan Hash Value=1036829859)
===============================================================================================================================
| Id |                Operation                |       Name         |  Rows   | Execs |   Rows   | Activity | Activity Detail |
|    |                                         |                    | (Estim) |       | (Actual) |   (%)    |   (# samples)   |
===============================================================================================================================
|  0 | SELECT STATEMENT                        |                    |         |     1 |    29225 |          |                 |
|  1 |   NESTED LOOPS                          |                    |       1 |     1 |    29225 |          |                 |
|  2 |    NESTED LOOPS                         |                    |       1 |     1 |    29205 |          |                 |
|  3 |     NESTED LOOPS                        |                    |       1 |     1 |    29205 |          |                 |
|  4 |      VIEW                               | VW_JF_SET$AD8EBC08 |    6147 |     1 |    29205 |          |                 |
|  5 |       UNION-ALL                         |                    |         |     1 |    29205 |          |                 |
|  6 |        NESTED LOOPS                     |                    |       9 |     1 |    28719 |          |                 |
|  7 |         NESTED LOOPS                    |                    |      53 |     1 |    28722 |          |                 |
|  8 |          NESTED LOOPS                   |                    |     370 |     1 |    31674 |          |                 |
|  9 |           NESTED LOOPS                  |                    |     370 |     1 |    31674 |          |                 |
| 10 |            NESTED LOOPS                 |                    |       3 |     1 |        1 |          |                 |
| 11 |             INDEX RANGE SCAN            | RULE_IDX_2         |       3 |     1 |        1 |          |                 |
| 12 |             TABLE ACCESS BY INDEX ROWID | BOOK               |       1 |     1 |        1 |          |                 |
| 13 |              INDEX UNIQUE SCAN          | BOOK_IDX           |       1 |     1 |        1 |          |                 |
| 14 |            INDEX RANGE SCAN             | ISBK_IDX           |     116 |     1 |    31674 |          |                 |
| 15 |           TABLE ACCESS BY INDEX ROWID   | INST               |       1 | 31674 |    31674 |          |                 |
| 16 |            INDEX UNIQUE SCAN            | ISTR_PK            |       1 | 31674 |    31674 |          |                 |
| 17 |          TABLE ACCESS BY INDEX ROWID    | BSTA               |       1 | 31674 |    28722 |          |                 |
| 18 |           INDEX UNIQUE SCAN             | BSTAC_IDX          |       1 | 31674 |    31654 |          |                 |
| 19 |         INDEX RANGE SCAN                | ISCO_4_IDX         |       1 | 28722 |    28719 |          |                 |
| 20 |        HASH JOIN                        |                    |    6138 |     1 |      486 |          |                 |
| 21 |         HASH JOIN ANTI                  |                    |    5716 |     1 |    54961 |    67.16 | Cpu (732)       |
| 22 |          HASH JOIN                      |                    |   24385 |     1 |    83680 |          |                 |
| 23 |           INDEX RANGE SCAN              | ISCO_4_IDX         |    170K |     1 |     377K |          |                 |
| 24 |           HASH JOIN                     |                    |    144K |     1 |     266K |          |                 |
| 25 |            TABLE ACCESS FULL            | BSTA               |    144K |     1 |     266K |     0.09 | Cpu (1)         |
| 26 |            TABLE ACCESS FULL            | INST               |      1M |     1 |       1M |          |                 |
| 27 |          VIEW                           | VW_SQ_1            |     53M |     1 |       4G |     5.60 | Cpu (61)        |
| 28 |           HASH JOIN                     |                    |     53M |     1 |       4G |    27.06 | Cpu (295)       |
| 29 |            MERGE JOIN CARTESIAN         |                    |    458K |     1 |     266K |          |                 |
| 30 |             NESTED LOOPS                |                    |       3 |     1 |        1 |          |                 |
| 31 |              INDEX RANGE SCAN           | RULE_IDX_2         |       3 |     1 |        1 |          |                 |
| 32 |              INDEX UNIQUE SCAN          | BOOK_IDX           |       1 |     1 |        1 |          |                 |
| 33 |             BUFFER SORT                 |                    |    144K |     1 |     266K |          |                 |
| 34 |              INDEX FAST FULL SCAN       | BSTA_IDX_2         |    144K |     1 |     266K |          |                 |
| 35 |            TABLE ACCESS FULL            | ISBK               |      2M |     1 |       2M |          |                 |
| 36 |         INDEX RANGE SCAN                | ISCO_4_IDX         |    170K |     1 |    32983 |          |                 |
| 37 |      TABLE ACCESS BY INDEX ROWID        | BORG               |       1 | 29205 |    29205 |          |                 |
| 38 |       INDEX UNIQUE SCAN                 | BORG_PK            |       1 | 29205 |    29205 |          |                 |
| 39 |     INDEX RANGE SCAN                    | ISCO_4_IDX         |       1 | 29205 |    29205 |          |                 |
| 40 |    INDEX RANGE SCAN                     | ISDM_IDX           |       1 | 29205 |    29225 |          |                 |
===============================================================================================================================

There’s a lot in this report.

First thing you might have spotted is the Join Factorisation going on @ step 4 as indicated by the name VW_JF*.

Maybe you’re always wary of the MERGE JOIN CARTESIAN … BUFFER SORT? Not always a problem of course but where there’s trouble you’ll often find her (in real production scenarios, I find that this mechanism is a problem not so much when there are missing join conditions – because these are rarely found in production code – but rather as a valid join mechanism but where the rowsource estimates are significantly inaccurate).

If you look at the SQL, you’ll see a function call as well.

There are also a whole bunch of estimates that are significantly off.

Where to start?

There are four main areas of questioning:

  1. What’s taking all the time? Are there some particular steps in the plan which are more problematic than others?
  2. If estimates are inaccurate, where do they go most wrong or go wrong first?
  3. Which predicates eliminate the most data? i.e. it’s rarely a good thing to join thousands upon thousands of rows only to do a late filter in the plan to reduce it down to a few handfuls. Aka eliminate early.
  4. What is the simplest / quickest / least invasive change that can be made to significantly improve performance? And will it actually be sufficient?

From “Activity %”, I hope it’s clear from the report that all the time is taken up by the bottom half of the UNION ALL.

  • The rowsource cardinality estimates are not that accurate.
  • Plus we find in that second half our old friend the MJC+BS.
  • And we’re just burning CPU down there.

So, I’d like to isolate that bottom half of the UNION ALL and run it standalone.

However, in a clear indication of the issues with it, it won’t run standalone – It’s just blown 30+ gig of temp space.

But it returns only 486 rows so let’s try to get a runnable standalone version.

Before we start looking at possible solutions, let’s start to ask questions about the query logic itself, keeping an eye out for redundant tables and joins and asking ourselves whether this is the best way to word the question we think is being asked.

Best way to start that is

So, we’ve got a few “filters” predicates, a couple of “join predicates” and a NOT EXISTS correlated subquery.

The logic of the subquery is the first thing that jumps out at me.
1. BOOK seems redundant
We join RULE to BOOK and BOOK to ISBK all by BOOK_NUM and ISBK is correlated to the outer INST by INST_NUM.
We do no filtering by any BOOK attribute so it serves no purpose so, let’s remove BOOK, join RULE straight to ISBK.

2. The subquery filtering by BSTA.CURRENT_MAT_DATE is irrelevant.
This is the same filter as in the outer select.
It doesn’t make sense.
Ignoring the actual evaluation order of all these predicates, we should effectively only be checking the NOT EXISTS against INST_NUMS that have passed the outer BSTA filter. So, by definition, this particular predicate in the subquery will always be true. Why repeat it? This is a mistake.
The only thing this subquery should be doing is checking they’re not in the RULE/ISBK combo. So, let’s remove that.

If we run just comment out the tables and joins as per suggestion above, then that bottom query runs in a couple of seconds:


Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    1.12 |    1.12 |     0.00 |     2 |  73879 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=2734204492)
==========================================================================================================
| Id |         Operation          |   Name     |  Rows   | Execs |   Rows   | Activity | Activity Detail |
|    |                            |            | (Estim) |       | (Actual) |   (%)    |   (# samples)   |
==========================================================================================================
|  0 | SELECT STATEMENT           |            |         |     1 |      489 |          |                 |
|  1 |   HASH JOIN RIGHT ANTI     |            |   38162 |     1 |      489 |          |                 |
|  2 |    VIEW                    | VW_SQ_1    |     555 |     1 |    31674 |          |                 |
|  3 |     NESTED LOOPS           |            |     555 |     1 |    31674 |          |                 |
|  4 |      INDEX RANGE SCAN      | RULE_IDX_2 |       3 |     1 |        1 |          |                 |
|  5 |      INDEX RANGE SCAN      | ISBK_IDX   |     176 |     1 |    31674 |          |                 |
|  6 |    HASH JOIN               |            |   38189 |     1 |    28700 |          |                 |
|  7 |     HASH JOIN              |            |   37626 |     1 |    28700 |          |                 |
|  8 |      HASH JOIN             |            |   35036 |     1 |    86956 |   100.00 | Cpu (1)         |
|  9 |       HASH JOIN            |            |   32625 |     1 |    87212 |          |                 |
| 10 |        HASH JOIN           |            |   31565 |     1 |    83593 |          |                 |
| 11 |         INDEX RANGE SCAN   | ISCO_4_IDX |    170K |     1 |     377K |          |                 |
| 12 |         HASH JOIN          |            |    144K |     1 |     266K |          |                 |
| 13 |          TABLE ACCESS FULL | BSTA       |    144K |     1 |     266K |          |                 |
| 14 |          TABLE ACCESS FULL | INST       |    796K |     1 |     796K |          |                 |
| 15 |        TABLE ACCESS FULL   | ISDM       |      1M |     1 |       1M |          |                 |
| 16 |       INDEX RANGE SCAN     | ISCO_4_IDX |    170K |     1 |     861K |          |                 |
| 17 |      INDEX RANGE SCAN      | ISCO_4_IDX |    170K |     1 |    32983 |          |                 |
| 18 |     TABLE ACCESS FULL      | BORG       |    328K |     1 |     328K |          |                 |
==========================================================================================================

Note that I’ve had to hint this with the /*+ monitor */ hint because by default this now executes beneath the default threshold for monitoring.

It looks like we’ve arrived at our quickest/simplest change just by going through the query quickly trying to understand the question it’s asking. No hints required.

So this is would be a good place to stop, thoroughly test the change, validate the original and the changed results and move on to something else.

We should drop it back into the UNION ALL and see what we get:


Global Stats
===========================================================
| Elapsed |   Cpu   | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
===========================================================
|    4.68 |    4.68 |    0.38 |     0.00 |    60 |   230K |
===========================================================

SQL Plan Monitoring Details (Plan Hash Value=4171599168)
===============================================================================================================================
| Id |                Operation                |       Name         |  Rows   | Execs |   Rows   | Activity | Activity Detail |
|    |                                         |                    | (Estim) |       | (Actual) |   (%)    |   (# samples)   |
===============================================================================================================================
|  0 | SELECT STATEMENT                        |                    |         |     1 |    29225 |          |                 |
|  1 |   NESTED LOOPS                          |                    |       1 |     1 |    29225 |          |                 |
|  2 |    NESTED LOOPS                         |                    |       1 |     1 |    29205 |          |                 |
|  3 |     HASH JOIN                           |                    |       1 |     1 |    29205 |          |                 |
|  4 |      TABLE ACCESS FULL                  | BORG               |    328K |     1 |     328K |          |                 |
|  5 |      VIEW                               | VW_JF_SET$AD8EBC08 |   26182 |     1 |    29205 |          |                 |
|  6 |       UNION-ALL                         |                    |         |     1 |    29205 |          |                 |
|  7 |        NESTED LOOPS                     |                    |       9 |     1 |    28719 |          |                 |
|  8 |         NESTED LOOPS                    |                    |      53 |     1 |    28722 |          |                 |
|  9 |          NESTED LOOPS                   |                    |     370 |     1 |    31674 |          |                 |
| 10 |           NESTED LOOPS                  |                    |     370 |     1 |    31674 |          |                 |
| 11 |            NESTED LOOPS                 |                    |       3 |     1 |        1 |          |                 |
| 12 |             INDEX RANGE SCAN            | RULE_IDX_2         |       3 |     1 |        1 |          |                 |
| 13 |             TABLE ACCESS BY INDEX ROWID | BOOK               |       1 |     1 |        1 |          |                 |
| 14 |              INDEX UNIQUE SCAN          | BOOK_IDX           |       1 |     1 |        1 |          |                 |
| 15 |            INDEX RANGE SCAN             | ISBK_IDX           |     116 |     1 |    31674 |          |                 |
| 16 |           TABLE ACCESS BY INDEX ROWID   | INST               |       1 | 31674 |    31674 |          |                 |
| 17 |            INDEX UNIQUE SCAN            | ISTR_PK            |       1 | 31674 |    31674 |          |                 |
| 18 |          TABLE ACCESS BY INDEX ROWID    | BSTA               |       1 | 31674 |    28722 |          |                 |
| 19 |           INDEX UNIQUE SCAN             | BSTA_IDX           |       1 | 31674 |    31654 |          |                 |
| 20 |         INDEX RANGE SCAN                | ISCO_4_IDX         |       1 | 28722 |    28719 |          |                 |
| 21 |        HASH JOIN RIGHT ANTI             |                    |   26173 |     1 |      486 |          |                 |
| 22 |         VIEW                            | VW_SQ_1            |     555 |     1 |    31674 |          |                 |
| 23 |          NESTED LOOPS                   |                    |     555 |     1 |    31674 |          |                 |
| 24 |           INDEX RANGE SCAN              | RULE_IDX_2         |       3 |     1 |        1 |          |                 |
| 25 |           INDEX RANGE SCAN              | ISBK_IDX           |     176 |     1 |    31674 |          |                 |
| 26 |         HASH JOIN                       |                    |   26187 |     1 |    28693 |          |                 |
| 27 |          HASH JOIN                      |                    |   24385 |     1 |    83680 |    33.33 | Cpu (1)         |
| 28 |           INDEX RANGE SCAN              | ISCO_4_IDX         |    170K |     1 |     377K |          |                 |
| 29 |           HASH JOIN                     |                    |    144K |     1 |     266K |          |                 |
| 30 |            TABLE ACCESS FULL            | BSTA               |    144K |     1 |     266K |          |                 |
| 31 |            TABLE ACCESS FULL            | INST               |      1M |     1 |       1M |          |                 |
| 32 |          INDEX RANGE SCAN               | ISCO_4_IDX         |    170K |     1 |    32983 |          |                 |
| 33 |     INDEX RANGE SCAN                    | ISCO_4_IDX         |       1 | 29205 |    29205 |          |                 |
| 34 |    INDEX RANGE SCAN                     | ISDM_IDX           |       1 | 29205 |    29225 |          |                 |
===============================================================================================================================

So we could leave it there.

However, I wouldn’t blame you if you wanted to go further, even if we’re bordering on Compulsive Tuning Disorder:

  • I don’t really like leaving behind a plan that’s got significantly inaccurate estimates – it leaves behind too much of a future threat.
  • And maybe we can also look at the original query and the direction the Join Factorisation was indicating and take it further? How about doing the UNION ALL a bit earlier and then do one lot of joining to the shared tables from the original SQL?
  • Let’s use some dynamic sampling to improve some single table cardinality estimates but then perhaps let’s go too far and add some join estimate adjustments (I say too far because I’m going to use opt_estimate but it’s undocumented and I’m not recommending it but if you do use it, then use it in conjunction with qb_name).
  • And let’s get rid of the function call which, take it from me, in this case is effectively a single table outer join lookup anyway.
SELECT  /*+
          find_me
          monitor
          qb_name(main)
          dynamic_sampling(sdol@main 4)
          dynamic_sampling(isin@main 4)
          */
        ... some columns ...
FROM   (SELECT /*+
                 qb_name(union1)
                 opt_estimate(join(isbk@union1 rule@union1) scale_rows=100)
                 */
               isbk.inst_num  inst_num
        ,      inix.inst_code nix_code
        ,      (SELECT book.name
                FROM   book
                WHERE  book.book_num = rule.book_num) book_name
        FROM   isbk
        ,      rule
        ,      inix
        WHERE  rule.rule_type_code    = 'IBK'
        AND    rule.book_num          = isbk.book_num 
        AND    inix.inst_num      (+) = isbk.inst_num
        AND    inix.xcod_code     (+) = 'NIX'
        UNION ALL
        SELECT /*+
                 qb_name(union2)
                 dynamic_sampling(inix@union2 4)
                 */
               inix.inst_num  inst_num
        ,      inix.inst_code nix_code
        ,      NULL           book_name
        FROM   inix
        WHERE  inix.xcod_code         = 'NIX'
        AND    NOT EXISTS (SELECT /*+ 
                                    qb_name(sub1)
                                    opt_estimate(join(isbk@sub1 rule@sub1) scale_rows=100)
                                    */
                                  1
                           FROM   isbk
                           ,      rule
                           WHERE  rule.rule_type_code    = 'IBK'
                           AND    rule.book_num          = isbk.book_num
                           AND    isbk.inst_num         = inix.inst_num))
       xxxx
,      bsta
,      isin
,      sdol
,      inst
,      borg
,      isdm
WHERE  bsta.inst_num          = xxxx.inst_num
AND    bsta.current_mat_date >= TO_DATE(:v_bus_date, 'yyyymmdd')
AND    isin.inst_num          = xxxx.inst_num 
AND    isin.xcod_code         = 'SEDL'
AND    sdol.inst_num          = xxxx.inst_num
AND    sdol.xcod_code         = 'ISIN'
AND    inst.inst_num          = xxxx.inst_num
AND    borg.borg_num          = inst.issuer_num
AND    isdm.inst_num          = xxxx.inst_num;

Which gives:


Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    1.40 |    1.40 |     0.00 |    60 |  79273 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=2392527494)
=======================================================================================================================
| Id |               Operation                |   Name      |  Rows   | Execs |   Rows   | Activity | Activity Detail |
|    |                                        |             | (Estim) |       | (Actual) |   (%)    |   (# samples)   |
=======================================================================================================================
|  0 | SELECT STATEMENT                       |             |         |     1 |    29225 |          |                 |
|  1 |   HASH JOIN                            |             |   94337 |     1 |    29225 |          |                 |
|  2 |    INDEX RANGE SCAN                    | ISCO_4_IDX  |    373K |     1 |     377K |          |                 |
|  3 |    HASH JOIN                           |             |   80747 |     1 |    29629 |          |                 |
|  4 |     HASH JOIN                          |             |   80747 |     1 |    29629 |          |                 |
|  5 |      HASH JOIN                         |             |   80747 |     1 |    29629 |          |                 |
|  6 |       HASH JOIN                        |             |   78122 |     1 |    29604 |          |                 |
|  7 |        MERGE JOIN                      |             |   78003 |     1 |    33947 |          |                 |
|  8 |         INDEX RANGE SCAN               | ISCO_4_IDX  |    818K |     1 |     861K |          |                 |
|  9 |         SORT JOIN                      |             |   55765 |  861K |    33947 |          |                 |
| 10 |          VIEW                          |             |   55765 |     1 |    33960 |          |                 |
| 11 |           UNION-ALL                    |             |         |     1 |    33960 |          |                 |
| 12 |            TABLE ACCESS BY INDEX ROWID | BOOK        |       1 |     1 |        1 |          |                 |
| 13 |             INDEX UNIQUE SCAN          | BOOK_IDX    |       1 |     1 |        1 |          |                 |
| 14 |            HASH JOIN OUTER             |             |   55485 |     1 |    31674 |          |                 |
| 15 |             NESTED LOOPS               |             |   55485 |     1 |    31674 |          |                 |
| 16 |              INDEX RANGE SCAN          | RULE_IDX_2  |       3 |     1 |        1 |          |                 |
| 17 |              INDEX RANGE SCAN          | ISBK_IDX    |   17609 |     1 |    31674 |          |                 |
| 18 |             INDEX RANGE SCAN           | ISCO_4_IDX  |    170K |     1 |    32983 |          |                 |
| 19 |            HASH JOIN ANTI              |             |     280 |     1 |     2286 |          |                 |
| 20 |             INDEX RANGE SCAN           | ISCO_4_IDX  |   28028 |     1 |    32983 |          |                 |
| 21 |             VIEW                       | VW_SQ_1     |   55485 |     1 |    31674 |          |                 |
| 22 |              NESTED LOOPS              |             |   55485 |     1 |    31674 |          |                 |
| 23 |               INDEX RANGE SCAN         | RULE_IDX_2  |       3 |     1 |        1 |          |                 |
| 24 |               INDEX RANGE SCAN         | ISBK_IDX    |   17609 |     1 |    31674 |          |                 |
| 25 |        TABLE ACCESS FULL               | BSTA        |    144K |     1 |     266K |          |                 |
| 26 |       TABLE ACCESS FULL                | ISDM        |      1M |     1 |       1M |          |                 |
| 27 |      TABLE ACCESS FULL                 | INST        |    796K |     1 |     796K |   100.00 | Cpu (1)         |
| 28 |     TABLE ACCESS FULL                  | BORG        |    328K |     1 |     328K |          |                 |
=======================================================================================================================

It might not be perfect but it’s certainly a lot better.

An acceptable compromise might be to refactor the UNION ALL as per above but omit the undocumented opt_estimate hints.

All we need to do is some more testing to validate the results and to also triple check the performance when the data is not cached and we’re done.

If you made it this far then Wow! I probably wouldn’t have done.

So, to summarise, what have we done?

  • Well, we haven’t needed to know much about the original intention of the query nor the schema.
  • We’ve improved the accuracies of some of the estimates.
  • We’ve found some redundant tables and joins.
  • And we’ve reordered the query slightly to better phrase the question that we think was being asked, in the process moving the UNION ALL earlier in the processing so that some of the joining tables only needed to be referenced once (an advantage that might not be preserved if the CBO decides to merge the UNION ALL but that would be the opposite of the recently developed join factorisation mechanism).

And these simple, quick steps effectively reduced the execution time of a query from 20 minutes to a few seconds.

In hindsight, a good example because of the gains realised but a bad example because of the length of query and the associated detail, particularly the redundant tables.

Hints of Acceptability

There are hints and then there are hints.

In version 11.2.0.3 there are 273 hints listed in V$SQL_HINT.

That’s four more than 11.2.0.2 by the way – (NO_)FULL_OUTER_JOIN_TO_OUTER and (NO_)OUTER_JOIN_TO_ANTI are the new additions.

But V$SQL_HINT doesn’t seem to be an absolutely comprehensive listing.

I only noticed one interesting omission – there’s no entry for PARALLEL.

There are entries for NO_PARALLEL / NOPARALLEL but these list their INVERSE as SHARED not PARALLEL.

I’ve never used or even heard of the SHARED hint but it certainly seems to just be synonymous with PARALLEL. Of course, the documentation documents PARALLEL but makes no mention of SHARED which has been a valid alternative since 8.1.0.

So, going down the entries in V$SQL_HINT, below is my initial attempt at a list of “hints of acceptability”, even if one or two are undocumented.

As long as their usage is appropriate, I think these can be used pretty much without guilt or sense of defeat / failure.

Those related to optimizer mode:

Those related to direct path operations:

Those related to optimizer cardinality/selectivity estimate adjustments:

  • CARDINALITY (Undocumented)
  • DYNAMIC_SAMPLING (In recent years, this has been my most favorite hint)
  • DYNAMIC_SAMPLING_EST_CDN (Undocumented since 9i)
  • OPT_ESTIMATE (Undocumented but useful link)

Those normally related to bugs and associated parameter changes and fix control:

Those related to bind variable/literal usage:

Those related to parallel operations:

Those related to remote operations:

Those related to real time sql monitoring:

Those related to tuning but which should not make it into production code:

Those related to caching and caching-like behaviours:

Those related to query block naming:

Have I missed any obvious candidates?
Is there anything you would add?

Of these listed above, let’s just dwell very briefly on those related to optimizer estimate adjustments.

Relatively speaking, do you not find that most – most not all – issues regarding SQL performance are related to accuracy – or rather inaccuracy – of rowsource estimates?

If so, then recommended reading should be Wolfgang Breitling’s Tuning by Cardinality Feeback, the bases of which are:

  1. The observation that:

    IF AN ACCESS PLAN IS NOT OPTIMAL IT IS BECAUSE THE CARDINALITY ESTIMATE FOR ONE OR MORE OF THE ROW SOURCES IS GROSSLY INCORRECT.

  2. The conjecture that:

    THE CBO DOES AN EXCELLENT JOB OF FINDING THE BEST ACCESS PLAN FOR A GIVEN SQL PROVIDED IT IS ABLE TO ACCURATELY ESTIMATE THE CARDINALITIES OF THE ROW SOURCES IN THE PLAN.

If the scope of a problem is one or two SQL statements, then a solution with a scope limited to one or two SQL statements – i.e. a rewrite or a hint – is more appropriate than something with a wider scope such as changing tab/column stats and/or histograms.

And in this respect a solution forcing an estimate adjustment – whether by a hard number by CARDINALITY or OPT_ESTIMATE, an adjustment fudge factor also via OPT_ESTIMATE or having a peek at some of the data in question via DYNAMIC_SAMPLING (only good for single table predicates) is more often than not a better, more flexible, longer lasting solution than forcing a nested loop or a hash join or a particular index.

There are, of course, times when you have no option – there are reasons why all these hundreds of hints exist after all.

But I always think that if I can’t get what I think I roughly want – and what I normally want is just for the estimates to be broadly accurate – either by rewriting the SQL or by using one of these acceptable hints then it’s almost an admission failure.

Away from the list above, hinting a SQL statement is not something which should be undertaken lightly.

Do you ever see a lot of sql statements joining at least a handful of tables but with a single USE_NL hint here or an INDEX hint there in the belief that this offers some sort of stability for the woolly concept of “the correct plan”? I know I do.

Bottom line: if you can avoid hinting you absolutely should.

But if you really are going to hint, you should be doing it properly.

What does this mean?:

  1. Being prescriptive and unambiguous with your directives – i.e. a single use_nl hint is not sufficient for a sql statement that joins eight tables for example.
  2. Using the full specification of the hint including queryblock and table specification syntax.

For more on queryblock naming see Jonathan Lewis’s article on qb_name including the discussions in the comments.

For more information on what you have to do to properly hint, see this excellent article on by Jonathan Lewis.

If Jonathan’s “simple” illustration is not enough to seriously make you reconsider your addiction to hinting, then you have issues and you have to be prepared to swallow the full specification including both query block and proper table specifications.

Yes, it’s ugly.

Yes, it’s not easy (compared to how you’ve probably been doing it).

But, if you’re thinking the above, perhaps revisit your attitude to hinting.

Follow

Get every new post delivered to your Inbox.

Join 83 other followers