[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.总结:
--//我仅仅想通过一些例子加深自己的学习与理解。

posted @ 2020-12-08 11:25  lfree  阅读(137)  评论(0编辑  收藏  举报