SQL plan different in dedicated vs. shared connection

[NetComrade]

Is this possible? I would think it would be possible with different
session settings (hash_area_size, sort_area_size, etc), but this (I
think) can only happen with work_area_policy set to auto, on this
system it was set to manual. Anything else I am missing? The
statistics on tables did not change between test.

Thanks.
.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
remove NSPAM to email

[yon...@yahoo.com]

If this is reproducible, I'm guessing the free memory in shared pool
and (if configured) large pool is affecting CBO. Can you show us the
two execution plans, plus relevant initialization parameters?

Yong Huang

[NetComrade]

Also note, there is some 'weirdness' in parameter setting (as well as
unnecessary counts within sql, and too many 1-to-1 table joins). This
is not 'my' database, I just assist with it once in a while when it
has performance problems. This db runs on Linux 2.4.21-27.0.1.ELsmp
(Opteron machine)

sort_area_size integer 5000000
sort_area_retained_size integer 0
hash_area_size integer 10000000
hash_join_enabled boolean TRUE
pga_aggregate_target big integer 838860800
workarea_size_policy string MANUAL
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
large_pool_size big integer 167772160
shared_pool_reserved_size big integer 15938355
shared_pool_size big integer 318767104

Via dedicated connection:

0 SELECT STATEMENTCost=47046 (card:641650)
1 SORT (card:641650)
2 FILTER (card:)
3 HASH JOIN (card:641650)
4 HASH JOIN (card:641650)
5 HASH JOIN (card:641650)
6 HASH JOIN (card:641650)
7 HASH JOIN (card:641650)
8 VIEW (card:641650)
9 COUNT (card:)
10 VIEW (card:641650)
11 SORT (card:641650)
12 COUNT (card:)
13 HASH JOIN (card:641650)
14 HASH JOIN (card:641650)
15 VIEW (card:641650)
16 SORT (card:641650)
17 INDEX CPV (card:1782955)
18 INDEX COIU2_LOAD11 (card:1580550)
19 TABLE ACCESS XYZ_CO_SCORE
(card:1580557)
20 TABLE ACCESS XYZ_CO_SCORE (card:1580557)
21 INDEX COIU2_LOAD11 (card:1580550)
22 TABLE ACCESS ADDRESS (card:1594133)
23 TABLE ACCESS XYZISEN_TICKER (card:12864)
24 TABLE ACCESS XYZISEN_EXCHANGE (card:173)

vis MTS:

0 SELECT STATEMENTCost=82596 (card:641650)
1 SORT (card:641650)
2 FILTER (card:)
3 HASH JOIN (card:641650)
4 MERGE JOIN (card:641650)
5 MERGE JOIN (card:641650)
6 MERGE JOIN (card:641650)
7 MERGE JOIN (card:641650)
8 SORT (card:641650)
9 VIEW (card:641650)
10 COUNT (card:)
11 VIEW (card:641650)
12 SORT (card:641650)
13 COUNT (card:)
14 MERGE JOIN (card:641650)
15 MERGE JOIN (card:641650)
16 VIEW (card:641650)
17 SORT (card:641650)
18 INDEX CPV (card:1782955)
19 SORT (card:1580550)
20 INDEX COIU2_LOAD11
(card:1580550)
21 SORT (card:1580557)
22 TABLE ACCESS XYZ_CO_SCORE
(card:1580557)
23 SORT (card:1580550)
24 INDEX COIU2_LOAD11 (card:1580550)
25 SORT (card:12864)
26 TABLE ACCESS XYZISEN_TICKER (card:12864)
27 SORT (card:1580557)
28 TABLE ACCESS XYZ_CO_SCORE (card:1580557)
29 SORT (card:1594133)
30 TABLE ACCESS ADDRESS (card:1594133)
31 TABLE ACCESS XYZISEN_EXCHANGE (card:173)

.......

[yon...@yahoo.com]

Sorry. I don't know the answer. I did some test on my 9.2 database
running on XP using much less complex tables. I can't get different
execution plans. When I switch to shared server mode, I only explicitly
change two parameters, shared_servers from 0 to 5 and dispatchers from
not set to set to 'TCP,5'. I checked all parameters including
undocumented ones between these two configurations. Only those related
to these two are changed (but _load_without_compile changed case from
"NONE" to "none"). I also checked 10053 event traces and can't find
anything. Ignore my guess about free memory affecting CBO's decision.

Please try to reproduce again, and if you open a Tar, let us know if
eventually you find the answer. Thanks.

Yong Huang
yong321ATyahoo.com

[danielr...@hotmail.com]

Run event 10053 under both dedicated and shared servers, and you should
be able to find why it generated different plans. Search this group if
you need to find the syntax.

Daniel

[Joel Garry]

Look at the bugs fixed for 9.2.0.6. Between wrong results (2243979)
and performance of queries affected there are some perturbing
situations.

Do you see sort activity in temp? I think Yong was on the right trail
with memory, maybe those sort params. Since the manual workarea policy
moves session information to the SGA with shared server, something is
telling CBO to do more sorting. And if that something is one of those
bugs, well, I don't know what to say. Try upping the sort parameters
for the session and see if the plan changes. And remember, plans lie,
if you are getting to this level of detail, use traces as Daniel Roy
said.

jg
--
@home.com is bogus.
http://www.nj.com/business/ledger/index.ssf?/base/business-0/110827537541270.xml#continue

[NetComrade]

Well, for one I know the execution is different, b/c dedicated
returned after X seconds, while MTS didn't after X minutes. (I didn't
trace though)

I'll research this further when I have time, thanks for all the
suggestions, for now we just switched to dedicated mode, like we were
supposed to be running, since App server is pooling connections into
the db.

On 14 Feb 2005 15:25:41 -0800, "Joel Garry" <joel-...@home.com>
wrote:

.......