Hi All,

I'm fighting with the weird problem. I have a public ASP application
and suddenly one user reported the problem - very slow rendering of a
page. I've localized the problem and it is about UNION operation. There
are two SELECT statements, each of them work very very normaly
(1-2secs) when being ran separately, but when I try to do UNION of
them, the whole query executes more than 3minutes (!!!). It's really
weird.
Anyone has any thoughts about it?

Thanks a lot in advance!!!


Here is the query:

select vw.ID as bweid,b.PE_SUBMITTED, vw.BUDGET_ID, vw.CATEGORY_ID,
vw.CATEGORY_NAME as CAPTION, vw.USER_DEFINED_CATEGORY as ADDLINE, 0 as
hasedit, vw.SUPERCATEGORY_ID as PARENTID, vw.SUPER_CATEGORY_NAME as
PARENTCAPTION, vw.PARENT_UDC, vw.CAPTION_UNIT_COST,
vw.CAPTION_MULTIPLIER_1, vw.CAPTION_MULTIPLIER_2,
vw.EXPENSE_ACCOUNT_CODE as EXPCODE, vw.UNIT_COST, vw.MULTIPLIER_1,
vw.MULTIPLIER_2, vw.COMMENTS, vw.APPLICABLE, vw.DISPLAY_SEQUENCE
from vw_BWS_entry vw, BUDGET b, budget_item bi
where vw.budget_id=b.id
and b.meeting_id='889445'
and applicable=1
and vw.BUDGET_ITEM_CATEGORY_ID=bi.BUDGET_ITEM_CATEGORY _ID
and bi.BUDGET_ID=b.ID
and vw.BUDGET_ITEM_CATEGORY_ID in
(
select distinct bic.id
from budget_item_category bic,budget_item bi,
invoice_line_item i, budget b
where bic.ID= bi.BUDGET_ITEM_CATEGORY_ID and
bi.ID=i.BUDGET_ITEM_ID and
b.ID=bi.BUDGET_ID
and b.meeting_id='889445' )

UNION

select vw.ID as bweid, b.PE_SUBMITTED, vw.BUDGET_ID, vw.CATEGORY_ID,
vw.CATEGORY_NAME as CAPTION, vw.USER_DEFINED_CATEGORY as ADDLINE, 1 as
hasedit, vw.SUPERCATEGORY_ID as PARENTID, vw.SUPER_CATEGORY_NAME as
PARENTCAPTION, vw.PARENT_UDC, vw.CAPTION_UNIT_COST,
vw.CAPTION_MULTIPLIER_1, vw.CAPTION_MULTIPLIER_2,
vw.EXPENSE_ACCOUNT_CODE as EXPCODE, vw.UNIT_COST, vw.MULTIPLIER_1,
vw.MULTIPLIER_2, vw.COMMENTS, vw.APPLICABLE, vw.DISPLAY_SEQUENCE
from vw_BWS_entry vw, BUDGET b, budget_item bi
where vw.budget_id=b.id
and b.meeting_id='889445'
and applicable=1
and vw.BUDGET_ITEM_CATEGORY_ID=bi.BUDGET_ITEM_CATEGORY _ID
and bi.BUDGET_ID=b.ID
and vw.BUDGET_ITEM_CATEGORY_ID not in
(
select distinct bic.id
from budget_item_category bic,budget_item bi,
invoice_line_item i, budget b
where bic.ID= bi.BUDGET_ITEM_CATEGORY_ID and
bi.ID=i.BUDGET_ITEM_ID and
b.ID=bi.BUDGET_ID
and b.meeting_id='889445'
)
order by DISPLAY_SEQUENCE,ADDLINE


Regards,
Goran M.

gmilosavljevic wrote:
Without seeing the wait events, data size, plan, Oracle version, and
init.ora parameters everything is likely to be a guess. One of the
things that I see is that you use UNION rather than UNION ALL. My
guess is that the query is hitting the temporary tablespace for
sorting, distinct, and even join operations.

Another way to help the SQL statement is to remove the subquery, if at
all possible. This can typically be done by converting it into an
inline view. The inline view only needs to be resolved once, rather
than once per row. For example, the second half of your SQL statement
will look like this (note the NOT IN syntax was replaced by an outer
join and then the right side of the join is specified as being null):

UNION ALL
SELECT
VW.ID AS BWEID,
B.PE_SUBMITTED,
VW.BUDGET_ID,
VW.CATEGORY_ID,
VW.CATEGORY_NAME AS CAPTION,
VW.USER_DEFINED_CATEGORY AS ADDLINE,
1 AS HASEDIT,
VW.SUPERCATEGORY_ID AS PARENTID,
VW.SUPER_CATEGORY_NAME AS PARENTCAPTION,
VW.PARENT_UDC,
VW.CAPTION_UNIT_COST,
VW.CAPTION_MULTIPLIER_1,
VW.CAPTION_MULTIPLIER_2,
VW.EXPENSE_ACCOUNT_CODE AS EXPCODE,
VW.UNIT_COST,
VW.MULTIPLIER_1,
VW.MULTIPLIER_2,
VW.COMMENTS,
VW.APPLICABLE,
VW.DISPLAY_SEQUENCE
FROM
VW_BWS_ENTRY VW,
BUDGET B,
BUDGET_ITEM BI,
(SELECT DISTINCT
BIC.ID
FROM
BUDGET_ITEM_CATEGORY BIC,
BUDGET_ITEM BI,
INVOICE_LINE_ITEM I,
BUDGET B
WHERE
BIC.ID=BI.BUDGET_ITEM_CATEGORY_ID
AND BI.ID=I.BUDGET_ITEM_ID
AND B.ID=BI.BUDGET_ID
AND B.MEETING_ID='889445') BNI
WHERE
VW.BUDGET_ITEM_CATEGORY_ID=BNI.ID(+)
AND BNI.ID IS NULL
AND VW.BUDGET_ID=B.ID
AND B.MEETING_ID='889445'
AND APPLICABLE=1
AND VW.BUDGET_ITEM_CATEGORY_ID=BI.BUDGET_ITEM_CATEGORY _ID
AND BI.BUDGET_ID=B.ID
ORDER BY
DISPLAY_SEQUENCE,
ADDLINE;

Depending on the version of Oracle, the hidden init.ora parameters, and
the size of the data set, the above may execute many times faster than
the original SQL statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.