Home » RDBMS Server » Performance Tuning » Performance issue - Views inside a view (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 )
| Performance issue - Views inside a view [message #469209] |
Tue, 03 August 2010 19:41  |
dharmspeakz
Messages: 8
Registered: August 2010
Location: San Francisco
|
Junior Member |
|
|
Hi All,
The following query takes approximately 15 - 23 seconds for 419 rows of data.
SELECT
arc.customer_number caid,
0 acl,
NVL (arc.attribute5, fpov.profile_option_value) historic_days,
SYSDATE report_date,
cfsb.invoiced invoiced,
cfsb.published published,
cfsb.estimated estimated,
cfsb.extrapolated extrapolated,
cfsb.c_current c_current,
cfsb.c_b_reserve c_b_reserve,
cfsb.c_win_b c_win_b,
cfsb.past_due past_due,
cfsb.f_annual f_annual,
cfsb.wac_current wac_current,
cfsb.wac_future wac_future,
cfsb.fal_adjustment fal_adjustment,
cfsb.virtual_b virtual_b,
cfsb.virtual_da virtual_da,
cfsb.virtual_rt virtual_rt,
cfsb.c_b_liability,
cfsb.sequence_id
FROM c_fal_summary_b cfsb
, ar_customers_v arc
, fnd_profile_option_values fpov
, fnd_profile_options fpo
WHERE arc.customer_number = to_char(cfsb.caid(+))
-- AND arc.customer_number = icsfs.caid (+)
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = 'C_EXTRAPOLATED_HISTORY_DAYS'
c_fal_summary_b is a complex view built on several views
The view returns 216 rows of data taking approximately 15-20 seconds.
this is the stucture of the view
CREATE OR REPLACE FORCE VIEW APPS.C_FAL_SUMMARY_B
(CAID, INVOICED, PUBLISHED, ESTIMATED, EXTRAPOLATED,
C_CURRENT, C_B_RESERVE, C_WIN_B, PAST_DUE, F_ANNUAL,
WAC_CURRENT, WAC_FUTURE, FAL_ADJUSTMENT, VIRTUAL_B, VIRTUAL_DA,
VIRTUAL_RT, C_B_LIABILITY, SEQUENCE_ID)
AS
SELECT caid, SUM (invoiced), SUM (published), SUM (estimated),
SUM (extrapolated), SUM (c_current), SUM (c_b_reserve),
SUM (c_win_b), SUM (past_due), SUM (f_annual),
SUM (wac_current), SUM (wac_future), SUM (fal_adjustment),
SUM (virtual_b), SUM (virtual_da), SUM (virtual_rt),
SUM (c_b_liability), MAX (sequence_id)
FROM (SELECT caid, NVL (ar_balance, 0) - NVL (ap_balance, 0) invoiced,
0 published, 0 estimated, 0 extrapolated, 0 c_current,
0 c_b_reserve, 0 c_win_b, 0 past_due,
0 f_annual, 0 wac_current, 0 wac_future,
0 fal_adjustment, 0 virtual_b, 0 virtual_da,
0 virtual_rt, 0 c_b_liability, NULL sequence_id
FROM c_fal_invoiced
UNION ALL
SELECT caid, 0 invoiced,
NVL (ar_balance, 0) - NVL (ap_balance, 0) published,
0 estimated, 0 extrapolated, 0 c_current,
0 c_b_reserve, 0 c_win_b, 0 past_due,
0 f_annual, 0 wac_current, 0 wac_future,
0 fal_adjustment, 0 virtual_b, 0 virtual_da,
0 virtual_rt, 0 c_b_liability, NULL sequence_id
FROM c_fal_published
UNION ALL
SELECT caid, 0 invoiced, 0 published,
NVL (ar_balance, 0) - NVL (ap_balance, 0) estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual, 0 wac_current,
0 wac_future, 0 fal_adjustment, 0 virtual_b,
0 virtual_da, 0 virtual_rt, 0 c_b_liability,
NULL sequence_id
FROM c_fal_estimated
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
NVL (ar_balance, 0) - NVL (ap_balance, 0) extrapolated,
0 c_current, 0 c_b_reserve, 0 c_win_b,
0 past_due, 0 f_annual, 0 wac_current, 0 wac_future,
0 fal_adjustment, 0 virtual_b, 0 virtual_da,
0 virtual_rt, 0 c_b_liability, NULL sequence_id
FROM c_fal_extrapolated
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated,
NVL (ar_balance, 0) - NVL (ap_balance, 0) c_current,
0 c_b_reserve, 0 c_win_b, 0 past_due,
0 f_annual, 0 wac_current, 0 wac_future,
0 fal_adjustment, 0 virtual_b, 0 virtual_da,
0 virtual_rt, 0 c_b_liability, NULL sequence_id
FROM c_fal_cvalue
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current,
NVL (ar_balance, 0) - NVL (ap_balance, 0) c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual, 0 wac_current,
0 wac_future, 0 fal_adjustment, 0 virtual_b,
0 virtual_da, 0 virtual_rt, 0 c_b_liability,
sequence_id
FROM c_fal_cbreserve
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
NVL (ar_balance, 0) - NVL (ap_balance, 0) c_win_b,
0 past_due, 0 f_annual, 0 wac_current, 0 wac_future,
0 fal_adjustment, 0 virtual_b, 0 virtual_da,
0 virtual_rt, 0 c_b_liability, sequence_id
FROM c_fal_cwinb
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b,
NVL (ar_balance, 0) - NVL (ap_balance, 0) past_due,
0 f_annual, 0 wac_current, 0 wac_future,
0 fal_adjustment, 0 virtual_b, 0 virtual_da,
0 virtual_rt, 0 c_b_liability, NULL sequence_id
FROM c_fal_pastdue
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due,
NVL (ar_balance, 0) - NVL (ap_balance, 0) f_annual,
0 wac_current, 0 wac_future, 0 fal_adjustment,
0 virtual_b, 0 virtual_da, 0 virtual_rt,
0 c_b_liability, NULL sequence_id
FROM c_fal_fannual
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual,
NVL (ar_balance, 0) - NVL (ap_balance, 0) wac_current,
0 wac_future, 0 fal_adjustment, 0 virtual_b,
0 virtual_da, 0 virtual_rt, 0 c_b_liability,
NULL sequence_id
FROM c_fal_waccurrent
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual, 0 wac_current,
NVL (ar_balance, 0) - NVL (ap_balance, 0) wac_future,
0 fal_adjustment, 0 virtual_b, 0 virtual_da,
0 virtual_rt, 0 c_b_liability, NULL sequence_id
FROM c_fal_wacfuture
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual, 0 wac_current,
0 wac_future,
NVL (ar_balance, 0) - NVL (ap_balance, 0) fal_adjustment,
0 virtual_b, 0 virtual_da, 0 virtual_rt,
0 c_b_liability, null sequence_id
FROM c_fal_adjustment
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual, 0 wac_current,
0 wac_future, 0 fal_adjustment,
NVL (ar_balance, 0) - NVL (ap_balance, 0) virtual_b,
0 virtual_da, 0 virtual_rt, 0 c_b_liability,
sequence_id
FROM c_fal_virtual_b
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual, 0 wac_current,
0 wac_future, 0 fal_adjustment, 0 virtual_b,
NVL (ar_balance, 0) - NVL (ap_balance, 0) virtual_da,
0 virtual_rt, 0 c_b_liability, sequence_id
FROM c_fal_virtual_da
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual, 0 wac_current,
0 wac_future, 0 fal_adjustment, 0 virtual_b,
0 virtual_da,
NVL (ar_balance, 0) - NVL (ap_balance, 0) virtual_rt,
0 c_b_liability,sequence_id
FROM c_fal_virtual_rt
UNION ALL
SELECT caid, 0 invoiced, 0 published, 0 estimated,
0 extrapolated, 0 c_current, 0 c_b_reserve,
0 c_win_b, 0 past_due, 0 f_annual, 0 wac_current,
0 wac_future, 0 fal_adjustment, 0 virtual_b,
0 virtual_da,0 virtual_rt,
NVL (ar_balance, 0) - NVL (ap_balance, 0) c_b_liability,
sequence_id
FROM c_fal_cbliability
GROUP BY caid;
the inline view containing c_fal_invoiced, c_fal_published, etc are all views again.
But their individual execution takes 2 seconds, but the moment i use functions in the select of the main view C_FAL_SUMMARY_B,
performance degrades.
Kindly suggest how to tune the same.
|
|
|
|
|
|
| Re: Performance issue - Views inside a view [message #469213 is a reply to message #469209] |
Tue, 03 August 2010 21:59  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
FROM c_fal_summary_b cfsb
, ar_customers_v arc
, fnd_profile_option_values fpov
, fnd_profile_options fpo
WHERE arc.customer_number = to_char(cfsb.caid(+))
-- AND arc.customer_number = icsfs.caid (+)
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = 'C_EXTRAPOLATED_HISTORY_DAYS'
I see ARC joined to CFSB, and FPOV joined to FPO, but there is no connection between those two groups, resulting in a cartesian product. Is this what you wanted?
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Performance issue - Views inside a view [message #469308 is a reply to message #469234] |
Wed, 04 August 2010 04:03 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've definitely got a cartesian, but it might not matter - what are the unique keys on fnd_profile_option_values and fnd_profile_options?
Probably the view needs rewriting but without seeing what the views it calls look like we really can't suggest anything.
|
|
|
|
| Re: Performance issue - Views inside a view [message #469444 is a reply to message #469308] |
Wed, 04 August 2010 12:13 |
dharmspeakz
Messages: 8
Registered: August 2010
Location: San Francisco
|
Junior Member |
|
|
The tables FND_PROFILE_OPTION_VALUES,FND_PROFILE_OPTIONS
are Oracle standard tables and they dont have constraints.
select * from all_constraints
where TABLE_NAME in ('FND_PROFILE_OPTION_VALUES','FND_PROFILE_OPTIONS')
and CONSTRAINT_TYPE = 'P'
-- no rows.
But there are indexes, am copying the index definitions
CREATE INDEX APPLSYS.FND_PROFILE_OPTION_VALUES_N1 ON APPLSYS.FND_PROFILE_OPTION_VALUES
(LEVEL_ID, LEVEL_VALUE, LEVEL_VALUE_APPLICATION_ID, LEVEL_VALUE2)
LOGGING
TABLESPACE APPS_TS_TX_IDX
CREATE UNIQUE INDEX APPLSYS.FND_PROFILE_OPTION_VALUES_U1 ON APPLSYS.FND_PROFILE_OPTION_VALUES
(APPLICATION_ID, PROFILE_OPTION_ID, LEVEL_ID, LEVEL_VALUE, LEVEL_VALUE_APPLICATION_ID,
LEVEL_VALUE2)
LOGGING
TABLESPACE APPS_TS_TX_IDX
CREATE UNIQUE INDEX APPLSYS.FND_PROFILE_OPTIONS_U1 ON APPLSYS.FND_PROFILE_OPTIONS
(APPLICATION_ID, PROFILE_OPTION_ID)
LOGGING
TABLESPACE APPS_TS_SEED
CREATE UNIQUE INDEX APPLSYS.FND_PROFILE_OPTIONS_U2 ON APPLSYS.FND_PROFILE_OPTIONS
(PROFILE_OPTION_NAME)
LOGGING
TABLESPACE APPS_TS_SEED
i can provide all the view definitions if required.
|
|
|
|
| Re: Performance issue - Views inside a view [message #469448 is a reply to message #469444] |
Wed, 04 August 2010 12:32 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you're doing a unique lookup FND_PROFILE_OPTIONS (profile_option_name) but not on fnd_profile_option_values as profile_option_id isn't unique in that table.
So, depending on the data, you most definitely can get a cartesian join - technically you already have one but if one of the two result sets being cartesianed together only consists of one row it won't make any difference.
|
|
|
|
|
|
|
|
|
|
| Re: Performance issue - Views inside a view [message #469464 is a reply to message #469455] |
Wed, 04 August 2010 15:11 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
dharmspeakz wrote on Wed, 04 August 2010 18:49thank you !!!
But i will not be able to change the constraints on FND_PROFILE_OPTIONS and fnd_profile_option_values as they are Oracle standard tables.
Would there be an alternative to tune the sql ?
Thanks much,
Dharam
I'm not suggesting the constraints are wrong, I'm suggesting your where clause is wrong.
And I'm not talking about performance - I'm talking about suddenly getting far more rows back from the query than you were expecting as soon as someone inserts an extra matching row into fnd_profile_option_values.
Which is to say you've got a bug that needs fixing before you even begin to worry about performance.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Performance issue - Views inside a view [message #469585 is a reply to message #469477] |
Thu, 05 August 2010 04:09 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
To be honest your best bet is probably to rewrite the sql so that it doesn't reference any views but goes straight to the tables.
What you've got at the moment is so complex I doubt any of us could make useful suggestions and I know from experience that when you have a query that joins lots of views oracle tends to give a sub-optimal plan - usually because the views include joins that your query doesn't need.
|
|
|
|
Goto Forum:
Current Time: Sun May 05 07:09:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
