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

        Dear Jonathan,

         

        Overlooked your last update.I am sorry for that.

         

        As requested,attached the explain plan output...one using select * from table(dbms_xplan.display); and other using SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));

         

        Eagerly waiting for your summary.

         

        Thank you

        • 16. Re: tkprof Analysis
          Jonathan Lewis

          Thanks.

          Unfortunately the 14 lines I extracted - which are the ones that really matter - show up as only 11 lines (20 - 30) in the plan produced by EXPLAIN PLAN, so either the stats have changed or a side effect of the bind variable inconsistency and lack of peeking for EXPLAIN PLAN mean we can't draw any sound conclusions about what's happening with the run-time plan.

           

          Regards

          Jonathan Lewis

          • 17. Re: tkprof Analysis
            3510875

            Dear Jonathan,

             

            Thanks for the update.In our test instance,where the sql is running almost same time as in prod,we have enabled statistics=all and pulled the runtime statistics.

            Attached for your reference.Actually this program will take 2 days to complete,once this is completed,i can provide the tkprof also.For now,i have attached the runtime statistics for last run of the same sql.Can you please provide your thoughts.

            • 18. Re: tkprof Analysis
              3510875

              Unable to attach...We have formatted to a good looking one.

               

              SQL> sELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'1n5q5gp58qzc9',format=>'ALLSTATS LAST +COST +BYTES +PEEKED_BINDS'));

               

               

              PLAN_TABLE_OUTPUT

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

              SQL_ID  1n5q5gp58qzc9, child number 0

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

              SELECT COUNT(applicant_id)          FROM (SELECT applicant_id,

                          academic_year,                      applicant_gender,

                             medium_of_study,                      education_type,

                                college_id,                      course_id,

                         medium_id,                      hostel_required,

                       preference_order,                      status_flag,

                        attribute7,  -- Added on 7-mar-20

              college_status_flag,                      percentage, --

                   group_percentage,                      caste_category,

                       alloted_category,                      NULL allotment_type

                               FROM (                 SELECT adt.applicant_id,

                            lmt_gender.lov_code applicant_gender,

              adt.medium_of_study,                      act.college_id,

                      lmt_education_type.lov_code education_type,

               

               

              Plan hash value: 2129674217

               

               

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

              | Id  | Operation                                         | Name                           | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

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

              |   0 | SELECT STATEMENT                                  |                                |      1 |        |       |   470 (100)|      1 |00:00:00.01 |    3196 |      |        |          |

              |   1 |  SORT AGGREGATE                                   |                                |      1 |      1 |    13 |            |      1 |00:00:00.01 |    3196 |      |        |          |

              |*  2 |   VIEW                                            |                                |      1 |      1 |    13 |   470   (1)|      0 |00:00:00.01 |    3196 |      |        |          |

              |*  3 |    COUNT STOPKEY                                  |                                |      1 |        |       |            |      6 |00:00:00.01 |    3196 |      |        |          |

              |   4 |     VIEW                                          |                                |      1 |      1 |    13 |   470   (1)|      6 |00:00:00.01 |    3196 |      |        |          |

              |*  5 |      SORT ORDER BY STOPKEY                        |                                |      1 |      1 |   188 |   470   (1)|      6 |00:00:00.01 |    3196 |  2048 |  2048 | 2048  (0)|

              |*  6 |       FILTER                                      |                                |      1 |        |       |            |     36 |00:00:00.01 |    3196 |      |        |          |

              |   7 |        NESTED LOOPS ANTI                          |                                |      1 |      1 |   188 |   460   (1)|     36 |00:00:00.01 |    3196 |      |        |          |

              |   8 |         NESTED LOOPS                              |                                |      1 |      1 |   178 |   459   (1)|     36 |00:00:00.01 |    3122 |      |        |          |

              |   9 |          NESTED LOOPS                             |                                |      1 |      1 |   165 |   458   (1)|     36 |00:00:00.01 |    3117 |      |        |          |

              |  10 |           NESTED LOOPS                            |                                |      1 |      1 |   152 |   457   (1)|     36 |00:00:00.01 |    3077 |      |        |          |

              |  11 |            NESTED LOOPS                           |                                |      1 |      1 |   139 |   456   (1)|     36 |00:00:00.01 |    3073 |      |        |          |

              |  12 |             NESTED LOOPS                          |                                |      1 |      1 |   126 |   455   (1)|     36 |00:00:00.01 |    3033 |      |        |          |

              |  13 |              NESTED LOOPS                         |                                |      1 |    212 |  9752 |   243   (2)|    210 |00:00:00.01 |     883 |      |        |          |

              |  14 |               NESTED LOOPS                        |                                |      1 |      1 |    19 |     2   (0)|      1 |00:00:00.01 |       4 |      |        |          |

              |  15 |                TABLE ACCESS BY INDEX ROWID        | XXADM_COLLEGE_MASTER_TBL       |      1 |      1 |     6 |     1   (0)|      1 |00:00:00.01 |       2 |      |        |          |

              |* 16 |                 INDEX UNIQUE SCAN                 | XXADM_COLLEGES_PK              |      1 |      1 |       |     0   (0)|      1 |00:00:00.01 |       1 |      |        |          |

              |  17 |                TABLE ACCESS BY INDEX ROWID        | XXADM_LOV_MASTER_TBL           |      1 |      1 |    13 |     1   (0)|      1 |00:00:00.01 |       2 |      |        |          |

              |* 18 |                 INDEX UNIQUE SCAN                 | XXADM_LOVS_PK                  |      1 |      1 |       |     0   (0)|      1 |00:00:00.01 |       1 |      |        |          |

              |* 19 |               TABLE ACCESS FULL                   | XXADM_APPLICANT_COURSPREFS_TBL |      1 |    212 |  5724 |   241   (2)|    210 |00:00:00.01 |     879 |      |        |          |

              |* 20 |              TABLE ACCESS BY INDEX ROWID          | XXADM_APPLICANT_DETAILS_TBL    |    210 |      1 |    80 |     1   (0)|     36 |00:00:00.01 |    2150 |      |        |          |

              |* 21 |               INDEX UNIQUE SCAN                   | XXADM_APPLICANTS_PK            |    210 |      1 |       |     0   (0)|    177 |00:00:00.01 |    1981 |      |        |          |

              |  22 |                NESTED LOOPS                       |                                |    210 |      1 |    26 |     3   (0)|      0 |00:00:00.01 |     630 |      |        |          |

              |* 23 |                 TABLE ACCESS BY INDEX ROWID       | XXADM_APPLICANT_COURSPREFS_TBL |    210 |      1 |    14 |     2   (0)|      0 |00:00:00.01 |     630 |      |        |          |

              |* 24 |                  INDEX UNIQUE SCAN                | XXADM_APPLCNT_PREF_ORDER_UK    |    210 |      1 |       |     1   (0)|    210 |00:00:00.01 |     420 |      |        |          |

              |* 25 |                 TABLE ACCESS BY INDEX ROWID       | XXADM_CATEGORY_MASTER_TBL      |      0 |      1 |    12 |     1   (0)|      0 |00:00:00.01 |       0 |      |        |          |

              |* 26 |                  INDEX UNIQUE SCAN                | XXADM_CATEGORY_PK              |      0 |      1 |       |     0   (0)|      0 |00:00:00.01 |       0 |      |        |          |

              |* 27 |                TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL |    210 |      1 |    10 |     3   (0)|     58 |00:00:00.01 |     615 |      |        |          |

              |* 28 |                 INDEX RANGE SCAN                  | XXADM_APPLCNT_PREFS_UK         |    210 |      5 |       |     2   (0)|   2038 |00:00:00.01 |     220 |      |        |          |

              |* 29 |                TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL |    179 |      1 |    10 |     3   (0)|      3 |00:00:00.01 |     528 |      |        |          |

              |* 30 |                 INDEX RANGE SCAN                  | XXADM_APPLCNT_PREFS_UK         |    179 |      5 |       |     2   (0)|   1756 |00:00:00.01 |     188 |      |        |          |

              |  31 |             TABLE ACCESS BY INDEX ROWID           | XXADM_LOV_MASTER_TBL           |     36 |      1 |    13 |     1   (0)|     36 |00:00:00.01 |      40 |      |        |          |

              |* 32 |              INDEX UNIQUE SCAN                    | XXADM_LOVS_PK                  |     36 |      1 |       |     0   (0)|     36 |00:00:00.01 |       4 |      |        |          |

              |* 33 |            INDEX UNIQUE SCAN                      | XXADM_LOVS_PK                  |     36 |      1 |       |     0   (0)|     36 |00:00:00.01 |       4 |      |        |          |

              |  34 |           TABLE ACCESS BY INDEX ROWID             | XXADM_LOV_MASTER_TBL           |     36 |      1 |    13 |     1   (0)|     36 |00:00:00.01 |      40 |      |        |          |

              |* 35 |            INDEX UNIQUE SCAN                      | XXADM_LOVS_PK                  |     36 |      1 |       |     0   (0)|     36 |00:00:00.01 |       4 |      |        |          |

              |* 36 |          TABLE ACCESS BY INDEX ROWID              | XXADM_LOV_MASTER_TBL           |     36 |      1 |    13 |     1   (0)|     36 |00:00:00.01 |       5 |      |        |          |

              |* 37 |           INDEX UNIQUE SCAN                       | XXADM_LOVS_PK                  |     36 |      1 |       |     0   (0)|     36 |00:00:00.01 |       4 |      |        |          |

              |* 38 |         TABLE ACCESS BY INDEX ROWID               | XXADM_APPLICANT_COURSPREFS_TBL |     36 |      1 |    10 |     1   (0)|      0 |00:00:00.01 |      74 |      |        |          |

              |* 39 |          INDEX UNIQUE SCAN                        | XXADM_APPLCNT_PREF_ORDER_UK    |     36 |      1 |       |     0   (0)|     36 |00:00:00.01 |      38 |      |        |          |

              |* 40 |        VIEW                                       | index$_join$_014               |      0 |      1 |    12 |     0   (0)|      0 |00:00:00.01 |       0 |      |        |          |

              |* 41 |         HASH JOIN                                 |                                |      0 |        |       |            |      0 |00:00:00.01 |       0 |  1888K|  1888K|          |

              |* 42 |          INDEX RANGE SCAN                         | XXADM_CATEGORY_PK              |      0 |      1 |    12 |     0   (0)|      0 |00:00:00.01 |       0 |      |        |          |

              |  43 |          INLIST ITERATOR                          |                                |      0 |        |       |            |      0 |00:00:00.01 |       0 |      |        |          |

              |* 44 |           INDEX UNIQUE SCAN                       | XXADM_CATEGORY_CODE_UK         |      0 |      1 |    12 |     0   (0)|      0 |00:00:00.01 |       0 |      |        |          |

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

               

               

              Peeked Binds (identified by position):

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

               

               

                 1 - :1 (NUMBER): 9

                 2 - :2 (NUMBER): 10

                 3 - (NUMBER): 17

                 4 - (VARCHAR2(30), CSID=873): 'N'

                 5 - (NUMBER): 6

               

               

              Predicate Information (identified by operation id):

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

               

               

                 2 - filter("APPLICANT_ID"=:P_APPLICANT_ID)

                 3 - filter(ROWNUM<=:P_SEATS)

                 5 - filter(ROWNUM<=:P_SEATS)

                 6 - filter(CASE "ACT"."HOSTEL_REQUIRED" WHEN 'Y' THEN CASE  WHEN ("ADT"."DISTANCE_IN_KMS">20 AND "LMT_RELIGION"."LOV_CODE"='HINDU' AND  IS NULL) THEN 1 ELSE 2 END  ELSE 1 END =1)

                16 - access("CMT"."COLLEGE_ID"=:P_COLLEGE_ID)

                18 - access("CMT"."EDUCATION_TYPE"="LMT_EDUCATION_TYPE"."LOV_ID")

                19 - 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))

                20 - filter(("ADT"."STATUS"='Active' AND (INTERNAL_FUNCTION("ADT"."COLLEGE_STATUS_FLAG") OR "ADT"."COLLEGE_STATUS_FLAG" IS NULL) AND "ADT"."COURSE_APPLIED_FOR"='DEG'))

                21 - access("ADT"."APPLICANT_ID"="ACT"."APPLICANT_ID")

                     filter(( IS NULL AND "ACT"."PREFERENCE_ORDER"<=NVL(,"ACT"."PREFERENCE_ORDER") AND "ACT"."PREFERENCE_ORDER">=NVL(,"ACT"."PREFERENCE_ORDER")))

                23 - filter((INTERNAL_FUNCTION("ACT1"."STATUS_FLAG") AND NVL("ACT1"."ATTRIBUTE7",'N')='N'))

                24 - access("ACT1"."APPLICANT_ID"=:B1 AND "ACT1"."PREFERENCE_ORDER"=:B2)

                25 - filter("CATEGORY_CODE"='OPENMERT')

                26 - access("CATEGORY_ID"=TO_NUMBER("ACT1"."ATTRIBUTE1"))

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

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

                29 - filter("STATUS_FLAG"='C')

                30 - access("ACT2"."APPLICANT_ID"=:B1)

                32 - access("ADT"."RELIGION"="LMT_RELIGION"."LOV_ID")

                33 - access("ADT"."APPEARANCE_TYPE"="LMT_APPEARANCE"."LOV_ID")

                35 - access("ADT"."PASS_TYPE"="LMT_PASS"."LOV_ID")

                36 - filter(CASE "ACT"."HOSTEL_REQUIRED" WHEN 'Y' THEN CASE  WHEN ("LMT_EDUCATION_TYPE"."LOV_CODE"='COEDUCOL' AND "LMT_GENDER"."LOV_CODE"='FEMALE') THEN 2 ELSE 1 END  ELSE 1 END

                            =1)

                37 - access("ADT"."APPLICANT_GENDER"="LMT_GENDER"."LOV_ID")

                38 - filter("ACT3"."STATUS_FLAG"='O')

                39 - access("ACT3"."APPLICANT_ID"="ADT"."APPLICANT_ID" AND "ACT"."PREFERENCE_ORDER"="ACT3"."PREFERENCE_ORDER")

                40 - filter(("CATEGORY_ID"=:B1 AND INTERNAL_FUNCTION("CATEGORY_CODE")))

                41 - access(ROWID=ROWID)

                42 - access("CATEGORY_ID"=:B1)

                44 - access(("CATEGORY_CODE"='BACKWRDC' OR "CATEGORY_CODE"='BACKWRDE'))

               

               

              Note

              -----

                 - dynamic statistics used: dynamic sampling (level=2)

                 - statistics feedback used for this statement

                 - this is an adaptive plan

                 - 4 Sql Plan Directives used for this statement

               

               

               

               

              119 rows selected.

               

               

              SQL>

              • 19. Re: tkprof Analysis
                3510875

                Dear Jonathan,

                 

                Really,not sure why i am unable to attached the formatted plan to this thread,it is really tough to read with unformatted text.Hence,i created a new thread and attached the plan to it.

                 

                https://community.oracle.com/message/15595458#15595458

                 

                Thank you

                • 20. Re: tkprof Analysis
                  Jonathan Lewis

                  We still have the same problem.

                  The plan from your test instance is 44 lines long, the plan from the prod instance (omitting the SELECT line at the top) was 47 lines; so Oracle was doing something different in production.  Again the 3 lines difference appears in the critical bit of the plan.  (Is test on exactly the same version of Oracle with exactly the same set of patches ? - are the object definitions exactly the same, in particular are there any NOT NULL columns in test that aren't declared NOT NULL in prod, or any column constraints in test that don't exist in prod ?)

                   

                  The other thing about test and prod that's a big unknown is the effect of feedback - test is reporting dynamic sampling, statistics feedback and plan directives - so you can't be certain about why it chose this plan.

                   

                  However, the critical section still gives us some ideas.  I've extracted a critical few lines from the plan and trimmed out redundant columns so that I can highlight one option:

                   

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

                  | Id  | Operation                                         | Name                           | Starts | A-Rows | Buffers |

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

                  |* 20 |              TABLE ACCESS BY INDEX ROWID          | XXADM_APPLICANT_DETAILS_TBL    |    210 |     36 |    2150 |

                  |* 21 |               INDEX UNIQUE SCAN                   | XXADM_APPLICANTS_PK            |    210 |    177 |    1981 |

                  |  22 |                NESTED LOOPS                       |                                |    210 |      0 |     630 |

                  |* 23 |                 TABLE ACCESS BY INDEX ROWID       | XXADM_APPLICANT_COURSPREFS_TBL |    210 |      0 |     630 |

                  |* 24 |                  INDEX UNIQUE SCAN                | XXADM_APPLCNT_PREF_ORDER_UK    |    210 |    210 |     420 |

                  |* 25 |                 TABLE ACCESS BY INDEX ROWID       | XXADM_CATEGORY_MASTER_TBL      |      0 |      0 |       0 |

                  |* 26 |                  INDEX UNIQUE SCAN                | XXADM_CATEGORY_PK              |      0 |      0 |       0 |

                  |* 27 |                TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL |    210 |     58 |     615 |

                  |* 28 |                 INDEX RANGE SCAN                  | XXADM_APPLCNT_PREFS_UK         |    210 |   2038 |     220 |

                  |* 29 |                TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL |    179 |      3 |     528 |

                  |* 30 |                 INDEX RANGE SCAN                  | XXADM_APPLCNT_PREFS_UK         |    179 |   1756 |     188 |

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

                   

                    20 - filter(("ADT"."STATUS"='Active' AND (INTERNAL_FUNCTION("ADT"."COLLEGE_STATUS_FLAG")

                                  OR "ADT"."COLLEGE_STATUS_FLAG" IS NULL) AND "ADT"."COURSE_APPLIED_FOR"='DEG'))

                    21 - access("ADT"."APPLICANT_ID"="ACT"."APPLICANT_ID")

                        filter(( IS NULL AND "ACT"."PREFERENCE_ORDER"<=NVL(,"ACT"."PREFERENCE_ORDER")

                                 AND "ACT"."PREFERENCE_ORDER">=NVL(,"ACT"."PREFERENCE_ORDER")))

                    23 - filter((INTERNAL_FUNCTION("ACT1"."STATUS_FLAG") AND NVL("ACT1"."ATTRIBUTE7",'N')='N'))

                    24 - access("ACT1"."APPLICANT_ID"=:B1 AND "ACT1"."PREFERENCE_ORDER"=:B2)

                    25 - filter("CATEGORY_CODE"='OPENMERT')

                    26 - access("CATEGORY_ID"=TO_NUMBER("ACT1"."ATTRIBUTE1"))

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

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

                    29 - filter("STATUS_FLAG"='C')

                    30 - access("ACT2"."APPLICANT_ID"=:B1)

                   

                  A very large fraction of your time spent was CPU time, which often correlates very closely with buffer gets, but could be due to the processing of rows and columns rather than buffers. This may be the case for at least part of your code.
                  If you look at operation 28 you start 210 times and acquire 2,038 rowids accessing 220 buffers. Using those rowids you then end up visiting the table 2,038 times, getting a further 395 buffers, to discard all but 48 rows. Similarly at operation 30 you start 179 times, acquire 1,756 rowids from 188 buffers, then visit the table getting a further 340 buffers and discarding all but 3 rows.
                  If you look at the table filter predicates (operations 27 and 29) you're discarding a huge fraction of the rows based on the status_flag. This suggests two options:
                  a) add the status_flag to the index you're using and avoid visiting the table thousands of times.
                  b) create a new index on (applicant_id, status_flag) to make the access as efficient as possible.
                  A refinement on (b) is to create two virtual columns and an index on those columns so that the index references only those rows that match you values of status flag you're interested in, and the modify the query to reference these virtual columns.  (Let me know if you want me to expand on this idea if you're allowed to do something like that to the application).  (The point of the extra complexity is that the new index would - on the basis of the stats from this run - be 1/40th of the size the simple 2-column index would have to be)
                  Regards
                  Jonathan Lewis

                   

                   

                   

                   

                   

                  17

                  • 21. Re: tkprof Analysis
                    3510875

                    Dear Jonathan,

                     

                    You are absolutely right.We observed optimizer adaptive features parameter is true in test where as in prod,it is set to false.

                     

                    Many thanks for the updates.We will go with first option as we dont have feasibility of changing the code.

                     

                    Existing index and its definition:

                     

                    CREATE UNIQUE INDEX "XXADM"."XXADM_APPLCNT_PREFS_UK" ON "

                    XXADM"."XXADM_APPLICANT_COURSPREFS_TBL"

                    ("APPLICANT_ID", "COLLEGE_ID", "COURSE_ID", "MEDIUM_ID", "HOSTEL_REQUIRED")

                     

                    SQL> select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION,TABLE_NAME from dba_ind_columns where TABLE_NAME='XXADM_APPLICANT_COURSPREFS_TBL' and TABLE_OWNER='XXADM'

                     

                     

                    INDEX_NAME                               COLUMN_NAME          COLUMN_POSITION TABLE_NAME

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

                    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

                     

                    We will create the index with status_flag in column_postion 6 as below and we will test the performance.

                     

                    CREATE UNIQUE INDEX "XXADM"."XXADM_APPLCNT_PREFS_UK" ON "

                    XXADM"."XXADM_APPLICANT_COURSPREFS_TBL"

                    ("APPLICANT_ID", "COLLEGE_ID", "COURSE_ID", "MEDIUM_ID", "HOSTEL_REQUIRED","STATUS_FLAG");

                     

                    Thanks again.

                    • 22. Re: tkprof Analysis
                      3510875

                      Dear Jonathan

                       

                      Want to ask one question like what made you to focus on steps 28 and 30.Just wanted to know as we have other 3 similar queries which needs to be tuned.It will be useful to further tune our queries.

                      The reason to ask this question was most of the team members who sees this plan are focussing on full table scan.They says,total cost of the statement is 607 and that full table scan alone took a cost of 448.(TABLE ACCESS FULL XXABC_APPLICANT_COURSPREFS_TBL (cr=1635 pr=0 pw=0 time=5324 us cost=448 size=3861) and says this is the problem.

                       

                      Thanks a lot

                      • 23. Re: tkprof Analysis
                        Jonathan Lewis

                        You can't just add a column to the current 5 and leave the index UNIQUE or you could get duplicates of the first 5. If you're going to add the column at position 6 then you need to create the index as non-unique but have a unique constraint on the table.

                         

                        If you generally have multiple rows for each applicant (and the stats on the plan you provided suggested that you have several rows per applicant_id) then you can create the index with the option "compress 1" to save a little space. On the other hand, since you've only got two indexes on this table I'd consider adding a whole new index on just "(applicant_id, status) compress 1"  (you could also rebuild both the other indexes with compress 1, of course).

                         

                        Regards

                        Jonathan Lewis

                        • 24. Re: tkprof Analysis
                          Jonathan Lewis

                          Remember that I picked operations 27-30 because (a) they were common to the two plans and I didn't want to make any comment about what was happening in the bit of the plan that changed between databases and (b) they showed an obvious easy example of things you might consider.

                           

                          I picked the entire block from 20-30 and discounted the full tablescan because the initial plan you posted (a) reported a time of 33,379 microseconds and the full tablescan reported only 5,324 microseconds so most of the was in something other than the tablescan and (b) the tablescan reported between 608 and 2,381 rows returned so wasn't likely to get much faster regardless of the path used to get the rows and (c) that block of lines showed 4 places where the number of index entries acquired was large but the number of surviving table rows was very small by comparison, indicating a lot of wasted work.

                           

                          Regards

                          Jonathan Lewis

                          • 25. Re: tkprof Analysis
                            3510875

                            Dear Jonathan,

                             

                            Thanks for the update.I searched for COMPRESS option and this is really wonderful.As suggsted,We are going to create a brand new index on applicant_id and status_flag.Because we are not getting proper support from developer and the one who wrote this code is not available now,I would like to ask one more question.Is this index need to be unique or nonunique?

                             

                            CREATE INDEX "XXADM"."XXADM_APPLCNT_APPLICANT_STATUS" ON "XXADM"."XXADM_APPLICANT_COURSPREFS_TBL"("APPLICANT_ID","STATUS_FLAG") COMPRESS 1;

                             

                            Thank you

                            • 26. Re: tkprof Analysis
                              Jonathan Lewis

                              3510875 wrote:


                              I would like to ask one more question.Is this index need to be unique or nonunique?

                               

                              CREATE INDEX "XXADM"."XXADM_APPLCNT_APPLICANT_STATUS" ON "XXADM"."XXADM_APPLICANT_COURSPREFS_TBL"("APPLICANT_ID","STATUS_FLAG") COMPRESS 1;

                               

                              There is no way to deduce whether or not the combination of  (application_id, status_flag) should be constrained to be unique across the table. However if that was supposed to be the case there would already be a uniqueness constraint on the table to express that rule, and that would have created the index automatically. So there are no indications that the combination is supposed to be unique, so I wouldn't introduce one now.  Create the index non-unique.


                              Regards

                              Jonathan Lewis

                              • 27. Re: tkprof Analysis
                                3510875

                                Dear Jonathan,

                                 

                                This is my expection.With the new index in place on applicant_id and status_flag,we will still go for index range scan to get the rowids,but we can avoid much  of the filtering while accessing the table.Please correct if i am wrong.

                                 

                                Thank you!. I got all the answers i am looking for and much satisfied.I will create the index and post the results here,might be within a week because the development team said,they cannot run this immediately as they need data from users to run the program again.

                                 

                                Thanks a lot for all the help you provided us.For sure,once testing is done-i will update the results here.

                                • 28. Re: tkprof Analysis
                                  3510875

                                  Dear Jonathan,

                                   

                                  After creating index in our test instance,below is the plan

                                   

                                  Once again,i am unable to attach the plan,hence pasting here

                                   

                                  Plan hash value: 105474107

                                   

                                   

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

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

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

                                  |   0 | SELECT STATEMENT                                  |                                |       |       |   460 (100)|          |

                                  |   1 |  SORT AGGREGATE                                   |                                |     1 |    13 |            |          |

                                  |*  2 |   VIEW                                            |                                |     1 |    13 |   460   (1)| 00:00:01 |

                                  |*  3 |    COUNT STOPKEY                                  |                                |       |       |            |          |

                                  |   4 |     VIEW                                          |                                |     1 |    13 |   460   (1)| 00:00:01 |

                                  |*  5 |      SORT ORDER BY STOPKEY                        |                                |     1 |   188 |   460   (1)| 00:00:01 |

                                  |*  6 |       FILTER                                      |                                |       |       |            |          |

                                  |   7 |        NESTED LOOPS ANTI                          |                                |     1 |   188 |   451   (1)| 00:00:01 |

                                  |   8 |         NESTED LOOPS                              |                                |     1 |   178 |   450   (1)| 00:00:01 |

                                  |   9 |          NESTED LOOPS                             |                                |     1 |   165 |   449   (1)| 00:00:01 |

                                  |  10 |           NESTED LOOPS                            |                                |     1 |   152 |   448   (1)| 00:00:01 |

                                  |  11 |            NESTED LOOPS                           |                                |     1 |   139 |   447   (1)| 00:00:01 |

                                  |  12 |             NESTED LOOPS                          |                                |     1 |   126 |   446   (1)| 00:00:01 |

                                  |  13 |              NESTED LOOPS                         |                                |   203 |  9338 |   243   (2)| 00:00:01 |

                                  |  14 |               NESTED LOOPS                        |                                |     1 |    19 |     2   (0)| 00:00:01 |

                                  |  15 |                TABLE ACCESS BY INDEX ROWID        | XXADM_COLLEGE_MASTER_TBL       |     1 |     6 |     1   (0)| 00:00:01 |

                                  |* 16 |                 INDEX UNIQUE SCAN                 | XXADM_COLLEGES_PK              |     1 |       |     0   (0)|          |

                                  |  17 |                TABLE ACCESS BY INDEX ROWID        | XXADM_LOV_MASTER_TBL           |     1 |    13 |     1   (0)| 00:00:01 |

                                  |* 18 |                 INDEX UNIQUE SCAN                 | XXADM_LOVS_PK                  |     1 |       |     0   (0)|          |

                                  |* 19 |               TABLE ACCESS FULL                   | XXADM_APPLICANT_COURSPREFS_TBL |   203 |  5481 |   241   (2)| 00:00:01 |

                                  |* 20 |              TABLE ACCESS BY INDEX ROWID          | XXADM_APPLICANT_DETAILS_TBL    |     1 |    80 |     1   (0)| 00:00:01 |

                                  |* 21 |               INDEX UNIQUE SCAN                   | XXADM_APPLICANTS_PK            |     1 |       |     0   (0)|          |

                                  |  22 |                NESTED LOOPS                       |                                |     1 |    26 |     3   (0)| 00:00:01 |

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

                                  |* 24 |                  INDEX UNIQUE SCAN                | XXADM_APPLCNT_PREF_ORDER_UK    |     1 |       |     1   (0)| 00:00:01 |

                                  |* 25 |                 TABLE ACCESS BY INDEX ROWID       | XXADM_CATEGORY_MASTER_TBL      |     1 |    12 |     1   (0)| 00:00:01 |

                                  |* 26 |                  INDEX UNIQUE SCAN                | XXADM_CATEGORY_PK              |     1 |       |     0   (0)|          |

                                  |* 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 |

                                  |  31 |             TABLE ACCESS BY INDEX ROWID           | XXADM_LOV_MASTER_TBL           |     1 |    13 |     1   (0)| 00:00:01 |

                                  |* 32 |              INDEX UNIQUE SCAN                    | XXADM_LOVS_PK                  |     1 |       |     0   (0)|          |

                                  |* 33 |            INDEX UNIQUE SCAN                      | XXADM_LOVS_PK                  |     1 |       |     0   (0)|          |

                                  |  34 |           TABLE ACCESS BY INDEX ROWID             | XXADM_LOV_MASTER_TBL           |     1 |    13 |     1   (0)| 00:00:01 |

                                  |* 35 |            INDEX UNIQUE SCAN                      | XXADM_LOVS_PK                  |     1 |       |     0   (0)|          |

                                  |* 36 |          TABLE ACCESS BY INDEX ROWID              | XXADM_LOV_MASTER_TBL           |     1 |    13 |     1   (0)| 00:00:01 |

                                  |* 37 |           INDEX UNIQUE SCAN                       | XXADM_LOVS_PK                  |     1 |       |     0   (0)|          |

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

                                  |* 39 |          INDEX UNIQUE SCAN                        | XXADM_APPLCNT_PREF_ORDER_UK    |     1 |       |     0   (0)|          |

                                  |* 40 |        VIEW                                       | index$_join$_014               |     1 |    12 |     0   (0)|          |

                                  |* 41 |         HASH JOIN                                 |                                |       |       |            |          |

                                  |* 42 |          INDEX RANGE SCAN                         | XXADM_CATEGORY_PK              |     1 |    12 |     0   (0)|          |

                                  |  43 |          INLIST ITERATOR                          |                                |       |       |            |          |

                                  |* 44 |           INDEX UNIQUE SCAN                       | XXADM_CATEGORY_CODE_UK         |     1 |    12 |     0   (0)|          |

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

                                   

                                   

                                  Predicate Information (identified by operation id):

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

                                   

                                   

                                     2 - filter("APPLICANT_ID"=:P_APPLICANT_ID)

                                     3 - filter(ROWNUM<=:P_SEATS)

                                     5 - filter(ROWNUM<=:P_SEATS)

                                     6 - filter(CASE "ACT"."HOSTEL_REQUIRED" WHEN 'Y' THEN CASE  WHEN ("ADT"."DISTANCE_IN_KMS">20 AND

                                                "LMT_RELIGION"."LOV_CODE"='HINDU' AND  IS NULL) THEN 1 ELSE 2 END  ELSE 1 END =1)

                                    16 - access("CMT"."COLLEGE_ID"=:P_COLLEGE_ID)

                                    18 - access("CMT"."EDUCATION_TYPE"="LMT_EDUCATION_TYPE"."LOV_ID")

                                    19 - 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))

                                    20 - filter(("ADT"."STATUS"='Active' AND (INTERNAL_FUNCTION("ADT"."COLLEGE_STATUS_FLAG") OR "ADT"."COLLEGE_STATUS_FLAG"

                                                IS NULL) AND "ADT"."COURSE_APPLIED_FOR"='DEG'))

                                    21 - access("ADT"."APPLICANT_ID"="ACT"."APPLICANT_ID")

                                         filter(( IS NULL AND "ACT"."PREFERENCE_ORDER"<=NVL(,"ACT"."PREFERENCE_ORDER") AND

                                                "ACT"."PREFERENCE_ORDER">=NVL(,"ACT"."PREFERENCE_ORDER")))

                                    23 - filter((INTERNAL_FUNCTION("ACT1"."STATUS_FLAG") AND NVL("ACT1"."ATTRIBUTE7",'N')='N'))

                                    24 - access("ACT1"."APPLICANT_ID"=:B1 AND "ACT1"."PREFERENCE_ORDER"=:B2)

                                    25 - filter("CATEGORY_CODE"='OPENMERT')

                                    26 - access("CATEGORY_ID"=TO_NUMBER("ACT1"."ATTRIBUTE1"))

                                    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')

                                    32 - access("ADT"."RELIGION"="LMT_RELIGION"."LOV_ID")

                                    33 - access("ADT"."APPEARANCE_TYPE"="LMT_APPEARANCE"."LOV_ID")

                                    35 - access("ADT"."PASS_TYPE"="LMT_PASS"."LOV_ID")

                                    36 - filter(CASE "ACT"."HOSTEL_REQUIRED" WHEN 'Y' THEN CASE  WHEN ("LMT_EDUCATION_TYPE"."LOV_CODE"='COEDUCOL' AND

                                                "LMT_GENDER"."LOV_CODE"='FEMALE') THEN 2 ELSE 1 END  ELSE 1 END =1)

                                    37 - access("ADT"."APPLICANT_GENDER"="LMT_GENDER"."LOV_ID")

                                    38 - filter("ACT3"."STATUS_FLAG"='O')

                                    39 - access("ACT3"."APPLICANT_ID"="ADT"."APPLICANT_ID" AND "ACT"."PREFERENCE_ORDER"="ACT3"."PREFERENCE_ORDER")

                                    40 - filter(("CATEGORY_ID"=:B1 AND INTERNAL_FUNCTION("CATEGORY_CODE")))

                                    41 - access(ROWID=ROWID)

                                    42 - access("CATEGORY_ID"=:B1)

                                    44 - access(("CATEGORY_CODE"='BACKWRDC' OR "CATEGORY_CODE"='BACKWRDE'))

                                   

                                  Thank you

                                  • 29. Re: tkprof Analysis
                                    3510875

                                    Will update the final runtime once program completes.