Mohamed Houri’s Oracle Notes

July 2, 2015

Don’t pre-empt the CBO from doing its work

Filed under: Oracle — hourim @ 2:03 pm

This is the last part of the parallel insert/select saga. As a reminder below is the two preceding episodes:

  •  Part 1: where I have explained why I was unable to get the corresponding SQL monitoring report because of the _sqlmon_max_planlines parameter.
  •  Part 2: where I have explained the oddity shown by the SQL monitoring report when monitoring non active parallel server for more than 30 minutes.

In Part 3 I will share with you how I have succeeded to solve this issue and convinced people to not pre-empt the Oracle optimizer from doing its work.

Thanks to the monitoring of this insert/select I have succeeded to isolate the part of the execution plan that needs absolutely to be tuned:

Error: ORA-12805
------------------------------
ORA-12805: parallel query server died unexpectedly

Global Information
------------------------------
 Status                                 :  DONE (ERROR)
 Instance ID                            :  2
 SQL ID                                 :  bg7h7s8sb5mnt
 SQL Execution ID                       :  33554432
 Execution Started                      :  06/24/2015 05:06:14
 First Refresh Time                     :  06/24/2015 05:06:21
 Last Refresh Time                      :  06/24/2015 09:05:10
 Duration                               :  14336s
 DOP Downgrade                          :  50%                 

Global Stats
============================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Cluster  |  Other   | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes |
============================================================================================
|   38403 |   35816 |     0.42 |        2581 |     0.16 |     6.09 |     7G |  103 | 824KB |
============================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3668294770)
======================================================================================================
| Id  |                Operation         |             Name  |  Rows   | Execs |   Rows   | Activity |
|     |                                  |                   | (Estim) |       | (Actual) |   (%)    |
======================================================================================================
| 357 |VIEW PUSHED PREDICATE             | NAEHCE            |      59 | 23570 |    23541 |          |
| 358 | NESTED LOOPS                     |                   |      2M | 23570 |    23541 |     0.05 |
| 359 |  INDEX FAST FULL SCAN            | TABLEIND1         |   27077 | 23570 |     667M |     0.19 |
| 360 |  VIEW                            | VW_JF_SET$E6DCA8A3|       1 |  667M |    23541 |     0.10 |
| 361 |   UNION ALL PUSHED PREDICATE     |                   |         |  667M |    23541 |    30.59 |
| 362 |    NESTED LOOPS                  |                   |       1 |  667M |     1140 |     0.12 |
| 363 |     TABLE ACCESS BY INDEX ROWID  | TABLE2            |       1 |  667M |    23566 |     1.25 |
| 364 |      INDEX UNIQUE SCAN           | IDX_TABLE2        |       1 |  667M |     667M |    17.81 |
| 365 |     TABLE ACCESS BY INDEX ROWID  | TABLE3            |       1 | 23566 |     1140 |          |
| 366 |      INDEX RANGE SCAN            | IDX_TABLE3        |      40 | 23566 |     174K |          |
| 367 |    NESTED LOOPS                  |                   |       1 |  667M |    22401 |     0.11 |
| 368 |     TABLE ACCESS BY INDEX ROWID  | TABLE2            |       1 |  667M |    23566 |     1.27 |
| 369 |      INDEX UNIQUE SCAN           | IDX_TABLE2        |       1 |  667M |     667M |    17.72 |
| 370 |     TABLE ACCESS BY INDEX ROWID  | TABLE3            |       1 | 23566 |    22401 |     0.01 |
| 371 |      INDEX RANGE SCAN            | TABLE31           |      36 | 23566 |       4M |          |

The NESTED LOOPS operation at line 358 has an INDEX FAST FULL SCAN (TABLEIND1) as an outer data source driven an inner data row source represented by an internal view (VW_JF_SET$E6DCA8A3) built by Oracle on the fly. Reduced to the bare minimum it should resemble to this:

SQL Plan Monitoring Details (Plan Hash Value=3668294770)
=====================================================================================
| Id  |                 Operation |             Name   |  Rows   | Execs |   Rows   |
|     |                           |                    | (Estim) |       | (Actual) |
=====================================================================================
| 358 |  NESTED LOOPS             |                    |      2M | 23570 |    23541 |
| 359 |   INDEX FAST FULL SCAN    | TABLEIND1          |   27077 | 23570 |     667M |
| 360 |   VIEW                    | VW_JF_SET$E6DCA8A3 |       1 |  667M |    23541 |

Observe carefully operation at line 359 which is the operation upon which Oracle makes its join method choice. Very often a NESTED LOOPS operation is wrongly chosen by the optimizer because of not accurate estimations made at the first operation of the NESTED LOOPS join. Let’s check the accuracy of the estimation done in this case by Oracle for operation at line 359:

   Rows(Estim) * Execs = 27077 * 23570 = 638204890 ~ 638M
   Rows(Actual = 667M

Estimations done by the optimizer at this step are good. So why in earth Oracle will decide to opt for a NESTED LOOPS operation when it knows prior the execution that the outer data row set will produce 667M of rows inducing the inner operations to be executed 667M times? There is no way that Oracle will opt for this solution unless it is instructed to do so. And indeed, looking to the huge insert/select statement I found, among a tremendous amount of hints, a use_nl (o h) hint which dictates the optimizer to join the TABLEIND table with the rest of the view using a NESTED LOOPS operation. It was then a battle to convince the client that he has to get rid of that hint. What makes the client hesitating is that very often the same insert/select statement (including the use_nl hint) completes in an acceptable time. I was then obliged to explain why despite the presence of the use_nl hint (I am suggesting to be the problem of the performance degradation) the insert/select very often completes in an acceptable execution time. To explain this situation it suffices to get the execution plan of the acceptable execution time (reduced to the bare minimum) and spot the obvious:

SQL Plan Monitoring Details (Plan Hash Value=367892000)
====================================================================================
| Id  |                Operation |             Name   |  Rows   | Execs |   Rows   |
|     |                          |                    | (Estim) |       | (Actual) |
====================================================================================
| 168 |VIEW PUSHED PREDICATE     | NAEHCE             |       1 | 35118 |    35105 |
| 169 | NESTED LOOPS             |                    |       2 | 35118 |    35105 |
| 170 |  VIEW                    | VW_JF_SET$86BE946E |       2 | 35118 |    35105 |
| 182 |  INDEX UNIQUE SCAN       | TABLEIND1          |       1 | 35105 |    35105 |

The join order switched from (TABLEIND1, VW_JF_SET$86BE946E) to (VW_JF_SET$86BE946E,TABLEIND1). As far as the use_nl (o h) hint is not completed by a leading (h o) hint in order to indicate in what order Oracle has to join this two objects, then the choice of the important outer operation is left to Oracle. When the index is chosen as the outer operation, the insert/select statement performs very poorly. However when the same index is used as the inner operation of the join then the insert/select statement performs in an acceptable time.

With that explained, the client has been convinced, the hints disabled and the insert/select re-launched and completed within few seconds thanks to the approriate HASH JOIN operation used by the optimizer:

Global Information
------------------------------
 Status                                 :  DONE
 Instance ID                            :  2
 SQL ID                                 :  9g2a3gstkr7dv
 SQL Execution ID                       :  33554432
 Execution Started                      :  06/24/2015 12:53:49
 First Refresh Time                     :  06/24/2015 12:53:52
 Last Refresh Time                      :  06/24/2015 12:54:05
 Duration                               :  16s                      

Global Stats
============================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Cluster  |  Other   | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes |
============================================================================================
|      23 |      21 |     0.91 |        0.03 |     0.22 |     0.31 |     1M |  187 |   1MB |
============================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3871743977)
=================================================================================================
| Id  |                           Operation   |             Name   |  Rows   | Execs |   Rows   |
|     |                                       |                    | (Estim) |       | (Actual) |
=================================================================================================
| 153 |       VIEW                            | NAEHCE             |      2M |     1 |       2M |
| 154 |        HASH JOIN                      |                    |      2M |     1 |       2M |
| 155 |         INDEX FAST FULL SCAN          | TABLEIND1          |   27077 |     1 |    28320 |
| 156 |         VIEW                          | VW_JF_SET$86BE946E |      2M |     1 |       2M |

Spot as well that when the optimizer opted for a HASH JOIN operation the VIEW PUSHED PREDICATE operation and the JPPD (JOIN PREDICATE PUSH DOWN) underlying transformation cease to used because it is occurs only with NESTED LOOP.

Bottom line: always try to supply Oracle with fresh and representative statistics and let it do its job. Don’t pre-empt it from doing its normal work by systematically hinting it when confronted to a performance issue. And when you decide to use hints make sure to hint correctly particularly for the outer (build) table and the inner(probe) table in case of NESTED LOOPS (HASH JOIN) hinted operation.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: