FAQ
Hi dear listers,

I have a very strange performance issue which I couldn't figure it out.
I have 2 11.2.0.1 databases with the exactly same parameters, they both
on the same server. One sql statement run on both database generated 2
diff plans. So I exported the whole schema stats from the good db,
imported it to the bad one. It didn't change the result. I can easily
fix the performance issue on the bad db by setting
optimizer_feature_enabled to 10g, but I just wondering why the 2
databases have different plans even after I imported the stats and
checked the stats.

Thanks,

Joan

Search Discussions

  • Ron Crisco at Apr 13, 2010 at 3:36 pm
    Rather than guessing, you can know the answer. Just run 10053 tracing in
    both instances and compare. To interpret the results, I highly recommend a
    paper written by Wolfgang Breitling, available at
    http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf

    Ron Crisco
    On Tue, Apr 13, 2010 at 9:54 AM, Joan Hsieh wrote:

    Hi dear listers,

    I have a very strange performance issue which I couldn't figure it out.
    I have 2 11.2.0.1 databases with the exactly same parameters, they both on
    the same server. One sql statement run on both database generated 2 diff
    plans. So I exported the whole schema stats from the good db, imported it to
    the bad one. It didn't change the result. I can easily fix the performance
    issue on the bad db by setting optimizer_feature_enabled to 10g, but I just
    wondering why the 2 databases have different plans even after I imported the
    stats and checked the stats.

    Thanks,

    Joan



    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Joan Hsieh at Apr 13, 2010 at 4:35 pm
    Hi all,

    I got the 10053 trace file, the base statistical infor are same.
    However, the access ps_job table are different.

    BASE STATISTICAL INFORMATION

    Table Stats::

    Table: PS_JOB Alias: J2
    #Rows: 336843 #Blks: 22604 AvgRowLen: 463.00
    Index Stats::

    Index: PS0JOB Col#: 5 1 2 163 164
    LVLS: 2 #LB: 1910 #DK: 336843 LB/K: 1.00 DB/K: 1.00 CLUF:

    309626.00

    Index: PS1JOB Col#: 6 1 2 163 164
    LVLS: 2 #LB: 2169 #DK: 336843 LB/K: 1.00 DB/K: 1.00 CLUF:

    314564.00

    Index: PS2JOB Col#: 7 1 2 163 164
    LVLS: 2 #LB: 1993 #DK: 336843 LB/K: 1.00 DB/K: 1.00 CLUF:

    314222.00

    Index: PS3JOB Col#: 8 1 2 163 164
    LVLS: 2 #LB: 1870 #DK: 336843 LB/K: 1.00 DB/K: 1.00 CLUF:

    310110.00

    Index: PS4JOB Col#: 9 1 2 163 164
    LVLS: 2 #LB: 1826 #DK: 336843 LB/K: 1.00 DB/K: 1.00 CLUF:

    309689.00

    Index: PSAJOB Col#: 1 2 163 164 6
    LVLS: 2 #LB: 2112 #DK: 336843 LB/K: 1.00 DB/K: 1.00 CLUF:

    309626.00

    Index: PSBJOB Col#: 1 2 163 164 15 12 11
    LVLS: 2 #LB: 2021 #DK: 336843 LB/K: 1.00 DB/K: 1.00 CLUF:

    309626.00

    Index: PSCJOB Col#: 142
    LVLS: 2 #LB: 688 #DK: 964 LB/K: 1.00 DB/K: 63.00 CLUF: 61489.00

    Index: PS_JOB Col#: 1 2 163 164
    LVLS: 2 #LB: 1660 #DK: 336843 LB/K: 1.00 DB/K: 1.00 CLUF:

    309626.00
    Access path analysis for PS_JOB

    SINGLE TABLE ACCESS PATH

    Single Table Cardinality Estimation for PS_JOB[J2]
    Table: PS_JOB Alias: J2
    Card: Original: 336843.000000 Rounded: 34 Computed: 33.68 Non
    Adjusted: 33.68

    Access Path: TableScan

    Cost: 6152.01 Resp: 6152.01 Degree: 0
    Cost_io: 6124.00 Cost_cpu: 235247591
    Resp_io: 6124.00 Resp_cpu: 235247591
    Access Path: index (index (FFS))
    Index: PS0JOB
    resc_io: 519.00 resc_cpu: 71033682
    ix_sel: 0.000000 ix_sel_with_filters: 1.000000
    Access Path: index (FFS)
    Cost: 527.46 Resp: 527.46 Degree: 1
    Cost_io: 519.00 Cost_cpu: 71033682
    Resp_io: 519.00 Resp_cpu: 71033682
    Access Path: index (index (FFS))
    Index: PS1JOB

    Bad one had the newdensity which was not in the good trace.

    Access path analysis for PS_JOB

    SINGLE TABLE ACCESS PATH

    Single Table Cardinality Estimation for PS_JOB[J2]
    Column (#1):
    NewDensity:0.000026, OldDensity:0.000036 BktCnt:254, PopBktCnt:0,
    PopValCnt:0, NDV:38130

    Column (#2):
    NewDensity:0.000192, OldDensity:0.000002 BktCnt:5195,
    PopBktCnt:5195, PopValCnt:7, NDV:7

    Table: PS_JOB Alias: J2
    Card: Original: 336843.000000 Rounded: 1 Computed: 1.26 Non
    Adjusted: 1.26

    Access Path: TableScan

    Cost: 6151.99 Resp: 6151.99 Degree: 0
    Cost_io: 6124.00 Cost_cpu: 235078951
    Resp_io: 6124.00 Resp_cpu: 235078951
    Access Path: index (index (FFS))
    Index: PS0JOB
    resc_io: 519.00 resc_cpu: 70865702
    ix_sel: 0.000000 ix_sel_with_filters: 1.000000
    Access Path: index (FFS)
    Cost: 527.44 Resp: 527.44 Degree: 1
    Cost_io: 519.00 Cost_cpu: 70865702
    Resp_io: 519.00 Resp_cpu: 70865702
  • Stefano Cislaghi at Apr 14, 2010 at 5:50 am
    That's interesting.
    May you run

    select column_name, num_distinct, density, histogram, num_buckets
    from user_tab_col_statistics
    where table_name = 'YOUR_TABLE'

    and check stats.

    Stefano

    --
    http://www.stefanocislaghi.eu

    2010/4/13 Joan Hsieh :
    Hi all,

    I got the 10053 trace file, the base statistical infor are same. However,
    the access ps_job table are different.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 13, '10 at 2:54p
activeApr 14, '10 at 5:50a
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase