Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query Execution plan change

User_OCZ1TSep 9 2018 — edited Sep 10 2018

We are using version 11.2.0.4 of oracle exadata. We got one query change its plan and ran for ~3hrs which used to finish within minutes and then in the subsequent execution it reverted back to the good plan automatically. Its mostly related to any specific stats but i am not able to understand the exact stats which has influenced it to go for that plan. I have mentioned below the bad plan and the good plan. Currently the query is by default going for good plan, so i collect the outline of the bad plan and force that to the query as a Hints to see the behavior , i do see now the cost has been increased but not able to get any clue what statistics must be influencing optimizer to go for the bad  plan? Want to know expert suggestion if anything obvious here which i am missing. Note- currently i don't see all the bad child cursors in gv$sql_shared_cursor, so not sure of the reason. index tab2_ix1 is on column-(tid,pcd).

Query:-

SELECT   COUNT (*) AS tcount

    FROM tab1, tmtd, tmc, tab2

   WHERE tab1.DID = tmtd.DID

     AND tmtd.CID = tmc.CID

     AND tmc.PIND = 1

     AND tab2.PCD = 'AA'

     AND tab2.TID = tab1.TID

     AND tab2.SDT = tab1.SDT

     AND tab1.SDT >= TRUNC(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))

     AND tab1.SID = :b2

GROUP BY tab1.SID

note- both tab1 and tab2 are range partitioned by truncated date column SDT.

Bad Plan from AWR:- (Full access of table tab2 is causing bottleneck, at line no-16)

----------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                          |                            |       |       |   448K(100)|          |       |       |

|   1 |  SORT GROUP BY NOSORT                     |                            |     1 |    60 |   448K  (3)| 01:29:42 |       |       |

|   2 |   NESTED LOOPS                            |                            |     1 |    60 |   448K  (3)| 01:29:42 |       |       |

|   3 |    VIEW                                   | VW_GBF_19                  |     1 |    41 |  1269   (1)| 00:00:16 |       |       |

|   4 |     HASH GROUP BY                         |                            |     1 |    52 |  1269   (1)| 00:00:16 |       |       |

|   5 |      NESTED LOOPS                         |                            |   223 | 11596 |  1268   (1)| 00:00:16 |       |       |

|   6 |       NESTED LOOPS                        |                            |   224 | 11596 |  1268   (1)| 00:00:16 |       |       |

|   7 |        NESTED LOOPS                       |                            |   224 | 10080 |   820   (1)| 00:00:10 |       |       |

|   8 |         PARTITION RANGE ITERATOR          |                            |   224 |  7616 |   372   (1)| 00:00:05 |   KEY |   270 |

|   9 |          TABLE ACCESS BY LOCAL INDEX ROWID| tab1                       |   224 |  7616 |   372   (1)| 00:00:05 |   KEY |   270 |

|  10 |           INDEX RANGE SCAN                | tab1_un1                   |   224 |       |   323   (1)| 00:00:04 |   KEY |   270 |

|  11 |         TABLE ACCESS BY INDEX ROWID       | tmtd                       |     1 |    11 |     2   (0)| 00:00:01 |       |       |

|  12 |          INDEX UNIQUE SCAN                | tmtd_pk                    |     1 |       |     1   (0)| 00:00:01 |       |       |

|  13 |        INDEX UNIQUE SCAN                  | tmc_pk                     |     1 |       |     1   (0)| 00:00:01 |       |       |

|  14 |       TABLE ACCESS BY INDEX ROWID         | tmc                        |     1 |     7 |     2   (0)| 00:00:01 |       |       |

|  15 |    PARTITION RANGE ITERATOR               |                            |     1 |    19 |   447K  (3)| 01:29:27 |   KEY |   KEY |

|  16 |     TABLE ACCESS STORAGE FULL             | tab2                       |     1 |    19 |   447K  (3)| 01:29:27 |   KEY |   KEY |

----------------------------------------------------------------------------------------------------------------------------------------

Good Plan from AWR:-

-----------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-----------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |                            |       |       |   891 (100)|          |       |       |

|   1 |  SORT GROUP BY NOSORT                      |                            |     1 |    49 |   891   (1)| 00:00:11 |       |       |

|   2 |   NESTED LOOPS                             |                            |     1 |    49 |   891   (1)| 00:00:11 |       |       |

|   3 |    NESTED LOOPS                            |                            |     1 |    49 |   891   (1)| 00:00:11 |       |       |

|   4 |     NESTED LOOPS                           |                            |     1 |    42 |   889   (1)| 00:00:11 |       |       |

|   5 |      VIEW                                  | VW_GBF_19                  |     1 |    31 |   887   (1)| 00:00:11 |       |       |

|   6 |       HASH GROUP BY                        |                            |     1 |    53 |   887   (1)| 00:00:11 |       |       |

|   7 |        NESTED LOOPS                        |                            |   106 |  5618 |   886   (1)| 00:00:11 |       |       |

