1 2 3 Previous Next 32 Replies Latest reply on Apr 24, 2020 3:23 PM by 3510875 Go to original post
      • 30. Re: tkprof Analysis
        Jonathan Lewis

        Looking at the plan you've got there's an interesting costing detail in play:

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

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

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

        |* 27 |                TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL |     1 |    10 |      3  (0)| 00:00:01 |

        |* 28 |                 INDEX RANGE SCAN                  | XXADM_APPLCNT_PREFS_UK         |     1 |       |      2  (0)| 00:00:01 |

        |  29 |                TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL |     1 |    10 |      2  (0)| 00:00:01 |

        |* 30 |                 INDEX RANGE SCAN                  | XXADM_APPLCNT_APPLICANT_STATUS |     3 |       |      1  (0)| 00:00:01 |

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

          27 - filter(("STATUS_FLAG"='B' OR "STATUS_FLAG"='C' OR "STATUS_FLAG"='O' OR "STATUS_FLAG"='T'))

          28 - access("ACT1"."APPLICANT_ID"=:B1)

          30 - access("ACT2"."APPLICANT_ID"=:B1 AND "STATUS_FLAG"='C')

         

        Operation 30 has used the new index and you can see in the predicate section that the status_flag is checked in the index, so you will only be visiting the table when you really need to at operation 29.

         

        Operation 28 doesn't use the new index and Oracle doesn't check the stats us_flag until it reaches the table (as shown in the predicate information).  This may be because Oracle thinks it's cheaper to check what it thinks is a few rows from the table rather than reading all the relevant index leaf blocks to eliminate redundant table access. We can check in the trace file whether the new index helped performance for 30/29, and if so you'll need to force Oracle to use it for 28/27,

         

        Regards

        Jonathan Lewis

        • 31. Re: tkprof Analysis
          3510875

          Dear Jonathan,

           

          Thanks a ton.The runtime reduced from 6.5 hours to 5 hours.We request your on 2 things.

           

          1)How can we force oracle to use index for 28/27

          2)As you have updated before,the script which we used earlier is giving wrong order.If possible and if you have some time-please can you provide the correct order for the execution plan.For Any plan what ever we have uploaded to this post.

           

          Thanks again.

          • 32. Re: tkprof Analysis
            3510875

            Dear Jonathan,

             

            I observed different plans might be due to the adaptive features in 12cR1 database.However,i have disabled the optimiser adaptive features and flushed the shared pool in test instance.This time ran the program again and captured the execution plan from cache.

             

            As this thread is growing large and it is already helpful to me and marked as correct.opened a new thread.

             

            https://community.oracle.com/thread/4324948?start=15&tstart=0

             

            Thank You

            1 2 3 Previous Next