I have two queries that run very fa

[MikeJones]

I have two queries that run very fast on their own (Milliseconds) however when I combine them with a UNION the performance nose dives. I can't think of anything obviuos, they reurn the same sort of data, and the query does parse OK, but it just takes for ever to return, also when I run the queries on their own the statement cost is 2 and 3, but the unioned one has a cost of 20!!. The query is below and the explain plan. If any one can see a problem or recommend a new approach I'd be very grateful

Cheers,

Mike.

select * from (select /* RULE */ fol_folder_title, fol_folder_id, fol_folder_no, 0 item_no, 0 item_id, 0 item_yn, 0 item
from nim_cfg_folders
where exists (select /* RULE */ 1
from nim_cfg_folder_items,
nim_cfg_items,
(select pac_access_id
from nim_sec_user_profiles,
nim_sec_profile_accesses
where pac_profile_id = upr_profile_id
and upr_user_id = 133351) pac2
where fol_folder_id = fit_folder_id
and fit_item_id = ite_item_id
and ite_web_dll_name is not null
and ite_briefcase_specific_yn = 'N'
and nvl(ite_access_method,pac2.pac_access_id) = pac2.pac_access_id
)
and exists (select 1
from nim_sec_user_profiles,
nim_sec_profile_accesses
where pac_profile_id = upr_profile_id
and upr_user_id = 133351
and nvl(fol_access_method,pac_access_id) = pac_access_id))
union
select ite_item_title, fol_folder_id, fol_folder_no, fit_item_no, fit_item_id, 1 item_yn, ite_item_id
from nim_cfg_folders, nim_cfg_folder_items, nim_cfg_items
where fol_folder_id = 10716
and fol_folder_id = fit_folder_id
and fit_item_id = ite_item_id
and ite_web_dll_name is not null
and ite_briefcase_specific_yn = 'N'
and exists (select 1
from nim_sec_user_profiles, nim_sec_profile_accesses
where upr_user_id = 133351
and pac_profile_id = upr_profile_id
and nvl(ite_access_method,pac_access_id) = pac_access_id
)
order by 1,3,5


SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=4 Bytes=406)
SORT (UNIQUE) (Cost=19 Card=4 Bytes=406)
UNION-ALL
NESTED LOOPS (Cost=9 Card=2 Bytes=230)
NESTED LOOPS (Cost=9 Card=32 Bytes=3424)
NESTED LOOPS (Cost=7 Card=2 Bytes=198)
NESTED LOOPS (Cost=7 Card=32 Bytes=2912)
NESTED LOOPS (Cost=5 Card=2 Bytes=166)
NESTED LOOPS (Cost=3 Card=2 Bytes=130)
TABLE ACCESS (FULL) OF NIM_CFG_ITEMS (Cost=1 Card=2 Bytes=118)
INDEX (FULL SCAN) OF FIT_C_PK (UNIQUE) (Cost=1 Card=51 Bytes=306)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_FOLDERS (Cost=1 Card=7 Bytes=126)
INDEX (UNIQUE SCAN) OF FOL_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)
NESTED LOOPS (Cost=6 Card=2 Bytes=176)
NESTED LOOPS (Cost=6 Card=32 Bytes=2560)
NESTED LOOPS (Cost=4 Card=2 Bytes=144)
NESTED LOOPS (Cost=2 Card=2 Bytes=128)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_FOLDERS (Cost=1 Card=1 Bytes=5)
INDEX (UNIQUE SCAN) OF FOL_C_PK (UNIQUE)
TABLE ACCESS (FULL) OF NIM_CFG_ITEMS (Cost=1 Card=2 Bytes=118)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_FOLDER_ITEMS (Cost=1 Card=8 Bytes=64)
INDEX (UNIQUE SCAN) OF FIT_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)

 

[MikeJones]

I think I may have narrowed the problem (but not the solution) down. If I comment out the second exists on the top query then the query returns in a bout 1/4 of a second. The query and explain plan are below.

I have no idea why this second exists slows the query down though??

