1 2 Previous Next 18 Replies Latest reply: Jan 11, 2013 11:16 AM by Nikolay Savvinov RSS

    sql is running very slow

    Jitendra-OC
         1.  What is the release level?  -------11.2.0.2
         2.  Is the query consistently slow, or occasionally? -----Yes this Query is consistently performing slow
         3.  Is the query slow only with certain bind variables? -----No
         4.  Has the query performed better in the past (i.e., execution plan regression)? ---- No
         5.  Are object stats up-to-date? ---- Yes. I collected stats today only
      
      Sql Statement-----
      
      SELECT   emplid, empl_rcd, dur, TO_CHAR (dur, 'YYYY-MM-DD'), punch_type,
               TO_CHAR (CAST ((punch_dttm) AS TIMESTAMP),
                        'YYYY-MM-DD-HH24.MI.SS.FF'),
               TO_CHAR (CAST ((punch_end) AS TIMESTAMP), 'YYYY-MM-DD-HH24.MI.SS.FF'),
               rule_element_1, rule_element_2, rule_element_3, rule_element_4,
               rule_element_5, taskgroup, oprid, reported_status, oprid_last_updt,
               TO_CHAR (CAST ((dttm_modified) AS TIMESTAMP),
                        'YYYY-MM-DD-HH24.MI.SS.FF'
                       ),
               TO_CHAR (CAST ((dttm_created) AS TIMESTAMP),
                        'YYYY-MM-DD-HH24.MI.SS.FF'
                       ),
               trc, tl_quantity, country, business_unit, LOCATION, deptid, jobcode,
               business_unit_pc, project_id, activity_id, resource_type,
               resource_category, resource_sub_cat, user_field_1, user_field_2,
               user_field_3, user_field_4, user_field_5,
               TO_CHAR (CAST ((lastupddttm) AS TIMESTAMP),
                        'YYYY-MM-DD-HH24.MI.SS.FF'
                       ),
               supervisor_id, NAME, descr, oi_bus_title, empl_status, empl_type,
               oi_sup_name, per_org, descr254, descr1, oi_proj_resp, tl_group_id,
               oi_comments
          FROM ps_oi_tl_rptim_vw5
         WHERE dur BETWEEN TO_DATE (:1, 'YYYY-MM-DD') AND TO_DATE (:2, 'YYYY-MM-DD')
           AND (tl_group_id) IN (SELECT tl_group_id
                                   FROM ps_oi_tl_group_sel
                                  WHERE oprid = 'FCAMERON');
      
      
      EXPLAIN PLAN------------------
      
      
      Plan hash value: 875496762
      
      -------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                             |                    |     1 |  2461 | 24367   (1)| 00:04:53 |
      |   1 |  SORT ORDER BY                               |                    |     1 |  2461 | 24367   (1)| 00:04:53 |
      |   2 |   NESTED LOOPS                               |                    |     1 |  2461 | 24367   (1)| 00:04:53 |
      |   3 |    VIEW                                      | PS_OI_TL_RPTIM_VW5 |     1 |  2447 | 24367   (1)| 00:04:53 |
      |   4 |     HASH UNIQUE                              |                    |     1 |   641 | 24367   (1)| 00:04:53 |
      |*  5 |      FILTER                                  |                    |       |       |            |          |
      |*  6 |       FILTER                                 |                    |       |       |            |          |
      |*  7 |        HASH JOIN                             |                    |     2 |  1282 |   583   (1)| 00:00:07 |
      |   8 |         NESTED LOOPS                         |                    |     1 |   623 |   532   (1)| 00:00:07 |
      |*  9 |          HASH JOIN                           |                    |    59 | 34456 |   414   (1)| 00:00:05 |
      |  10 |           NESTED LOOPS                       |                    |    62 | 34534 |   369   (1)| 00:00:05 |
      |* 11 |            HASH JOIN                         |                    |    62 | 33976 |   369   (1)| 00:00:05 |
      |* 12 |             HASH JOIN                        |                    |    64 | 33792 |   365   (1)| 00:00:05 |
      |  13 |              NESTED LOOPS                    |                    |    45 | 23130 |   361   (0)| 00:00:05 |
      |  14 |               NESTED LOOPS OUTER             |                    |    45 | 21915 |   271   (0)| 00:00:04 |
      |  15 |                NESTED LOOPS OUTER            |                    |    45 | 19845 |   226   (0)| 00:00:03 |
      |  16 |                 NESTED LOOPS                 |                    |    45 | 12015 |   225   (0)| 00:00:03 |
      |  17 |                  NESTED LOOPS                |                    |     7 |  1463 |   204   (0)| 00:00:03 |
      |  18 |                   TABLE ACCESS BY INDEX ROWID| PS_NAMES           |     6 |   222 |   167   (0)| 00:00:03 |
      |* 19 |                    INDEX SKIP SCAN           | PSANAMES           |     6 |       |   160   (0)| 00:00:02 |
      |  20 |                     SORT AGGREGATE           |                    |     1 |    21 |            |          |
      |* 21 |                      INDEX RANGE SCAN        | PS_NAMES           |     1 |    21 |     2   (0)| 00:00:01 |
      |  22 |                   TABLE ACCESS BY INDEX ROWID| PS_TL_RPTD_TIME    |     1 |   172 |     7   (0)| 00:00:01 |
      |* 23 |                    INDEX RANGE SCAN          | PS_TL_RPTD_TIME    |     4 |       |     5   (0)| 00:00:01 |
      |  24 |                  TABLE ACCESS BY INDEX ROWID | PS_JOB             |     6 |   348 |     3   (0)| 00:00:01 |
      |* 25 |                   INDEX RANGE SCAN           | IDX$$_3F450003     |     1 |       |     2   (0)| 00:00:01 |
      |  26 |                 TABLE ACCESS BY INDEX ROWID  | PS_OI_TL_PRJ_COMNT |     1 |   174 |     1   (0)| 00:00:01 |
      |* 27 |                  INDEX UNIQUE SCAN           | PS_OI_TL_PRJ_COMNT |     1 |       |     0   (0)| 00:00:01 |
      |  28 |                TABLE ACCESS BY INDEX ROWID   | PS_PROJECT         |     1 |    46 |     1   (0)| 00:00:01 |
      |* 29 |                 INDEX UNIQUE SCAN            | PS_PROJECT         |     1 |       |     0   (0)| 00:00:01 |
      |* 30 |               INDEX RANGE SCAN               | PSASET_CNTRL_REC   |     1 |    27 |     2   (0)| 00:00:01 |
      |  31 |              INDEX FAST FULL SCAN            | PS_TL_TRC_TBL      |  1066 | 14924 |     3   (0)| 00:00:01 |
      |  32 |             INDEX FAST FULL SCAN             | PS4DEPT_TBL        |  1815 | 36300 |     4   (0)| 00:00:01 |
      |* 33 |            INDEX UNIQUE SCAN                 | PS_PERSONAL_DATA   |     1 |     9 |     0   (0)| 00:00:01 |
      |  34 |           INDEX FAST FULL SCAN               | PS0PERSONAL_DATA   | 26178 |   690K|    44   (0)| 00:00:01 |
      |  35 |          TABLE ACCESS BY INDEX ROWID         | PS_OI_BUS_TITLE    |     1 |    39 |     2   (0)| 00:00:01 |
      |* 36 |           INDEX UNIQUE SCAN                  | PS_OI_BUS_TITLE    |     1 |       |     1   (0)| 00:00:01 |
      |  37 |         INDEX FAST FULL SCAN                 | PS_TL_GROUP_DTL    | 51579 |   906K|    50   (0)| 00:00:01 |
      |  38 |       SORT AGGREGATE                         |                    |     1 |    20 |            |          |
      |* 39 |        INDEX RANGE SCAN                      | PSAJOB             |     1 |    20 |     3   (0)| 00:00:01 |
      |  40 |       SORT AGGREGATE                         |                    |     1 |    14 |            |          |
      |* 41 |        INDEX RANGE SCAN                      | PS_TL_TRC_TBL      |     1 |    14 |     2   (0)| 00:00:01 |
      |  42 |       SORT AGGREGATE                         |                    |     1 |    20 |            |          |
      |* 43 |        INDEX RANGE SCAN                      | PS_DEPT_TBL        |     1 |    20 |     2   (0)| 00:00:01 |
      |  44 |       SORT AGGREGATE                         |                    |     1 |    23 |            |          |
      |* 45 |        INDEX RANGE SCAN                      | PSAJOB             |     1 |    23 |     3   (0)| 00:00:01 |
      |* 46 |    INDEX UNIQUE SCAN                         | PS_OI_TL_GROUP_SEL |     1 |    14 |     0   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - filter("SYS_ALIAS_6"."EFFDT"= (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM
                    SYSADM."PS_JOB" "B_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND
                    SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B1) AND "B_ED"."EMPL_RCD"=:B2 AND "B_ED"."EMPLID"=:B3 AND
                    SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B4) AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT
                    MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_TL_TRC_TBL" "G_ED" WHERE
                    SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B5) AND "G_ED"."TRC"=:B6
                    AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B7) AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT
                    MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_DEPT_TBL" "F_ED" WHERE
                    SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B8) AND
                    "F_ED"."DEPTID"=:B9 AND "F_ED"."SETID"=:B10 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B11) AND
                    "SYS_ALIAS_6"."EFFSEQ"= (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ"))) FROM SYSADM."PS_JOB"
                    "B_ES" WHERE SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B12) AND "B_ES"."EMPL_RCD"=:B13 AND
                    "B_ES"."EMPLID"=:B14 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B15))
         6 - filter(TO_DATE(:1,'YYYY-MM-DD')<=TO_DATE(:2,'YYYY-MM-DD'))
         7 - access("A"."EMPLID"="L"."EMPLID" AND "A"."EMPL_RCD"="L"."EMPL_RCD")
         9 - access("C1"."EMPLID"="SYS_ALIAS_6"."SUPERVISOR_ID")
        11 - access("SYS_ALIAS_8"."SETID"="F1"."SETID" AND "SYS_ALIAS_8"."DEPTID"="SYS_ALIAS_6"."DEPTID")
        12 - access("SYS_ALIAS_10"."TRC"="A"."TRC")
        19 - access("SYS_ALIAS_13"."NAME_TYPE"='PRI')
             filter("SYS_ALIAS_13"."NAME_TYPE"='PRI' AND "SYS_ALIAS_13"."EFFDT"= (SELECT
                    MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM SYSADM."PS_NAMES" "B" WHERE SYS_OP_DESCEND("EFFDT") IS
                    NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
                     AND "B"."NAME_TYPE"=:B1 AND "B"."EMPLID"=:B2 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHA
                    R(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')))
        21 - access("B"."EMPLID"=:B1 AND "B"."NAME_TYPE"=:B2 AND
                    SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND
                    SYS_OP_DESCEND("EFFDT") IS NOT NULL)
             filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-D
                    D'))
        23 - access("A"."EMPLID"="SYS_ALIAS_13"."EMPLID" AND "A"."DUR">=TO_DATE(:1,'YYYY-MM-DD') AND
                    "A"."DUR"<=TO_DATE(:2,'YYYY-MM-DD'))
             filter("A"."DUR">=TO_DATE(:1,'YYYY-MM-DD') AND "A"."DUR"<=TO_DATE(:2,'YYYY-MM-DD'))
        25 - access("A"."EMPLID"="SYS_ALIAS_6"."EMPLID" AND "A"."EMPL_RCD"="SYS_ALIAS_6"."EMPL_RCD")
        27 - access("A"."EMPLID"="M"."EMPLID"(+) AND "A"."EMPL_RCD"="M"."EMPL_RCD"(+) AND "A"."DUR"="M"."DUR"(+)
                    AND "A"."SEQ_NBR"="M"."SEQ_NBR"(+))
             filter("M"."DUR"(+)>=TO_DATE(:1,'YYYY-MM-DD') AND "M"."DUR"(+)<=TO_DATE(:2,'YYYY-MM-DD'))
        29 - access("D"."BUSINESS_UNIT"(+)="A"."BUSINESS_UNIT_PC" AND "D"."PROJECT_ID"(+)="A"."PROJECT_ID")
        30 - access("F1"."SETCNTRLVALUE"="SYS_ALIAS_6"."BUSINESS_UNIT" AND "F1"."RECNAME"='DEPT_TBL')
        33 - access("C"."EMPLID"="SYS_ALIAS_6"."EMPLID")
        36 - access("E"."EMPLID"="A"."EMPLID" AND "E"."EMPL_RCD"="A"."EMPL_RCD" AND
                    SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND("SYS_ALIAS_6"."EFFDT") AND
                    SYS_OP_DESCEND("EFFSEQ")=SYS_OP_DESCEND("SYS_ALIAS_6"."EFFSEQ"))
             filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))="SYS_ALIAS_6"."EFFDT" AND
                    SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ"))="SYS_ALIAS_6"."EFFSEQ")
        39 - access("B_ED"."EMPLID"=:B1 AND "B_ED"."EMPL_RCD"=:B2 AND
                    SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B3) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
             filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1)
        41 - access("G_ED"."TRC"=:B1 AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B2) AND
                    SYS_OP_DESCEND("EFFDT") IS NOT NULL)
             filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1)
        43 - access("F_ED"."SETID"=:B1 AND "F_ED"."DEPTID"=:B2 AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B3)
                    AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
             filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1)
        45 - access("B_ES"."EMPLID"=:B1 AND "B_ES"."EMPL_RCD"=:B2 AND
                    SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B3))
             filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B1)
        46 - access("OPRID"='FCAMERON' AND "TL_GROUP_ID"="TL_GROUP_ID")
      
      114 rows selected.
      
      -------View "ps_oi_tl_rptim_vw5"   text-----------
      
      
      CREATE OR REPLACE FORCE VIEW sysadm.ps_oi_tl_rptim_vw5 (emplid,
                                                              empl_rcd,
                                                              dur,
                                                              punch_type,
                                                              punch_dttm,
                                                              punch_end,
                                                              rule_element_1,
                                                              rule_element_2,
                                                              rule_element_3,
                                                              rule_element_4,
                                                              rule_element_5,
                                                              taskgroup,
                                                              oprid,
                                                              reported_status,
                                                              oprid_last_updt,
                                                              dttm_modified,
                                                              dttm_created,
                                                              trc,
                                                              tl_quantity,
                                                              country,
                                                              business_unit,
                                                              LOCATION,
                                                              deptid,
                                                              jobcode,
                                                              business_unit_pc,
                                                              project_id,
                                                              activity_id,
                                                              resource_type,
                                                              resource_category,
                                                              resource_sub_cat,
                                                              user_field_1,
                                                              user_field_2,
                                                              user_field_3,
                                                              user_field_4,
                                                              user_field_5,
                                                              lastupddttm,
                                                              supervisor_id,
                                                              NAME,
                                                              descr,
                                                              oi_bus_title,
                                                              empl_status,
                                                              empl_type,
                                                              oi_sup_name,
                                                              per_org,
                                                              descr254,
                                                              descr1,
                                                              oi_proj_resp,
                                                              tl_group_id,
                                                              oi_comments
                                                             )
      AS
         SELECT DISTINCT a.emplid, a.empl_rcd, a.dur, a.punch_type, a.punch_dttm,
                         a.punch_end, a.rule_element_1, a.rule_element_2,
                         a.rule_element_3, a.rule_element_4, a.rule_element_5,
                         a.taskgroup, a.oprid, a.reported_status, a.oprid_last_updt,
                         a.dttm_modified, a.dttm_created, a.trc, a.tl_quantity,
                         a.country, a.business_unit, a.LOCATION, b.deptid,
                         b.jobcode, a.business_unit_pc, a.project_id, a.activity_id,
                         a.resource_type, a.resource_category, a.resource_sub_cat,
                         a.user_field_1, a.user_field_2, a.user_field_3,
                         a.user_field_4, a.user_field_5, a.lastupddttm,
                         b.supervisor_id, n.NAME,
                         CASE
                            WHEN d.descr IS NULL
                               THEN 'No Project Information'
                            ELSE d.descr
                         END,
                         e.oi_bus_title, b.empl_status, b.empl_type, c1.NAME,
                         b.per_org, d.descr, ' ', d.project_user1, l.tl_group_id,
                         NVL (DBMS_LOB.SUBSTR (m.oi_comments, 2000, 1),
                              'No Comment')
                    FROM ps_tl_rptd_time a LEFT OUTER JOIN ps_project d
                         ON d.business_unit = a.business_unit_pc
                       AND d.project_id = a.project_id
                         LEFT OUTER JOIN ps_oi_tl_prj_comnt m
                         ON a.emplid = m.emplid
                       AND a.empl_rcd = m.empl_rcd
                       AND a.dur = m.dur
                       AND a.seq_nbr = m.seq_nbr
                         ,
                         ps_job b,
                         ps_personal_data c,
                         ps_personal_data c1,
                         ps_oi_bus_title e,
                         ps_dept_tbl f,
                         ps_set_cntrl_rec f1,
                         ps_tl_trc_tbl g,
                         ps_person_name n,
                         ps_tl_group_dtl l
                   WHERE a.emplid = b.emplid
                     AND a.empl_rcd = b.empl_rcd
                     AND a.emplid = n.emplid
                     AND b.effdt =
                            (SELECT MAX (b_ed.effdt)
                               FROM ps_job b_ed
                              WHERE b_ed.emplid = b.emplid
                                AND b_ed.empl_rcd = b.empl_rcd
                                AND b_ed.effdt <= a.dur)
                     AND b.effseq =
                            (SELECT MAX (b_es.effseq)
                               FROM ps_job b_es
                              WHERE b_es.emplid = b.emplid
                                AND b_es.empl_rcd = b.empl_rcd
                                AND b_es.effdt = b.effdt)
                     AND c.emplid = b.emplid
                     AND c1.emplid = b.supervisor_id
                     AND e.emplid = a.emplid
                     AND e.empl_rcd = a.empl_rcd
                     AND e.effdt = b.effdt
                     AND e.effseq = b.effseq
                     AND f.setid = f1.setid
                     AND f.deptid = b.deptid
                     AND f.effdt =
                            (SELECT MAX (f_ed.effdt)
                               FROM ps_dept_tbl f_ed
                              WHERE f_ed.setid = f.setid
                                AND f_ed.deptid = f.deptid
                                AND f_ed.effdt <= a.dur)
                     AND f1.setcntrlvalue = b.business_unit
                     AND f1.recname = 'DEPT_TBL'
                     AND g.trc = a.trc
                     AND g.effdt = (SELECT MAX (g_ed.effdt)
                                      FROM ps_tl_trc_tbl g_ed
                                     WHERE g_ed.trc = g.trc AND g_ed.effdt <= a.dur)
                     AND a.emplid = l.emplid
                     AND a.empl_rcd = l.empl_rcd;
      
      
      
      
      
      
      Thanks
      Jitendra
        • 1. Re: sql is running very slow
          moreajays
          Hi,

          So many Nested Loops , check for changing logic of the query else try using /*+ PARALLEL(ps_oi_tl_rptim_vw5,20) */ Hint

          Thanks,
          Ajay More
          http://moreajays.blogspot.com
          • 2. Re: sql is running very slow
            Jitendra-OC
            Hint provided By you is not working although I used parallel hint and it is reducing time to one fourth. But I don't want to use the hints so Can you please rewrite sql or give me some advice that I can follow to rewrite the query.
            • 3. Re: sql is running very slow
              SomeoneElse
              Code tags are lowercase:
                                                                                                                                                                                                                                              
              • 4. Re: sql is running very slow
                Jitendra-OC
                Thanks, But Please provide me help in tuning my Query.

                Thanks
                Jitendra
                • 5. Re: sql is running very slow
                  moreajays
                  Hi,

                  Its very huge query to tune & see very little scope as view being referred & multiple joins using Index scan as well , better use sql tuning analyzer.
                  Identify sql_id for this query & replace it every where in below & check for recommandation
                  DECLARE
                    my_task_name VARCHAR2(30);
                    my_sqltext CLOB;
                  BEGIN
                    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                                             sql_id =>'c0mj20vr9dk0z',
                                             scope => 'COMPREHENSIVE',
                                             time_limit => 60,
                                             task_name => 'sql_ims1_c0mj20vr9dk0z',
                                             description => 'Task to tune a query');
                  END;
                  Begin
                  dbms_sqltune.Execute_tuning_task (task_name => 'sql_ims1_c0mj20vr9dk0z');
                  end;
                  set long 100000
                  set linesize 5000
                  set pages 4000
                  select dbms_sqltune.report_tuning_task('sql_ims1_c0mj20vr9dk0z') from dual;
                  Thanks,
                  Ajay More
                  http://moreajays.blogspot.com
                  • 6. Re: sql is running very slow
                    SomeoneElse
                    Please read these threads:

                    When your query takes too long
                    When your query takes too long ...

                    How to Post a SQL statement tuning request
                    HOW TO: Post a SQL statement tuning request - template posting

                    (part of the requirements is to use proper code tags)
                    • 7. Re: sql is running very slow
                      Jitendra-OC
                      How to attach a file in OTN

                      Thanks
                      Jitendra
                      • 8. Re: sql is running very slow
                        moreajays
                        Hi,

                        no it can not be attached , refer recommandation line of the tuning report , what it says ?

                        Thanks,
                        Ajay More
                        http://moreajays.blogspot.com
                        • 9. Re: sql is running very slow
                          Jitendra-OC
                          Hi

                          I have already ran the sql tuning advisor and implemented its recommendation. There is no benefit of it.

                          Thanks
                          Jitendra
                          • 10. Re: sql is running very slow
                            sb92075
                            901629 wrote:
                            Hi

                            I have already ran the sql tuning advisor and implemented its recommendation. There is no benefit of it.

                            Thanks
                            Jitendra
                            not every SQL statement can be improved.
                            at some point it is as good as it can be.
                            • 11. Re: sql is running very slow
                              moreajays
                              Hi,

                              Can you confirm the size & NUM_ROWS of the tables involved , if its huge you have to purge some old records & rebuild/defrag/shrink the table/indexes.


                              Thanks,
                              Ajay More
                              http://moreajays.blogspot.com
                              • 12. Re: sql is running very slow
                                riedelme
                                Huge query with several factors that could be causing slowness, together or by themselves
                                * Distinct clause forcing sort
                                * Subqueries performing group by - of subqueries performing group by!
                                * outer joins
                                * uncorrelated IN subqueries
                                * skip scan index operation
                                * rererencing a complex view in a subquery

                                Start by looking closely at the index skip scan operation in step 19. See if a new index can avoid the skip scan and perform a possibly more efficient index range scan. The columns referenced in the WHERE clause where the skip scan is invoked should be examined for such a new index.

                                The biggest jump in cost in the plan occur between 4 and 7. The 2 filter operations in steps 5 and 6 have no costs listed. Can the SQL be simplified to avoid these filter operations? Remember that although cost is not always reliable as a metric it can be used to determine when optimizer processes change behavior between modifications.
                                • 13. Re: sql is running very slow
                                  Oceaner
                                  Hi,

                                  On behalf of OP, I am posting plan for the query which is making query run faster. This is basically a sql profile suggested by SQL Tuning Advisor. Can someone please shed some important points comparing the two plans..that what can be making the difference and what can be the problem.
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                  | Id  | Pid | Ord | Operation                                  | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | A-Time Self |Bufs Self |Reads Self|A-Ti S-Graph |Bufs S-Graph |Reads S-Graph|LIO Ratio |TCF Graph |E-Rows*Sta|
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                  |   0 |     |  46 | SELECT STATEMENT                           |                    |      1 |        |   7275 |00:00:30.79 |     358K|   5512 |       |       |          | 00:00:00.00 |        0 |        0 |             |             |             |        0 |          |          |
                                  |   1 |   0 |  45 |  NESTED LOOPS                              |                    |      1 |      1 |   7275 |00:00:30.79 |     358K|   5512 |       |       |          | 00:00:00.00 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |*  2 |   1 |   1 |   INDEX RANGE SCAN                         | PS_OI_TL_GROUP_SEL |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          | 00:00:00.00 |        2 |        0 |             |             |             |        2 |          |        1 |
                                  |   3 |   1 |  44 |   VIEW PUSHED PREDICATE                    | PS_OI_TL_RPTIM_VW5 |      1 |      1 |   7275 |00:00:30.79 |     358K|   5512 |       |       |          | 00:00:00.01 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |   4 |   3 |  43 |    SORT UNIQUE                             |                    |      1 |      1 |   7275 |00:00:30.78 |     358K|   5512 |  3171K|   785K| 2818K (0)| 00:00:00.25 |     1079 |        5 |             |             |             |        0 | +++      |        1 |
                                  |   5 |   4 |  42 |     NESTED LOOPS                           |                    |      1 |        |   7276 |00:00:30.53 |     357K|   5507 |       |       |          | 00:00:00.01 |        0 |        0 |             |             |             |        0 |          |          |
                                  |   6 |   5 |  40 |      NESTED LOOPS                          |                    |      1 |      1 |   7276 |00:00:30.51 |     350K|   5507 |       |       |          | 00:00:00.01 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |   7 |   6 |  38 |       NESTED LOOPS                         |                    |      1 |      1 |   7276 |00:00:30.49 |     349K|   5507 |       |       |          | 00:00:00.01 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |   8 |   7 |  36 |        NESTED LOOPS                        |                    |      1 |      1 |   7276 |00:00:30.47 |     347K|   5507 |       |       |          | 00:00:00.00 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |   9 |   8 |  32 |         NESTED LOOPS                       |                    |      1 |      1 |   7276 |00:00:30.42 |     346K|   5507 |       |       |          | 00:00:00.01 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |  10 |   9 |  30 |          NESTED LOOPS                      |                    |      1 |      1 |   7276 |00:00:30.40 |     346K|   5507 |       |       |          | 00:00:00.03 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |  11 |  10 |  23 |           NESTED LOOPS                     |                    |      1 |      1 |  70543 |00:00:30.07 |     328K|   5507 |       |       |          | 00:00:00.02 |        0 |        0 |             |             |             |        0 | ++++     |        1 |
                                  |  12 |  11 |  20 |            NESTED LOOPS OUTER              |                    |      1 |      1 |   7276 |00:00:29.86 |     263K|   5507 |       |       |          | 00:00:00.01 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |  13 |  12 |  17 |             NESTED LOOPS OUTER             |                    |      1 |      1 |   7276 |00:00:29.81 |     252K|   5507 |       |       |          | 00:00:00.01 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |  14 |  13 |  14 |              NESTED LOOPS                  |                    |      1 |      1 |   7276 |00:00:29.77 |     250K|   5507 |       |       |          | 00:00:00.01 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |  15 |  14 |  10 |               NESTED LOOPS                 |                    |      1 |      1 |   7276 |00:00:29.72 |     246K|   5507 |       |       |          | 00:00:00.06 |        0 |        0 |             |             |             |        0 | +++      |        1 |
                                  |  16 |  15 |   8 |                NESTED LOOPS                |                    |      1 |      2 |    161K|00:00:29.46 |     233K|   5507 |       |       |          | 00:00:00.06 |        0 |        0 |             |             |             |        0 | ++++     |        2 |
                                  |  17 |  16 |   5 |                 TABLE ACCESS BY INDEX ROWID| PS_NAMES           |      1 |      3 |  26187 |00:00:00.54 |   53655 |     12 |       |       |          | 00:00:00.10 |    25835 |        2 |             | @           |             |        0 | +++      |        3 |
                                  |* 18 |  17 |   4 |                  INDEX SKIP SCAN           | PSANAMES           |      1 |      3 |  26187 |00:00:00.44 |   27820 |     10 |       |       |          | 00:00:00.18 |      166 |       10 |             |             |             |        0 | +++      |        3 |
                                  |  19 |  18 |   3 |                   SORT AGGREGATE           |                    |  27533 |      1 |  27533 |00:00:00.25 |   27654 |      0 |       |       |          | 00:00:00.06 |        0 |        0 |             |             |             |        0 |          |    27533 |
                                  |* 20 |  19 |   2 |                    INDEX RANGE SCAN        | PS_NAMES           |  27533 |  29594 |  32815 |00:00:00.19 |   27654 |      0 |       |       |          | 00:00:00.19 |    27654 |        0 |             | @           |             |        0 | ----     |      815M|
                                  |  21 |  16 |   7 |                 TABLE ACCESS BY INDEX ROWID| PS_TL_RPTD_TIME    |  26187 |      1 |    161K|00:00:28.87 |     180K|   5495 |       |       |          | 00:00:00.48 |    72095 |        5 |             | @@          |             |        0 |          |    26187 |
                                  |* 22 |  21 |   6 |                  INDEX RANGE SCAN          | PS_TL_RPTD_TIME    |  26187 |      3 |    161K|00:00:28.39 |     108K|   5490 |       |       |          | 00:00:28.39 |      108K|     5490 | @@@@@@@@@@@ | @@@@        | @@@@@@@@@@@@|        0 |          |    78561 |
                                  |* 23 |  15 |   9 |                INDEX UNIQUE SCAN           | PS_TL_GROUP_DTL    |    161K|      1 |   7276 |00:00:00.19 |   13173 |      0 |       |       |          | 00:00:00.19 |    13173 |        0 |             |             |             |        1 | -        |      162K|
                                  |* 24 |  14 |  13 |               INDEX RANGE SCAN             | PS_TL_TRC_TBL      |   7276 |      1 |   7276 |00:00:00.05 |    3888 |      0 |       |       |          | 00:00:00.05 |     3586 |        0 |             |             |             |        0 |          |     7276 |
                                  |  25 |  24 |  12 |                SORT AGGREGATE              |                    |    347 |      1 |    347 |00:00:00.01 |     302 |      0 |       |       |          | 00:00:00.00 |        0 |        0 |             |             |             |        0 |          |      347 |
                                  |* 26 |  25 |  11 |                 INDEX RANGE SCAN           | PS_TL_TRC_TBL      |    347 |      1 |    347 |00:00:00.01 |     302 |      0 |       |       |          | 00:00:00.00 |      302 |        0 |             |             |             |        0 |          |      347 |
                                  |  27 |  13 |  16 |              TABLE ACCESS BY INDEX ROWID   | PS_OI_TL_PRJ_COMNT |   7276 |      1 |      5 |00:00:00.02 |    1342 |      0 |       |       |          | 00:00:00.01 |        5 |        0 |             |             |             |        1 | ---      |     7276 |
                                  |* 28 |  27 |  15 |               INDEX UNIQUE SCAN            | PS_OI_TL_PRJ_COMNT |   7276 |      1 |      5 |00:00:00.02 |    1337 |      0 |       |       |          | 00:00:00.02 |     1337 |        0 |             |             |             |      267 | ---      |     7276 |
                                  |  29 |  12 |  19 |             TABLE ACCESS BY INDEX ROWID    | PS_PROJECT         |   7276 |      1 |   7276 |00:00:00.04 |   11733 |      0 |       |       |          | 00:00:00.02 |     7276 |        0 |             |             |             |        1 |          |     7276 |
                                  |* 30 |  29 |  18 |              INDEX UNIQUE SCAN             | PS_PROJECT         |   7276 |      1 |   7276 |00:00:00.02 |    4457 |      0 |       |       |          | 00:00:00.02 |     4457 |        0 |             |             |             |        0 |          |     7276 |
                                  |  31 |  11 |  22 |            TABLE ACCESS BY INDEX ROWID     | PS_OI_BUS_TITLE    |   7276 |      7 |  70543 |00:00:00.19 |   64924 |      0 |       |       |          | 00:00:00.14 |    62101 |        0 |             | @@          |             |        0 |          |    50932 |
                                  |* 32 |  31 |  21 |             INDEX RANGE SCAN               | PS_OI_BUS_TITLE    |   7276 |      1 |  70543 |00:00:00.05 |    2823 |      0 |       |       |          | 00:00:00.05 |     2823 |        0 |             |             |             |        0 |          |     7276 |
                                  |  33 |  10 |  29 |           TABLE ACCESS BY INDEX ROWID      | PS_JOB             |  70543 |      1 |   7276 |00:00:00.29 |   18057 |      0 |       |       |          | 00:00:00.06 |     7276 |        0 |             |             |             |        1 |          |    70543 |
                                  |* 34 |  33 |  28 |            INDEX UNIQUE SCAN               | PS_JOB             |  70543 |      1 |   7276 |00:00:00.23 |   10781 |      0 |       |       |          | 00:00:00.18 |     7830 |        0 |             |             |             |        1 |          |    70543 |
                                  |  35 |  34 |  25 |             SORT AGGREGATE                 |                    |   4403 |      1 |   4403 |00:00:00.05 |    2535 |      0 |       |       |          | 00:00:00.02 |        0 |        0 |             |             |             |        0 |          |     4403 |
                                  |* 36 |  35 |  24 |              INDEX RANGE SCAN              | PSAJOB             |   4403 |      1 |  32990 |00:00:00.04 |    2535 |      0 |       |       |          | 00:00:00.04 |     2535 |        0 |             |             |             |        0 |          |     4403 |
                                  |  37 |  34 |  27 |             SORT AGGREGATE                 |                    |    206 |      1 |    206 |00:00:00.01 |     416 |      0 |       |       |          | 00:00:00.00 |        0 |        0 |             |             |             |        0 |          |      206 |
                                  |* 38 |  37 |  26 |              INDEX RANGE SCAN              | PSAJOB             |    206 |      1 |    244 |00:00:00.01 |     416 |      0 |       |       |          | 00:00:00.00 |      416 |        0 |             |             |             |        1 |          |      206 |
                                  |* 39 |   9 |  31 |          INDEX RANGE SCAN                  | PSASET_CNTRL_REC   |   7276 |      1 |   7276 |00:00:00.02 |      20 |      0 |       |       |          | 00:00:00.02 |       20 |        0 |             |             |             |        0 |          |     7276 |
                                  |* 40 |   8 |  35 |         INDEX RANGE SCAN                   | PS_DEPT_TBL        |   7276 |      1 |   7276 |00:00:00.04 |     913 |      0 |       |       |          | 00:00:00.04 |      614 |        0 |             |             |             |        0 |          |     7276 |
                                  |  41 |  40 |  34 |          SORT AGGREGATE                    |                    |    752 |      1 |    752 |00:00:00.01 |     299 |      0 |       |       |          | 00:00:00.00 |        0 |        0 |             |             |             |        0 |          |      752 |
                                  |* 42 |  41 |  33 |           INDEX RANGE SCAN                 | PS_DEPT_TBL        |    752 |      1 |   1097 |00:00:00.01 |     299 |      0 |       |       |          | 00:00:00.00 |      299 |        0 |             |             |             |        0 |          |      752 |
                                  |* 43 |   7 |  37 |        INDEX UNIQUE SCAN                   | PS_PERSONAL_DATA   |   7276 |      1 |   7276 |00:00:00.01 |    1424 |      0 |       |       |          | 00:00:00.01 |     1424 |        0 |             |             |             |        0 |          |     7276 |
                                  |* 44 |   6 |  39 |       INDEX UNIQUE SCAN                    | PS_PERSONAL_DATA   |   7276 |      1 |   7276 |00:00:00.01 |    1404 |      0 |       |       |          | 00:00:00.01 |     1404 |        0 |             |             |             |        0 |          |     7276 |
                                  |  45 |   5 |  41 |      TABLE ACCESS BY INDEX ROWID           | PS_PERSONAL_DATA   |   7276 |      1 |   7276 |00:00:00.02 |    7276 |      0 |       |       |          | 00:00:00.02 |     7276 |        0 |             |             |             |        1 |          |     7276 |
                                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                  
                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------
                                  
                                     2 - access("OPRID"='FCAMERON')
                                    18 - access("SYS_ALIAS_13"."NAME_TYPE"='PRI')
                                         filter(("SYS_ALIAS_13"."NAME_TYPE"='PRI' AND "SYS_ALIAS_13"."EFFDT"=))
                                    20 - access("B"."EMPLID"=:B1 AND "B"."NAME_TYPE"=:B2 AND "B"."SYS_NC00030$">=SYS_OP_DESCEND(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
                                                AND "B"."SYS_NC00030$" IS NOT NULL)
                                         filter(SYS_OP_UNDESCEND("B"."SYS_NC00030$")<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
                                    22 - access("A"."EMPLID"="SYS_ALIAS_13"."EMPLID" AND "A"."DUR">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."DUR"<=TO_DATE('
                                                2012-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
                                         filter(("A"."DUR">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."DUR"<=TO_DATE(' 2012-01-31 00:00:00', 'syyyy-mm-dd
                                                hh24:mi:ss')))
                                    23 - access("L"."TL_GROUP_ID"="TL_GROUP_ID" AND "A"."EMPLID"="L"."EMPLID" AND "A"."EMPL_RCD"="L"."EMPL_RCD")
                                    24 - access("SYS_ALIAS_10"."TRC"="A"."TRC")
                                         filter(SYS_OP_UNDESCEND("SYS_ALIAS_10"."SYS_NC00045$")=)
                                    26 - access("G_ED"."TRC"=:B1 AND "G_ED"."SYS_NC00045$">=SYS_OP_DESCEND(:B2) AND "G_ED"."SYS_NC00045$" IS NOT NULL)
                                         filter(SYS_OP_UNDESCEND("G_ED"."SYS_NC00045$")<=:B1)
                                    28 - access("A"."EMPLID"="M"."EMPLID" AND "A"."EMPL_RCD"="M"."EMPL_RCD" AND "A"."DUR"="M"."DUR" AND "A"."SEQ_NBR"="M"."SEQ_NBR")
                                         filter(("M"."DUR"<=TO_DATE(' 2012-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "M"."DUR">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd
                                                hh24:mi:ss')))
                                    30 - access("D"."BUSINESS_UNIT"="A"."BUSINESS_UNIT_PC" AND "D"."PROJECT_ID"="A"."PROJECT_ID")
                                    32 - access("E"."EMPLID"="A"."EMPLID" AND "E"."EMPL_RCD"="A"."EMPL_RCD")
                                    34 - access("A"."EMPLID"="SYS_ALIAS_6"."EMPLID" AND "A"."EMPL_RCD"="SYS_ALIAS_6"."EMPL_RCD" AND "E"."SYS_NC00008$"="SYS_ALIAS_6"."SYS_NC00163$" AND
                                                "E"."SYS_NC00009$"="SYS_ALIAS_6"."SYS_NC00164$")
                                         filter((SYS_OP_UNDESCEND("E"."SYS_NC00008$")=SYS_OP_UNDESCEND("SYS_ALIAS_6"."SYS_NC00163$") AND
                                                SYS_OP_UNDESCEND("E"."SYS_NC00009$")=SYS_OP_UNDESCEND("SYS_ALIAS_6"."SYS_NC00164$") AND SYS_OP_UNDESCEND("SYS_ALIAS_6"."SYS_NC00163$")= AND
                                                SYS_OP_UNDESCEND("SYS_ALIAS_6"."SYS_NC00164$")=))
                                    36 - access("B_ED"."EMPLID"=:B1 AND "B_ED"."EMPL_RCD"=:B2 AND "B_ED"."SYS_NC00163$">=SYS_OP_DESCEND(:B3) AND "B_ED"."SYS_NC00163$" IS NOT NULL)
                                         filter(SYS_OP_UNDESCEND("B_ED"."SYS_NC00163$")<=:B1)
                                    38 - access("B_ES"."EMPLID"=:B1 AND "B_ES"."EMPL_RCD"=:B2 AND "B_ES"."SYS_NC00163$"=SYS_OP_DESCEND(:B3))
                                         filter(SYS_OP_UNDESCEND("B_ES"."SYS_NC00163$")=:B1)
                                    39 - access("F1"."SETCNTRLVALUE"="SYS_ALIAS_6"."BUSINESS_UNIT" AND "F1"."RECNAME"='DEPT_TBL')
                                    40 - access("SYS_ALIAS_8"."SETID"="F1"."SETID" AND "SYS_ALIAS_8"."DEPTID"="SYS_ALIAS_6"."DEPTID")
                                         filter(SYS_OP_UNDESCEND("SYS_ALIAS_8"."SYS_NC00054$")=)
                                    42 - access("F_ED"."SETID"=:B1 AND "F_ED"."DEPTID"=:B2 AND "F_ED"."SYS_NC00054$">=SYS_OP_DESCEND(:B3) AND "F_ED"."SYS_NC00054$" IS NOT NULL)
                                         filter(SYS_OP_UNDESCEND("F_ED"."SYS_NC00054$")<=:B1)
                                    43 - access("C"."EMPLID"="SYS_ALIAS_6"."EMPLID")
                                    44 - access("C1"."EMPLID"="SYS_ALIAS_6"."SUPERVISOR_ID")
                                  
                                  Note
                                  -----
                                     - SQL profile SYS_SQLPROF_023c261cae740000 used for this statement
                                  
                                  
                                  116 rows selected.
                                  • 14. Re: sql is running very slow
                                    Oceaner
                                    One more update..

                                    If I change the query, Instead of fetching the value for TL_GROUP_ID from ps_oi_tl_group_sel, I put it as a constant value (Though just for testing), query runs fine..
                                    SELECT emplid, 
                                           empl_rcd, 
                                           dur, 
                                           To_char(dur, 'YYYY-MM-DD'), 
                                           punch_type, 
                                           To_char(Cast(( punch_dttm ) AS TIMESTAMP), 'YYYY-MM-DD-HH24.MI.SS.FF'), 
                                           To_char(Cast(( punch_end ) AS TIMESTAMP), 'YYYY-MM-DD-HH24.MI.SS.FF'), 
                                           rule_element_1, 
                                           rule_element_2, 
                                           rule_element_3, 
                                           rule_element_4, 
                                           rule_element_5, 
                                           taskgroup, 
                                           oprid, 
                                           reported_status, 
                                           oprid_last_updt, 
                                           To_char(Cast(( dttm_modified ) AS TIMESTAMP), 'YYYY-MM-DD-HH24.MI.SS.FF') 
                                           , 
                                           To_char(Cast(( dttm_created ) AS TIMESTAMP), 
                                           'YYYY-MM-DD-HH24.MI.SS.FF'), 
                                           trc, 
                                           tl_quantity, 
                                           country, 
                                           business_unit, 
                                           location, 
                                           deptid, 
                                           jobcode, 
                                           business_unit_pc, 
                                           project_id, 
                                           activity_id, 
                                           resource_type, 
                                           resource_category, 
                                           resource_sub_cat, 
                                           user_field_1, 
                                           user_field_2, 
                                           user_field_3, 
                                           user_field_4, 
                                           user_field_5, 
                                           To_char(Cast(( lastupddttm ) AS TIMESTAMP), 'YYYY-MM-DD-HH24.MI.SS.FF'), 
                                           supervisor_id, 
                                           name, 
                                           descr, 
                                           oi_bus_title, 
                                           empl_status, 
                                           empl_type, 
                                           oi_sup_name, 
                                           per_org, 
                                           descr254, 
                                           descr1, 
                                           oi_proj_resp, 
                                           tl_group_id, 
                                           oi_comments 
                                    FROM   ps_oi_tl_rptim_vw5 
                                    WHERE  dur BETWEEN To_date(:1, 'YYYY-MM-DD') AND To_date(:2, 'YYYY-MM-DD') 
                                           AND ( tl_group_id ) = 'FCAMERON' 
                                    ORDER  BY emplid, 
                                              empl_rcd, 
                                              dur 
                                    And explain plan for this is
                                    ------------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
                                    ------------------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT                            |                    |     1 |  2446 |    68   (2)| 00:00:01 |
                                    |   1 |  SORT ORDER BY                              |                    |     1 |  2446 |    68   (2)| 00:00:01 |
                                    |   2 |   VIEW                                      | PS_OI_TL_RPTIM_VW5 |     1 |  2446 |    68   (2)| 00:00:01 |
                                    |   3 |    HASH UNIQUE                              |                    |     1 |   498 |    68   (2)| 00:00:01 |
                                    |*  4 |     FILTER                                  |                    |       |       |            |          |
                                    |   5 |      NESTED LOOPS                           |                    |       |       |            |          |
                                    
                                    PLAN_TABLE_OUTPUT
                                    -----------------------------------------------------------------------------------------------------------------------------
                                    |   6 |       NESTED LOOPS                          |                    |     1 |   498 |    43   (0)| 00:00:01 |
                                    |   7 |        NESTED LOOPS                         |                    |     1 |   471 |    42   (0)| 00:00:01 |
                                    |   8 |         NESTED LOOPS                        |                    |     1 |   462 |    42   (0)| 00:00:01 |
                                    |   9 |          NESTED LOOPS                       |                    |     1 |   442 |    41   (0)| 00:00:01 |
                                    |  10 |           NESTED LOOPS                      |                    |     1 |   415 |    39   (0)| 00:00:01 |
                                    |  11 |            NESTED LOOPS                     |                    |     1 |   357 |    37   (0)| 00:00:01 |
                                    |  12 |             NESTED LOOPS OUTER              |                    |     1 |   318 |    34   (0)| 00:00:01 |
                                    |  13 |              NESTED LOOPS                   |                    |     1 |   272 |    33   (0)| 00:00:01 |
                                    |  14 |               NESTED LOOPS OUTER            |                    |     1 |   258 |    32   (0)| 00:00:01 |
                                    |  15 |                NESTED LOOPS                 |                    |     1 |   227 |    31   (0)| 00:00:01 |
                                    |  16 |                 NESTED LOOPS                |                    |     9 |  1710 |    13   (0)| 00:00:01 |
                                    
                                    PLAN_TABLE_OUTPUT
                                    -----------------------------------------------------------------------------------------------------------------------------
                                    |* 17 |                  INDEX RANGE SCAN           | PS_TL_GROUP_DTL    |     5 |    90 |     2   (0)| 00:00:01 |
                                    |  18 |                  TABLE ACCESS BY INDEX ROWID| PS_TL_RPTD_TIME    |     2 |   344 |     3   (0)| 00:00:01 |
                                    |* 19 |                   INDEX RANGE SCAN          | PS_TL_RPTD_TIME    |     1 |       |     2   (0)| 00:00:01 |
                                    |  20 |                 TABLE ACCESS BY INDEX ROWID | PS_NAMES           |     1 |    37 |     2   (0)| 00:00:01 |
                                    |* 21 |                  INDEX RANGE SCAN           | PS_NAMES           |     1 |       |     1   (0)| 00:00:01 |
                                    |  22 |                   SORT AGGREGATE            |                    |     1 |    21 |            |          |
                                    |* 23 |                    INDEX RANGE SCAN         | PS_NAMES           |     1 |    21 |     2   (0)| 00:00:01 |
                                    |  24 |                TABLE ACCESS BY INDEX ROWID  | PS_OI_TL_PRJ_COMNT |     1 |    31 |     1   (0)| 00:00:01 |
                                    |* 25 |                 INDEX UNIQUE SCAN           | PS_OI_TL_PRJ_COMNT |     1 |       |     0   (0)| 00:00:01 |
                                    |* 26 |               INDEX RANGE SCAN              | PS_TL_TRC_TBL      |     1 |    14 |     1   (0)| 00:00:01 |
                                    |  27 |                SORT AGGREGATE               |                    |     1 |    14 |            |          |
                                    
                                    PLAN_TABLE_OUTPUT
                                    -----------------------------------------------------------------------------------------------------------------------------
                                    |* 28 |                 INDEX RANGE SCAN            | PS_TL_TRC_TBL      |     1 |    14 |     2   (0)| 00:00:01 |
                                    |  29 |              TABLE ACCESS BY INDEX ROWID    | PS_PROJECT         |     1 |    46 |     1   (0)| 00:00:01 |
                                    |* 30 |               INDEX UNIQUE SCAN             | PS_PROJECT         |     1 |       |     0   (0)| 00:00:01 |
                                    |  31 |             TABLE ACCESS BY INDEX ROWID     | PS_OI_BUS_TITLE    |     7 |   273 |     3   (0)| 00:00:01 |
                                    |* 32 |              INDEX RANGE SCAN               | PS_OI_BUS_TITLE    |     1 |       |     2   (0)| 00:00:01 |
                                    |  33 |            TABLE ACCESS BY INDEX ROWID      | PS_JOB             |     1 |    58 |     2   (0)| 00:00:01 |
                                    |* 34 |             INDEX UNIQUE SCAN               | PS_JOB             |     1 |       |     1   (0)| 00:00:01 |
                                    |  35 |              SORT AGGREGATE                 |                    |     1 |    20 |            |          |
                                    |* 36 |               INDEX RANGE SCAN              | PSAJOB             |     1 |    20 |     3   (0)| 00:00:01 |
                                    |  37 |              SORT AGGREGATE                 |                    |     1 |    23 |            |          |
                                    |* 38 |               INDEX RANGE SCAN              | PSAJOB             |     1 |    23 |     3   (0)| 00:00:01 |
                                    
                                    PLAN_TABLE_OUTPUT
                                    -----------------------------------------------------------------------------------------------------------------------------
                                    |* 39 |           INDEX RANGE SCAN                  | PSASET_CNTRL_REC   |     1 |    27 |     2   (0)| 00:00:01 |
                                    |* 40 |          INDEX RANGE SCAN                   | PS_DEPT_TBL        |     1 |    20 |     1   (0)| 00:00:01 |
                                    |  41 |           SORT AGGREGATE                    |                    |     1 |    20 |            |          |
                                    |* 42 |            INDEX RANGE SCAN                 | PS_DEPT_TBL        |     1 |    20 |     2   (0)| 00:00:01 |
                                    |* 43 |         INDEX UNIQUE SCAN                   | PS_PERSONAL_DATA   |     1 |     9 |     0   (0)| 00:00:01 |
                                    |* 44 |        INDEX UNIQUE SCAN                    | PS_PERSONAL_DATA   |     1 |       |     0   (0)| 00:00:01 |
                                    |  45 |       TABLE ACCESS BY INDEX ROWID           | PS_PERSONAL_DATA   |     1 |    27 |     1   (0)| 00:00:01 |
                                    1 2 Previous Next