IN clause causing high execution time

Question

I have a 118 line query similar to this:

select * from (The inner query with many joins) WHERE campus_code IN ('560','598')

The campus_code is getting generated by a function f_get_bookstore(SSBSECT_TERM_CODE,SSBSECT_CRN) AS SSBSECT_CAMP_CODE in the inner query. If I run the inner query alone its count is 18 & it is getting executed in 13 s. But if I add the WHERE campus_code IN ('560','598') then it is taking more than 2 min.

Another strange thing is, I have another query which is also like

select * from (The inner query with many joins) WHERE campus_code IN ('560','598'). Here the inner query slightly different than previous one. The inner query is fast & returning 92 rows. But here the campus_code IN filtering is fast even though it is working on 92 rows (unlike 18 rows for the previous query). Here also the campus_code is generated through the same function.

Can any body help to tune the query? Please tell what more info do you need.

This is the entire query:

    SELECT *
FROM
  (SELECT 'columbusstate' bk_institution_id,
    'columbusstate.'
    || scbcrse_subj_code
    || '.'
    || scbcrse_crse_numb
    ||'.'
    || Ssbsect_Crn
    || '.'
    || ssbsect_term_code bk_section_id,
    ssbsect_camp_code AS campus_code,
    scbcrse_subj_code
    || '.'
    || scbcrse_crse_numb
    ||'.'
    || Ssbsect_Crn
    || '.'
    || ssbsect_term_code institution_section_id,
    ssbsect_crn short_description,
    scbcrse_title AS description,
    ssbsect_crn sections_ssbsect_crn,
    ssbsect_term_code sections_ssbsect_term_code,
    'na' instructor_first_name,
    'na' instructor_last_name,
    scbcrse_subj_code
    || '.'
    || scbcrse_crse_numb rel_institution_course_id,
    scbcrse_title     AS rel_course_description,
    scbcrse_title     AS rel_course_name,
    scbcrse_crse_numb AS course_short_desc,
    scbcrse_crse_numb course_number,
    stvterm_desc term_short_desc,
    stvterm_code rel_institution_term_id,
    section.ssbsect_crn,
    ssbsect_ssts_code AS ssbsect_ssts_code,
    'columbusstate.'
    || stvterm_code rel_bk_term_id,
    'columbusstate.'
    || scbcrse_subj_code
    || '.'
    || scbcrse_crse_numb rel_bk_course_id,
    ssbsect_seq_numb,
    ssbsect_enrl ssbsect_enrl,
    ssbsect_enrl estimated_enrl,
    ssbsect_max_enrl ssbsect_max_enrl,
    ssbsect_crn ssbsect_section_key,
    ssbsect_crn ssbsect_section_number,
    'parent' relationship,
    course.scbcrse_subj_code institution_department_id ,
    'DFLT' institution_division_id,
    'DFLT' division_short_desc
  FROM
    (SELECT *
    FROM
      (SELECT SSBSECT_TERM_CODE,
        SSBSECT_CRN,
        SSBSECT_SUBJ_CODE,
        SSBSECT_CRSE_NUMB,
        SSBSECT_PTRM_CODE,        
        SSBSECT_SEQ_NUMB,
        SSBSECT_SSTS_CODE,
        SSBSECT_MAX_ENRL,
        SSBSECT_ENRL,
        SSBSECT_PRNT_IND,
        f_get_bookstore(SSBSECT_TERM_CODE,SSBSECT_CRN) AS SSBSECT_CAMP_CODE
      FROM SSBSECT
      ) sect
    JOIN
      (SELECT *
      FROM
        (SELECT * FROM saturn.stvterm WHERE STVTERM_CODE >= '201401' 
        )
      ) term
    ON term.stvterm_code=sect.ssbsect_term_code
    ) section
  JOIN
    (SELECT C1.scbcrse_subj_code,
      C1.scbcrse_dept_code,
      C1.scbcrse_crse_numb,
      C1.scbcrse_title
    FROM saturn.scbcrse C1          
    LEFT JOIN saturn.scbcrse C2
    ON (C1.scbcrse_subj_code             = C2.scbcrse_subj_code       
    AND C1.scbcrse_crse_numb             = C2.scbcrse_crse_numb
    AND C1.scbcrse_eff_term              < C2.scbcrse_eff_term)
    WHERE c2.scbcrse_eff_term           IS NULL
    ) Course ON Course.scbcrse_subj_code = Section.ssbsect_subj_code
  AND scbcrse_crse_numb                  = section.ssbsect_crse_numb
  AND ssbsect_ssts_code                 IN ('A','V','X')
  LEFT JOIN
    (SELECT sirasgn_term_code sirasgn_term_code,
      sirasgn_crn,
      MAX(spriden_pidm) spriden_pidm,
      MAX(spriden_first_name) instructor_first_name,
      MAX(spriden_last_name) instructor_last_name
    FROM
      (SELECT Pidm spriden_pidm,
        Csu_Id SPRIDEN_ID,
        First_Name spriden_first_name,
        Last_Name spriden_last_name,        
        Mi spriden_mi,
        External_User_Id login,
        Email
      FROM Csuapps.Wfollett_Person
      ) persons
    JOIN
      ( SELECT * FROM saturn.sirasgn WHERE SIRASGN_PRIMARY_IND = 'Y'
      ) relations
    ON persons.spriden_pidm=relations.sirasgn_pidm
    GROUP BY sirasgn_crn,
      sirasgn_term_code
    ) instr ON section.ssbsect_term_code=instr.sirasgn_term_code
  AND section.ssbsect_crn               =instr.sirasgn_crn
  WHERE Course.scbcrse_subj_code        = 'ACCT'
  AND section.ssbsect_term_code         = '201702'
  )
WHERE campus_code IN ('560','1157')

Execution Plan:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2063389120

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                    |    77 |   968K|       |  4364   (3)| 00:00:01 |
|   1 |  SORT ORDER BY                                 |                    |    77 |   968K|   624K|  4364   (3)| 00:00:01 |
|*  2 |   VIEW                                         |                    |    77 |   968K|       |  4155   (3)| 00:00:01 |
|   3 |    COUNT                                       |                    |       |       |       |            |          |
|   4 |     VIEW                                       |                    |    77 |   967K|       |  4155   (3)| 00:00:01 |
|   5 |      SORT ORDER BY                             |                    |    77 |   774K|       |  4155   (3)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  6 |       FILTER                                   |                    |       |       |       |            |          |
|*  7 |        HASH JOIN RIGHT OUTER                   |                    |    77 |   774K|       |  4154   (3)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN                       | SCBCRSE_KEY_INDEX  |    83 |  1494 |       |     1   (0)| 00:00:01 |
|   9 |         NESTED LOOPS                           |                    |    77 |   772K|       |  4153   (3)| 00:00:01 |
|  10 |          NESTED LOOPS OUTER                    |                    |    23 |   229K|       |  4139   (3)| 00:00:01 |
|* 11 |           HASH JOIN OUTER                      |                    |    23 |  1656 |       |  3783   (1)| 00:00:01 |
|  12 |            NESTED LOOPS                        |                    |    23 |  1334 |       |     7   (0)| 00:00:01 |
|  13 |             TABLE ACCESS BY INDEX ROWID        | STVTERM            |     1 |    22 |       |     1   (0)| 00:00:01 |
|* 14 |              INDEX UNIQUE SCAN                 | PK_STVTERM         |     1 |       |       |     1   (0)| 00:00:01 |
|* 15 |             TABLE ACCESS BY INDEX ROWID BATCHED| SSBSECT            |    23 |   828 |       |     6   (0)| 00:00:01 |
|* 16 |              INDEX RANGE SCAN                  | SSBSECT_INDEX_SUBJ |    25 |       |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  17 |            VIEW                                |                    |  2172 | 30408 |       |  3775   (1)| 00:00:01 |
|  18 |             HASH GROUP BY                      |                    |  2172 |  8537K|    16M|  3775   (1)| 00:00:01 |
|* 19 |              HASH JOIN                         |                    |  4166 |    15M|       |  1364   (1)| 00:00:01 |
|  20 |               VIEW                             | VW_GBF_14          |  2312 | 18496 |       |   211   (1)| 00:00:01 |
|  21 |                HASH GROUP BY                   |                    |  2312 | 48552 |       |   211   (1)| 00:00:01 |
|* 22 |                 TABLE ACCESS FULL              | SIRASGN            |  2312 | 48552 |       |   210   (1)| 00:00:01 |
|  23 |               VIEW                             | WFOLLETT_PERSON    |  3714 |    14M|       |  1153   (1)| 00:00:01 |
|  24 |                SORT UNIQUE                     |                    |  3714 |   342K|       |  1153   (1)| 00:00:01 |
|  25 |                 UNION-ALL                      |                    |       |       |       |            |          |
|  26 |                  NESTED LOOPS                  |                    |     1 |    59 |       |     2   (0)| 00:00:01 |
|  27 |                   NESTED LOOPS                 |                    |     1 |    59 |       |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 28 |                    INDEX RANGE SCAN            | PK_GLBEXTR         |     1 |    38 |       |     1   (0)| 00:00:01 |
|* 29 |                    INDEX UNIQUE SCAN           | PK_GOBTPAC         |     1 |       |       |     1   (0)| 00:00:01 |
|  30 |                   TABLE ACCESS BY INDEX ROWID  | GOBTPAC            |     1 |    21 |       |     1   (0)| 00:00:01 |
|  31 |                  NESTED LOOPS                  |                    |  1912 |   130K|       |   605   (0)| 00:00:01 |
|  32 |                   NESTED LOOPS                 |                    |  1912 |   130K|       |   605   (0)| 00:00:01 |
|* 33 |                    HASH JOIN                   |                    |  1912 | 93688 |       |    31   (0)| 00:00:01 |
|  34 |                     VIEW                       | VW_SQ_2            |  3076 | 83052 |       |    10   (0)| 00:00:01 |
|  35 |                      HASH GROUP BY             |                    |  3076 | 39988 |       |    10   (0)| 00:00:01 |
|  36 |                       INDEX FULL SCAN          | PK_SIBINST         |  6243 | 81159 |       |    10   (0)| 00:00:01 |
|* 37 |                     TABLE ACCESS FULL          | SIBINST            |  3881 | 85382 |       |    21   (0)| 00:00:01 |
|* 38 |                    INDEX UNIQUE SCAN           | PK_GOBTPAC         |     1 |       |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  39 |                   TABLE ACCESS BY INDEX ROWID  | GOBTPAC            |     1 |    21 |       |     1   (0)| 00:00:01 |
|  40 |                  NESTED LOOPS                  |                    |  1801 | 41423 |       |   543   (1)| 00:00:01 |
|  41 |                   NESTED LOOPS                 |                    |  1801 | 41423 |       |   543   (1)| 00:00:01 |
|  42 |                    VIEW                        | VW_DTP_6100A9C4    |  1801 |  3602 |       |     3  (34)| 00:00:01 |
|  43 |                     HASH UNIQUE                |                    |  1801 | 23413 |       |     3  (34)| 00:00:01 |
|* 44 |                      INDEX RANGE SCAN          | PK_SIRASGN         |  4161 | 54093 |       |     2   (0)| 00:00:01 |
|* 45 |                    INDEX UNIQUE SCAN           | PK_GOBTPAC         |     1 |       |       |     1   (0)| 00:00:01 |
|  46 |                   TABLE ACCESS BY INDEX ROWID  | GOBTPAC            |     1 |    21 |       |     1   (0)| 00:00:01 |
|  47 |           VIEW PUSHED PREDICATE                |                    |     1 | 10165 |       |    16  (25)| 00:00:01 |
|* 48 |            FILTER                              |                    |       |       |       |            |          |
|  49 |             NESTED LOOPS                       |                    |     1 | 10164 |       |    16  (25)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  50 |              NESTED LOOPS                      |                    |     1 |    79 |       |     4   (0)| 00:00:01 |
|* 51 |               FILTER                           |                    |       |       |       |            |          |
|  52 |                NESTED LOOPS OUTER              |                    |     1 |    60 |       |     3   (0)| 00:00:01 |
|  53 |                 NESTED LOOPS                   |                    |     1 |    42 |       |     2   (0)| 00:00:01 |
|  54 |                  TABLE ACCESS BY INDEX ROWID   | SSBSECT            |     1 |    24 |       |     1   (0)| 00:00:01 |
|* 55 |                   INDEX UNIQUE SCAN            | PK_SSBSECT         |     1 |       |       |     1   (0)| 00:00:01 |
|* 56 |                  INDEX RANGE SCAN              | SCBCRSE_KEY_INDEX  |     1 |    18 |       |     1   (0)| 00:00:01 |
|* 57 |                 INDEX RANGE SCAN               | SCBCRSE_KEY_INDEX  | 15046 |   264K|       |     1   (0)| 00:00:01 |
|* 58 |               INDEX RANGE SCAN                 | PK_SIRASGN         |     1 |    19 |       |     1   (0)| 00:00:01 |
|  59 |              VIEW                              | WFOLLETT_PERSON    |     1 | 10085 |       |    12  (34)| 00:00:01 |
|  60 |               SORT UNIQUE                      |                    |     3 |   304 |       |    12  (34)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  61 |                UNION ALL PUSHED PREDICATE      |                    |       |       |       |            |          |
|  62 |                 NESTED LOOPS                   |                    |     1 |    59 |       |     2   (0)| 00:00:01 |
|  63 |                  TABLE ACCESS BY INDEX ROWID   | GOBTPAC            |     1 |    21 |       |     1   (0)| 00:00:01 |
|* 64 |                   INDEX UNIQUE SCAN            | PK_GOBTPAC         |     1 |       |       |     1   (0)| 00:00:01 |
|* 65 |                  INDEX RANGE SCAN              | PK_GLBEXTR         |     1 |    38 |       |     1   (0)| 00:00:01 |
|  66 |                 NESTED LOOPS                   |                    |     1 |    70 |       |     3   (0)| 00:00:01 |
|  67 |                  NESTED LOOPS                  |                    |     1 |    70 |       |     3   (0)| 00:00:01 |
|  68 |                   NESTED LOOPS                 |                    |     1 |    48 |       |     2   (0)| 00:00:01 |
|  69 |                    TABLE ACCESS BY INDEX ROWID | GOBTPAC            |     1 |    21 |       |     1   (0)| 00:00:01 |
|* 70 |                     INDEX UNIQUE SCAN          | PK_GOBTPAC         |     1 |       |       |     1   (0)| 00:00:01 |
|  71 |                    VIEW                        | VW_SQ_1            |     1 |    27 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  72 |                     SORT GROUP BY              |                    |     1 |    13 |       |     1   (0)| 00:00:01 |
|* 73 |                      INDEX RANGE SCAN          | PK_SIBINST         |     2 |    26 |       |     1   (0)| 00:00:01 |
|* 74 |                   INDEX UNIQUE SCAN            | PK_SIBINST         |     1 |       |       |     1   (0)| 00:00:01 |
|* 75 |                  TABLE ACCESS BY INDEX ROWID   | SIBINST            |     1 |    22 |       |     1   (0)| 00:00:01 |
|  76 |                 NESTED LOOPS                   |                    |     1 |    23 |       |     4  (25)| 00:00:01 |
|  77 |                  TABLE ACCESS BY INDEX ROWID   | GOBTPAC            |     1 |    21 |       |     1   (0)| 00:00:01 |
|* 78 |                   INDEX UNIQUE SCAN            | PK_GOBTPAC         |     1 |       |       |     1   (0)| 00:00:01 |
|* 79 |                  VIEW                          | VW_DTP_32C68FB6    |     1 |     2 |       |     3  (34)| 00:00:01 |
|  80 |                   SORT UNIQUE                  |                    |     2 |    26 |       |     3  (34)| 00:00:01 |
|* 81 |                    INDEX RANGE SCAN            | PK_SIRASGN         |     2 |    26 |       |     2   (0)| 00:00:01 |
|  82 |          TABLE ACCESS BY INDEX ROWID BATCHED   | SCBCRSE            |     3 |   126 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 83 |           INDEX RANGE SCAN                     | SCBCRSE_KEY_INDEX  |     1 |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("from$_subquery$_002"."CAMPUS_CODE"='1157' OR "from$_subquery$_002"."CAMPUS_CODE"='560') AND "R">=1 
              AND "R"<=1500)
   6 - filter("C2"."SCBCRSE_EFF_TERM" IS NULL)
   7 - access("C1"."SCBCRSE_SUBJ_CODE"="C2"."SCBCRSE_SUBJ_CODE"(+) AND 
              "C1"."SCBCRSE_CRSE_NUMB"="C2"."SCBCRSE_CRSE_NUMB"(+))

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       filter("C1"."SCBCRSE_EFF_TERM"<"C2"."SCBCRSE_EFF_TERM"(+))
   8 - access("C2"."SCBCRSE_SUBJ_CODE"(+)='ACCT')
  11 - access("SSBSECT_TERM_CODE"="INSTR"."SIRASGN_TERM_CODE"(+) AND "SSBSECT_CRN"="INSTR"."SIRASGN_CRN"(+))
  14 - access("STVTERM"."STVTERM_CODE"='201702')
  15 - filter("SSBSECT_SSTS_CODE"='A' OR "SSBSECT_SSTS_CODE"='V' OR "SSBSECT_SSTS_CODE"='X')
  16 - access("SSBSECT_SUBJ_CODE"='ACCT' AND "SSBSECT_TERM_CODE"='201702')
       filter("SSBSECT_TERM_CODE"='201702')
  19 - access("PIDM"="ITEM_1")
  22 - filter("SIRASGN"."SIRASGN_TERM_CODE"='201702' AND "SIRASGN_PRIMARY_IND"='Y')
  28 - access("GLBEXTR_APPLICATION"='STUDENT' AND "GLBEXTR_SELECTION"='CURR_ENRL' AND "GLBEXTR_CREATOR_ID"='GSHOUL')
  29 - access("GOBTPAC_PIDM"=TO_NUMBER("GLBEXTR_KEY"))

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  33 - access("A"."SIBINST_TERM_CODE_EFF"="MAX(B.SIBINST_TERM_CODE_EFF)" AND "ITEM_2"="A"."SIBINST_PIDM")
  37 - filter("A"."SIBINST_FCST_CODE"='AC' AND "A"."SIBINST_FCTG_CODE"<>'EMPL')
  38 - access("A"."SIBINST_PIDM"="GOBTPAC_PIDM")
  44 - access("SIRASGN_TERM_CODE">="TOOLS"."GETTERMCODE"(NULL,SYSDATE@!))
  45 - access("ITEM_1"="GOBTPAC_PIDM")
  48 - filter("SSBSECT_TERM_CODE">='201602' AND "SSBSECT_TERM_CODE"='201702')
  51 - filter("C2"."SCBCRSE_EFF_TERM" IS NULL)
  55 - access("SSBSECT_TERM_CODE"="SSBSECT_TERM_CODE" AND "SSBSECT_CRN"="SSBSECT_CRN")
       filter("SSBSECT_TERM_CODE"='201702')
  56 - access("SSBSECT_SUBJ_CODE"="C1"."SCBCRSE_SUBJ_CODE" AND "SSBSECT_CRSE_NUMB"="C1"."SCBCRSE_CRSE_NUMB")
  57 - access("C1"."SCBCRSE_SUBJ_CODE"="C2"."SCBCRSE_SUBJ_CODE"(+) AND 

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              "C1"."SCBCRSE_CRSE_NUMB"="C2"."SCBCRSE_CRSE_NUMB"(+) AND "C1"."SCBCRSE_EFF_TERM"<"C2"."SCBCRSE_EFF_TERM"(+) AND 
              "C2"."SCBCRSE_EFF_TERM"(+) IS NOT NULL)
  58 - access("REL"."SIRASGN_TERM_CODE"="SSBSECT_TERM_CODE" AND "REL"."SIRASGN_CRN"="SSBSECT_CRN")
       filter("REL"."SIRASGN_TERM_CODE"="SSBSECT_TERM_CODE" AND "REL"."SIRASGN_TERM_CODE"='201702' AND 
              "REL"."SIRASGN_CRN"="SSBSECT_CRN")
  64 - access("GOBTPAC_PIDM"="REL"."SIRASGN_PIDM")
  65 - access("GLBEXTR_APPLICATION"='STUDENT' AND "GLBEXTR_SELECTION"='CURR_ENRL' AND "GLBEXTR_CREATOR_ID"='GSHOUL')
       filter(TO_NUMBER("GLBEXTR_KEY")="REL"."SIRASGN_PIDM" AND "GOBTPAC_PIDM"=TO_NUMBER("GLBEXTR_KEY"))
  70 - access("GOBTPAC_PIDM"="REL"."SIRASGN_PIDM")
  73 - access("B"."SIBINST_PIDM"="REL"."SIRASGN_PIDM")
  74 - access("A"."SIBINST_PIDM"="REL"."SIRASGN_PIDM" AND "A"."SIBINST_TERM_CODE_EFF"="MAX(B.SIBINST_TERM_CODE_EFF)")

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       filter("A"."SIBINST_PIDM"="GOBTPAC_PIDM" AND "ITEM_1"="A"."SIBINST_PIDM")
  75 - filter("A"."SIBINST_FCST_CODE"='AC' AND "A"."SIBINST_FCTG_CODE"<>'EMPL')
  78 - access("GOBTPAC_PIDM"="REL"."SIRASGN_PIDM")
  79 - filter("ITEM_1"="GOBTPAC_PIDM")
  81 - access("SIRASGN_TERM_CODE">="TOOLS"."GETTERMCODE"(NULL,SYSDATE@!) AND "SIRASGN_PIDM"="REL"."SIRASGN_PIDM")
       filter("SIRASGN_PIDM"="REL"."SIRASGN_PIDM")
  83 - access("C1"."SCBCRSE_SUBJ_CODE"='ACCT' AND "C1"."SCBCRSE_CRSE_NUMB"="SSBSECT_CRSE_NUMB")

