This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jan 11, 2013 9:16 AM by Nikolay Savvinov RSS

sql is running very slow

Jitendra-OC Newbie
Currently Being Moderated
   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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Code tags are lowercase:
                                                                                                                                                                                                                                    
  • 4. Re: sql is running very slow
    Jitendra-OC Newbie
    Currently Being Moderated
    Thanks, But Please provide me help in tuning my Query.

    Thanks
    Jitendra
  • 5. Re: sql is running very slow
    moreajays Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    How to attach a file in OTN

    Thanks
    Jitendra
  • 8. Re: sql is running very slow
    moreajays Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points