1 2 Previous Next 18 Replies Latest reply on May 15, 2020 6:22 AM by 3510875

    Order of executionplan and the Variations after index creation

    3510875

      Dear Jonathan

       

      Attached the current plan and the sql for your reference.Can you please let us know the order of the plan and if possible how can force the index on 40/41

       

      From plan,we could see the new index used on step 43 thereby eliminating the filter.

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

       

      We would like to achieve the same index on steps 40/41.Please suggest.

       

      Thank You

        • 1. Re: Order of executionplan and the Variations after index creation
          Jonathan Lewis

          There's a step to take to test what happens, and a refinement on that step to address the issue in production.

          Step 1 - add a hint to the SQL to tell it to use that index; to do this add the 'alias' and 'outline' format option to your call to dbms_xplan.display_cursor(), this will add two sections of the report.  See my blog for an example of the query block / object alias section of the plan - find operation 41 in that section in your plan and take note of the query block name and object alias. Then look at the outline section and search for the hint in that will be either index() or index_rs_asc() and references exactly that query block, and that alias, and specifies the wrong index (by column list, probably, rather than name). Take that hint and change the index reference to describe the new index and write that hint in at the top of the query immediately after the very first SELECT.

           

          e.g.  (using a line from an outline for a query referencing all_objects):

           

          INDEX(@"SEL$D312661C" "DO"@"SEL$54" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

           

          The bit you would need to change is the equivalent of the highlighted index definition:

          All the double-quote marks are redundant and could be deleted.

           

          The drawback to adding just one hint to a query is that even though it's the hint that tells Oracle what you want it to do at that point, the optimizer might decide that that makes the query so expensive it changes the plan completely. So typically you end up adding the entire hint set to the query with just that one hint changed - then you capture the resulting plan as an SQL Baseline.

           

          Regards

          Jonathan Lewis

          • 2. Re: Order of executionplan and the Variations after index creation
            3510875

            Dear Jonathan,

             

            Thanks for the update.We have uploaded the alias and outline sections to this post.Also uploaded runtime stats.

             

            Step 40 and 41 from alias section:

             

            40 - SEL$5        / ACT1@SEL$5

            41 - SEL$5        / ACT1@SEL$5

             

            From Outline section,which matches above alias is below lines.

             

            INDEX_RS_ASC(@"SEL$5" "ACT1"@"SEL$5" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID"

            "XXADM_APPLICANT_COURSPREFS_TBL"."COLLEGE_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."COURSE_ID"

            "XXADM_APPLICANT_COURSPREFS_TBL"."MEDIUM_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."HOSTEL_REQUIRED"))

             

            I am sorry.Please can you help us how can we use this as hint in our query.

             

            Thank you

            • 3. Re: Order of executionplan and the Variations after index creation
              3510875

              Dear Jonathan,

               

              Is this hint fine.

               

              SELECT /*+ INDEX_RS_ASC(@"SEL$5" "ACT1"@"SEL$5" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID","XXADM_APPLICANT_COURSPREFS_TBL".STATUS_FLAG")) */ COUNT(applicant_id)

               

              Can you please let us know the order of execution plan.Script we are using is not giving correct order as you have suggested earlier.

               

              Thank you

              • 4. Re: Order of executionplan and the Variations after index creation
                Jonathan Lewis

                3510875 wrote:

                 

                 

                SELECT /*+ INDEX_RS_ASC(@"SEL$5" "ACT1"@"SEL$5" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID","XXADM_APPLICANT_COURSPREFS_TBL".STATUS_FLAG")) */ COUNT(applicant_id)

                 

                 

                When you use your own hint you can get rid of the double quotes, and get rid of the table name in the index definition (except for bitmap join indexes), so you could write that:

                select

                        /*+ INDEX_RS_ASC(@SEL$5 ACT1@SEL$5 (APPLICANT_ID, STATUS_FLAG)) */

                        COUNT(applicant_id)

                 

                That will work if your index is, or starts with: (APPLICANT_ID, STATUS_FLAG) - which seems to be the case judging from the hint against alias ACT2 that appears for query block SEL$6.

                 

                 

                Regards

                Jonathan Lewis

                 

                 

                UPDATE:  This plan doesn't contain any "pushed" subqueries, and isn't using parallel execution mechanisms so the program you have for getting the order of operation should give the correct results. On an earlier variation the three subqueries that appear as child operations to the FILTER at  operation 6 were pushed to a much earlier point in the plan, which is where the poperation ordering went wrong.

                • 5. Re: Order of executionplan and the Variations after index creation
                  3510875

                  Dear Jonathan,

                   

                  Thanks for the update.Something wrong as i am unable to upload the attachments to my latest updates,so attaching to previous updates only.

                  Right now,program is running without index and the order of the plan is attached(orderwithoutindex.txt).We will put the hint and run the program again.I will try to find out the order and if it is complex,then only i will approach you.I have learnt a lot with you posts in this 1 week..

                   

                  Thank a lot

                  • 6. Re: Order of executionplan and the Variations after index creation
                    3510875

                    Dear Jonathan,

                     

                    There is one and half hour difference in program runtime with index and without index.I would like to add the hint as you suggested and update to the developer to submit the program.we will also check the runtime with hint as well.

                     

                    Before submitting the program,i have ran explain plan using our hint and attached the execution plan with name plan_with_hint.txt to the initial post.From this,can we say if this help to reduce any run time?Because 574 is the cost with index(without hint) and 575 is the cost for without index.Haven't run the program with hint yet(have to submit that) but when i run explain plan,it shows me 578 as the cost with hint.

                     

                    Thank you

                    • 7. Re: Order of executionplan and the Variations after index creation
                      3510875

                      Dear Jonathan,

                       

                      We have submitted the program with suggested hint and it is in progress.Attached the plan from the cursor.Cost for 25,26 operations are showing some more values.

                       

                      In operation 25 and 26,We can see a full table scan taking place,As this sql runs for more than 70,000 times-i think this may create some problem,not sure though.

                       

                      |* 25 |                 TABLE ACCESS FULL           | XXADM_APPLICANT_COURSPREFS_TBL |   478 | 65008 |   241   (2)| 00:00:01 |

                      |* 26 |                TABLE ACCESS FULL            | XXADM_APPLICANT_DETAILS_TBL    |  6685 |   522K|   311   (2)| 00:00:01 |

                       

                      Operations 25 and 26 from predicate section:

                       

                        25 - filter(("ACT"."COURSE_ID"=:P_COURSE_ID AND "ACT"."COLLEGE_ID"=:P_COLLEGE_ID AND "ACT"."MEDIUM_ID"=:P_MEDIUM_ID

                                    AND "ACT"."HOSTEL_REQUIRED"=:P_HOSTEL_REQUIRED))

                        26 - filter(("ADT"."STATUS"='Active' AND "ADT"."COURSE_APPLIED_FOR"='DEG' AND

                                    (INTERNAL_FUNCTION("ADT"."COLLEGE_STATUS_FLAG") OR "ADT"."COLLEGE_STATUS_FLAG" IS NULL)))

                       

                      We already have index created for columns listed in 25 under predicate section.

                       

                      select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION,TABLE_NAME from dba_ind_columns where TABLE_NAME='XXADM_APPLICANT_COURSPREFS_TBL' and TABLE_OWNER='XXADM' order by INDEX_NAME,COLUMN_POSITION;

                       

                       

                      INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION TABLE_NAME

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

                      XXADM_APPLCNT_APPLICANT_STATUS APPLICANT_ID                                 1 XXADM_APPLICANT_COURSPREFS_TBL

                      XXADM_APPLCNT_APPLICANT_STATUS STATUS_FLAG                                  2 XXADM_APPLICANT_COURSPREFS_TBL

                      XXADM_APPLCNT_PREFS_UK         APPLICANT_ID                                 1 XXADM_APPLICANT_COURSPREFS_TBL

                      XXADM_APPLCNT_PREFS_UK         COLLEGE_ID                                   2 XXADM_APPLICANT_COURSPREFS_TBL

                      XXADM_APPLCNT_PREFS_UK         COURSE_ID                                    3 XXADM_APPLICANT_COURSPREFS_TBL

                      XXADM_APPLCNT_PREFS_UK         MEDIUM_ID                                    4 XXADM_APPLICANT_COURSPREFS_TBL

                      XXADM_APPLCNT_PREFS_UK         HOSTEL_REQUIRED                              5 XXADM_APPLICANT_COURSPREFS_TBL

                      XXADM_APPLCNT_PREF_ORDER_UK    APPLICANT_ID                                 1 XXADM_APPLICANT_COURSPREFS_TBL

                      XXADM_APPLCNT_PREF_ORDER_UK    PREFERENCE_ORDER                             2 XXADM_APPLICANT_COURSPREFS_TBL

                       

                      Step 26:

                      SQL> select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION,TABLE_NAME from dba_ind_columns where TABLE_NAME='XXADM_APPLICANT_DETAILS_TBL' ;

                       

                       

                      INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION TABLE_NAME

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

                      XXADM_APPLICANTS_PK            APPLICANT_ID                                 1 XXADM_APPLICANT_DETAILS_TBL

                      XXADM_APPLICANTS_AA_UK         APPLICANT_AADHAR#                            1 XXADM_APPLICANT_DETAILS_TBL

                       

                      Can you please let us know why it is not using index in step 25.Is it possible to use/create any index for step 26?

                       

                      I think,we are almost very near to fix the problem.Thanks for your valuable suggestions throught out discussions on this sql.

                       

                      Thank You

                      • 8. Re: Order of executionplan and the Variations after index creation
                        3510875

                        Also attached runtime stats

                        • 9. Re: Order of executionplan and the Variations after index creation
                          Jonathan Lewis

                          You've also posted all this information on my blog in two places over the course of two hours.

                          If I don't leap to answer your questions on the forum it's because I have other things to do with my time, and pushing your questions onto my blog isn't going to get you a faster response.

                           

                          If you read the entire thread - starting from the previous thread - you'll see that I pointed out that the full tablescan was a red herring and there were more important time components to worry about. If you look at operation 13 on the stuff you posted on my block you'll see that it took 0.02 seconds to produce 2,040 rows when you used two tablescans and a FURTHER 0.04 seconds running all the subqueries nested loops a couple of thousand times to get to the final answer.

                           

                          I've also walked you through fixing up one hint to get Oracle to do what you want (if you're lucky).

                          You don't like the full tablescans at operations 25 and 26 - so look at the objects there in the Query block output;

                            25 - SEL$7E0D484F / ACT@SEL$3

                            26 - SEL$7E0D484F / ADT@SEL$3

                           

                          Now find where that query block and those objects are referenced in the Outline information with a FULL hint

                           

                                FULL(@"SEL$7E0D484F" "ACT"@"SEL$3")

                                FULL(@"SEL$7E0D484F" "ADT"@"SEL$3")

                           

                          Change those two hints from FULL to INDEX, adding the relevant index definition to the hint as I explained for the occasion when you wanted to change Oracle's choice of index.

                           

                          The ACT table is subject to a nested loop join already, but the the ADT table is subject to a hash join so find the join hint:

                           

                                USE_HASH(@"SEL$7E0D484F" "ADT"@"SEL$3")

                           

                          Change that to USE_NL(...)

                           

                          If you're lucky just that set of hints (plus any you've already got in place) will be sufficient to change the plan to the one you want to see. If it doesn't then you'll have to edit those hints into the full OUTLINE information and use the full set in the query.

                           

                           

                          As for your question about "will it go faster when the cost goes up".

                          GUIDELINE - when you hint a query you decide how to hint it because you think it will probably go faster; you EXPECT the cost to go up, because if the cost of the hinted query were lower then the optimizer would (usually) have taken that path automatically.

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: Order of executionplan and the Variations after index creation
                            3510875

                            Dear Jonathan,

                             

                            Thanks for the update.I understand you are busy person and really its not my intention to post in different places.Due a technical problem while saving and so i  have posted in different places thinking the first one is not saved.I realised it later but i could not delete them.

                             

                            You are helping us a lot and we noted your updates and sure we understand.

                             

                            Thank You

                            • 11. Re: Order of executionplan and the Variations after index creation
                              3510875

                              Dear Jonathan,

                               

                              I remember you said that full table scan is a red herring.I requested you to know how to use the hint to avoid full table scan is only for my understanding.

                              Jonathan,As you have suggested ealier,i have created the index and we got 2hours reduction in program runtime.Adding hint INDEX_RS_ASC(@SEL$5 ACT1@SEL$5 (APPLICANT_ID, STATUS_FLAG)) does not reduce the runtime.

                              Can we reduce the runtime any further by looking into runtime stats.?Please bear with me,i am not an experienced engineer with optimiser plans.

                              I started going through all your blogs and getting grip on the topics.

                               

                              Thank You

                              • 12. Re: Order of executionplan and the Variations after index creation
                                Jonathan Lewis

                                3510875 wrote:

                                 

                                 

                                 

                                As you have suggested ealier,i have created the index and we got 2hours reduction in program runtime.Adding hint INDEX_RS_ASC(@SEL$5 ACT1@SEL$5 (APPLICANT_ID, STATUS_FLAG)) does not reduce the runtime.

                                Just to clarify:

                                You created the index (applicant_id, status_flag) and improved the runtime by 2 hours - and I think you then showed us a plan where the the index was used for one of the two subqueries but nor for the other.

                                You then added the hint INDEX_RS_ASC(@SEL$5 ACT1@SEL$5 (APPLICANT_ID, STATUS_FLAG)) but got no further improvement - and I think you posted the plan from that run to my blog because that plan shows the index being used for both subqueries:

                                 

                                |  40 |        INLIST ITERATOR                      |                                |   2038 |        |       |            |    437 |00:00:00.01 |    6916 |       |      |           |

                                |  41 |         TABLE ACCESS BY INDEX ROWID BATCHED | XXADM_APPLICANT_COURSPREFS_TBL |   8152 |      1 |    10 |     6   (0)|    437 |00:00:00.01 |    6916 |       |      |           |

                                |* 42 |          INDEX RANGE SCAN                   | XXADM_APPLCNT_APPLICANT_STATUS |   8152 |      4 |       |     5   (0)|    437 |00:00:00.01 |    6514 |       |      |           |

                                |  43 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |   1949 |      1 |    10 |     2   (0)|     33 |00:00:00.01 |    1981 |       |      |           |

                                |* 44 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_APPLICANT_STATUS |   1949 |      1 |       |     1   (0)|     33 |00:00:00.01 |    1949 |       |      |           |

                                 

                                I've highlighted "no further" - because I want to know whether the performance went back to the original after adding the hint, or whether it stayed at the "2 hour improvement" level.  An effect of forcing Oracle into using that index is that it's now using an INLIST ITERATOR.  Previously it did a range-scan of the index for the applicant_id and then then visited the table 4 times to check the status_flag; now it's doing 4 separate range scans (and staying in the index almost always) when I was thinking it would still do one range scan and check entries for all 4  values during that range scan.   Technically it would be possible to force it to do what I want with the undocumented hint https://jonathanlewis.wordpress.com/2018/11/15/num_index_keys/

                                 

                                num_index_keys(@queryblock   table_alias   index_name    columns_to_range_scan )

                                num_index_keys(@SEL$5 ACT1@SEL$5 XXADM_APPLCNT_APPLICANT_STATUS 1 )

                                 

                                 

                                The big problem, though it that you generate anything between a few hundred and a couple of thousand rows, then you do several nested loop joins or subqueries (that have transformed to neasted loop joins) for each row - and that's using up a lot of CPU: then you sort and discard most of the data because you only want the first few rows.

                                 

                                Obviously you can try forcing index usage for the two tablescans - it may trim some time off the total run time when used across the tens of thousands of executions; but I think you probably need a more radical solution to re-engineer the query or even to go one step further and re-examine the requirement and entire implementation of this part of the code.

                                 

                                One thought has crossed my mind about the query.  Given the "rownum < :bind" predicate, Oracle will be doing "first_rows_n" optimisation. This may have a significant impact on the way it produces the plan - it's going to favour nested loop joins to get the first rows to use as quickly as possible; but the query isn't a good candidate for that approach.  It would be worth testing the query without the rownum predicate to see if Oracle produces a completely different plan for (say) an applicant_id that produces 2000 rows in mid-query that MIGHT turn out to be faster  than its plan to produce the first 10 of 2,000.

                                 

                                Regards

                                Jonathan Lewis

                                • 13. Re: Order of executionplan and the Variations after index creation
                                  3510875

                                  Dear Jonathan,

                                   

                                  Thanks for the update.

                                   

                                  I've highlighted "no further" - because I want to know whether the performance went back to the original after adding the hint, or whether it stayed at the "2 hour improvement" level.

                                   

                                   

                                  The performance went back to original after adding the hint.

                                   

                                  Sure jonathan,Will update the same to development team to look into query reengineering

                                   

                                  Before closing the thread,i have 2 concerns for my understanding.Thanks for bearing with me

                                   

                                  Need confirmation on "Obviously you can try forcing index usage for the two tablescans".Is this about below tables?

                                   

                                  |* 25 |                 TABLE ACCESS FULL           | XXADM_APPLICANT_COURSPREFS_TBL |   478 | 65008 |   241   (2)| 00:00:01 |

                                  |* 26 |                TABLE ACCESS FULL            | XXADM_APPLICANT_DETAILS_TBL    |  6685 |   522K|   311   (2)| 00:00:01 |

                                   

                                  When we try using the hint for step 25 INDEX(@SEL$7E0D484F ACT@SEL$3 (APPLICANT_ID,COLLEGE_ID,COURSE_ID,MEDIUM_ID,HOSTEL_REQUIRED)),we could see below predicate section for that operation.What can be the reason for showing as both filter and acess as below.

                                   

                                  25 - access("ACT"."COLLEGE_ID"=TO_NUMBER(:P_COLLEGE_ID) AND "ACT"."COURSE_ID"=TO_NUMBER(:P_COURSE_ID) AND

                                                "ACT"."MEDIUM_ID"=TO_NUMBER(:P_MEDIUM_ID) AND "ACT"."HOSTEL_REQUIRED"=:P_HOSTEL_REQUIRED)

                                         filter("ACT"."HOSTEL_REQUIRED"=:P_HOSTEL_REQUIRED AND "ACT"."COURSE_ID"=TO_NUMBER(:P_COURSE_ID) AND

                                                "ACT"."COLLEGE_ID"=TO_NUMBER(:P_COLLEGE_ID) AND "ACT"."MEDIUM_ID"=TO_NUMBER(:P_MEDIUM_ID)

                                   

                                  If i want to avoid full table scan for operation 26,which index shall i create because i can see some function in predicate section for that operation

                                    26 - filter("ADT"."STATUS"='Active' AND "ADT"."COURSE_APPLIED_FOR"='DEG' AND (("ADT"."COLLEGE_STATUS_FLAG"='B' OR

                                                "ADT"."COLLEGE_STATUS_FLAG"='C' OR "ADT"."COLLEGE_STATUS_FLAG"='N' OR "ADT"."COLLEGE_STATUS_FLAG"='O' OR

                                                "ADT"."COLLEGE_STATUS_FLAG"='T') OR "ADT"."COLLEGE_STATUS_FLAG" IS NULL))

                                   

                                  Thank you

                                  • 14. Re: Order of executionplan and the Variations after index creation
                                    Jonathan Lewis

                                    I've just started writing up an anaylsis of the query and plan for my blog - and I've published the initial comments so that you can see them straight away because once you stop looking at the query itself and start thinking about WHY the query exists at all it looks as if it's a huge waste of time doing seomthing that shouldn't need to be done in a way that easy for the programmer but a huge waste of resources.

                                     

                                    https://jonathanlewis.wordpress.com/2020/05/05/execution-plans-5/

                                     

                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next