Home » RDBMS Server » Performance Tuning » Help for tuning the sql Query (11.2.0.1)
Help for tuning the sql Query [message #639742] Wed, 15 July 2015 03:04 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

Sql query

SELECT  TEMP.NAME, TEMP.CODE, TEMP.ID, TEMP.C_ORDBKD, TEMP.C_NS, TEMP.C_COS, TEMP.UM_PCT, nvl(TEMP.Y_C_ORDBKD,0) Y_C_ORDBKD, nvl(TEMP.Y_C_NS,0) Y_C_NS, 
		nvl(TEMP.Y_C_COS,0) Y_C_COS, Case nvl(TEMP.Y_C_NS, 0) when 0 Then 0 Else ((TEMP.Y_C_NS - nvl(TEMP.Y_C_COS, 0))/TEMP.Y_C_NS)*100 End Y_UM_PCT
		,nvl(TEMP.Q_C_ORDBKD,0) Q_C_ORDBKD, nvl(TEMP.Q_C_NS,0) Q_C_NS, nvl(TEMP.Q_C_COS,0) Q_C_COS
		,Case nvl(TEMP.Q_C_NS, 0) when 0 Then 0 Else ((TEMP.Q_C_NS - nvl(TEMP.Q_C_COS, 0))/TEMP.Q_C_NS)*100 End Q_UM_PCT
		,nvl(TEMP.M_C_ORDBKD,0) M_C_ORDBKD, nvl(TEMP.M_C_NS,0) M_C_NS, nvl(TEMP.M_C_COS,0) M_C_COS, 
		Case nvl(TEMP.M_C_NS, 0) when 0 Then 0 Else ((TEMP.M_C_NS - nvl(TEMP.M_C_COS, 0))/TEMP.M_C_NS)*100 End M_UM_PCT
		,PTD_C_ORDBKD, PTD_C_NS, PTD_C_COS, PTD_UM_PCT, PTD_Y_C_ORDBKD, PTD_Y_C_NS, PTD_Y_C_COS, PTD_Y_UM_PCT, PTD_Q_C_ORDBKD, PTD_Q_C_NS
		,PTD_Q_C_COS, PTD_Q_UM_PCT, PTD_M_C_ORDBKD, PTD_M_C_NS, PTD_M_C_COS, PTD_M_UM_PCT, EOM_PDATE
FROM
(	with rd AS
        ( SELECT TRUNC(sysdate, 'MM') slice_end_date, TRUNC(sysdate, 'YYYY') year_start, TRUNC(sysdate, 'Q') qtr_start, TRUNC(sysdate, 'MM') month_start
			FROM dual)
SELECT INV.NAME, INV.CODE, INV.ID, (P_ORDBKD) C_ORDBKD, (P_NS) C_NS, (P_COS) C_COS, ROUND(UM_PCT,2)UM_PCT
		-- EOM_PDATE
		,(select last_day(add_months(sysdate, -1)) from dual) EOM_PDATE
		--Year to Date
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
			from eri_slices s where upper(slice_name)='P_ORDBKD'
			and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month')  AND rd.year_start < s.finish_date
		)Y_C_ORDBKD
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
			from eri_slices s where upper(slice_name)='P_NS'
		and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month') AND rd.year_start < s.finish_date  )Y_C_NS
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
		from eri_slices s where upper(slice_name)='P_COS'
		and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month') AND rd.year_start < s.finish_date )Y_C_COS
		
		--Quarter to Date
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
		from eri_slices s where upper(slice_name)='P_ORDBKD'
		and investment_code=inv.code and START_DATE BETWEEN trunc(trunc(sysdate,'q'),'Q') AND trunc(sysdate,'mm')-1 AND rd.year_start < s.finish_date) Q_C_ORDBKD
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
		from eri_slices s where upper(slice_name)='P_NS'
		and investment_code=inv.code and START_DATE BETWEEN trunc(trunc(sysdate,'q'),'Q') AND trunc(sysdate,'mm')-1 AND rd.year_start < s.finish_date )Q_C_NS
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
		from eri_slices s where upper(slice_name)='P_COS'
		and investment_code=inv.code and START_DATE BETWEEN trunc(trunc(sysdate,'q'),'Q') AND trunc(sysdate,'mm')-1 AND rd.year_start < s.finish_date) Q_C_COS
		
		--Month to Date
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
		from eri_slices s where upper(slice_name)='P_ORDBKD'
		and investment_code=inv.code and START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1 AND rd.year_start < s.finish_date ) M_C_ORDBKD
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
		from eri_slices s where upper(slice_name)='P_NS'
		and investment_code=inv.code and START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1 AND rd.year_start < s.finish_date )M_C_NS
		,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
		from eri_slices s where upper(slice_name)='P_COS'
		and investment_code=inv.code and START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1 AND rd.year_start < s.finish_date  )M_C_COS
		--PTD data todate
		,(P_ORDBKD)PTD_C_ORDBKD, (P_NS )PTD_C_NS, (P_COS )PTD_C_COS, round(UM_PCT,2 )PTD_UM_PCT
		--PTD Year to Date
		,ROUND(FIN.p_ordbk_ytd,2) PTD_Y_C_ORDBKD, ROUND(FIN.p_ns_ytd,2) PTD_Y_C_NS, ROUND(FIN.p_cos_ytd,2) PTD_Y_C_COS, ROUND(FIN.um_pct_ytd,2) PTD_Y_UM_PCT
		--PTD Quarter to Date
		,FIN.p_ordbk_qtd PTD_Q_C_ORDBKD, FIN.p_ns_qtd PTD_Q_C_NS, FIN.p_cos_qtd PTD_Q_C_COS, FIN.um_pct_qtd PTD_Q_UM_PCT
		--PTD Month to Date
		,FIN.p_ordbk_mtd PTD_M_C_ORDBKD, FIN.p_ns_mtd PTD_M_C_NS, FIN.p_cos_mtd PTD_M_C_COS, FIN.um_pct_mtd PTD_M_UM_PCT
