[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Query tuning help needed

Posted on 2012-03-17
9
Medium Priority
?
402 Views
Last Modified: 2012-04-17
Hi,

Query :
SELECT   a.acct_nr, d.cmpgn_yr, d.cmpgn_nr, a.ord_id, b.vol_nr, b.fnsh_stk_cd, fcr.rordr_shrt_rqst_strt_ts, b.prod_qty, a.zone_cd, a.colctn_typ,
trunc(d.inspctn_dt) LAST_MVMNT_DT, d.in68_cd, a.wrhs_cd,a.FSCL_NTE_DT,
(SELECT cmpgn_nr FROM (SELECT   vcc.cmpgn_nr FROM sir_vw_cmpgn_calndr vcc WHERE (TRUNC (SYSDATE) BETWEEN vcc.init_dt AND vcc.end_dt ) ORDER BY vcc.cmpgn_nr)WHERE ROWNUM = 1) currnt_cmpgn_nr,
(SELECT cmpgn_yr FROM (SELECT   vcc.cmpgn_yr FROM sir_vw_cmpgn_calndr vcc WHERE (TRUNC (SYSDATE) BETWEEN vcc.init_dt AND vcc.end_dt ) ORDER BY vcc.cmpgn_nr)WHERE ROWNUM = 1) currnt_cmpgn_yr,
 a.julian_dt, fcr.actn_desc, fcr.fsc_desc
FROM sir_invc a, sir_carr c, sir_invc_item b, sir_mnfst d , SIR_FSC_CNCLTN_RQST FCR
WHERE a.julian_dt = 58
AND a.ord_id =  120320
AND b.vol_nr = 001
AND a.wrhs_cd = c.wrhs_cd
AND a.acct_nr = b.acct_nr
AND a.cmpgn_yr = b.cmpgn_yr
AND a.cmpgn_nr = b.cmpgn_nr
AND a.ord_id = b.ord_id
AND a.mnfst_nr = d.mnfst_nr
AND b.fnsh_stk_cd = FCR.fnsh_stk_cd
AND FCR.WORK_DT = trunc(sysdate)
AND FCR.RORDR_SHRT_RQST_END_TS is null
ORDER BY b.fnsh_stk_cd DESC

Explain Plan :
PLAN_TABLE_OUTPUT

Plan hash value: 2545911630
 
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |     1 |   166 |   173   (3)| 00:00:02 |        |      |
|   1 |  REMOTE                           |                 |       |       |            |          | LCLOD~ | R->S |
|   2 |  REMOTE                           |                 |       |       |            |          | LCLOD~ | R->S |
|   3 |  SORT ORDER BY                    |                 |     1 |   166 |   173   (3)| 00:00:02 |        |      |
|*  4 |   HASH JOIN                       |                 |     1 |   166 |   172   (2)| 00:00:02 |        |      |
|   5 |    NESTED LOOPS                   |                 |     1 |   142 |     7   (0)| 00:00:01 |        |      |
|   6 |     NESTED LOOPS                  |                 |     1 |   138 |     6   (0)| 00:00:01 |        |      |
|   7 |      MERGE JOIN CARTESIAN         |                 |     1 |   109 |     5   (0)| 00:00:01 |        |      |
|*  8 |       TABLE ACCESS FULL           | FSC_CNCLTN_RQST |     1 |    67 |     4   (0)| 00:00:01 |        |      |
|   9 |       BUFFER SORT                 |                 |     1 |    42 |     1   (0)| 00:00:01 |        |      |
|* 10 |        TABLE ACCESS BY INDEX ROWID| INVC            |     1 |    42 |     1   (0)| 00:00:01 |        |      |
|* 11 |         INDEX RANGE SCAN          | IX5_INVC        |     1 |       |     1   (0)| 00:00:01 |        |      |
|  12 |      TABLE ACCESS BY INDEX ROWID  | INVC_ITEM       |     1 |    29 |     1   (0)| 00:00:01 |        |      |
|* 13 |       INDEX UNIQUE SCAN           | PK_INVC_ITEM    |     1 |       |     1   (0)| 00:00:01 |        |      |
|* 14 |     INDEX UNIQUE SCAN             | PK_CARR         |     1 |     4 |     1   (0)| 00:00:01 |        |      |
|  15 |    TABLE ACCESS FULL              | MNFST           | 45708 |  1071K|   165   (2)| 00:00:02 |        |      |
---------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."MNFST_NR"="D"."MNFST_NR")
   8 - filter("FCR"."WORK_DT"=TRUNC(SYSDATE@!) AND "FCR"."RORDR_SHRT_RQST_END_TS" IS NULL)
  10 - filter("A"."MNFST_NR" IS NOT NULL)
  11 - access("A"."ORD_ID"=120320 AND "A"."JULIAN_DT"=58)
  13 - access("A"."ACCT_NR"="B"."ACCT_NR" AND "A"."CMPGN_YR"="B"."CMPGN_YR" AND
              "A"."CMPGN_NR"="B"."CMPGN_NR" AND "B"."ORD_ID"=120320 AND "B"."VOL_NR"=001 AND
              "B"."FNSH_STK_CD"=TO_NUMBER("FCR"."FNSH_STK_CD"))
  14 - access("A"."WRHS_CD"="C"."WRHS_CD")
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   1 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A1"."CMPGN_NR" FROM  (SELECT "A2"."OPERTNL_CMPGN_NR"
       "CMPGN_NR" FROM "OW_ODSQ"."OPERTNL_CMPGN" "A2" WHERE "A2"."OPERTNL_CMPGN_STRT_DT"<=TRUNC(SYSDATE@!) AND
       "A2"."OPERTNL_CMPGN_END_DT">=TRUNC(SYSDATE@!) AND "A2"."OPERTNL_CMPGN_NR"<>20 ORDER BY
       "A2"."OPERTNL_CMPGN_NR") "A1" WHERE ROWNUM=1 (accessing 'LCLODSQ_SIRQX2' )
 
   2 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A1"."CMPGN_YR" FROM  (SELECT "A2"."OPERTNL_CMPGN_YR_NR"
       "CMPGN_YR" FROM "OW_ODSQ"."OPERTNL_CMPGN" "A2" WHERE "A2"."OPERTNL_CMPGN_STRT_DT"<=TRUNC(SYSDATE@!) AND
       "A2"."OPERTNL_CMPGN_END_DT">=TRUNC(SYSDATE@!) AND "A2"."OPERTNL_CMPGN_NR"<>20 ORDER BY
       "A2"."OPERTNL_CMPGN_NR") "A1" WHERE ROWNUM=1 (accessing 'LCLODSQ_SIRQX2' )

