Home » RDBMS Server » Performance Tuning » Performance Issue!!!
Performance Issue!!! [message #214419] Tue, 16 January 2007 06:43 Go to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi All,

I have following query..which is taking lot of time to execute need your suggestion on index or any performance enhancement tips...

Oracle Version is 8.1.7.4

	SELECT DATA_SET_NAME,
		   DEPTCLASS,DEPT,
		   SMCBOM_FLEX_BUDGET.GET_PERIOD(V_DATE) V_PERIOD,
		   SUM(V_HR),
		   -1,
		   SYSDATE,
		   -1,
		   SYSDATE 
	FROM ( 
SELECT PLAN_LEVEL,
		SP.DATA_SET_NAME, 
		SMCBOM_FLEX_BUDGET.CALCULATE_PERIOD_DAYS(sp.value14), 
		OPERATIONSEQ, 
		SBOV.GROUP_ID,
		SP.ALLOY,
		SP.PLANNER_CODE, 
		DEPTCLASS,
		DEPT, 
                DECODE(PLAN_LEVEL,		       1,(sp.value14/SMCBOM_FLEX_BUDGET.CALCULATE_PERIOD_DAYS(sp.period14)) * (PERCENT/100) * INWEIGHT * USAGERATE * 
			(SELECT MAX(INWEIGHT) 
			 FROM  SMCBOM_BOM_OPERATION_VIEW 
			 WHERE ALLOY=SBOV.ALLOY 
			 AND   PLANNER_CODE=SBOV.PLANNER_CODE 
			 AND   PLAN_LEVEL = 0 
			 AND   GROUP_ID = SBOV.GROUP_ID ), 
		  0,(SP.VALUE14/SMCBOM_FLEX_BUDGET.CALCULATE_PERIOD_DAYS(sp.period14)) * (PERCENT/100) * INWEIGHT * USAGERATE , 
		  1) V_HR,
   SMCBOM_FLEX_BUDGET.GET_START_DATE(sp.period14)+SBOV.TOTALOFFSETDAYS V_DATE, 
  	   SUM(-SBOV.TOTALOFFSETDAYS) OVER (PARTITION BY SP.ALLOY,SP.PLANNER_CODE,SBOV.GROUP_ID	ORDER BY PLAN_LEVEL ASC,OPERATIONSEQ DESC) NEW_OFFSET 
	FROM SMCBOM_BOM_OPERATION_VIEW SBOV,
		 SMCBOM_SALES_PROD_FORECASTS SP 
	WHERE SBOV.ALLOY= SP.ALLOY 
	AND   SBOV.PLANNER_CODE=SP.PLANNER_CODE 
	AND   SP.DATA_SET_NAME = 'DATA_SET_NAME'
	AND   SBOV.GROUP_ID=521187
	) 
GROUP BY DATA_SET_NAME,DEPTCLASS,DEPT,SMCBOM_FLEX_BUDGET.GET_PERIOD(V_DATE)



Explain Plan for above Query is following way....

 SELECT STATEMENT Optimizer=RULE
  SORT (GROUP BY)
    VIEW
      MERGE JOIN
        SORT (JOIN)S
          TABLE ACCESS (FULL) OF SMCBOM_SALES_PROD_FORECASTS
        SORT (JOIN)
          VIEW OF SMCBOM_BOM_OPERATION_VIEW
            SORT (UNIQUE)
              UNION-ALL
                MERGE JOIN
                  SORT (JOIN)
                    MERGE JOIN
                      SORT (JOIN)
                        TABLE ACCESS (FULL) OF SMCBOM_BUDGET_DATA_TEMP
                      SORT (JOIN)
                        TABLE ACCESS (FULL) OF SMCBOM_FLEXBUDGET_BOM_TEMP
                  SORT (JOIN)
                    TABLE ACCESS (FULL) OF SMCBOM_ROUTING_MODELS
                MERGE JOIN
                  MERGE JOIN
                    SORT (JOIN)
                      TABLE ACCESS (FULL) OF SMCBOM_BUDGET_DATA_TEMP
                    SORT (JOIN)
                      TABLE ACCESS (FULL) OF SMCBOM_FLEXBUDGET_BOM_TEMP
                  SORT (JOIN)
                    TABLE ACCESS (FULL) OF SMCBOM_ROUTING_MODELS


Table Used in the Query are custom table

1) SMCBOM_ROUTING_MODELS
COLUMN_NAME	NULLABLE	DATA_TYPE
ROUTING_SET_NAME	N	VARCHAR2
ALLOY	Y	VARCHAR2
PLANNER_CODE	Y	VARCHAR2
GROUP_NAME	Y	VARCHAR2
PERCENT	Y	NUMBER
ROUTING_ITEM	Y	VARCHAR2
CREATED_BY	Y	NUMBER
CREATION_DATE	Y	DATE
LAST_UPDATED_BY	Y	NUMBER
LAST_UPDATE_DATE	Y	DATE
ROUTING_DAYS	Y	NUMBER
About 600 Rows

