Home » RDBMS Server » Performance Tuning » Query running slow (oracle,10202,AIX)
Query running slow [message #330898] Tue, 01 July 2008 13:22 Go to next message
areeb
Messages: 9
Registered: February 2008
Location: NJ
Junior Member
Hi Guys,
I have to tune up the below query, can anybody please help me out with this.stats on the tables are upto date
SELECT j.cd_activity_minor, j.desc_activity_minor desc_activity_minor,

       j.started_on, j.started_by, j.id_case, j.id_member,

       spkg_common_gets.sf_get_member_name(j.id_member) cp_ncp_name,

       j.id_worker_update, j.cd_status, j.seq_order, j.seq_major_int,

       j.seq_minor_int, j.id_forum, j.id_topic,

       DECODE('A', 'I', 10 - j.days_overdue, j.days_overdue) days_overdue,

       j.dt_due, j.seq_txn_event, j.cd_activity_major,

       (SELECT desc_activity

          FROM vamjr b

         WHERE b.cd_activity_major = j.cd_activity_major

           AND b.dt_end_validity = '31-DEC-9999') desc_activity_major,

       j.id_othp_source, j.cd_type_othp_source, j.cd_subsystem, j.row_count

  FROM (SELECT z.cd_activity_minor cd_activity_minor,

               z.desc_activity_minor desc_activity_minor,

               z.started_on started_on, z.started_by started_by,

               z.id_case id_case, z.id_member id_member,

               z.id_worker_update id_worker_update, z.cd_status cd_status,

               z.seq_order seq_order, z.seq_major_int seq_major_int,

               z.seq_minor_int seq_minor_int, z.id_forum id_forum,

               z.id_topic id_topic, z.days_overdue days_overdue,

               z.dt_due dt_due, z.seq_txn_event seq_txn_event,

               z.cd_activity_major cd_activity_major,

               z.id_othp_source id_othp_source,

               z.cd_type_othp_source cd_type_othp_source,

               z.cd_subsystem cd_subsystem, z.id_docket id_docket, ROWNUM rnm,

               row_count

          FROM (SELECT a.cd_activity_minor cd_activity_minor,

                       a.desc_activity_minor desc_activity_minor,

                       a.started_on started_on, a.started_by started_by,

                       a.id_case id_case, a.id_member id_member,

                       a.id_worker_update id_worker_update,

                       a.cd_status cd_status, a.seq_order seq_order,

                       a.seq_major_int seq_major_int,

                       a.seq_minor_int seq_minor_int, a.id_forum id_forum,

                       a.id_topic id_topic, a.days_overdue days_overdue,

                       a.dt_due dt_due, a.seq_txn_event seq_txn_event,

                       a.cd_activity_major cd_activity_major,

                       a.id_othp_source id_othp_source,

                       a.cd_type_othp_source cd_type_othp_source,

                       a.cd_subsystem cd_subsystem, a.id_docket id_docket,

                       COUNT(1) OVER() row_count

                  FROM (SELECT   d.cd_activity_minor cd_activity_minor,

                                 a.desc_activity desc_activity_minor,

                                 TRUNC(d.dt_entered) started_on,

                                 d.id_worker_update started_by,

                                 d.id_case id_case,

                                 spkg_wrkl.sf_get_member_by_role

                                                  ('XYZ',

                                                   d.id_case

                                                  ) id_member,

                                 c.id_docket id_docket,

                                 d.id_worker_update id_worker_update,

                                 d.cd_status cd_status, d.seq_order seq_order,

                                 d.seq_major_int seq_major_int,

                                 d.seq_minor_int seq_minor_int,

                                 d.id_forum id_forum, d.id_topic id_topic,

                                 CASE

                                    WHEN SIGN

                                            (  TO_DATE('30-JUN-08')

                                             - TO_DATE(d.dt_due)

                                            ) = -1 THEN NULL

                                    WHEN SIGN(  TO_DATE('30-JUN-08')

                                              - TO_DATE(d.dt_due)

                                             ) = 0 THEN NULL

                                    ELSE ROUND((  TO_DATE('30-JUN-08')

                                                - TO_DATE(d.dt_due)

                                               )

                                              )

                                 END days_overdue,

                                 d.dt_due dt_due, d.seq_txn_event seq_txn_event,

                                 j.cd_activity_major cd_activity_major,

                                 j.id_othp_source id_othp_source,

                                 j.cd_type_othp_source cd_type_othp_source,

                                 j.cd_subsystem cd_subsystem

                            FROM vdmnr d, vdmjr j, vcase c, vamnr a

                           WHERE d.id_case = j.id_case

                             AND d.seq_order = j.seq_order

                             AND d.seq_major_int = j.seq_major_int

                             AND a.cd_activity_minor = d.cd_activity_minor

                             AND d.id_case = c.id_case

                             AND d.dt_alert_prior <= '30-JUN-2008'

                             AND d.cd_status = 'STRT'

                             AND a.ind_action_alert = 'A'

                             AND d.dt_entered BETWEEN '01-JAN-2008'

                                                  AND   TO_DATE('01-AUG-2008',

                                                                'DD-MON-YYYY'

                                                               )

                                                      + 0.9999

                             AND (    d.id_worker_delegate != ' '

                                  AND d.id_worker_delegate IN(

                                         SELECT 'XYZ'

                                           FROM DUAL

                                         UNION ALL

                                         SELECT id_worker

                                           FROM vusrl

                                          WHERE id_worker_sub =

                                                               'XYZ'

                                            AND dt_effective <= '30-JUN-2008'

                                            AND dt_expire >= '30-JUN-08'

                                            AND dt_end_validity = '31-DEC-9999')

                                 )

                        UNION ALL

                        SELECT   d.cd_activity_minor cd_activity_minor,

                                 a.desc_activity desc_activity_minor,

                                 TRUNC(d.dt_entered) started_on,

                                 d.id_worker_update started_by,

                                 d.id_case id_case,

                                 spkg_wrkl.sf_get_member_by_role

                                                  ('XYZ',

                                                   d.id_case

                                                  ) id_member,

                                 c.id_docket id_docket,

                                 d.id_worker_update id_worker_update,

                                 d.cd_status cd_status, d.seq_order seq_order,

                                 d.seq_major_int seq_major_int,

                                 d.seq_minor_int seq_minor_int,

                                 d.id_forum id_forum, d.id_topic id_topic,

                                 CASE

                                    WHEN SIGN

                                            (  TO_DATE('30-JUN-08')

                                             - TO_DATE(d.dt_due)

                                            ) = -1 THEN NULL

                                    WHEN SIGN(  TO_DATE('30-JUN-08')

                                              - TO_DATE(d.dt_due)

                                             ) = 0 THEN NULL

                                    ELSE ROUND((  TO_DATE('30-JUN-08')

                                                - TO_DATE(d.dt_due)

                                               )

                                              )

                                 END days_overdue,

                                 d.dt_due dt_due, d.seq_txn_event seq_txn_event,

                                 j.cd_activity_major cd_activity_major,

                                 j.id_othp_source id_othp_source,

                                 j.cd_type_othp_source cd_type_othp_source,

                                 j.cd_subsystem cd_subsystem

                            FROM vdmnr d, vdmjr j, vcase c, vamnr a

                           WHERE d.id_case = j.id_case

                             AND d.seq_order = j.seq_order

                             AND d.seq_major_int = j.seq_major_int

                             AND a.cd_activity_minor = d.cd_activity_minor

                             AND d.id_case = c.id_case

                             AND d.dt_alert_prior <= '30-JUN-2008'

                             AND d.cd_status = 'STRT'

                             AND a.ind_action_alert = 'A'

                             AND d.dt_entered BETWEEN '01-JAN-2008'

                                                  AND   TO_DATE('01-AUG-2008',

                                                                'DD-MON-YYYY'

                                                               )

                                                      + 0.9999

                             AND (    d.id_worker_delegate = ' '

                                  AND d.id_case IN(

                                         SELECT id_case

                                           FROM vcwrk

                                          WHERE id_worker IN(

                                                   SELECT 'XYZ'

                                                     FROM DUAL

                                                   UNION ALL

                                                   SELECT id_worker

                                                     FROM vusrl

                                                    WHERE id_worker_sub =

                                                               'XYZ'

                                                      AND dt_effective <=

                                                                   '30-JUN-2008'

                                                      AND dt_expire >=

                                                                   '30-JUN-2008'

                                                      AND dt_end_validity =

                                                                   '31-DEC-9999')

                                            AND dt_effective <= '30-JUN-2008'

                                            AND dt_expire >= '30-JUN-2008'

                                            AND dt_end_validity = '31-DEC-9999')

                                 )

                        ORDER BY dt_due DESC, id_case, seq_order, seq_major_int,

                                 seq_minor_int) a) z

         WHERE ((1 = 0) OR(ROWNUM <= 10))) j

 WHERE rnm >= 1



Explain Plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 2666051352

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

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

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

|   0 | SELECT STATEMENT                |                      |    10 | 22380 |
       | 27987   (2)| 00:05:36 |

|*  1 |  TABLE ACCESS BY INDEX ROWID    | REF_MAJOR_ACTIVITY   |     1 |    39 |
       |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN              | PK_VAMJR             |     1 |       |
       |     1   (0)| 00:00:01 |

|*  3 |  VIEW                           |                      |    10 | 22380 |
       | 27987   (2)| 00:05:36 |

|*  4 |   COUNT STOPKEY                 |                      |       |       |
       |            |          |

|   5 |    VIEW                         |                      | 61920 |   131M|
       | 27987   (2)| 00:05:36 |

|   6 |     WINDOW BUFFER               |                      | 61920 |   130M|
       | 27987   (2)| 00:05:36 |

|   7 |      VIEW                       |                      | 61920 |   130M|
       | 27987   (2)| 00:05:36 |

|   8 |       SORT ORDER BY             |                      | 61920 |    12M|
    26M| 27987   (2)| 00:05:36 |

|*  9 |        HASH JOIN                |                      | 61920 |    12M|
       | 25214   (3)| 00:05:03 |

|* 10 |         TABLE ACCESS FULL       | REF_MINOR_ACTIVITY   |   564 | 25944 |
       |     5   (0)| 00:00:01 |

|* 11 |         HASH JOIN               |                      | 94311 |    14M|
    12M| 25208   (3)| 00:05:03 |

|* 12 |          HASH JOIN              |                      | 94557 |    11M|
     9M| 17578   (3)| 00:03:31 |

|* 13 |           HASH JOIN RIGHT SEMI  |                      | 94557 |  8957K|
       | 12673   (3)| 00:02:33 |

|  14 |            VIEW                 | VW_NSO_2             | 30175 |   353K|
       |  1217   (4)| 00:00:15 |

|* 15 |             HASH JOIN           |                      | 30175 |  1738K|
       |  1217   (4)| 00:00:15 |

|  16 |              VIEW               | VW_NSO_1             |    32 |   544 |
       |     6   (0)| 00:00:01 |

|  17 |               UNION-ALL         |                      |       |       |
       |            |          |

|  18 |                FAST DUAL        |                      |     1 |       |
       |     2   (0)| 00:00:01 |

|* 19 |                TABLE ACCESS FULL| USER_OFFICE_ROLES    |    31 |  1147 |
       |     4   (0)| 00:00:01 |

|* 20 |              TABLE ACCESS FULL  | CASE_WORKER          |   532K|    21M|
       |  1205   (3)| 00:00:15 |

|* 21 |            TABLE ACCESS FULL    | MINOR_ACTIVITY_DIARY |   908K|    73M|
       | 11445   (3)| 00:02:18 |

|  22 |           TABLE ACCESS FULL     | CASE_DETAILS         |   531K|    14M|
       |  3175   (3)| 00:00:39 |

|  23 |          TABLE ACCESS FULL      | MAJOR_ACTIVITY_DIARY |  1231K|    39M|
       |  4308   (2)| 00:00:52 |

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


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

   1 - filter("B"."DT_END_VALIDITY"=TO_DATE('9999-12-31 00:00:00', 'yyyy-mm-dd h
h24:mi:ss'))

   2 - access("B"."CD_ACTIVITY_MAJOR"=:B1)
   3 - filter("RNM">=1)
   4 - filter(ROWNUM<=10)
   9 - access("A"."CD_ACTIVITY_MINOR"="D"."CD_ACTIVITY_MINOR")
  10 - filter("A"."IND_ACTION_ALERT"='A')
  11 - access("D"."ID_CASE"="J"."ID_CASE" AND "D"."SEQ_ORDER"="J"."SEQ_ORDER" AN
D

              "D"."SEQ_MAJOR_INT"="J"."SEQ_MAJOR_INT")
  12 - access("D"."ID_CASE"="C"."ID_CASE")
  13 - access("D"."ID_CASE"="$nso_col_1")
  15 - access("ID_WORKER"="$nso_col_1")
  19 - filter("ID_WORKER_SUB"='XYZ' AND "DT_END_VALIDITY"=TO_DATE('9
999-12-31 00:00:00',

              'yyyy-mm-dd hh24:mi:ss') AND "DT_EXPIRE">=TO_DATE('2008-06-30 00:0
0:00', 'yyyy-mm-dd hh24:mi:ss') AND

              "DT_EFFECTIVE"<=TO_DATE('2008-06-30 00:00:00', 'yyyy-mm-dd hh24:mi
:ss'))

  20 - filter("DT_END_VALIDITY"=TO_DATE('9999-12-31 00:00:00', 'yyyy-mm-dd hh24:
mi:ss') AND

              "DT_EFFECTIVE"<=TO_DATE('2008-06-30 00:00:00', 'yyyy-mm-dd hh24:mi
:ss') AND

              "DT_EXPIRE">=TO_DATE('2008-06-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss
'))

  21 - filter("D"."CD_STATUS"='STRT' AND "D"."DT_ENTERED">=TO_DATE('2008-01-01 0
0:00:00', 'yyyy-mm-dd

              hh24:mi:ss') AND "D"."DT_ALERT_PRIOR"<=TO_DATE('2008-06-30 00:00:0
0', 'yyyy-mm-dd hh24:mi:ss') AND

              "D"."DT_ENTERED"<=TO_DATE('2008-08-01 23:59:51', 'yyyy-mm-dd hh24:
mi:ss') AND

              "D"."ID_WORKER_DELEGATE"=' ')


Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
     112903  consistent gets
      61325  physical reads
          0  redo size
       1527  bytes sent via SQL*Net to client
       5744  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          3  rows processed
Any suggestion would be helpful.
Re: Query running slow [message #330902 is a reply to message #330898] Tue, 01 July 2008 13:39 Go to previous message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
I have some general comments.
The extra blank lines make this DIFFICULT to read.

(ab)using "TO_DATE('30-JUN-08')" without a mask is a disaster waiting to happen.

>AND d.dt_alert_prior <= '30-JUN-2008' comparing a date with a string is a disaster waiting to happen.

bizarre & useless SQL below
AND d.id_worker_delegate IN(
SELECT 'XYZ' FROM DUAL 
UNION ALL
SELECT id_worker

what/why SELECT 'XYZ' FROM DUAL UNION ?????

[Updated on: Tue, 01 July 2008 13:41] by Moderator

Report message to a moderator

Previous Topic: multiple dbwr processes and multiple archver process
Next Topic: free command
Goto Forum:
  


Current Time: Mon Dec 05 11:20:26 CST 2016

Total time taken to generate the page: 0.04801 seconds