4 Replies Latest reply on Aug 25, 2009 6:04 AM by 649748

    PIO is more than LIO althought PIO is 'db file scattered read'

    649748
      I ran a sql like
       select /*+ index(test,test_ix) */ count(*) from test where a>10000 and a<20000 and b=100
      The 10046 raw trace file is like the following. The 'db file scattered read' is used on index, I think that's because of index block prefetch, it's fine to me, but the strange thing is the stats as "cr=44 pr=128", since the blocks read by scattered read which will go thought SGA, which cr is smaller than pr.?
      PARSING IN CURSOR #18 len=89 dep=0 uid=25 oct=3 lid=25 tim=50313956033685 hv=479173677 ad='d4f8d268'
      
       select /*+ index(test,test_ix) */ count(*) from test where a>10000 and a<20000 and b=100
      
      END OF STMT
      
      PARSE #18:c=0,e=209,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=50313956033674
      
      BINDS #18:
      
      EXEC #18:c=0,e=195,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=50313956034157
      
      WAIT #18: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=79213 tim=50313956034270
      
      MBR: get old_reading=-32 new_reading=0, pw=1
      
      MBR: Begin ispf=0, cnt=0, pw=1, mx=128, l=63, r=95, slid=63, nmb=32, lim=0, nc=0, pdb=40, pidx=680168458
      
      MBR: got bufs pf=0, cnt=0, pw=1, mx=128, l=63, r=95, maxr=95, slid=63, nmbr=32
      
      WAIT #18: nam='db file scattered read' ela= 3037 file#=1185 block#=691209 blocks=32 obj#=79213 tim=50313956038055 
      
      MBR: free old_reading=32 new_reading=0, pw=1
      
      MBR: get old_reading=-32 new_reading=0, pw=1
      
      MBR: Begin ispf=0, cnt=0, pw=1, mx=128, l=47, r=79, slid=47, nmb=32, lim=0, nc=0, pdb=40, pidx=680130074
      
      MBR: got bufs pf=0, cnt=0, pw=1, mx=128, l=47, r=79, maxr=79, slid=47, nmbr=32
      
      WAIT #18: nam='db file scattered read' ela= 2419 file#=1185 block#=652809 blocks=32 obj#=79212 tim=50313956041702
      
      MBR: free old_reading=32 new_reading=0, pw=1
      
      MBR: get old_reading=-32 new_reading=0, pw=1
      
      MBR: Begin ispf=0, cnt=0, pw=1, mx=128, l=64, r=96, slid=64, nmb=32, lim=0, nc=0, pdb=40, pidx=680168489
      
      MBR: got bufs pf=0, cnt=0, pw=1, mx=128, l=64, r=96, maxr=96, slid=64, nmbr=32
      
      WAIT #18: nam='db file scattered read' ela= 2319 file#=1185 block#=691241 blocks=32 obj#=79213 tim=50313956060650
      
      MBR: free old_reading=32 new_reading=0, pw=1
      
      MBR: get old_reading=-32 new_reading=0, pw=1
      
      MBR: Begin ispf=0, cnt=0, pw=1, mx=128, l=64, r=96, slid=64, nmb=32, lim=0, nc=0, pdb=40, pidx=680130089
      
      MBR: got bufs pf=0, cnt=0, pw=1, mx=128, l=64, r=96, maxr=96, slid=64, nmbr=32
      
      WAIT #18: nam='db file scattered read' ela= 2320 file#=1185 block#=652841 blocks=32 obj#=79212 tim=50313956078972 
      MBR: free old_reading=32 new_reading=0, pw=1
      
      FETCH #18:c=50000,e=52670,p=128,cr=44,cu=0,mis=0,r=1,dep=0,og=3,tim=50313956087058
      
      WAIT #18: nam='SQL*Net message from client' ela= 446 driver id=1650815232 #bytes=1 p3=0 obj#=79212 tim=50313956087715
      
      FETCH #18:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=50313956087834
      
      WAIT #18: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=79212 tim=50313956087932
      
      *** 2009-07-02 22:53:01.378
      
      WAIT #18: nam='SQL*Net message from client' ela= 146920321 driver id=1650815232 #bytes=1 p3=0 obj#=79212 tim=50314103008351
      
      XCTEND rlbk=0, rd_only=1
      
      STAT #18 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=44 pr=128 pw=0 time=52684 us)'
      
      STAT #18 id=2 cnt=9999 pid=1 pos=1 obj=79212 op='TABLE ACCESS BY INDEX ROWID TEST (cr=44 pr=128 pw=0 time=47936 us)'
      
      STAT #18 id=3 cnt=9999 pid=2 pos=1 obj=79213 op='INDEX RANGE SCAN TEST_IX (cr=25 pr=64 pw=0 time=14352 us)'