Re: [Fwd: Re: System Statistics and the CBO]

It is unfortunate that the 10053 trace does not give the index (scan) access path for X01 - it only gives the index (equal) path. The CPU and IO resource values for X01 cannot be directly compared to those for X04 since the values for X04 are for the index (scan) access path. It is also unfortunate that the trace does not provide the final cost assigned to each access path. All we have is the final cost of the chosen path.  Access path: index (equal)
Index: X01CLAIM_FORM
TABLE: CLAIM_FORM
RSC_CPU: 279741 RSC_IO: 13
IX_SEL: 0.0000e+00 TB_SEL: 7.1429e-02
...
Access path: index (scan)
Index: X04CLAIM_FORM
TABLE: CLAIM_FORM
RSC_CPU: 21475 RSC_IO: 3
IX_SEL: 3.8341e-05 TB_SEL: 3.8341e-05
 Aside:
 Patty sent me both 10053 and 10046 traces for both cases (with and without system stats) and while the 10053 traces agree with what autotrace told us
(that X01 is used with system stats and X04 is used without them), the 10046
STAT lines show that the same execution plan is used in both cases.  Without system stats:

• SESSION ID:(26.939) 2005-06-17 13:12:46.004 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=39972 op='TABLE ACCESS BY INDEX ROWID CLM_DETAIL (cr=13 r=2 w=0 time=37005 us)' STAT #1 id=2 cnt=3 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=12 r=2 w=0 time=36981 us)' STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=9 r=2 w=0 time=36925 us)' STAT #1 id=4 cnt=1 pid=3 pos=1 obj=39833 op='TABLE ACCESS BY INDEX ROWID CLAIM_FORM (cr=5 r=2 w=0 time=36861 us)' STAT #1 id=5 cnt=1 pid=4 pos=1 obj=39836 op='INDEX RANGE SCAN X04CLAIM_FORM
  (cr=4 r=2 w=0 time=36814 us)'
  STAT #1 id=6 cnt=1 pid=3 pos=2 obj=39823 op='TABLE ACCESS BY INDEX ROWID CLAIM (cr=4 r=0 w=0 time=42 us)' STAT #1 id=7 cnt=1 pid=6 pos=1 obj=39824 op='INDEX RANGE SCAN XPKCLAIM (cr=3 r=0 w=0 time=29 us)' STAT #1 id=8 cnt=1 pid=2 pos=2 obj=39975 op='INDEX RANGE SCAN XPKCLM_DETAIL
  (cr=3 r=0 w=0 time=42 us)'
  

  ---

  With system stats:
• 2005-06-17 13:14:13.781 WAIT #1: nam='SQL*Net message from client' ela= 11487200 p1=1413697536 p2=1 p3=0 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=39972 op='TABLE ACCESS BY INDEX ROWID CLM_DETAIL (cr=12 r=0 w=0 time=242 us)' STAT #1 id=2 cnt=3 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=11 r=0 w=0 time=223 us)' STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=8 r=0 w=0 time=151 us)' STAT #1 id=4 cnt=1 pid=3 pos=1 obj=39833 op='TABLE ACCESS BY INDEX ROWID CLAIM_FORM (cr=4 r=0 w=0 time=102 us)' STAT #1 id=5 cnt=1 pid=4 pos=1 obj=39836 op='INDEX RANGE SCAN X04CLAIM_FORM
  (cr=3 r=0 w=0 time=59 us)'
  STAT #1 id=6 cnt=1 pid=3 pos=2 obj=39823 op='TABLE ACCESS BY INDEX ROWID CLAIM (cr=4 r=0 w=0 time=42 us)' STAT #1 id=7 cnt=1 pid=6 pos=1 obj=39824 op='INDEX RANGE SCAN XPKCLAIM (cr=3 r=0 w=0 time=25 us)' STAT #1 id=8 cnt=1 pid=2 pos=2 obj=39975 op='INDEX RANGE SCAN XPKCLM_DETAIL
  (cr=3 r=0 w=0 time=60 us)'
  

  ---

  This combined with the fact that autotrace reported that both cases issued exactly the same number of logical reads might lead one to question if autotrace and 10053 are telling the truth. Autotrace has been known to fib. Range scans of two different indexes yielding exactly the same number of logical reads is mildly suspicious, although quite possible. Either the 10046 traces are not correct (perhaps a mistake was made in collecting them?) or autotrace and 10053 are fibbing (or something else changed between collecting the 10053 and 10046 traces that we're not aware of). John Smiley Technical Management Consultant TUSC, Inc

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