当前位置:网站首页>[20201208]为什么返回2行记录补充.txt

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

2020-12-08 12:07:13 程序猿欧文

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

--//在itpub问的问题 =>[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         .........

版权声明
本文为[程序猿欧文]所创,转载请带上原文链接,感谢
https://my.oschina.net/mikeowen/blog/4780838