139 rows selected. 

Answer 1

The performance of your query depends on the details, that we don't know, and on the joins. In general, if the joins result in a huge set of rows, that will eat a lot of cpu and ram. So try to add where clauses that minimize the number of rows in the joins.

Said in an other way, just imagine you are the cpu. Go through your query and try to estimate the number of rows that you need to process, and how you could limit that number of rows.

Are you using an index on crucial tables to speed up selects? Are you modifying the search column used in an index, so that the index does not work? Stuff like this is very important.

Good luck!

---

update:

The plan shows to me that most cpu is burned for the outermost query, something that you already noticed in the timing measurements. My strategy would be to try to move the outermost where-clause towards the inner queries. If the database engine can apply the where-clause in an earlier phase, the query will need less memory and cpu.

Now it is not possible to see to which table each output field belongs. If I where you I would give EVERY table in each FROM an alias.

For the sake of testing performance, and for posting here, you could simplify the lists of output fields. Leave out fields that are not required by outer queries, or use asterisk. Also, try to present a perfectly pretty printed query.

Apart from the Plan, could you get some info on memory usage? With the outer query, perhaps it needs to get a lot of data in memory before outputting results. Just imagine if this results in exhausting ram and needs swap, that will cost a lot of time. Memory use may also come from holding a lock on possibly big tables, so do not get fooled by your 'just 18 rows' of output.

