杏悦2主管2540437[20201208]为什么返回2行记录补充.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.总结：  --//我仅仅想通过一些例子加深自己的学习与理解。