Please help me what can be done to make this faster.

Best Regards..
0
Comment
Question by:neoarwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
9 Comments
 
LVL 23

Expert Comment

by:David
ID: 37733612
What can be done includes statement hints, indexes, table statistics, system load, partitioned tables, etc.  The first (real) issue that catches my eye is your Cartesian Join (full table scan).  Very, very bad for performance, see http://www.dba-oracle.com/t_merge_join_cartesian.htm et.al.

TOAD, and perhaps the latest version of SQL*Developer, have ways to suggest code rewrites for better utilization.  But the bottom line is if you reading all rows, find a way to cut down the number of rows required (purge, partition).
0
 
LVL 11

Accepted Solution

by:
yuching earned 2000 total points
ID: 37736245
Can you post the index for each table so that we can have a better analysis?

Make some changes in query to
- stop querying table sir_vw_cmpgn_calndr  each time
- change in the query to use ANSI join for better readibility

Could you please check the performance

SELECT   a.acct_nr, d.cmpgn_yr, d.cmpgn_nr, a.ord_id, b.vol_nr, b.fnsh_stk_cd,
fcr.rordr_shrt_rqst_strt_ts, b.prod_qty, a.zone_cd, a.colctn_typ,
trunc(d.inspctn_dt) LAST_MVMNT_DT, d.in68_cd, a.wrhs_cd,a.FSCL_NTE_DT,
e.currnt_cmpgn_nr,
e.currnt_cmpgn_yr,
 a.julian_dt, fcr.actn_desc, fcr.fsc_desc
