Mohamed Houri’s Oracle Notes

April 18, 2013

Interpreting Execution Plan

Filed under: explain plan — hourim @ 12:11 pm

I have been confronted to a performance issue with a query that started performing badly (6 sec.  instead of the usual 2 sec. ) following a change request that introduces a new business requirement. Below is the new execution plan stripped to the bare minimum and where table and index names have been a little bit disguised to protect the innocent.  I have manually introduced two aliases (MHO and YAS) in this execution plan so that the predicate part will be easily linked to its corresponding table (I know there is a difference between E-Rows and A-Rows for certain operations; that’s not my intention to deal with  here in this blog post)

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------------------------------------------------------
|*  8 |         HASH JOIN OUTER                    |                              |      1 |      1 |   2218 |00:00:05.45 |
|   9 |--->      NESTED LOOPS                      |                              |      1 |      1 |   2218 |00:00:03.44 |
|  10 |           NESTED LOOPS                     |                              |      1 |      1 |   2218 |00:00:03.43 |
|  11 |       ---> VIEW                            | XXX_DEMANDE_MANAGMENT_V      |      1 |    251 |    125K|00:00:02.43 |
|  12 |             UNION-ALL                      |                              |      1 |        |    125K|00:00:02.43 |
|  13 |              NESTED LOOPS                  |                              |      1 |      1 |      0 |00:00:00.01 |
|  14 |               INDEX FULL SCAN              | XXX_CLS_BUR_OPR_UK           |      1 |      1 |      0 |00:00:00.01 |
|  15 |               TABLE ACCESS BY INDEX ROWID  | XXX_ASPECT                   |      0 |      1 |      0 |00:00:00.01 |
|* 16 |                INDEX UNIQUE SCAN           | XXX_BUR_PK                   |      0 |      1 |      0 |00:00:00.01 |
|* 17 |              FILTER                        |                              |      1 |        |    125K|00:00:02.31 |
|* 18 |               HASH JOIN                    |                              |      1 |    126K|    125K|00:00:01.18 |
|  19 |                NESTED LOOPS                |                              |      1 |    251 |    251 |00:00:00.01 |
|  20 |                 VIEW                       | index$_join$_054             |      1 |    251 |    251 |00:00:00.01 |
|* 21 |                  HASH JOIN                 |                              |      1 |        |    251 |00:00:00.01 |
|  22 |                   INDEX FAST FULL SCAN     | XXX_BUR_SOM_FK_I             |      1 |    251 |    251 |00:00:00.01 |
|  23 |                   INDEX FAST FULL SCAN     | XXX_BUR_MSF_BUR_FK_I         |      1 |    251 |    251 |00:00:00.01 |
|* 24 |                 INDEX UNIQUE SCAN          | XXX_SOM_PK                   |    251 |      1 |    251 |00:00:00.01 |
|  25 |                VIEW                        | index$_join$_053             |      1 |    126K|    125K|00:00:00.79 |
|* 26 |                 HASH JOIN                  |                              |      1 |        |    125K|00:00:00.67 |
|  27 |                  INDEX FAST FULL SCAN      | XXX_RIP_PK                   |      1 |    126K|    125K|00:00:00.01 |
|  28 |                  INDEX FAST FULL SCAN      | XXX_RIP_BUR_FK_I             |      1 |    126K|    125K|00:00:00.01 |
|* 29 |               INDEX RANGE SCAN             | XXX_CLS_RIP_FK_I             |    125K|      1 |      0 |00:00:00.66 |
|* 30 |       ---> TABLE ACCESS BY INDEX ROWID     | XXX_DEMANDE_ORDINAIR (MHO)   |    125K|      1 |   2218 |00:00:02.97 |
|* 31 |             INDEX UNIQUE SCAN              | XXX_RIP_PK                   |    125K|      1 |    125K|00:00:00.89 |
|  33 |--->      VIEW (YAS)                        |                              |      1 |     82 |   1218 |00:00:00.08 |
|  34 |           SORT UNIQUE                      |                              |      1 |     82 |   1218 |00:00:00.08 |
--------------------------------------------------------------------------------------------------------------------------

8 - access("YAS"."PK_ID"="MHO"."PK_ID")
30 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

Well, from where am I going to start here?

Hmmm… always the same question when trying to interpret an execution plan.

After looking carefully to that execution plan and to its predicate part (always consider the predicate part) I ended up asking myself the following question:

There is HASH JOIN OUTER (Id 8) between a NESTED LOOPS (Id 9) and the VIEW (YAS – id 33)

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------
|*  8 |         HASH JOIN OUTER     |        |      1 |      1 |   2218 |00:00:05.45 |
|   9 |--->      NESTED LOOPS       |        |      1 |      1 |   2218 |00:00:03.44 |
|  33 |--->      VIEW (YAS)         |        |      1 |     82 |   1218 |00:00:00.08 |
--------------------------------------------------------------------------------------

On which a filter operation is applied in order to filter the result by comparing the YAS view with the MHO table via their ”primary key” (PK_ID)

8 - access("YAS"."PK_ID"="MHO"."PK_ID")

That’s seems a little bit strange. Why not a direct HASH JOIN OUTER between the YAS view and the MHO table instead of a JOIN between the YAS view and that NESTED LOOPS (where an access to MHO table is made)?

Have you already pointed out how the predicate part can make you asking good questions?

My second step has been to look at the predicate part of the MHO table access (operation 30) re-printed here below:

30 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

I have an adequate function based index in place that should have been used provided this INTERNAL_FUNCTION has not being used by the CBO

create index XXX_RIP_CREATION_DATE_I on MHO(trunc(creation_date)) ;

This is why I was tempted to force my query to use this index via the appropriate hint. Here below is the resulting execution plan

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Starts | E-Rows | A-Rows |   A-Time    |
---------------------------------------------------------------------------------------------------------------------------
|   8 |         NESTED LOOPS                        |                              |      1 |      1 |   2218 |00:00:02.57 |
|*  9 |--->      HASH JOIN OUTER                    |                              |      1 |      1 |   2218 |00:00:02.48 |
|* 10 |      ---> TABLE ACCESS BY INDEX ROWID       | XXX_DEMANDE_ORDINAIR(MHO)    |      1 |      1 |   2218 |00:00:02.34 |
|  11 |            INDEX FULL SCAN                  | XXX_RIP_CREATION_DATE_I      |      1 |    126K|    125K|00:00:01.82 |
|  12 |      ---> VIEW(YAS)                         |                              |      1 |     82 |   1218 |00:00:00.08|
|  13 |            SORT UNIQUE                      |                              |      1 |     82 |   1218 |00:00:00.08 |
|  41 |--->      VIEW                               | XXX_DEMANDE_MANAGMENT_V      |   2218 |      1 |   2218 |00:00:00.08 |
|  42 |           UNION-ALL PARTITION               |                              |   2218 |        |   2218 |00:00:00.08 |
|  43 |            NESTED LOOPS                     |                              |   2218 |      1 |      0 |00:00:00.01 |
|  44 |             TABLE ACCESS BY INDEX ROWID     | XXX_DEMANDE_RESPONSABLE      |   2218 |      1 |      0 |00:00:00.01 |
|* 45 |              INDEX RANGE SCAN               | XXX_CLS_RIP_FK_I             |   2218 |      1 |      0 |00:00:00.01 |
|  46 |             TABLE ACCESS BY INDEX ROWID     | XXX_ASPECT                   |      0 |      1 |      0 |00:00:00.01 |
|* 47 |              INDEX UNIQUE SCAN              | XXX_BUR_PK                   |      0 |      1 |      0 |00:00:00.01 |
|  48 |            NESTED LOOPS                     |                              |   2218 |      1 |   2218 |00:00:00.06 |
|  49 |             NESTED LOOPS                    |                              |   2218 |      1 |   2218 |00:00:00.05 |
|  50 |              TABLE ACCESS BY INDEX ROWID    | XXX_DEMANDE_ORDINAIR         |   2218 |      1 |   2218 |00:00:00.03 |
|* 51 |               INDEX UNIQUE SCAN             | XXX_RIP_PK                   |   2218 |      1 |   2218 |00:00:00.02 |
|* 52 |                INDEX RANGE SCAN             | XXX_CLS_RIP_FK_I             |   2218 |      1 |      0 |00:00:00.01 |
|  53 |              TABLE ACCESS BY INDEX ROWID    | XXX_ASPECT_                  |   2218 |    251 |   2218 |00:00:00.01 |
|* 54 |               INDEX UNIQUE SCAN             | XXX_BUR_PK                   |   2218 |      1 |   2218 |00:00:00.01 |
|* 55 |             INDEX UNIQUE SCAN               | XXX_SOM_PK                   |   2218 |     36 |   2218 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------------------
9 - access("YAS"."PK_ID"="MHO"."PK_ID")
10 - filter(("MHO"."CLOSED"<>3) AND "PRI"."SYS_NC00047$"=TRUNC(TO_DATE('12042013','ddmmrrrr'))) OR
(TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr')))

That is a better step accomplished (from 5,45 to 2,57 seconds). Isn’t it?  Look now how my HASH OUTER JOIN became

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Starts | E-Rows | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------------------
|   8 |         NESTED LOOPS                  |                          |      1 |      1 |   2218 |00:00:02.57 |
|*  9 |--->      HASH JOIN OUTER              |                          |      1 |      1 |   2218 |00:00:02.48 |
|* 10 |      ---> TABLE ACCESS BY INDEX ROWID | XXX_DEMANDE_ORDINAIR(MHO)|      1 |      1 |   2218 |00:00:02.34 |
|  11 |            INDEX FULL SCAN            | XXX_RIP_CREATION_DATE_I  |      1 |    126K|    125K|00:00:01.82 |
|  12 |      ---> VIEW(YAS)                   |                          |      1 |     82 |   1218 |00:00:00.08 |
------------------------------------------------------------------------------------------------------------------

As I wanted it to be: directly between the MHO table and the YAS view.

But wait a moment please. It seems for me that this INDEX FULL SCAN operation is still to be tuned.  Do you know why? Because this operation is feeding back its parent operation (id 10) with 125,000 rowids of which only 2218 records are kept. 98% of those rowids are thrown away by the filter operation n° 10

10 - filter(("MHO"."CLOSED"<>3) AND "PRI"."SYS_NC00047$"=TRUNC(TO_DATE('12042013','ddmmrrrr'))) OR
(TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr')))

Clearly this is a waste of time and resource. It is better to have a more precise index or to not use that XXX_RIP_CREATION_DATE_I index at all.  But how can I  (see this is another question again) make the CBO generating the HASH JOIN OUTER I want without forcing the use of that function based index?

Okay…

It’s time to look to the content of the query. The part of the query involving my two tables join looks like this

select
 mho.*
,abc.col1
,abc.col2
from
  XXX_DEMANDE_ORDINAIR       mho
, XXX_DEMANDE_MANAGMENT_V    abc
, my_view                    yas
where  mho.pk        = abc.pk
and    mho.pk_id     = yas.pk_id(+)
etc…

The view YAS is not selected from. It should be taken out from the join and put into the where clause as an EXISTS condition.

select
mho.*
,abc.col1
,abc.col2
from
 XXX_DEMANDE_ORDINAIR       mho
,XXX_DEMANDE_MANAGMENT_V    abc
where  mho.pk        = abc.pk
and  exists (select null
             from  my_view yas
             where mho.pk_id  = yas.pk_id)
etc…

I was going to change this when one of my colleagues suggested me to change the above query as follows (please spot the difference there is only a (+) added)

select
 mho.*
,abc.col1
,abc.col2
from
 XXX_DEMANDE_ORDINAIR       mho
,XXX_DEMANDE_MANAGMENT_V    abc
,my_view                    yas
where  mho.pk        = abc.pk(+) --> This will not change the result because I know there is always a record in abc table
and    mho.pk_id     = yas.pk_id(+)
etc…

Doing so, the ABC table will not be considered by the CBO as the driving table because it is the table that is outer joined (is this correct? I have to admit that I need to test it deeply in order to be sure enough about that fact).  As such, the CBO will directly join the MHO table with the YAS view first (this is what I want in fact) and then outer join the result to the third table.

Anyway, I did as suggested and here below what I ended up with

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------
|   9 |         NESTED LOOPS          |                          |      1 |      1 |   2218 |00:00:01.07 |
|* 10 |--->      HASH JOIN OUTER      |                          |      1 |      1 |   2218 |00:00:00.99 |
|  11 |       -->  TABLE ACCESS FULL  | XXX_DEMANDE_ORDINAIR(MHO)|      1 |      1 |   2218 |00:00:00.27 |
|  12 |       -->  VIEW(YAS)          |                          |      1 |     82 |   1218 |00:00:00.08 |
---------------------------------------------------------------------------------------------------------
9 - access("YAS"."PK_ID"="MHO"."PK_ID")
10 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

The query is now responding better than before the change request. Doing a full table scan on MHO table in this case is better than to access it via the existing non precise function based index and then filter the returned rows(by rowid) to through 97% of them.

Bottom line: the goal of this article is to show how important the predicate part can be in tuning a query via its execution plan. I started questioning myself from the join predicate part followed by the use of an adequate index and finally I ended up by searching the best  order of the table  join

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

The Rubric Theme. Blog at WordPress.com.

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

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

Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

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

Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: