Re: performance question
- From: Joan Hsieh <joan.hsieh@xxxxxxxxx>
- To: Ron Crisco <ron.crisco@xxxxxxxxxxxx>
- Date: Tue, 13 Apr 2010 12:35:35 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