FROM rd 
join INV_INVESTMENTS INV on 1=1
JOIN ODF_CA_PROJECT ODFP
ON INV.ID=ODFP.ID
JOIN ODF_CA_PROJFINPROPERTIES FIN
ON ODFP.ID=FIN.ID
WHERE INV.ID = &p_project_id ) TEMP;


Explain plan:

--------------------------------------------------------------------------------------------------------------
Plan hash value: 2746247480
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |     1 |   135 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|*  2 |   FILTER                       |                             |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|   5 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|*  6 |   FILTER                       |                             |       |       |            |          |
|   7 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|   9 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|* 10 |   FILTER                       |                             |       |       |            |          |
|  11 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|  13 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|* 14 |   FILTER                       |                             |       |       |            |          |
|  15 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|  17 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|* 18 |   FILTER                       |                             |       |       |            |          |
|  19 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|  21 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|* 22 |   FILTER                       |                             |       |       |            |          |
|  23 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|* 24 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|  25 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|* 26 |   FILTER                       |                             |       |       |            |          |
|  27 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|* 28 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|  29 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|* 30 |   FILTER                       |                             |       |       |            |          |
|  31 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|* 32 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|  33 |  SORT AGGREGATE                |                             |     1 |    44 |            |          |
|* 34 |   FILTER                       |                             |       |       |            |          |
|  35 |    TABLE ACCESS BY INDEX ROWID | ERI_SLICES                  |     1 |    44 |     7   (0)| 00:00:01 |
|* 36 |     INDEX RANGE SCAN           | ERI_SLICE_TABLE_U01         |     1 |       |     6   (0)| 00:00:01 |
|  37 |  FAST DUAL                     |                             |     1 |       |     2   (0)| 00:00:01 |
|  38 |  NESTED LOOPS                  |                             |     1 |   135 |     5   (0)| 00:00:01 |
|  39 |   NESTED LOOPS                 |                             |     1 |   135 |     3   (0)| 00:00:01 |
|  40 |    NESTED LOOPS                |                             |     1 |    83 |     2   (0)| 00:00:01 |
|  41 |     TABLE ACCESS BY INDEX ROWID| ODF_CA_PROJFINPROPERTIES    |     1 |    77 |     2   (0)| 00:00:01 |
|* 42 |      INDEX UNIQUE SCAN         | ODF_CA_PROJFINPROPERTIES_PK |     1 |       |     1   (0)| 00:00:01 |
|* 43 |     INDEX UNIQUE SCAN          | ODF_CA_PROJECT_PK           |     1 |     6 |     0   (0)| 00:00:01 |
|  44 |    TABLE ACCESS BY INDEX ROWID | INV_INVESTMENTS             |     1 |    52 |     1   (0)| 00:00:01 |
|* 45 |     INDEX UNIQUE SCAN          | INV_INVESTMENTS_PK          |     1 |       |     0   (0)| 00:00:01 |
|  46 |   FAST DUAL                    |                             |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TRUNC(SYSDATE@!,'fmmonth')>TRUNC(SYSDATE@!,'fmyyyy') AND 
              TRUNC(SYSDATE@!,'fmy')<=TRUNC(SYSDATE@!,'fmmonth'))
   4 - access("INVESTMENT_CODE"=:B1 AND "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND 
              "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
       filter(UPPER("SLICE_NAME")='P_ORDBKD' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
   6 - filter(TRUNC(SYSDATE@!,'fmmonth')>TRUNC(SYSDATE@!,'fmyyyy') AND 
              TRUNC(SYSDATE@!,'fmy')<=TRUNC(SYSDATE@!,'fmmonth'))
   8 - access("INVESTMENT_CODE"=:B1 AND "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND 
              "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
       filter(UPPER("SLICE_NAME")='P_NS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
  10 - filter(TRUNC(SYSDATE@!,'fmmonth')>TRUNC(SYSDATE@!,'fmyyyy') AND 
              TRUNC(SYSDATE@!,'fmy')<=TRUNC(SYSDATE@!,'fmmonth'))
  12 - access("INVESTMENT_CODE"=:B1 AND "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND 
              "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
       filter(UPPER("SLICE_NAME")='P_COS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
  14 - filter(TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq')<=TRUNC(SYSDATE@!,'fmmm')-1)
  16 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND 
              "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
       filter(UPPER("SLICE_NAME")='P_ORDBKD' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
  18 - filter(TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq')<=TRUNC(SYSDATE@!,'fmmm')-1)
  20 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND 
              "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
       filter(UPPER("SLICE_NAME")='P_NS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
  22 - filter(TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq')<=TRUNC(SYSDATE@!,'fmmm')-1)
  24 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND 
              "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
       filter(UPPER("SLICE_NAME")='P_COS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
  26 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth')<=TRUNC(SYSDATE@!,'fmmonth')-1)
  28 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND 
              "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
       filter(UPPER("SLICE_NAME")='P_ORDBKD' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND 
              "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
  30 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth')<=TRUNC(SYSDATE@!,'fmmonth')-1)
  32 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND 
              "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
       filter(UPPER("SLICE_NAME")='P_NS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND 
              "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
  34 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth')<=TRUNC(SYSDATE@!,'fmmonth')-1)
  36 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND 
              "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
       filter(UPPER("SLICE_NAME")='P_COS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND 
              "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND 
              "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
  42 - access("FIN"."ID"=TO_NUMBER(:P_PROJECT_ID))
  43 - access("ODFP"."ID"=TO_NUMBER(:P_PROJECT_ID))
  45 - access("INV"."ID"=TO_NUMBER(:P_PROJECT_ID))

Re: Help for tuning the sql Query [message #639743 is a reply to message #639742] Wed, 15 July 2015 03:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
How long does the query take?
Because according to that plan it's really fast.
Re: Help for tuning the sql Query [message #639744 is a reply to message #639743] Wed, 15 July 2015 03:23 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
It is taking nearly 1 min to 3 mins.

Can we tune further.
Re: Help for tuning the sql Query [message #639745 is a reply to message #639743] Wed, 15 July 2015 03:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many rows in eri_slices for each slice_name?
How many rows in eri_slices for each of the 3 date ranges?
How many rows in eri_slices in total?
Re: Help for tuning the sql Query [message #639748 is a reply to message #639745] Wed, 15 July 2015 04:02 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
select slice_name, count(*) from eri_slices group by slice_name;

slice_name      count(*)
------------------------
p_ns_cs	        125312
p_ns_hw	        125312
c_ordbkd_sw	125312
c_ns_cs	        125312
p_act_cst_cs	125312
p_ordbkd_cs	125312
c_act_cst_cs	125312
p_act_cst	125312
c_ns_hw	        125312
c_ns	        125312
p_ns	        125312
p_act_cst_sw	125312
c_cos	        125312
p_cos_cs	125312
c_act_cst	125312
c_act_cst_hw	125312
c_act_cst_sw	125312
p_act_cst_hw	125312
c_cos_cs	125312
c_cos_hw	125312
c_cos_sw	125312
p_ns_sw	        125312
p_ordbkd_hw	125312
p_cos_hw	125312
c_ordbkd_hw	125312
p_cos_sw	125312
p_ordbkd_sw	125312
c_ordbkd_cs	125312
c_ns_sw	        125312
p_ordbkd	125312
c_ordbkd	125312
p_cos	        125312

32 rows selected

--Year to Date
select COUNT(*)	from eri_slices s, inv_investments inv
			where upper(slice_name) in ('P_ORDBKD', 'P_NS', 'P_COS')
			and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month')  AND TRUNC(sysdate, 'YYYY') < s.finish_date;

COUNT(*)
------------
89802

select count(*) from eri_slices

COUNT(*)
-------
4009984

Re: Help for tuning the sql Query [message #639750 is a reply to message #639748] Wed, 15 July 2015 04:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd try joining the main query to eri_slices restricting it to the three slice_names and the maximum date range.
Then get rid of all sub-selects and do a sum(case) where the case does all the restrictions the sub-select where clause was doing.
e.g.
SELECT .........
    ,sum(Case WHEN slice_name ='p_cos'
              AND START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1 
              AND rd.year_start < s.finish_date
         then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End) 
FROM rd 
join INV_INVESTMENTS INV on 1=1
JOIN ODF_CA_PROJECT ODFP
ON INV.ID=ODFP.ID
JOIN ODF_CA_PROJFINPROPERTIES FIN
ON ODFP.ID=FIN.ID
JOIN eri_slices e ON e.investment_code = inv.code
WHERE INV.ID = &p_project_id 
AND e.slice_name IN ('p_ordbkd', 'p_ns', 'p_cos')
AND START_date > LEAST(trunc(add_months(sysdate,-1),'month'), TRUNC(SYSDATE,'y'))
GROUP BY ......
) TEMP;


You can fill in the rest
Re: Help for tuning the sql Query [message #639753 is a reply to message #639750] Wed, 15 July 2015 05:21 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I didnot understand the conditions like

in sum clause
 AND START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1 

in where clause
AND START_date > LEAST(trunc(add_months(sysdate,-1),'month'), TRUNC(SYSDATE,'y'))

But in previous query: 
(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)) 
		from eri_slices s where upper(slice_name)='P_COS'
		and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month') AND rd.year_start < s.finish_date )

Please clarify.
Re: Help for tuning the sql Query [message #639759 is a reply to message #639753] Wed, 15 July 2015 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got three date ranges.
If you join to the table once then you need to ensure that all the records that can fall into the three date ranges are picked up by the join.
That's what the least clause I wrote does (I assume there are no eri_slices records that have future dates) - it specifies the oldest possible from date that any of the three dates ranges can use.

Having done that you need to write CASE statements that only sum the eri_slice records that fall into the appropriate date range. The example I used was from the Month to Date set.
The CASE conditions for the other two sets will be different.
Re: Help for tuning the sql Query [message #639764 is a reply to message #639759] Wed, 15 July 2015 06:30 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much.
Re: Help for tuning the sql Query [message #639782 is a reply to message #639764] Thu, 16 July 2015 03:22 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did that approach improve the performance?
Previous Topic: Change in he execution plan
Next Topic: Drop Unused columns
Goto Forum:
  


Current Time: Thu Mar 28 18:19:36 CDT 2024