5 Replies Latest reply on Jun 1, 2020 10:48 AM by Jonathan Lewis

    Sudden Spike in cost

    3510875

      Dear Jonathan,

       

      This is related with Re: tkprof Analysis and Re: Order of executionplan and the Variations after index creation

       

      Need your help

       

      Exactly not sure what happened,the cost of the query raised from 560 to 1100 and the runtime increased.From our observation,we could see step 6 under predicate section seems running the subquery without unnesting,which is not the case earlier.Attached the execution plan for your reference.Can you please help us?

       

      Thank You

        • 1. Re: Sudden Spike in cost
          Jonathan Lewis

          There's one change in the plan - which is the index at operation 41. It's possible that this accounts for the extra run time - perhaps combined with a fairly small change in the data size.

           

          The apparent change in the FILTER behaviour at operation 6 is the difference between doing EXPLAIN PLAN and reporting the plan table and reporting the plan  in memory using display_cursor().

           

          The "double the cost" may simply be an indication that you've gathered stats recently and the numbers have changed slightly - especially if you've got some histograms in place - it may also be the difference between explain plan NOT having any actual values to drive the arithmetic and a run-time plan peeking at bind variables.

           

          Regards

          Jonathan Lewis

          • 2. Re: Sudden Spike in cost
            3510875

            Dear Jonathan,

             

            Thanks for the update.We are going through the plan.

            Regarding histograms,we dont have any

             

            SQL> SELECT distinct

              histogram

            FROM

              all_tab_col_statistics

            WHERE

              owner = 'XXADM'  2    3    4    5    6  ;

             

            HISTOGRAM

            ----------

            NONE

             

            We will run the program in couple of days and will monitor the same

             

            Thank You,

            • 3. Re: Sudden Spike in cost
              3510875

              Dear Jonathan,

               

              Need your help

              We have submitted the program and captured the current runtime stats.Trying to attach the current run time stats and previous runtime stats with +outline/alias sections.

               

              Runtime Stats:

              =============

               

              Previous Plan:

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

               

              SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1n5q5gp58qzc9',1,format=>'ALLSTATS LAST +COST +ALIAS +OUTLINE +PEEKED_BINDS'));

               

               

              PLAN_TABLE_OUTPUT

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

              SQL_ID  1n5q5gp58qzc9, child number 1

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

              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: 2702070113

               

               

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

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

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

              |   0 | SELECT STATEMENT                            |                                |      1 |        |   574 (100)|      1 |00:00:00.02 |    3822 |       |       |         |

              |   1 |  SORT AGGREGATE                             |                                |      1 |      1 |            |      1 |00:00:00.02 |    3822 |       |       |         |

              |*  2 |   VIEW                                      |                                |      1 |      1 |   574   (2)|      0 |00:00:00.02 |    3822 |       |       |         |

              |*  3 |    COUNT STOPKEY                            |                                |      1 |        |            |      2 |00:00:00.02 |    3822 |       |       |         |

              |   4 |     VIEW                                    |                                |      1 |      1 |   574   (2)|      2 |00:00:00.02 |    3822 |       |       |         |

              |*  5 |      SORT ORDER BY STOPKEY                  |                                |      1 |      1 |   574   (2)|      2 |00:00:00.02 |    3822 |  2048 |  2048 | 2048  (0)|

              |*  6 |       FILTER                                |                                |      1 |        |            |    171 |00:00:00.02 |    3822 |       |       |         |

              |   7 |        NESTED LOOPS                         |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    3128 |       |       |         |

              |   8 |         NESTED LOOPS                        |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    2946 |       |       |         |

              |   9 |          NESTED LOOPS                       |                                |      1 |      1 |   567   (2)|    182 |00:00:00.02 |    2942 |       |       |         |

              |  10 |           NESTED LOOPS                      |                                |      1 |      1 |   566   (2)|    182 |00:00:00.02 |    2938 |       |       |         |

              |  11 |            NESTED LOOPS ANTI                |                                |      1 |      1 |   565   (2)|    182 |00:00:00.02 |    2752 |       |       |         |

              |  12 |             NESTED LOOPS ANTI               |                                |      1 |      1 |   562   (2)|    182 |00:00:00.02 |    2388 |       |       |         |

              |* 13 |              HASH JOIN                      |                                |      1 |      5 |   557   (2)|    182 |00:00:00.02 |    2022 |  1599K|  1599K| 1503K (0)|

              |  14 |               VIEW                          | index$_join$_008               |      1 |    127 |     2   (0)|    127 |00:00:00.01 |       8 |       |       |         |

              |* 15 |                HASH JOIN                    |                                |      1 |        |            |    127 |00:00:00.01 |       8 |  1368K|  1368K| 1522K (0)|

              |  16 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_CODE_UK             |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |

              |  17 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_PK                  |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |

              |* 18 |               HASH JOIN                     |                                |      1 |    478 |   555   (2)|    182 |00:00:00.01 |    2014 |  1245K|  1245K| 1277K (0)|

              |  19 |                NESTED LOOPS                 |                                |      1 |    478 |   243   (2)|    209 |00:00:00.01 |     883 |       |       |         |

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

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

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

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

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

              |* 25 |                 TABLE ACCESS FULL           | XXADM_APPLICANT_COURSPREFS_TBL |      1 |    478 |   241   (2)|    209 |00:00:00.01 |     879 |       |       |         |

              |* 26 |                TABLE ACCESS FULL            | XXADM_APPLICANT_DETAILS_TBL    |      1 |   6685 |   311   (2)|  10488 |00:00:00.01 |    1131 |       |       |         |

              |* 27 |              TABLE ACCESS BY INDEX ROWID    | XXADM_APPLICANT_COURSPREFS_TBL |    182 |   8881 |     1   (0)|      0 |00:00:00.01 |     366 |       |       |         |

              |* 28 |               INDEX UNIQUE SCAN             | XXADM_APPLCNT_PREF_ORDER_UK    |    182 |      1 |     0   (0)|    182 |00:00:00.01 |     184 |       |       |         |

              |  29 |             VIEW PUSHED PREDICATE           | VW_SQ_1                        |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |       |       |         |

              |  30 |              NESTED LOOPS                   |                                |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |       |       |         |

              |* 31 |               TABLE ACCESS BY INDEX ROWID   | XXADM_APPLICANT_COURSPREFS_TBL |    182 |      1 |     2   (0)|      0 |00:00:00.01 |     364 |       |       |         |

              |* 32 |                INDEX UNIQUE SCAN            | XXADM_APPLCNT_PREF_ORDER_UK    |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     184 |       |       |         |

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

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

              |  35 |            TABLE ACCESS BY INDEX ROWID      | XXADM_LOV_MASTER_TBL           |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     186 |       |       |         |

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

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

              |* 38 |          INDEX UNIQUE SCAN                  | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |       |       |         |

              |  39 |         TABLE ACCESS BY INDEX ROWID         | XXADM_LOV_MASTER_TBL           |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     182 |       |       |         |

              |* 40 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    182 |      1 |     3   (0)|     29 |00:00:00.01 |     507 |       |       |         |

              |* 41 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_PREFS_UK         |    182 |      5 |     2   (0)|   1450 |00:00:00.01 |     191 |       |       |         |

              |  42 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    171 |      1 |     2   (0)|      0 |00:00:00.01 |     173 |       |       |         |

              --|* 43 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_APPLICANT_STATUS |    171 |      1 |     1   (0)|      0 |00:00:00.01 |     173 |       |       |         |

              |* 44 |        VIEW                                 | index$_join$_014               |      6 |      1 |     0   (0)|      0 |00:00:00.01 |      14 |       |       |         |

              |* 45 |         HASH JOIN                           |                                |      6 |        |            |      0 |00:00:00.01 |      14 |  1519K|  1519K|  666K (0)|

              |* 46 |          INDEX RANGE SCAN                   | XXADM_CATEGORY_PK              |      6 |      1 |     0   (0)|      6 |00:00:00.01 |       6 |       |       |         |

              |  47 |          INLIST ITERATOR                    |                                |      6 |        |            |     12 |00:00:00.01 |       8 |       |       |         |

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

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

               

               

              Query Block Name / Object Alias (identified by operation id):

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

               

               

                 1 - SEL$1

                 2 - SEL$2        / from$_subquery$_001@SEL$1

                 3 - SEL$2

                 4 - SEL$7E0D484F / from$_subquery$_002@SEL$2

                 5 - SEL$7E0D484F

                14 - SEL$082F290F / LMT_GENDER@SEL$3

                15 - SEL$082F290F

                16 - SEL$082F290F / indexjoin$_alias$_001@SEL$082F290F

                17 - SEL$082F290F / indexjoin$_alias$_002@SEL$082F290F

                21 - SEL$7E0D484F / CMT@SEL$3

                22 - SEL$7E0D484F / CMT@SEL$3

                23 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3

                24 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3

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

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

                27 - SEL$7E0D484F / ACT3@SEL$7

                28 - SEL$7E0D484F / ACT3@SEL$7

                29 - SEL$A75BE177 / VW_SQ_1@SEL$67DC521B

                30 - SEL$A75BE177

                31 - SEL$A75BE177 / ACT1@SEL$8

                32 - SEL$A75BE177 / ACT1@SEL$8

                33 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9

                34 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9

                35 - SEL$7E0D484F / LMT_PASS@SEL$3

                36 - SEL$7E0D484F / LMT_PASS@SEL$3

                37 - SEL$7E0D484F / LMT_APPEARANCE@SEL$3

                38 - SEL$7E0D484F / LMT_RELIGION@SEL$3

                39 - SEL$7E0D484F / LMT_RELIGION@SEL$3

                40 - SEL$5        / ACT1@SEL$5

                41 - SEL$5        / ACT1@SEL$5

                42 - SEL$6        / ACT2@SEL$6

                43 - SEL$6        / ACT2@SEL$6

                44 - SEL$F665FE1B / XXADM_CATEGORY_MASTER_TBL@SEL$4

                45 - SEL$F665FE1B

                46 - SEL$F665FE1B / indexjoin$_alias$_001@SEL$F665FE1B

                48 - SEL$F665FE1B / indexjoin$_alias$_002@SEL$F665FE1B

               

               

              Outline Data

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

               

               

                /*+

                    BEGIN_OUTLINE_DATA

                    IGNORE_OPTIM_EMBEDDED_HINTS

                    OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

                    DB_VERSION('12.1.0.2')

                    OPT_PARAM('_optimizer_use_feedback' 'false')

                    OPT_PARAM('_optimizer_dsdir_usage_control' 0)

                    OPT_PARAM('_optimizer_adaptive_plans' 'false')

                    OPT_PARAM('_optimizer_gather_feedback' 'false')

                    ALL_ROWS

                    OUTLINE_LEAF(@"SEL$F665FE1B")

                    OUTLINE_LEAF(@"SEL$4")

                    OUTLINE_LEAF(@"SEL$5")

                    OUTLINE_LEAF(@"SEL$6")

                    OUTLINE_LEAF(@"SEL$A75BE177")

                    PUSH_PRED(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B" 16 15)

                    OUTLINE_LEAF(@"SEL$082F290F")

                    OUTLINE_LEAF(@"SEL$7E0D484F")

                    UNNEST(@"SEL$9D10C90A")

                    UNNEST(@"SEL$7")

                    OUTLINE_LEAF(@"SEL$2")

                    OUTLINE_LEAF(@"SEL$1")

                    OUTLINE(@"SEL$180402DE")

                    OUTLINE(@"SEL$7E0D484F")

                    UNNEST(@"SEL$9D10C90A")

                    UNNEST(@"SEL$7")

                    OUTLINE(@"SEL$67DC521B")

                    OUTLINE(@"SEL$9D10C90A")

                    UNNEST(@"SEL$9")

                    OUTLINE(@"SEL$7")

                    OUTLINE(@"SEL$C04829E0")

                    ELIMINATE_JOIN(@"SEL$3" "CRMT"@"SEL$3")

                    ELIMINATE_JOIN(@"SEL$3" "MMT"@"SEL$3")

                    OUTLINE(@"SEL$8")

                    OUTLINE(@"SEL$9")

                    OUTLINE(@"SEL$3")

                    NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")

                    NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")

                    INDEX_RS_ASC(@"SEL$7E0D484F" "CMT"@"SEL$3" ("XXADM_COLLEGE_MASTER_TBL"."COLLEGE_ID"))

                    INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_EDUCATION_TYPE"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

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

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

                    INDEX_JOIN(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_CODE") ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

                    INDEX_RS_ASC(@"SEL$7E0D484F" "ACT3"@"SEL$7" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."PREFERENCE_ORDER"))

                    NO_ACCESS(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B")

                    INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_PASS"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

                    INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_APPEARANCE"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

                    INDEX(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

                    LEADING(@"SEL$7E0D484F" "CMT"@"SEL$3" "LMT_EDUCATION_TYPE"@"SEL$3" "ACT"@"SEL$3" "ADT"@"SEL$3" "LMT_GENDER"@"SEL$3" "ACT3"@"SEL$7" "VW_SQ_1"@"SEL$67DC521B"

                            "LMT_PASS"@"SEL$3" "LMT_APPEARANCE"@"SEL$3" "LMT_RELIGION"@"SEL$3")

                    USE_NL(@"SEL$7E0D484F" "LMT_EDUCATION_TYPE"@"SEL$3")

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

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

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

                    USE_NL(@"SEL$7E0D484F" "ACT3"@"SEL$7")

                    USE_NL(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B")

                    USE_NL(@"SEL$7E0D484F" "LMT_PASS"@"SEL$3")

                    USE_NL(@"SEL$7E0D484F" "LMT_APPEARANCE"@"SEL$3")

                    USE_NL(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3")

                    NLJ_BATCHING(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3")

                    SWAP_JOIN_INPUTS(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3")

                    PQ_FILTER(@"SEL$7E0D484F" SERIAL)

                    INDEX_RS_ASC(@"SEL$A75BE177" "ACT1"@"SEL$8" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."PREFERENCE_ORDER"))

                    INDEX_RS_ASC(@"SEL$A75BE177" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9" ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_ID"))

                    LEADING(@"SEL$A75BE177" "ACT1"@"SEL$8" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9")

                    USE_NL(@"SEL$A75BE177" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9")

                    INDEX_RS_ASC(@"SEL$6" "ACT2"@"SEL$6" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."STATUS_FLAG"))

                    BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$6" "ACT2"@"SEL$6")

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

                    BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "ACT1"@"SEL$5")

                    INDEX_JOIN(@"SEL$4" "XXADM_CATEGORY_MASTER_TBL"@"SEL$4" ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_ID") ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_CODE"))

                    END_OUTLINE_DATA

                */

               

               

              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(("ACT"."PREFERENCE_ORDER"<=NVL(,"ACT"."PREFERENCE_ORDER") AND "ACT"."PREFERENCE_ORDER">=NVL(,"ACT"."PREFERENCE_ORDER") AND 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))

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

                     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)

                15 - access(ROWID=ROWID)

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

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

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

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

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

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

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

                32 - access("ACT1"."APPLICANT_ID"="ADT"."APPLICANT_ID" AND "ACT1"."PREFERENCE_ORDER"="ACT"."PREFERENCE_ORDER")

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

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

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

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

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

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

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

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

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

                45 - access(ROWID=ROWID)

                46 - access("CATEGORY_ID"=:B1)

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

               

               

               

               

              226 rows selected.

               

               

              SQL>

               

               

               

               

               

               

               

               

              Current Plan:

              ==============

              SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1n5q5gp58qzc9',1,format=>'ALLSTATS LAST +COST +ALIAS +OUTLINE +PEEKED_BINDS'));

               

               

              PLAN_TABLE_OUTPUT

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

              SQL_ID  1n5q5gp58qzc9, child number 1

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

              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: 693777368

               

               

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

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

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

              |   0 | SELECT STATEMENT                            |                                |      1 |        |  1129 (100)|      1 |00:00:00.04 |   10433 |       |       |         |

              |   1 |  SORT AGGREGATE                             |                                |      1 |      1 |            |      1 |00:00:00.04 |   10433 |       |       |         |

              |*  2 |   VIEW                                      |                                |      1 |      1 |  1129   (2)|      0 |00:00:00.04 |   10433 |       |       |         |

              |*  3 |    COUNT STOPKEY                            |                                |      1 |        |            |      4 |00:00:00.04 |   10433 |       |       |         |

              |   4 |     VIEW                                    |                                |      1 |      1 |  1129   (2)|      4 |00:00:00.04 |   10433 |       |       |         |

              |*  5 |      SORT ORDER BY STOPKEY                  |                                |      1 |      1 |  1129   (2)|      4 |00:00:00.04 |   10433 |  2048 |  2048 | 2048  (0)|

              |*  6 |       FILTER                                |                                |      1 |        |            |    649 |00:00:00.04 |   10433 |       |       |         |

              |   7 |        NESTED LOOPS                         |                                |      1 |      1 |  1123   (2)|    701 |00:00:00.03 |    8188 |       |       |         |

              |   8 |         NESTED LOOPS                        |                                |      1 |      1 |  1123   (2)|    701 |00:00:00.03 |    7487 |       |       |         |

              |   9 |          NESTED LOOPS                       |                                |      1 |      1 |  1122   (2)|    701 |00:00:00.03 |    7483 |       |       |         |

              |  10 |           NESTED LOOPS                      |                                |      1 |      1 |  1121   (2)|    701 |00:00:00.03 |    7479 |       |       |         |

              |  11 |            NESTED LOOPS ANTI                |                                |      1 |      1 |  1120   (2)|    701 |00:00:00.03 |    6774 |       |       |         |

              |  12 |             NESTED LOOPS ANTI               |                                |      1 |      1 |  1117   (2)|    701 |00:00:00.02 |    5443 |       |       |         |

              |* 13 |              HASH JOIN                      |                                |      1 |      7 |  1110   (2)|    701 |00:00:00.02 |    4039 |  1599K|  1599K| 1488K (0)|

              |  14 |               VIEW                          | index$_join$_008               |      1 |    127 |     2   (0)|    127 |00:00:00.01 |       8 |       |       |         |

              |* 15 |                HASH JOIN                    |                                |      1 |        |            |    127 |00:00:00.01 |       8 |  1368K|  1368K| 1454K (0)|

              |  16 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_CODE_UK             |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |

              |  17 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_PK                  |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |

              |* 18 |               HASH JOIN                     |                                |      1 |    682 |  1108   (2)|    701 |00:00:00.02 |    4031 |  1263K|  1263K| 1455K (0)|

              |  19 |                NESTED LOOPS                 |                                |      1 |    682 |   450   (2)|    818 |00:00:00.01 |    1639 |       |       |         |

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

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

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

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

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

              |* 25 |                 TABLE ACCESS FULL           | XXADM_APPLICANT_COURSPREFS_TBL |      1 |    682 |   448   (2)|    818 |00:00:00.01 |    1635 |       |       |         |

              |* 26 |                TABLE ACCESS FULL            | XXADM_APPLICANT_DETAILS_TBL    |      1 |   5393 |   657   (2)|  10488 |00:00:00.01 |    2392 |       |       |         |

              |* 27 |              TABLE ACCESS BY INDEX ROWID    | XXADM_APPLICANT_COURSPREFS_TBL |    701 |   5890 |     1   (0)|      0 |00:00:00.01 |    1404 |       |       |         |

              |* 28 |               INDEX UNIQUE SCAN             | XXADM_APPLCNT_PREF_ORDER_UK    |    701 |      1 |     0   (0)|    701 |00:00:00.01 |     703 |       |       |         |

              |  29 |             VIEW PUSHED PREDICATE           | VW_SQ_1                        |    701 |      1 |     3   (0)|      0 |00:00:00.01 |    1331 |       |       |         |

              |  30 |              NESTED LOOPS                   |                                |    701 |      1 |     3   (0)|      0 |00:00:00.01 |    1331 |       |       |         |

              |* 31 |               TABLE ACCESS BY INDEX ROWID   | XXADM_APPLICANT_COURSPREFS_TBL |    701 |      1 |     2   (0)|      0 |00:00:00.01 |    1331 |       |       |         |

              |* 32 |                INDEX UNIQUE SCAN            | XXADM_APPLCNT_PREF_ORDER_UK    |    701 |      1 |     1   (0)|    701 |00:00:00.01 |     703 |       |       |         |

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

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

              |  35 |            TABLE ACCESS BY INDEX ROWID      | XXADM_LOV_MASTER_TBL           |    701 |      1 |     1   (0)|    701 |00:00:00.01 |     705 |       |       |         |

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

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

              |* 38 |          INDEX UNIQUE SCAN                  | XXADM_LOVS_PK                  |    701 |      1 |     0   (0)|    701 |00:00:00.01 |       4 |       |       |         |

              |  39 |         TABLE ACCESS BY INDEX ROWID         | XXADM_LOV_MASTER_TBL           |    701 |      1 |     1   (0)|    701 |00:00:00.01 |     701 |       |       |         |

              |* 40 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    701 |      1 |     3   (0)|    107 |00:00:00.01 |    1573 |       |       |         |

              |* 41 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_PREF_ORDER_UK    |    701 |      1 |     2   (0)|   5465 |00:00:00.01 |     714 |       |       |         |

              |  42 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    654 |      1 |     2   (0)|      1 |00:00:00.01 |     658 |       |       |         |

              |* 43 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_APPLICANT_STATUS |    654 |      3 |     1   (0)|      1 |00:00:00.01 |     657 |       |       |         |

              |* 44 |        VIEW                                 | index$_join$_014               |      6 |      1 |     0   (0)|      0 |00:00:00.01 |      14 |       |       |         |

              |* 45 |         HASH JOIN                           |                                |      6 |        |            |      0 |00:00:00.01 |      14 |  1519K|  1519K|  707K (0)|

              |* 46 |          INDEX RANGE SCAN                   | XXADM_CATEGORY_PK              |      6 |      1 |     0   (0)|      6 |00:00:00.01 |       6 |       |       |         |

              |  47 |          INLIST ITERATOR                    |                                |      6 |        |            |     12 |00:00:00.01 |       8 |       |       |         |

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

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

               

               

              Query Block Name / Object Alias (identified by operation id):

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

               

               

                 1 - SEL$1

                 2 - SEL$2        / from$_subquery$_001@SEL$1

                 3 - SEL$2

                 4 - SEL$7E0D484F / from$_subquery$_002@SEL$2

                 5 - SEL$7E0D484F

                14 - SEL$082F290F / LMT_GENDER@SEL$3

                15 - SEL$082F290F

                16 - SEL$082F290F / indexjoin$_alias$_001@SEL$082F290F

                17 - SEL$082F290F / indexjoin$_alias$_002@SEL$082F290F

                21 - SEL$7E0D484F / CMT@SEL$3

                22 - SEL$7E0D484F / CMT@SEL$3

                23 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3

                24 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3

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

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

                27 - SEL$7E0D484F / ACT3@SEL$7

                28 - SEL$7E0D484F / ACT3@SEL$7

                29 - SEL$A75BE177 / VW_SQ_1@SEL$67DC521B

                30 - SEL$A75BE177

                31 - SEL$A75BE177 / ACT1@SEL$8

                32 - SEL$A75BE177 / ACT1@SEL$8

                33 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9

                34 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9

                35 - SEL$7E0D484F / LMT_PASS@SEL$3

                36 - SEL$7E0D484F / LMT_PASS@SEL$3

                37 - SEL$7E0D484F / LMT_APPEARANCE@SEL$3

                38 - SEL$7E0D484F / LMT_RELIGION@SEL$3

                39 - SEL$7E0D484F / LMT_RELIGION@SEL$3

                40 - SEL$5        / ACT1@SEL$5

                41 - SEL$5        / ACT1@SEL$5

                42 - SEL$6        / ACT2@SEL$6

                43 - SEL$6        / ACT2@SEL$6

                44 - SEL$F665FE1B / XXADM_CATEGORY_MASTER_TBL@SEL$4

                45 - SEL$F665FE1B

                46 - SEL$F665FE1B / indexjoin$_alias$_001@SEL$F665FE1B

                48 - SEL$F665FE1B / indexjoin$_alias$_002@SEL$F665FE1B

               

               

              Outline Data

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

               

               

                /*+

                    BEGIN_OUTLINE_DATA

                    IGNORE_OPTIM_EMBEDDED_HINTS

                    OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

                    DB_VERSION('12.1.0.2')

                    OPT_PARAM('_optimizer_use_feedback' 'false')

                    OPT_PARAM('_optimizer_dsdir_usage_control' 0)

                    OPT_PARAM('_optimizer_adaptive_plans' 'false')

                    OPT_PARAM('_optimizer_gather_feedback' 'false')

                    ALL_ROWS

                    OUTLINE_LEAF(@"SEL$F665FE1B")

                    OUTLINE_LEAF(@"SEL$4")

                    OUTLINE_LEAF(@"SEL$5")

                    OUTLINE_LEAF(@"SEL$6")

                    OUTLINE_LEAF(@"SEL$A75BE177")

                    PUSH_PRED(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B" 16 15)

                    OUTLINE_LEAF(@"SEL$082F290F")

                    OUTLINE_LEAF(@"SEL$7E0D484F")

                    UNNEST(@"SEL$9D10C90A")

                    UNNEST(@"SEL$7")

                    OUTLINE_LEAF(@"SEL$2")

                    OUTLINE_LEAF(@"SEL$1")

                    OUTLINE(@"SEL$180402DE")

                    OUTLINE(@"SEL$7E0D484F")

                    UNNEST(@"SEL$9D10C90A")

                    UNNEST(@"SEL$7")

                    OUTLINE(@"SEL$67DC521B")

                    OUTLINE(@"SEL$9D10C90A")

                    UNNEST(@"SEL$9")

                    OUTLINE(@"SEL$7")

                    OUTLINE(@"SEL$C04829E0")

                    ELIMINATE_JOIN(@"SEL$3" "CRMT"@"SEL$3")

                    ELIMINATE_JOIN(@"SEL$3" "MMT"@"SEL$3")

                    OUTLINE(@"SEL$8")

                    OUTLINE(@"SEL$9")

                    OUTLINE(@"SEL$3")

                    NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")

                    NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")

                    INDEX_RS_ASC(@"SEL$7E0D484F" "CMT"@"SEL$3" ("XXADM_COLLEGE_MASTER_TBL"."COLLEGE_ID"))

                    INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_EDUCATION_TYPE"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

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

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

                    INDEX_JOIN(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_CODE") ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

                    INDEX_RS_ASC(@"SEL$7E0D484F" "ACT3"@"SEL$7" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."PREFERENCE_ORDER"))

                    NO_ACCESS(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B")

                    INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_PASS"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

                    INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_APPEARANCE"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

                    INDEX(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))

                    LEADING(@"SEL$7E0D484F" "CMT"@"SEL$3" "LMT_EDUCATION_TYPE"@"SEL$3" "ACT"@"SEL$3" "ADT"@"SEL$3" "LMT_GENDER"@"SEL$3" "ACT3"@"SEL$7" "VW_SQ_1"@"SEL$67DC521B"

                            "LMT_PASS"@"SEL$3" "LMT_APPEARANCE"@"SEL$3" "LMT_RELIGION"@"SEL$3")

                    USE_NL(@"SEL$7E0D484F" "LMT_EDUCATION_TYPE"@"SEL$3")

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

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

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

                    USE_NL(@"SEL$7E0D484F" "ACT3"@"SEL$7")

                    USE_NL(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B")

                    USE_NL(@"SEL$7E0D484F" "LMT_PASS"@"SEL$3")

                    USE_NL(@"SEL$7E0D484F" "LMT_APPEARANCE"@"SEL$3")

                    USE_NL(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3")

                    NLJ_BATCHING(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3")

                    SWAP_JOIN_INPUTS(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3")

                    PQ_FILTER(@"SEL$7E0D484F" SERIAL)

                    INDEX_RS_ASC(@"SEL$A75BE177" "ACT1"@"SEL$8" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."PREFERENCE_ORDER"))

                    INDEX_RS_ASC(@"SEL$A75BE177" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9" ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_ID"))

                    LEADING(@"SEL$A75BE177" "ACT1"@"SEL$8" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9")

                    USE_NL(@"SEL$A75BE177" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9")

                    INDEX_RS_ASC(@"SEL$6" "ACT2"@"SEL$6" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."STATUS_FLAG"))

                    BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$6" "ACT2"@"SEL$6")

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

                    BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "ACT1"@"SEL$5")

                    INDEX_JOIN(@"SEL$4" "XXADM_CATEGORY_MASTER_TBL"@"SEL$4" ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_ID") ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_CODE"))

                    END_OUTLINE_DATA

                */

               

               

              Peeked Binds (identified by position):

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

               

               

                 1 - :1 (NUMBER): 9

                 2 - :2 (NUMBER): 3

                 3 - (NUMBER): 17

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

                 5 - (NUMBER): 4

               

               

              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(("ACT"."PREFERENCE_ORDER"<=NVL(,"ACT"."PREFERENCE_ORDER") AND "ACT"."PREFERENCE_ORDER">=NVL(,"ACT"."PREFERENCE_ORDER") AND 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))

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

                     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)

                15 - access(ROWID=ROWID)

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

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

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

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

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

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

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

                32 - access("ACT1"."APPLICANT_ID"="ADT"."APPLICANT_ID" AND "ACT1"."PREFERENCE_ORDER"="ACT"."PREFERENCE_ORDER")

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

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

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

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

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

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

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

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

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

                45 - access(ROWID=ROWID)

                46 - access("CATEGORY_ID"=:B1)

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

               

              234 rows selected.

               

              Thank You

              • 4. Re: Sudden Spike in cost
                3510875

                Dear Jonathan,

                 

                With current plan,the runtime increased by 4hours

                 

                Thank You

                • 5. Re: Sudden Spike in cost
                  Jonathan Lewis

                  I haven't downloaded the file, but looking at the two plans you've posted I will repeat my previous comment.

                   

                  The only difference in path is at operation 41 where there's a change of index. However the index uses only the applicant_id column and postpones the test for status_flag until it reaches the table.  As I've said elsewhere you will get some saving if you can check the status_flag in the index WITHOUT while stopping the optimizer from using an "inlist iterator" to do 4 separate index probes.

                   

                  There are two points about the change in cost between the two queries:

                  1) operation 25 is where the basic volume of the query appears (though operation 13 is where you see the slightly reduced volume carries through to the end of the main query).  In the first plan the tablescan of XXADM_APPLICANT_COURSPREFS_TBL predicats 682 rows and gets 818 rows; in the second plan it predicts 241 rows and gets 209 rows: it's not suprising from those figures that the cost shows a significant jump.

                   

                  2) The tablescans at operations 25 and 26 report a cost of 241 and 311 respectively in the first plan and 448 and 657 respectively in the second plan - which suggest that the table have doubled in size or someone has changed some of the table or system statistics.  HOWEVER - the actual number of buffer gets has doubled for both tables (though the number of rows returned (A-rows) for the second table has stayed the same) and that's an oddity that needs an explanation (for example - it MIGHT mean someone has inserted all the data a second time then deleted the old data then committed - doubling the size of the table but leaving half of it empty).

                   

                  As I've said before, though - it looks like the batch job is doing something once per applicant ID when you need a design that executes a piece of SQL like this once per combination of the bind variables you use at operation 25:

                   

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

                   

                  .

                   

                  Regards

                  Jonathan Lewis