Home » RDBMS Server » Performance Tuning » Performance Issue!!!
| Performance Issue!!! [message #214419] |
Tue, 16 January 2007 06:43  |
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 #214438 is a reply to message #214435] |
Tue, 16 January 2007 08:13  |
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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 16 13:17:13 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