Answer 2

OK, I'm going to put this in a "answer", since comments seem to be ignored.

The only way to know what is going on and how to fix your problem is to get the execution plan. Otherwise every answer is a guess. You may get lucky that a guess works, but then you will never know why. So please help us to help you.

To get an execution plan (for both the queries with and without the WHERE clause), run this after your query...

set lines 500
set pages 10000

select * from table( dbms_xplan.display_cursor( null, null, 'TYPICAL' ));

Answer 3

Is campus_code an integer? If it is, try removing the apostrophes, like this:

WHERE campus_code IN (560,598)

Otherwise, each value gets converted before being compared, and that can take a lot of time.

Hope this helps.

Answer 4

What you observe is by 99% a merging predicates in an inline view.

This works typically fine and helps performance as illustrated on an example below.

Sample Data

 create table T1 as
 select 'xxxx'||rownum crampus_code from dual connect by level <= 100000;

 create index idx1 on t1(crampus_code);

This query can take a substantial time on a large table as the whole table must be accessed.

 select crampus_code, count(*) from T1 group by crampus_code;

A following query using the previous query as a subquery can be very effective. How?

The WHERE clause in MERGE in the subquery and only the data for the two keys are accessed (index) and aggregated.

with agg as
(
    select 
        a.crampus_code, count(*) 
    from 
        T1 a  
    group by 
        crampus_code
)
select * 
from agg
where crampus_code in ('xxxx42','xxxx399'); 

