Home » RDBMS Server » Performance Tuning » Performance tuning (Oracle 11g)
Performance tuning [message #649550] Wed, 30 March 2016 14:41 Go to next message
lappi
Messages: 8
Registered: March 2016
Location: New york
Junior Member
  SELECT A.ACCTNO,
    A.IUBC,
    MIN (A.ACCT_DT),
    NULL AS RPT_ACCT_NO,
    A.SID,
    A.FTC,
    CASE
      WHEN MAX (C.FTC_GEN_FL) = 'N'
      THEN SUM (
        CASE
          WHEN A.ACCT_DT = ACTRL.PRR_VAL_EFF_DT
          THEN A.TRANS_AMT
          ELSE 0
        END)
      ELSE 0
    END AS O_BALANCES,
    CASE
      WHEN MAX (C.FTC_GEN_FL) = 'N'
      THEN SUM (
        CASE
          WHEN A.ACCT_DT = TEMP.END_DATE
          THEN A.TRANS_AMT
          ELSE 0
        END)
      ELSE 0
    END                             AS CL_BL,
  
    MAX (B.OFS)    AS F_SH_OUT,
      MAX (B.OPEN_NAV)                AS NAV,
    CASE
      WHEN MAX (C.FTC_GEN_FL) = 'N'
      THEN ( SUM (
        CASE
          WHEN A.ACCT_DT = TEMP.END_DATE
          THEN A.TRANS_AMT
          ELSE 0.00
        END) - SUM (
        CASE
          WHEN A.ACCT_DT = ACTRL.PRR_VAL_EFF_DT
          THEN A.TRANS_AMT
          ELSE 0.00
        END))
      WHEN MAX (C.FTC_GEN_FL) = 'Y'
      THEN SUM (
        CASE
          WHEN A.ACCT_DT >= TEMP.STRT_DT
          THEN A.TRANS_AMT
          ELSE 0
        END)
    END                    AS NET_ACTIVITY,
     'BALANCE' AS TP,
    4                      AS SRT,
    MAX (TEMP.RPT_ACCT_NO) AS TRPT_ACCT_NO,
    MAX (TEMP.STRT_DT)    AS TSTRT_DT,
      MAX (TEMP.STR_COL5)    AS TTRAN_TYPE,
    MAX (C.RPT_CAT_LVL_1_NM),
    MAX (C.RPT_CAT_LVL_2_NM),
    MAX (C.BS_SORT),
    MAX (C.FD_TRANS_RPT_NM_1),
    MAX (C.RPT_CAT_LVL_3_NM),
    MAX (C.FD_TRANS_RPT_NM_2),
    MAX (C.NAV_SORT)
  FROM TRANSACTIONS A
  JOIN (	select '5037' ACCTNO , '5037'  UAN,  'C5'  BC, 
TO_DATE('2015-03-01','YYYY-MM-DD')  STRT_DT, TO_DATE('2015-03-31','YYYY-MM-DD')  END_DATE from dual
select '5037'  IAN RPT_ACCT_NO from dual) TEMP
  ON A.IUBC = TEMP.BC
  AND A.ACCTNO     = TEMP.UAN
  JOIN CATEGORY C
  ON A.CODE         = C.FTC
  AND C.FD_TRANS_RPT_NM     = 'ALL'
  AND C.IUBC = 'XB'
  JOIN
    (SELECT TRANS.IUBC,
      TRANS.ACCT_DT,
      TRANS.ACCTNO,
      TRANS.SID,
            SUM (
      CASE
        WHEN TRANS.CODE     = 'SO'
        AND TRANS.ACCT_DT = ACTRL.PRR_VAL_EFF_DT
        THEN TRANS.TRANS_AMT
        ELSE 0
      END) AS OFS,
      SUM (
      CASE
        WHEN TRANS.CODE     = 'NV'
        AND TRANS.ACCT_DT = ACTRL.PRR_VAL_EFF_DT
        THEN TRANS.TRANS_AMT
        ELSE 0
      END) AS OPEN_NAV,
        FROM TRANSACTIONS TRANS,
      (	select '5037' ACCTNO , '5037'  UAN,  'C5'  BC, 
TO_DATE('2015-03-01','YYYY-MM-DD')  STRT_DT, TO_DATE('2015-03-31','YYYY-MM-DD')  END_DATE from dual
select '5037'  IAN RPT_ACCT_NO from dual) TEMP,
      ACCT_CTRL ACTRL
    WHERE TRANS.IUBC = BC
    AND TRANS.ACCT_DT     = ACTRL.PRR_VAL_EFF_DT
    AND TRANS.ACCTNO        = TEMP.UAN
    AND TRANS.CODE         IN ('AB', 'SO', 'NV', 'N8', 'UO')
    AND TRANS.SID             < TEMP.STR_COL4
    AND ACTRL.IUBC  = TRANS.IUBC
    AND ACTRL.ACCTNO       = TRANS.ACCTNO
    AND ACTRL.AEFFDT     = TEMP.STRT_DT
    GROUP BY TRANS.IUBC,
      TRANS.ACCT_DT,
      TRANS.ACCTNO,
      TRANS.SID
    ) B
  ON A.IUBC = B.IUBC
  AND B.ACCTNO     = A.ACCTNO
  AND A.SID          = B.SID
  JOIN ACCT_CTRL ACTRL
  ON ACTRL.IUBC = A.IUBC
  AND A.ACCT_DT BETWEEN ACTRL.PRR_VAL_EFF_DT AND TEMP.END_DATE
  AND ACTRL.ACCTNO   = A.ACCTNO
  AND ACTRL.AEFFDT = TEMP.STRT_DT
  JOIN
    (SELECT ACCTNO,
      MIN (CTRL.AEFFDT) AS AEFFDT
    FROM ACCT_CTRL CTRL
    JOIN (	select '5037' ACCTNO , '5037'  UAN,  'C5'  BC, 
TO_DATE('2015-03-01','YYYY-MM-DD')  STRT_DT, TO_DATE('2015-03-31','YYYY-MM-DD')  END_DATE from dual
select '5037'  IAN RPT_ACCT_NO from dual) TEMP
    ON CTRL.AEFFDT     = TEMP.STRT_DT
    AND CTRL.ACCTNO      = TEMP.UAN
    AND CTRL.IUBC = BC
    GROUP BY ACCTNO
    ) CAT ON ACTRL.ACCTNO = CAT.ACCTNO
  AND ACTRL.AEFFDT        = CAT.AEFFDT
  GROUP BY A.IUBC,
    A.ACCTNO,
    A.SID,
    A.FTC


in this table Transactions has billions of rows and is partitioned.first part A of this query has only 150000 rows,but when it is combined with query B its very slow,can you suggest how to combine A and B in single query to see if perforamnce improves,it shd complete in atleast few secs but now its taking 4 hrs for this count.Table TRANSACTIONS she be used only once,thats wht my thought but unable to come up with better query to speed up.Is it possible to write in single step with partition by for this query.


*BlackSwan added {code} tags. Please do so yourself in the future.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

[Updated on: Wed, 30 March 2016 15:13] by Moderator

Report message to a moderator

Re: Performance tuning [message #649551 is a reply to message #649550] Wed, 30 March 2016 14:47 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Performance tuning [message #649552 is a reply to message #649551] Wed, 30 March 2016 14:51 Go to previous messageGo to next message
lappi
Messages: 8
Registered: March 2016
Location: New york
Junior Member
there is nothing shown in explain plan and indexes and partitions are used correctly.
Re: Performance tuning [message #649553 is a reply to message #649552] Wed, 30 March 2016 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
lappi wrote on Wed, 30 March 2016 12:51
there is nothing shown in explain plan and indexes and partitions are used correctly.


If you you choose to NOT actually post the requested details, then the mystery remains & you likely won't obtain any answers from here.
Re: Performance tuning [message #649554 is a reply to message #649553] Wed, 30 March 2016 15:19 Go to previous messageGo to next message
lappi
Messages: 8
Registered: March 2016
Location: New york
Junior Member
we dont have access to get explain plan etc,but when verified with DBA his suggestion is to simplify query by reducing joins.
Re: Performance tuning [message #649555 is a reply to message #649554] Wed, 30 March 2016 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
lappi wrote on Wed, 30 March 2016 13:19
we dont have access to get explain plan etc,but when verified with DBA his suggestion is to simplify query by reducing joins.


Both you & DBA are free to do what ever you desire or deem appropriate.

We don't have your tables or data.
Therefore we can't run, test, modify or improve posted SQL.

You're On Your Own (YOYO)!
Re: Performance tuning [message #649565 is a reply to message #649555] Thu, 31 March 2016 02:57 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
There is no way this:
  FROM TRANSACTIONS A
  JOIN (	select '5037' ACCTNO , '5037'  UAN,  'C5'  BC, 
TO_DATE('2015-03-01','YYYY-MM-DD')  STRT_DT, TO_DATE('2015-03-31','YYYY-MM-DD')  END_DATE from dual
select '5037'  IAN RPT_ACCT_NO from dual) TEMP

is valid SQL.

Post the real query, along with the additional information BS asked for.
Re: Performance tuning [message #649574 is a reply to message #649554] Thu, 31 March 2016 04:20 Go to previous message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
lappi wrote on Wed, 30 March 2016 21:19
we dont have access to get explain plan etc,but when verified with DBA his suggestion is to simplify query by reducing joins.

Then how are you able to make the statement:
Quote:
there is nothing shown in explain plan and indexes and partitions are used correctly.

[Updated on: Thu, 31 March 2016 04:21]

Report message to a moderator

Previous Topic: Clustering Factor for LOB Index_Type is NULL
Next Topic: stale (local) index statistics on partitioned table
Goto Forum:
  


Current Time: Sat Feb 24 17:28:29 CST 2018

Total time taken to generate the page: 0.01025 seconds