[20201208]为什么返回2行记录补充.txt

来源：这里教程网时间：2026-03-03 16:18:18 作者：

[20201208]为什么返回2行记录补充.txt --//在itpub问的问题，http://www.itpub.net/thread-2140116-1-1.html。花了一点点时间，大概知道问题在哪里， --//问题出在一致性读取上，参考链接http://blog.itpub.net/267265/viewspace-2213824/ =>[20180907]访问v$视图与一致性读取.txt 1.问题提出： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> select sid from v$mystat where rownum=1; SID ---------- 32 SCOTT@book> select sid ,osuser from v$session where sid = 32; SID OSUSER ---------- ------------------------------ 32 oracle --//返回1行.如果加入提示。 SCOTT@book> alter session set statistics_level = all; Session altered. SCOTT@book> select /*+ leading(s e w) */ sid ,osuser from v$session where sid = 32; SID OSUSER ---------- ------------------------------ 32 oracle 32 oracle SCOTT@book> select /*+ leading(s e w) */ sid ,osuser,EVENT#,event from v$session where sid = 32; SID OSUSER EVENT# EVENT ---------- ------------------------------ ---------- ---------------------------------------- 32 oracle 350 SQL*Net message to client 32 oracle 354 SQL*Net message from client --//以上是我昨天的测试，可能一些细节我没有讲清楚，实际上几个表的连接使用nested loop，这样就导致出现返回2行的情况。 --//x$视图oracle无法保证查询的一致性的。在nested loop连接时先连接出现等待事件是SQL*Net message to client，显示后再 --//出现SQL*Net message from client等待事件，这样看到的结果就是2行。 2.如果我在加入提示USE_MERGE： SCOTT@book> select sid from v$mystat where rownum=1; SID ---------- 325 SCOTT@book> select /*+ leading(e s w) USE_MERGE( W@SEL$4) */ sid ,osuser,EVENT#,event from v$session where sid = 325; SID OSUSER EVENT# EVENT ---------- ------------------------------ ---------- ---------------------------------------- 325 oracle 350 SQL*Net message to client SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID argukfafpgxwf, child number 0 ------------------------------------- select /*+ leading(e s w) USE_MERGE( W@SEL$4) */ sid ,osuser,EVENT#,event from v$session where sid = 325 Plan hash value: 111210288 --------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | | | | | 1 | MERGE JOIN | | 1 | 1 | 142 | 2 (100)| 00:00:01 | 1 |00:00:00.01 | | | | | 2 | MERGE JOIN CARTESIAN | | 1 | 1 | 116 | 0 (0)| | 352 |00:00:00.01 | | | | | 3 | FIXED TABLE FULL | X$KSLED | 1 | 100 | 4700 | 0 (0)| | 352 |00:00:00.01 | | | | | 4 | BUFFER SORT | | 352 | 1 | 69 | 0 (0)| | 352 |00:00:00.01 | 2048 | 2048 | 2048 (0)| |* 5 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 1 | 69 | 0 (0)| | 1 |00:00:00.01 | | | | |* 6 | SORT JOIN | | 352 | 1 | 26 | 1 (100)| 00:00:01 | 1 |00:00:00.01 | 2048 | 2048 | 2048 (0)| |* 7 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 | 1 | 26 | 0 (0)| | 1 |00:00:00.01 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$88122447 3 - SEL$88122447 / E@SEL$4 5 - SEL$88122447 / S@SEL$4 7 - SEL$88122447 / W@SEL$4 Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(("S"."INDX"=325 AND "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)) 6 - access("S"."INDX"="W"."KSLWTSID" AND "W"."KSLWTEVT"="E"."INDX") filter(("W"."KSLWTEVT"="E"."INDX" AND "S"."INDX"="W"."KSLWTSID")) 7 - filter("W"."KSLWTSID"=325) --//你可以发现使用USE_MERGE( W)无效，而必须加入别名USE_MERGE( W@SEL$4) ，你这样看到就是返回1行。因为使用USE_MERGE要排序 --//再输出，这样就看到第一个SQL*Net message to client等待事件。 3.继续测试： --//12c的sqlplus提供设置rowprefetch的功能，实际上11g缺省就是1，而12c sqlplus客户端可以提供参数rowprefetch修改它。 --//也就是在11g下你使用10046跟踪，你会看到有一个fetch r=1的情况。参考链接： http://blog.itpub.net/267265/viewspace-2712755/=>[20200818]12c 10046跟踪时间戳.txt --//检查跟踪文件: ===================== PARSING IN CURSOR #646021600 len=19 dep=0 uid=81 oct=3 lid=81 tim=2572120902 hv=4071881952 ad='7ff11e35c78' sqlid='4g0qfgmtb7z70' select * from dept END OF STMT PARSE #646021600:c=0,e=238,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3383998547,tim=2572120899 EXEC #646021600:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3383998547,tim=2572121955 WAIT #646021600: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2572122196 FETCH #646021600:c=0,e=502,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=3383998547,tim=2572122875 ~~~~~~~~~~~~~~~~=> r=1 WAIT #646021600: nam='SQL*Net message from client' ela= 6393 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2572129463 WAIT #646021600: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2572129793 FETCH #646021600:c=0,e=241,p=0,cr=4,cu=0,mis=0,r=3,dep=0,og=1,plh=3383998547,tim=2572129980 ~~~~~~~~~~~~~~~~=> r=3 STAT #646021600 id=1 cnt=4 pid=0 pos=1 obj=22487 op='TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 str=1 time=242 us cost=3 size=80 card=4)' --//我使用12c的sqlplus作为客户端连接数据库，修改rowprefetch参数呢？ SCOTT@78> show sqlpluscompatibility sqlpluscompatibility 12.2.0 SCOTT@78> select sid from v$mystat where rownum=1; SID ---------- 32 SCOTT@78> set rowprefetch 2 SCOTT@78> select /*+ leading(e s w) */ sid ,osuser,EVENT#,event from v$session where sid = 32; SID OSUSER EVENT# EVENT ---------- -------------------- ---------- ------------------------------ 32 Administrator 350 SQL*Net message to client --//^_^，神奇般的返回1行。 SCOTT@78> set rowprefetch 1 SCOTT@78> select /*+ leading(e s w) */ sid ,osuser,EVENT#,event from v$session where sid = 32; SID OSUSER EVENT# EVENT ---------- -------------------- ---------- ------------------------------ 32 Administrator 350 SQL*Net message to client 32 Administrator 354 SQL*Net message from client --//^_^，神奇般的返回2行。 4.总结： --//我仅仅想通过一些例子加深自己的学习与理解。