|   8 |         NESTED LOOPS                       |                            | 16800 |  5618 |   886   (1)| 00:00:11 |       |       |

|   9 |          PARTITION RANGE ITERATOR          |                            |   105 |  3570 |   372   (1)| 00:00:05 |   KEY |   271 |

|  10 |           TABLE ACCESS BY LOCAL INDEX ROWID| tab1                       |   105 |  3570 |   372   (1)| 00:00:05 |   KEY |   271 |

|  11 |            INDEX RANGE SCAN                | tab1_un1                   |   105 |       |   301   (1)| 00:00:04 |   KEY |   271 |

|  12 |          PARTITION RANGE AND               |                            |   160 |       |     3   (0)| 00:00:01 |KEY(AP)|KEY(AP)|

|  13 |           INDEX RANGE SCAN                 | TAB2_IX1                   |   160 |       |     3   (0)| 00:00:01 |KEY(AP)|KEY(AP)|

|  14 |         TABLE ACCESS BY LOCAL INDEX ROWID  | tab2                       |     1 |    19 |   109   (0)| 00:00:02 |     1 |     1 |

|  15 |      TABLE ACCESS BY INDEX ROWID           | tmtd                       |     1 |    11 |     2   (0)| 00:00:01 |       |       |

|  16 |       INDEX UNIQUE SCAN                    | tmtd_pk                    |     1 |       |     1   (0)| 00:00:01 |       |       |

|  17 |     INDEX UNIQUE SCAN                      | tmc_pk                     |     1 |       |     1   (0)| 00:00:01 |       |       |

|  18 |    TABLE ACCESS BY INDEX ROWID             | tmc                        |     1 |     7 |     2   (0)| 00:00:01 |       |       |

-----------------------------------------------------------------------------------------------------------------------------------------

When i now forced the Outline of bad  plan to the query manually:-

Execution Plan

----------------------------------------------------------

Plan hash value: 1070293224

----------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                          |                            |     1 |    60 |   451K  (3)| 01:30:24 |       |       |

|   1 |  SORT GROUP BY NOSORT                     |                            |     1 |    60 |   451K  (3)| 01:30:24 |       |       |

|   2 |   NESTED LOOPS                            |                            |     1 |    60 |   451K  (3)| 01:30:24 |       |       |

|   3 |    VIEW                                   | VW_GBF_19                  |     1 |    41 |  5715   (1)| 00:01:09 |       |       |

|   4 |     HASH GROUP BY                         |                            |     1 |    52 |  5715   (1)| 00:01:09 |       |       |

|   5 |      NESTED LOOPS                         |                            |  1174 | 61048 |  5714   (1)| 00:01:09 |       |       |

|   6 |       NESTED LOOPS                        |                            |  1180 | 61048 |  5714   (1)| 00:01:09 |       |       |

|   7 |        NESTED LOOPS                       |                            |  1180 | 53100 |  3353   (1)| 00:00:41 |       |       |

|   8 |         PARTITION RANGE ITERATOR          |                            |  1180 | 40120 |   993   (1)| 00:00:12 |   KEY |   270 |

|   9 |          TABLE ACCESS BY LOCAL INDEX ROWID| tab1                       |  1180 | 40120 |   993   (1)| 00:00:12 |   KEY |   270 |

|* 10 |           INDEX RANGE SCAN                | tab1_un1                   |   212 |       |   947   (1)| 00:00:12 |   KEY |   270 |

|  11 |         TABLE ACCESS BY INDEX ROWID       | tmtd                       |     1 |    11 |     2   (0)| 00:00:01 |       |       |

|* 12 |          INDEX UNIQUE SCAN                | tmtd_pk                    |     1 |       |     1   (0)| 00:00:01 |       |       |

|* 13 |        INDEX UNIQUE SCAN                  | tmc_pk                     |     1 |       |     1   (0)| 00:00:01 |       |       |

|* 14 |       TABLE ACCESS BY INDEX ROWID         | tmc                        |     1 |     7 |     2   (0)| 00:00:01 |       |       |

|  15 |    PARTITION RANGE ITERATOR               |                            |     1 |    19 |   446K  (3)| 01:29:16 |   KEY |   KEY |

|* 16 |     TABLE ACCESS STORAGE FULL             | tab2                       |     1 |    19 |   446K  (3)| 01:29:16 |   KEY |   KEY |

----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  10 - access("tab1"."SID"=:B2 AND "tab1"."SDT">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY

              HH24:MI:SS')))

       filter("tab1"."SDT">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))

  12 - access("tab1"."DID"="tmtd"."DID")

  13 - access("tmtd"."CID"="tmc"."CID")

  14 - filter(TO_NUMBER("tmc"."PIND")=1)

  16 - storage("tab2"."PCD"='AA')

       filter("tab2"."PCD"='AA' AND "tab2"."TID"="IT2" AND

              "tab2"."SDT"="IT1")

This post has been answered by AndrewSayer on Sep 9 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 8 2018
Added on Sep 9 2018
12 comments
751 views