select fol_folder_title, fol_folder_id, fol_folder_no, 0 item_no, 0 item_id, 0 item_yn, 0 item
from nim_cfg_folders
where exists (select 1
from nim_cfg_folder_items,
nim_cfg_items,
(select pac_access_id
from nim_sec_user_profiles,
nim_sec_profile_accesses
where pac_profile_id = upr_profile_id
and upr_user_id = 133351) pac2
where fol_folder_id = fit_folder_id
and fit_item_id = ite_item_id
and ite_web_dll_name is not null
and ite_briefcase_specific_yn = 'N'
and nvl(ite_access_method,pac2.pac_access_id) = pac2.pac_access_id
)
-- and exists (select 1
-- from nim_sec_user_profiles,
-- nim_sec_profile_accesses
-- where pac_profile_id = upr_profile_id
-- and upr_user_id = 133351
-- and nvl(fol_access_method,pac_access_id) = pac_access_id)
union
select ite_item_title, fol_folder_id, fol_folder_no, fit_item_no, fit_item_id, 1 item_yn, ite_item_id
from nim_cfg_folders, nim_cfg_folder_items, nim_cfg_items
where fol_folder_id = 10716
and fol_folder_id = fit_folder_id
and fit_item_id = ite_item_id
and ite_web_dll_name is not null
and ite_briefcase_specific_yn = 'N'
and exists (select 1
from nim_sec_user_profiles, nim_sec_profile_accesses
where upr_user_id = 133351
and pac_profile_id = upr_profile_id
and nvl(ite_access_method,pac_access_id) = pac_access_id
)
order by 1,3,5

SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=4 Bytes=368)
SORT (UNIQUE) (Cost=15 Card=4 Bytes=368)
UNION-ALL
NESTED LOOPS (Cost=5 Card=2 Bytes=192)
NESTED LOOPS (Cost=5 Card=32 Bytes=2816)
NESTED LOOPS (Cost=3 Card=2 Bytes=160)
MERGE JOIN (CARTESIAN) (Cost=3 Card=14 Bytes=1036)
TABLE ACCESS (FULL) OF NIM_CFG_ITEMS (Cost=1 Card=2 Bytes=118)
SORT (JOIN) (Cost=2 Card=7 Bytes=105)
TABLE ACCESS (FULL) OF NIM_CFG_FOLDERS (Cost=1 Card=7 Bytes=105)
INDEX (UNIQUE SCAN) OF FIT_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)
NESTED LOOPS (Cost=6 Card=2 Bytes=176)
NESTED LOOPS (Cost=6 Card=32 Bytes=2560)
NESTED LOOPS (Cost=4 Card=2 Bytes=144)
NESTED LOOPS (Cost=2 Card=2 Bytes=128)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_FOLDERS (Cost=1 Card=1 Bytes=5)
INDEX (UNIQUE SCAN) OF FOL_C_PK (UNIQUE)
TABLE ACCESS (FULL) OF NIM_CFG_ITEMS (Cost=1 Card=2 Bytes=118)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_FOLDER_ITEMS (Cost=1 Card=8 Bytes=64)
INDEX (UNIQUE SCAN) OF FIT_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)

 

[MikeLacey]

Mike,

Could I suggest that the line:

TABLE ACCESS (FULL) OF NIM_CFG_ITEMS (Cost=1 Card=2 Bytes=118)

from your explain plan might be a starting point.

Would it be possible to run a session with trace turned on and then run TKPROF on the resulting trace file? Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[MikeJones]

Thanks Mike,

I should pay more attention to my query plans, I developed the two queries seperately (and tuned them seperately as well) and then just bolted them together with a UNION. When I have bolted them together the Cost based optomiser has changed my query plan for me.

To try and keep the waters a bit clearer I've narrowed this down to the following example, if some one can explain to me how to stop it transforming the query when I add the union I should be able to make the correct changes to my overall query...

My first query looks like this...

select fol_folder_title, fol_folder_id, fol_folder_no, 0 item_no, 0 item_id, 0 item_yn, 0 item
from nim_cfg_folders
where exists (select /*+ ORDERED INDEX(nim_cfg_folder_items fit_c_pk) INDEX(nim_cfg_items ite_c_pk)
USE_NL(nim_cfg_folder_items nim_cfg_items) USE_NL(nim_cfg_items nim_sec_user_profiles)
USE_NL(nim_sec_user_profiles nim_sec_profile_accesses) */
1
from nim_cfg_folder_items,
nim_cfg_items,
nim_sec_user_profiles,
nim_sec_profile_accesses
where fit_folder_id = fol_folder_id
and fit_item_id = ite_item_id
and ite_web_dll_name is not null
and ite_briefcase_specific_yn = 'N'
and nvl(ite_access_method, pac_access_id) = pac_access_id
and pac_profile_id = upr_profile_id
and upr_user_id = :user_id
)
and exists (select 1
from nim_sec_user_profiles,
nim_sec_profile_accesses
where pac_profile_id = upr_profile_id
and upr_user_id = :user_id
and nvl(fol_access_method,pac_access_id) = pac_access_id)

