Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL plan different in dedicated vs. shared connection

Re: SQL plan different in dedicated vs. shared connection

From: <danielroy10junk_at_hotmail.com>
Date: 13 Feb 2005 10:26:14 -0800
Message-ID: <1108319174.560615.309040@c13g2000cwb.googlegroups.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

NetComrade wrote:
> 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 Sun Feb 13 2005 - 12:26:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US