The proof is in the execution plan (that you don't provided)

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     2 |    48 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|      |     2 |    48 |     3   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR    |      |       |       |            |          |
|*  3 |    INDEX RANGE SCAN  | IDX1 |     2 |    48 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."CRAMPUS_CODE"='xxxx399' OR 
              "A"."CRAMPUS_CODE"='xxxx42')

You see the table is accesse via index for only the two keys.

Your Case

This is only speculation but IMO during the merge predicate phase you get a much worst execution plan that without it.

This explain the increase in elapsed time by addin the WHERE clause.

Only if you show both plans (subquery and the query with WHERE) you can be sure...

Workaround

So what can you do if the predicate merge destroys the performance?

Simple turn it off. Unfortunately you must use an undocumented hint MATERIALIZE, that set up the result of the query as temporary table and the WHERE predicate will be applied on it.

This should work for your 18 rows. I'm not sure, but thing you must rewrite query using the subquery factoring (WITH).

with agg as
(select  /*+ MATERIALIZE */ a.crampus_code, count(*) from T1 a  group by crampus_code)
select * from agg
where crampus_code in ('xxxx42','xxxx399'); 

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             | 97325 |  3516K|    94   (5)| 00:00:02 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D673A_CE046F62 |       |       |            |          |
|   3 |    HASH GROUP BY           |                             | 97325 |  2281K|    41  (10)| 00:00:01 |
|   4 |     TABLE ACCESS FULL      | T1                          | 97325 |  2281K|    37   (0)| 00:00:01 |
|*  5 |   VIEW                     |                             | 97325 |  3516K|    54   (2)| 00:00:01 |
|   6 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D673A_CE046F62 | 97325 |  2281K|    54   (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("CRAMPUS_CODE"='xxxx399' OR "CRAMPUS_CODE"='xxxx42')