I am having doubt regardingthe Fetch calls which we see in the SQl monitor plan and the one which we are seeing in the data dictionary, fetches_delta or fetches_total in dba_hist_sqlstat.
What i found , for one query i am seeing the fetch calls as ~242121, and total record out of the query is 88Million, so it means at client side we have fetch size set, somewhat close to 88million/242121 = ~360, that is nothing but arraysize at sqlplus. Please correct if i am wrong.
Fetch Calls : 242121
SQL Plan Monitoring Details (Plan Hash Value=271033473)
==============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
==============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 9039 | +10 | 1 | 88M | | | 10.89 | Cpu (421) |
For same query i see in dba_hist_sqlstats SUM (fetches_delta) / (SUM (executions_delta) is coming around ~100 for all the execution , so how is this different from the other one.
Another question is , as we have fetch size>100, shouldnot i see lots of "cell multi block physical read" rather "cell single block physical read"? Why i am seeing most wait evets for this as cell single block physical read?