Why dose Oracle have 2 fetches for 1 exectuion on dual?(zt)-CSDN博客

上次过去Rudolf问我的问题, 一时不知怎么回答, 其实我很少使用trprof进行诊断, 只是在使用执行计划与相关系统信息都不能找出较优或者无法找到问题的时候, 才会考虑使用tkprof来解决问题. 这位老兄的帖子刚好解决了我的疑惑.. 就直接引用过来了:-)

如有版权问题, 请联系本人^_^

Why dose Oracle have 2 fetches for 1 exectuion on dual?

Have you ever noticed that there are 2 fetches for 1 exectuion of such simple sql

SQL>select * from dual;

Let's turn on the 10046 event and check the trace file.

PARSING IN CURSOR #1 len=19 dep=0 uid=869 oct=3 lid=869 tim=3996827602 hv=3499509676 ad='19637558'
select * from dual
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=3996827602
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=3996827602
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=3,tim=3996827602
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=3996827602
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0

As you can see, there are 2 fetches. The first fetch returns 1 row and the second returns 0 row.

Oracle's pre-fetch behaviour explains this. For Oracle 8/9, it will prefetch 1 row. So, even the dual only has 1 row, you can see 2 fetches for 1 exectuion.

Let's go furthermore.

SQL> show arraysize
arraysize 15

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select * from mytab where rownum<=32;

32 rows selected.

PARSING IN CURSOR #1 len=37 dep=0 uid=869 oct=3 lid=869 tim=3996829833 hv=76520296 ad='1efedad0'
select * from mytab where rownum<=32
END OF STMT
PARSE #1:c=2,e=2,p=0,cr=46,cu=3,mis=1,r=0,dep=0,og=3,tim=3996829833
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=3996829833
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=5,cu=12,mis=0,r=1,dep=0,og=3,tim=3996829833
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=3,tim=3996829833
WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=1,e=0,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=3,tim=3996829834
WAIT #1: nam='SQL*Net message from client' ela= 22 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=3,tim=3996829856
WAIT #1: nam='SQL*Net message from client' ela= 743 p1=1650815232 p2=1 p3=0
STAT #1 id=1 cnt=32 pid=0 pos=0 obj=0 op='COUNT STOPKEY '
STAT #1 id=2 cnt=32 pid=1 pos=1 obj=425662 op='TABLE ACCESS FULL MYTAB '
XCTEND rlbk=0, rd_only=1

Explaination:

The first fetch returns 1 row, because it's Oracle's prefetch.
The second and third fetch return 15 rows respectively, because the arraysize is 15.
The fouth fetch returns 1 row, because that's the last row we need to fetch.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-796421/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-796421/