FROM sir_invc a
INNER JOIN sir_carr c ON a.wrhs_cd = c.wrhs_cd
INNER JOIN sir_invc_item b ON a.acct_nr = b.acct_nr AND a.cmpgn_yr = b.cmpgn_yr
      AND a.cmpgn_nr = b.cmpgn_nr AND a.ord_id = b.ord_id
INNER JOIN sir_mnfst d ON a.mnfst_nr = d.mnfst_nr
INNER JOIN SIR_FSC_CNCLTN_RQST FCR ON b.fnsh_stk_cd = FCR.fnsh_stk_cd
INNER JOIN (
      SELECT Min(vcc.cmpgn_nr) AS currnt_cmpgn_nr,
            Min(vcc.cmpgn_yr) AS currnt_cmpgn_yr
      FROM sir_vw_cmpgn_calndr vcc
      WHERE (TRUNC (SYSDATE) BETWEEN vcc.init_dt AND vcc.end_dt
      
) e ON 1=1 -- Join to produce one record per row
WHERE a.julian_dt = 58
AND a.ord_id =  120320
AND b.vol_nr = 001
AND FCR.WORK_DT = trunc(sysdate)
AND FCR.RORDR_SHRT_RQST_END_TS is null
ORDER BY b.fnsh_stk_cd DESC
0
 

Author Comment

by:neoarwin
ID: 37762763
Hi, Based on your suggestion , i went for the change in query and due to some functionality changes, i had to drop two tables already used in query and below is my latest query.

SELECT   a.acct_nr, a.ord_id, b.vol_nr, b.fnsh_stk_cd,
         fcr.rordr_shrt_rqst_strt_ts, b.prod_qty, a.zone_cd, a.colctn_typ,
         a.wrhs_cd, a.fscl_nte_dt, e.currnt_cmpgn_nr, e.currnt_cmpgn_yr,
         a.julian_dt, fcr.actn_desc, fcr.fsc_desc
    FROM sir_invc a INNER JOIN sir_invc_item b
         ON a.acct_nr = b.acct_nr
       AND a.cmpgn_yr = b.cmpgn_yr
       AND a.cmpgn_nr = b.cmpgn_nr
       AND a.ord_id = b.ord_id
         INNER JOIN
         (SELECT   MAX (rordr_shrt_rqst_strt_ts) AS rordr_shrt_rqst_strt_ts,
                   actn_desc, fnsh_stk_cd, fsc_desc, in68_cd
              FROM sir_fsc_cncltn_rqst
             WHERE work_dt = TRUNC (SYSDATE)
               AND rordr_shrt_rqst_end_ts IS NULL
               AND in68_cd = 125
          GROUP BY actn_desc,
                   fsc_desc,
                   fnsh_stk_cd,
                   rordr_shrt_rqst_strt_ts,
                   in68_cd) fcr ON b.fnsh_stk_cd = fcr.fnsh_stk_cd
         INNER JOIN
         (SELECT MIN (vcc.cmpgn_nr) AS currnt_cmpgn_nr,
                 MIN (vcc.cmpgn_yr) AS currnt_cmpgn_yr
            FROM sir_vw_cmpgn_calndr vcc
           WHERE TRUNC (SYSDATE) BETWEEN vcc.init_dt AND vcc.end_dt) e
         ON 1 = 1                    
   WHERE a.julian_dt = 58 AND a.ord_id = 120320 AND b.vol_nr = 001
ORDER BY b.fnsh_stk_cd DESC

Query execution plan :

PLAN_TABLE_OUTPUT

Plan hash value: 3514400056
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |     1 |   165 |    10  (20)| 00:00:01 |        |      |
|   1 |  SORT ORDER BY                    |                    |     1 |   165 |    10  (20)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS                    |                    |     1 |   165 |     9  (12)| 00:00:01 |        |      |
|   3 |    MERGE JOIN CARTESIAN           |                    |     1 |   131 |     8  (13)| 00:00:01 |        |      |
|   4 |     NESTED LOOPS                  |                    |     1 |    60 |     4   (0)| 00:00:01 |        |      |
|   5 |      VIEW                         |                    |     1 |    26 |     3   (0)| 00:00:01 |        |      |
|   6 |       REMOTE                      |                    |       |       |            |          | LCLOD~ | R->S |
|   7 |      TABLE ACCESS BY INDEX ROWID  | INVC               |     1 |    34 |     1   (0)| 00:00:01 |        |      |
|*  8 |       INDEX RANGE SCAN            | IX5_INVC           |     1 |       |     1   (0)| 00:00:01 |        |      |
|   9 |     BUFFER SORT                   |                    |     1 |    71 |     7  (15)| 00:00:01 |        |      |
|  10 |      VIEW                         |                    |     1 |    71 |     4  (25)| 00:00:01 |        |      |
|  11 |       HASH GROUP BY               |                    |     1 |   122 |     4  (25)| 00:00:01 |        |      |
|* 12 |        TABLE ACCESS BY INDEX ROWID| FSC_CNCLTN_RQST    |     1 |   122 |     3   (0)| 00:00:01 |        |      |
|* 13 |         INDEX RANGE SCAN          | PK_FSC_CNCLTN_RQST |   286 |       |     1   (0)| 00:00:01 |        |      |
|  14 |    TABLE ACCESS BY INDEX ROWID    | INVC_ITEM          |     1 |    34 |     1   (0)| 00:00:01 |        |      |
|* 15 |     INDEX UNIQUE SCAN             | PK_INVC_ITEM       |     1 |       |     1   (0)| 00:00:01 |        |      |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access("A"."ORD_ID"=120320 AND "A"."JULIAN_DT"=58)
  12 - filter("RORDR_SHRT_RQST_END_TS" IS NULL AND "WORK_DT"=TRUNC(SYSDATE@!))
  13 - access("IN68_CD"=125)
  15 - access("A"."ACCT_NR"="B"."ACCT_NR" AND "A"."CMPGN_YR"="B"."CMPGN_YR" AND "A"."CMPGN_NR"="B"."CMPGN_NR"
              AND "B"."ORD_ID"=120320 AND "B"."VOL_NR"=001 AND "B"."FNSH_STK_CD"=TO_NUMBER("FCR"."FNSH_STK_CD"))
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   6 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT MIN("A1"."OPERTNL_CMPGN_NR"),MIN("A1"."OPERTNL_CMPGN_YR_NR")
       FROM "OW_ODSD"."OPERTNL_CMPGN" "A1" WHERE "A1"."OPERTNL_CMPGN_STRT_DT"<=TRUNC(SYSDATE@!) AND
       "A1"."OPERTNL_CMPGN_END_DT">=TRUNC(SYSDATE@!) AND "A1"."OPERTNL_CMPGN_NR"<>20 (accessing 'LCLODSD_SIRDX2' )
 
Can we reduce the cost further on this?
Please help me , thanks.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Expert Comment

by:yuching
ID: 37766042
Hi neoarwin, the explain plan looks just fine. All are using index scan.
What's the time to run the query?
Can you also post the number of records in each tables?
0
 

Author Comment

by:neoarwin
ID: 37823137
I've requested that this question be closed as follows:

Accepted answer: 0 points for neoarwin's comment #37762763

for the following reason:

good
0
 
LVL 23

Expert Comment

by:David
ID: 37823138
The author states the solution is based upon experts' suggestions.
0
 
LVL 23

Expert Comment

by:David
ID: 37843532
My objections are that the author did not provide a workable solution to his/her problem, but instead replied "good" in http:#a37823137.  I recommend yu's answer in http:#37736245.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question