The explain plan looks like this and is nearly how I expect it to look. I would like to start the filter from nim_cfg_folder_items rather than nim_cfg_items but I'm not sure how to hint it to say start with nim_cfg_folder_items I've used the ordered hint but it's not having it.

Anyway the explain plan looks like this...

SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18)
FILTER
TABLE ACCESS (FULL) OF NIM_CFG_FOLDERS (Cost=1 Card=1 Bytes=18)
NESTED LOOPS (Cost=109 Card=2 Bytes=162)
NESTED LOOPS (Cost=13 Card=48 Bytes=3504)
NESTED LOOPS (Cost=9 Card=2 Bytes=130)
INDEX (RANGE SCAN) OF FIT_C_PK (UNIQUE) (Cost=1 Card=8 Bytes=48)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_ITEMS (Cost=1 Card=2 Bytes=118)
INDEX (UNIQUE SCAN) OF ITE_C_PK (UNIQUE)
INDEX (RANGE SCAN) OF UPR_C_PK (UNIQUE) (Cost=2 Card=24 Bytes=192)
INDEX (RANGE SCAN) OF PAC_C_PK (UNIQUE) (Cost=2 Card=1576 Bytes=12608)
NESTED LOOPS (Cost=6 Card=17 Bytes=272)
INDEX (FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=6 Card=16 Bytes=128)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)


Which is nice enough and performs well enough.

If I add a union to it though (In this example I'm just selecting 1 predefined row from dual but in real life it would be another select)

select fol_folder_title, fol_folder_id, fol_folder_no, 0 item_no, 0 item_id, 0 item_yn, 0 item
from nim_cfg_folders
where exists (select /*+ ORDERED INDEX(nim_cfg_folder_items fit_c_pk) INDEX(nim_cfg_items ite_c_pk)
USE_NL(nim_cfg_folder_items nim_cfg_items) USE_NL(nim_cfg_items nim_sec_user_profiles)
USE_NL(nim_sec_user_profiles nim_sec_profile_accesses) */
1
from nim_cfg_folder_items,
nim_cfg_items,
nim_sec_user_profiles,
nim_sec_profile_accesses
where fit_folder_id = fol_folder_id
and fit_item_id = ite_item_id
and ite_web_dll_name is not null
and ite_briefcase_specific_yn = 'N'
and nvl(ite_access_method, pac_access_id) = pac_access_id
and pac_profile_id = upr_profile_id
and upr_user_id = :user_id
)
and exists (select 1
from nim_sec_user_profiles,
nim_sec_profile_accesses
where pac_profile_id = upr_profile_id
and upr_user_id = :user_id
and nvl(fol_access_method,pac_access_id) = pac_access_id)
union
select 'A' ite_item_title, 0 fol_folder_id, 0 fol_folder_no, 0 fit_item_no, 0 fit_item_id, 1 item_yn, 0 ite_item_id
from dual

Like so I would expect it just to add a union all, a sort and a select from dual. Instead it has removed my filter from the top query and turned them into NL's, this seems to be wrecking the performance (well the query won't return and I can see no other difference)

SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=84 Bytes=230)
SORT (UNIQUE) (Cost=15 Card=84 Bytes=230)
UNION-ALL
NESTED LOOPS (Cost=10 Card=2 Bytes=230)
NESTED LOOPS (Cost=10 Card=32 Bytes=3424)
NESTED LOOPS (Cost=8 Card=2 Bytes=198)
NESTED LOOPS (Cost=8 Card=32 Bytes=2912)
NESTED LOOPS (Cost=6 Card=2 Bytes=166)
NESTED LOOPS (Cost=4 Card=2 Bytes=130)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_ITEMS (Cost=2 Card=2 Bytes=118)
INDEX (FULL SCAN) OF ITE_C_PK (UNIQUE) (Cost=1 Card=2)
INDEX (FULL SCAN) OF FIT_C_PK (UNIQUE) (Cost=1 Card=51 Bytes=306)
TABLE ACCESS (BY INDEX ROWID) OF NIM_CFG_FOLDERS (Cost=1 Card=7 Bytes=126)
INDEX (UNIQUE SCAN) OF FOL_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)
INDEX (FAST FULL SCAN) OF PAC_C_PK (UNIQUE) (Cost=1 Card=1576 Bytes=12608)
INDEX (UNIQUE SCAN) OF UPR_C_PK (UNIQUE)
TABLE ACCESS (FULL) OF DUAL (Cost=1 Card=82)


Sorry about the bad spacing, if anyone's got some ideas as to how I can tell the cost based optomiser to stop messing up my query I'd be grateful.

Thanks,

Mike.