Re: SQL plan different in dedicated vs. shared connection

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)

On 11 Feb 2005 06:58:31 -0800, yong321_at_yahoo.com wrote:

>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
>

.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes remove NSPAM to email Received on Fri Feb 11 2005 - 09:40:31 CST