[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.總結：
--//我僅僅想通過一些例子加深自己的學習與理解，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/231896.html
標籤：其他
下一篇：Mysql創建定時任務