2) SMCBOM_FLEXBUDGET_BOM_TEMP

COLUMN_NAME	NULLABLE	DATA_TYPE
ASSEMBLY_ITEM	N	VARCHAR2
COMPONENT_ITEM	Y	VARCHAR2
GROUP_ID	Y	NUMBER
DESCRIPTION	Y	VARCHAR2
ALTERNATE_DESIGNATOR	Y	VARCHAR2
PLAN_LEVEL	Y	NUMBER
EFFECTIVE_DATE	Y	DATE
DISABLE_DATE	Y	DATE
CREATE_BY	Y	NUMBER
CREATION_DATE	Y	DATE
LAST_UPDATED_BY	Y	NUMBER
LAST_UPDATE_DATE	Y	DATE
PERCENT	Y	NUMBER
About 8000 rows
3) SMCBOM_PROD_SALES_FORECASTS

COLUMN_NAME	NULLABLE	DATA_TYPE
DATA_SET_NAME	N	VARCHAR2
DATA_SET_TYPE	Y	VARCHAR2
ALLOY	Y	VARCHAR2
PLANNER_CODE	Y	VARCHAR2
PERIOD1	Y	VARCHAR2
VALUE1	Y	NUMBER
PERIOD2	Y	VARCHAR2
VALUE2	Y	NUMBER
PERIOD3	Y	VARCHAR2
VALUE3	Y	NUMBER
PERIOD4	Y	VARCHAR2
VALUE4	Y	NUMBER
PERIOD5	Y	VARCHAR2
VALUE5	Y	NUMBER
CREATED_BY	Y	NUMBER
CREATION_DATE	Y	DATE
LAST_UPDATED_BY	Y	NUMBER
LAST_UPDATE_DATE	Y	DATE
About 150 Rows


Thanks in advance

[Updated on: Tue, 16 January 2007 06:46]

Report message to a moderator

Re: Performance Issue!!! [message #214435 is a reply to message #214419] Tue, 16 January 2007 08:05 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
You might want to analyze your tables so that the CBO is utilized.
Re: Performance Issue!!! [message #214438 is a reply to message #214435] Tue, 16 January 2007 08:13 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi,

Tables are analyzed daily and following is the execution plan comparision but dont see much of difference in query performance...

any more suggestions....thanks in advance



With CBO Optimizer


 SELECT DATA_SET_NAME,
     DEPTCLASS,DEPT,
     SMCBOM_FLEX_BUDGET.GET_PERIOD(V_DATE) V_PERIOD,
     SUM(V_HR),
     -1,
     SYSDATE,
     -1,
     SYSDATE
 FROM (
SELECT /*+ ALL_ROWS */ PLAN_LEVEL,
  SP.DATA_SET_NAME,
  SMCBOM_FLEX_BUDGET.CALCULATE_PERIOD_DAYS(sp.value14),
  OPERATIONSEQ,
  SBOV.GROUP_ID,
  SP.ALLOY,
  SP.PLANNER_CODE,
  DEPTCLASS,
  DEPT,
  DECODE(PLAN_LEVEL,
     1,(sp.value14/SMCBOM_FLEX_BUDGET.CALCULATE_PERIOD_DAYS(sp.period14)) * (PERCENT/100) * INWEIGHT * USAGERATE *
     (SELECT MAX(INWEIGHT)
      FROM  SMCBOM_BOM_OPERATION_VIEW
      WHERE ALLOY=SBOV.ALLOY
      AND   PLANNER_CODE=SBOV.PLANNER_CODE
      AND   PLAN_LEVEL = 0
      AND   GROUP_ID = SBOV.GROUP_ID ),
    0,(SP.VALUE14/SMCBOM_FLEX_BUDGET.CALCULATE_PERIOD_DAYS(sp.period14)) * (PERCENT/100) * INWEIGHT * USAGERATE ,
    1) V_HR,
   SMCBOM_FLEX_BUDGET.GET_START_DATE(sp.period14)+SBOV.TOTALOFFSETDAYS V_DATE,
      SUM(-SBOV.TOTALOFFSETDAYS) OVER (PARTITION BY SP.ALLOY,SP.PLANNER_CODE,SBOV.GROUP_ID ORDER BY PLAN_LEVEL ASC,OPERATIONSEQ DESC) NEW_OFFSET
 FROM SMCBOM_BOM_OPERATION_VIEW SBOV,
   SMCBOM_SALES_PROD_FORECASTS SP
 WHERE SBOV.ALLOY= SP.ALLOY
 AND   SBOV.PLANNER_CODE=SP.PLANNER_CODE
 AND   SP.DATA_SET_NAME = 'DATA_SET_NAME'
 AND   SBOV.USAGERATE <> 0
 )
GROUP BY DATA_SET_NAME,DEPTCLASS,DEPT,SMCBOM_FLEX_BUDGET.GET_PERIOD(V_DATE)

106 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=26 Card=1 By
          tes=61)

   1    0   SORT (GROUP BY) (Cost=26 Card=1 Bytes=61)
   2    1     VIEW (Cost=24 Card=68 Bytes=4148)
   3    2       HASH JOIN (Cost=24 Card=68 Bytes=20128)
   4    3         TABLE ACCESS (FULL) OF 'SMCBOM_SALES_PROD_FORECASTS'
           (Cost=1 Card=152 Bytes=8208)

   5    3         VIEW OF 'SMCBOM_BOM_OPERATION_VIEW' (Cost=26 Card=16
          3 Bytes=39446)

   6    5           SORT (UNIQUE) (Cost=22 Card=163 Bytes=16952)
   7    6             UNION-ALL
   8    7               HASH JOIN (Cost=8 Card=158 Bytes=16432)
   9    8                 HASH JOIN (Cost=6 Card=92 Bytes=6072)
  10    9                   TABLE ACCESS (FULL) OF 'SMCBOM_FLEXBUDGET_
          BOM_TEMP' (Cost=1 Card=450 Bytes=9000)
11    9                   TABLE ACCESS (FULL) OF 'SMCBOM_BUDGET_DATA
        _TEMP' (Cost=4 Card=7835 Bytes=360410)

12    8                 TABLE ACCESS (FULL) OF 'SMCBOM_ROUTING_MODEL
        S' (Cost=1 Card=580 Bytes=22040)

13    7               HASH JOIN (Cost=8 Card=5 Bytes=520)
14   13                 HASH JOIN (Cost=3 Card=26 Bytes=1508)
15   14                   TABLE ACCESS (FULL) OF 'SMCBOM_FLEXBUDGET_
        BOM_TEMP' (Cost=1 Card=15 Bytes=300)

16   14                   TABLE ACCESS (FULL) OF 'SMCBOM_ROUTING_MOD
        ELS' (Cost=1 Card=580 Bytes=22040)

17   13                 TABLE ACCESS (FULL) OF 'SMCBOM_BUDGET_DATA_T
        EMP' (Cost=4 Card=7877 Bytes=362342)

Statistics
----------------------------------------------------------
      27346  recursive calls
     115944  db block gets
    1256242  consistent gets
          0  physical reads
          0  redo size
      12238  bytes sent via SQL*Net to client
       1195  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
       2138  sorts (memory)
          0  sorts (disk)
        106  rows processed


WITH Rule Optimizer

106 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (GROUP BY)
   2    1     VIEW
   3    2       MERGE JOIN
   4    3         SORT (JOIN)
   5    4           TABLE ACCESS (FULL) OF 'SMCBOM_SALES_PROD_FORECAST
          S'

   6    3         SORT (JOIN)
   7    6           VIEW OF 'SMCBOM_BOM_OPERATION_VIEW'
   8    7             SORT (UNIQUE)
   9    8               UNION-ALL
  10    9                 MERGE JOIN
  11   10                   SORT (JOIN)
  12   11                     MERGE JOIN
  13   12                       SORT (JOIN)
  14   13                         TABLE ACCESS (FULL) OF 'SMCBOM_BUDGE
          T_DATA_TEMP'
15   12                       SORT (JOIN)
16   15                         TABLE ACCESS (FULL) OF 'SMCBOM_FLEXB
        UDGET_BOM_TEMP'

17   10                   SORT (JOIN)
18   17                     TABLE ACCESS (FULL) OF 'SMCBOM_ROUTING_M
        ODELS'

19    9                 MERGE JOIN
20   19                   MERGE JOIN
21   20                     SORT (JOIN)
22   21                       TABLE ACCESS (FULL) OF 'SMCBOM_BUDGET_
        DATA_TEMP'

23   20                     SORT (JOIN)
24   23                       TABLE ACCESS (FULL) OF 'SMCBOM_FLEXBUD
        GET_BOM_TEMP'

  25   19                   SORT (JOIN)
  26   25                     TABLE ACCESS (FULL) OF 'SMCBOM_ROUTING_M
          ODELS'





Statistics
----------------------------------------------------------
      27346  recursive calls
     123964  db block gets
    1419398  consistent gets
          0  physical reads
          0  redo size
      12238  bytes sent via SQL*Net to client
       1195  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
      14690  sorts (memory)
          0  sorts (disk)
        106  rows processed


Re: Performance Issue!!! [message #214458 is a reply to message #214419] Tue, 16 January 2007 08:59 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF and view definitions.
What indexes are defined for SMCBOM_FLEXBUDGET_BOM_TEMP table?(
Do you have an index on GROUP_ID column?).

Re: Performance Issue!!! [message #214493 is a reply to message #214458] Tue, 16 January 2007 13:37 Go to previous message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi,

Used CHOOSE hint and it worked well than ALL_ROWS.

Thanks for reply
Previous Topic: Partitioning
Next Topic: Help with general preformance increase...
Goto Forum:
  


Current Time: Thu Dec 08 02:04:01 CST 2016

Total time taken to generate the page: 0.12385 seconds