Re: performance question


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

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


Other related posts: