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