[20180905]lob與direct path read.txt
[20180905]lob與direct path read.txt
--//如果表有存在lob欄位,通常採用直接路徑讀取.
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> @ ev_name "direct path read"
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS DISPLAY_NAME CON_ID
------ ---------- --------------------- ----------- ---------- ---------- ------------- ----------- ---------- --------------------- ------
204 3926164927 direct path read file number first dba block cnt 1740759767 8 User I/O direct path read 0
205 861319509 direct path read temp file number first dba block cnt 1740759767 8 User I/O direct path read temp 0
SCOTT@test01p> @ ev_name direct%lob
no rows selected
--//沒有單獨direct path read/write(lob)等待事件,我開始以為lob會單獨區別開來.
create table t1 as select rownum id from dual connect by level<=100;
create table t2 (id number ,text clob);
insert into t2 select rownum id,lpad('a',5000,'a') text from dual connect by level<=100;
--//注:我使用12c,可以插入大於4000的字元.
commit;
--//分析表略.
2.測試:
SCOTT@test01p> alter system flush buffer_cache;
System altered.
@ 10046on 12
select * from t1,t2 where t1.id = t2.id ;
@ 10046off
3.檢查跟蹤檔案:
--//擷取一段:
=====================
PARSING IN CURSOR #395065056 len=40 dep=0 uid=109 oct=3 lid=109 tim=3316897002 hv=430785427 ad='7ff1ec5a3e0' sqlid='13a4sa4cuuhwm'
select * from t1,t2 where t1.id = t2.id
END OF STMT
PARSE #395065056:c=15600,e=4362,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1838229974,tim=3316896999
EXEC #395065056:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1838229974,tim=3316897592
WAIT #395065056: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3316897848
WAIT #395065056: nam='SQL*Net message from client' ela= 27109 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3316925177
WAIT #395065056: nam='Disk file operations I/O' ela= 178 FileOperation=2 fileno=9 filetype=2 obj#=107979 tim=3316925953
WAIT #395065056: nam='db file sequential read' ela= 18341 file#=9 block#=202 blocks=1 obj#=107979 tim=3316944366
WAIT #395065056: nam='db file sequential read' ela= 409 file#=9 block#=203 blocks=1 obj#=107979 tim=3316945039
WAIT #395065056: nam='db file sequential read' ela= 410 file#=9 block#=210 blocks=1 obj#=107980 tim=3316946295
WAIT #395065056: nam='db file scattered read' ela= 499 file#=9 block#=211 blocks=5 obj#=107980 tim=3316946928
WAIT #395065056: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=107980 tim=3316947083
FETCH #395065056:c=0,e=21773,p=8,cr=10,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3316947149
WAIT #395065056: nam='SQL*Net message from client' ela= 223 driver id=1413697536 #bytes=1 p3=0 obj#=107980 tim=3316947449
WAIT #0: nam='direct path read' ela= 149 file number=9 first dba=247 block cnt=1 obj#=107981 tim=3317031776
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=107981 tim=3317032377
WAIT #0: nam='SQL*Net vector data to client' ela= 369 driver id=1413697536 #bytes=8060 p3=2 obj#=107981 tim=3317032816
WAIT #0: nam='SQL*Net vector data to client' ela= 61 driver id=1413697536 #bytes=1940 p3=2 obj#=107981 tim=3317032980
LOBREAD: c=0,e=85538,p=2,cr=1,cu=0,tim=3317033061
WAIT #0: nam='SQL*Net message from client' ela= 24719 driver id=1413697536 #bytes=1 p3=0 obj#=107981 tim=3317057879
WAIT #0: nam='direct path read' ela= 342 file number=9 first dba=243 block cnt=1 obj#=107981 tim=3317068651
LOBREAD: c=0,e=11012,p=1,cr=1,cu=0,tim=3317069214
--//注意看下劃線內容,可以發現WAIT #0: 怪不得在對應sql_id看不到lob端的直接路徑讀.
SCOTT@test01p> SELECT * FROM V$ACTIVE_SESSION_HISTORY WHERE sample_time >= '2018-09-04 20:50:56' AND event = 'direct path read';
no rows selected
--//awr竟然沒有抓到.
D:\tools\rlwrap>grep -i "direct path read" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_3992.trc | head -4
grep -i "direct path read" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_3992.trc | head -4
WAIT #0: nam='direct path read' ela= 149 file number=9 first dba=247 block cnt=1 obj#=107981 tim=3317031776
WAIT #0: nam='direct path read' ela= 342 file number=9 first dba=243 block cnt=1 obj#=107981 tim=3317068651
WAIT #0: nam='direct path read' ela= 170 file number=9 first dba=246 block cnt=1 obj#=107981 tim=3317105728
WAIT #0: nam='direct path read' ela= 8544 file number=9 first dba=439 block cnt=1 obj#=107981 tim=3317119670
SCOTT@test01p> @ which_obj 9 247
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT SYS_LOB0000107980C00 LOBSEGMENT USERS 0 9 232 131072 16 9
002$$
SCOTT@test01p> SCOTT@test01p> select segment_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in (select segment_name from DBA_LOBS where table_name='T2' and owner=user);
SEGMENT_NAME GB
-------------------- ----------
SYS_LOB0000107980C00 .003051758
002$$
--//能對上沒有問題.
--//檢視生成的tkpfor報表:
********************************************************************************
SQL ID: 13a4sa4cuuhwm Plan Hash: 1838229974
select *
from
t1,t2 where t1.id = t2.id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 101 0.03 0.05 8 109 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 103 0.04 0.05 8 109 0 100
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100 100 100 HASH JOIN (cr=109 pr=8 pw=0 time=21697 us cost=6 size=9300 card=100)
100 100 100 TABLE ACCESS FULL T1 (cr=3 pr=2 pw=0 time=19474 us cost=3 size=300 card=100)
100 100 100 TABLE ACCESS FULL T2 (cr=106 pr=6 pw=0 time=1190 us cost=3 size=9000 card=100)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 102 0.00 0.00
SQL*Net message from client 102 8.85 8.92
Disk file operations I/O 1 0.00 0.00
db file sequential read 3 0.01 0.01
db file scattered read 1 0.00 0.00
********************************************************************************
--//可以發現對應sql語句也沒有對應的direct path read.
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 101 0.03 0.05 8 109 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 105 0.04 0.05 8 109 0 100
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 303 0.00 0.00
SQL*Net message from client 303 8.85 20.52
Disk file operations I/O 1 0.00 0.00
db file sequential read 3 0.01 0.01
db file scattered read 1 0.00 0.00
direct path read 199 0.01 0.10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL*Net vector data to client 200 0.00 0.08
D:\tools\rlwrap>grep -i "direct path read" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_3992.trc | wc
199 2985 21498
--//為什麼會是199次,有點意外.測了幾次數量都變化,不過我插入的lpad('a',5000,'a'),儲存資訊與安裝字符集定義有關.
--//我的定義NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK.這樣佔2塊.
D:\tools\rlwrap>grep -i "direct path read" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_3992.trc | cut -f11 -d" " | sort | uniq -c
1 dba=243
1 dba=246
1 dba=247
2 dba=439
1 dba=440
1 dba=441
1 dba=442
1 dba=443
...
1 dba=633
1 dba=634
1 dba=635
1 dba=636
1 dba=637
1 dba=638
D:\tools\rlwrap>grep -i "FETCH" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_3992.trc
grep -i "FETCH" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_3992.trc
FETCH #395065056:c=0,e=21773,p=8,cr=10,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3316947149
FETCH #395065056:c=0,e=446,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317076447
FETCH #395065056:c=0,e=320,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317104531
FETCH #395065056:c=0,e=156,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317137430
FETCH #395065056:c=0,e=278,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317163755
FETCH #395065056:c=0,e=536,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317192693
FETCH #395065056:c=0,e=284,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317219213
FETCH #395065056:c=0,e=212,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317247117
FETCH #395065056:c=0,e=73,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317274761
FETCH #395065056:c=0,e=239,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317304771
...
FETCH #395065056:c=0,e=225,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3327492053
FETCH #395065056:c=0,e=162,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1838229974,tim=3327631486
D:\tools\rlwrap>grep -i "FETCH" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_3992.trc |wc
101 202 9302
--//可以發現r=1.這也是為什麼訪問lob慢的原因,每次fetch 1行.最後一次fetch r=0.
FETCH #395065056:c=0,e=21773,p=8,cr=10,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3316947149
WAIT #395065056: nam='SQL*Net message from client' ela= 223 driver id=1413697536 #bytes=1 p3=0 obj#=107980 tim=3316947449
WAIT #0: nam='direct path read' ela= 149 file number=9 first dba=247 block cnt=1 obj#=107981 tim=3317031776
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=107981 tim=3317032377
WAIT #0: nam='SQL*Net vector data to client' ela= 369 driver id=1413697536 #bytes=8060 p3=2 obj#=107981 tim=3317032816
WAIT #0: nam='SQL*Net vector data to client' ela= 61 driver id=1413697536 #bytes=1940 p3=2 obj#=107981 tim=3317032980
LOBREAD: c=0,e=85538,p=2,cr=1,cu=0,tim=3317033061
WAIT #0: nam='SQL*Net message from client' ela= 24719 driver id=1413697536 #bytes=1 p3=0 obj#=107981 tim=3317057879
WAIT #0: nam='direct path read' ela= 342 file number=9 first dba=243 block cnt=1 obj#=107981 tim=3317068651
LOBREAD: c=0,e=11012,p=1,cr=1,cu=0,tim=3317069214
WAIT #0: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=107981 tim=3317069372
WAIT #0: nam='SQL*Net message from client' ela= 6160 driver id=1413697536 #bytes=1 p3=0 obj#=107981 tim=3317075637
WAIT #395065056: nam='SQL*Net message to client' ela= 11 driver id=1413697536 #bytes=1 p3=0 obj#=107981 tim=3317076216
FETCH #395065056:c=0,e=446,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=1838229974,tim=3317076447
--//想起以前的測試,連結:http://blog.itpub.net/267265/viewspace-1760052/=>[20150803]無法透過sql_id找到sql語句2.txt
SCOTT@test01p> select object_id,data_object_id from dba_objects where owner=user and object_name='T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
107980 107980
SCOTT@test01p> @ 10to16 107980
10 to 16 HEX REVERSE16
----------------- ------------------------------
000000000001a5cc 0xcca50100-00000000
SCOTT@test01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
233 257 5808:4024 DEDICATED 3992 39 31 alter system kill session '233,257' immediate;
SCOTT@test01p> select * from V$OPEN_CURSOR where sid=233 and sql_text like 'table%\_1a5cc%' escape '\';
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE CHILD_ADDRESS CON_ID
---------------- --- --------- ---------------- ---------- ------------- --------------------- ------------------- ----------- ----------- ---------------- ----------
000007FF306D61C0 233 SCOTT 000007FF1EC446D8 1102864567 3qnfp350vss5r table_4_9_1a5cc_0_0_0 OPEN 000007FF1EC43468 3
--//ADDRESS=000007FF1EC446D8,對應父遊標控制程式碼地址.
--//CHILD_ADDRESS=000007FF1EC43468,對應子游標控制程式碼地址.
SYS@test> @ sharepool/shp4 3qnfp350vss5r 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- --------------------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000007FF1EC43468 000007FF1EC446D8 table_4_9_1a5cc_0_0_ 1 2 1 000007FF25E2F9B8 000007FF1EC43D58 8144 4032 3102 15278 15278 1102864567 3qnfp350vss5r 0
父遊標控制程式碼地址 000007FF1EC446D8 000007FF1EC446D8 table_4_9_1a5cc_0_0_ 1 0 1 000007FF1EC44620 00 4072 0 0 4072 4072 1102864567 3qnfp350vss5r 65535
--//這樣就很好理解我前面診斷連結http://blog.itpub.net/267265/viewspace-2213256/,為什麼檢視sql_id看不到lob段的direct path read.
3.實際上lob欄位可以放入資料快取的,修改lob屬性cache或者cache read就ok了.
SCOTT@test01p> ALTER TABLE T2 MODIFY LOB (TEXT) ( CACHE);
Table altered.
--//這樣讀寫都可以進入資料快取,如果修改ALTER TABLE T2 MODIFY LOB (TEXT) ( CACHE READS);注意read後面有1個S.
--//僅僅讀進入資料快取,寫入依舊走direct path write.
SCOTT@test01p> ALTER TABLE T2 MODIFY LOB (TEXT) ( CACHE READs);
Table altered.
SCOTT@test01p> ALTER TABLE T2 MODIFY LOB (TEXT) ( CACHE );
Table altered.
--//oracle預設不這樣定義,主要是避免大量消耗資料快取.如果你這些物件頻繁讀取,可以根據需要修改.
--//退出會話重新測試:
alter system flush buffer_cache;
@ 10046on 12
select * from t1,t2 where t1.id = t2.id ;
@ 10046off
D:\tools\rlwrap>grep "direct path read" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_2768.trc
grep "direct path read" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_2768.trc
--//可以發現cache後沒有再出現direct path read等待事件.
SYS@test> @ bh 9 243
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
000007FF2F096DE8 9 243 1 data block xcur 1 0 0 0 0 0 000007FF1B520000
SYS@test01p> @ bh 9 247
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
000007FF2F1E3868 9 247 1 data block xcur 1 0 0 0 0 0 000007FF19E60000 SYS_LOB0000107980C00
002$$
--//可以lob段進入資料快取.前面@ bh 9 243看不到OBJECT_NAME,是因為pdb的原因.
SCOTT@test01p> @ which_obj 9 243
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT SYS_LOB0000107980C00 LOBSEGMENT USERS 0 9 232 131072 16 9
002$$
4.在測試結束,我突然想起一些工具比如toad,在data grid裡面執行時可能並不訪問lob.繼續測試:
SCOTT@test01p> ALTER TABLE T2 MODIFY LOB (TEXT) ( noCACHE );
Table altered.
SCOTT@test01p> alter system flush buffer_cache;
System altered.
--//在toad下執行:
begin sys.dbms_monitor.session_trace_enable(231,1653,true,true); end;
select * from t1,t2 where t1.id = t2.id;
begin sys.dbms_monitor.session_trace_disable(231,1653); end;
--//檢查轉儲:
begin sys.dbms_monitor.session_trace_enable(231,1653,true,true); end;
=====================
PARSING IN CURSOR #688704480 len=39 dep=0 uid=109 oct=3 lid=109 tim=9446964636 hv=3964999012 ad='7ff25d5db20' sqlid='5fx1rnbq5a5b4'
select * from t1,t2 where t1.id = t2.id
END OF STMT
PARSE #688704480:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1838229974,tim=9446964636
EXEC #688704480:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1838229974,tim=9446964761
WAIT #688704480: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=28 tim=9446964824
WAIT #688704480: nam='SQL*Net message from client' ela= 1055 driver id=1413697536 #bytes=1 p3=0 obj#=28 tim=9446965916
WAIT #688704480: nam='db file sequential read' ela= 5806 file#=9 block#=202 blocks=1 obj#=107979 tim=9446971899
WAIT #688704480: nam='db file sequential read' ela= 339 file#=9 block#=203 blocks=1 obj#=107979 tim=9446972418
WAIT #688704480: nam='db file sequential read' ela= 363 file#=9 block#=210 blocks=1 obj#=107980 tim=9446973203
WAIT #688704480: nam='db file scattered read' ela= 495 file#=9 block#=211 blocks=5 obj#=107980 tim=9446973863
WAIT #688704480: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=107980 tim=9446974045
WAIT #688704480: nam='SQL*Net more data to client' ela= 86 driver id=1413697536 #bytes=8102 p3=0 obj#=107980 tim=9446974245
FETCH #688704480:c=15601,e=8382,p=8,cr=10,cu=0,mis=0,r=100,dep=0,og=1,plh=1838229974,tim=9446974357
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STAT #688704480 id=1 cnt=100 pid=0 pos=1 obj=0 op='HASH JOIN (cr=10 pr=8 pw=0 time=8447 us cost=6 size=9300 card=100)'
STAT #688704480 id=2 cnt=100 pid=1 pos=1 obj=107979 op='TABLE ACCESS FULL T1 (cr=3 pr=2 pw=0 time=6489 us cost=3 size=300 card=100)'
STAT #688704480 id=3 cnt=100 pid=1 pos=2 obj=107980 op='TABLE ACCESS FULL T2 (cr=7 pr=6 pw=0 time=1395 us cost=3 size=9000 card=100)'
*** 2018-09-04 22:33:09.756
WAIT #688704480: nam='SQL*Net message from client' ela= 3534786 driver id=1413697536 #bytes=1 p3=0 obj#=107980 tim=9450509316
=====================
PARSING IN CURSOR #816744024 len=60 dep=0 uid=109 oct=47 lid=109 tim=9450510050 hv=2791809845 ad='7ff23b373b0' sqlid='7frv1cum6g8tp'
begin sys.dbms_monitor.session_trace_disable(231,1653); end;
END OF STMT
--//可以發現fetch r=100,也說明在toad下沒有提取lob欄位內容(特別在資料段外,我覺得資料段內會訪問,這樣才能獲得locate資訊).沒有發現direct path read 等待事件.
--//如果透過toad選單,view=>toad options=>Data Grids 可以發現Preview CLOB and LONG data沒有選上.
--//如果選上重複測試.toad下與sqplus操作方式存在不同:
--//測試注意一個細節:要選擇auto trace或者執行後拖動到最後1條.這樣才算執行完成,不然實際上僅僅訪問前面幾條記錄.
=====================
=====================
PARSING IN CURSOR #381342464 len=39 dep=0 uid=109 oct=3 lid=109 tim=4113882451 hv=3964999012 ad='7ff1f96bac0' sqlid='5fx1rnbq5a5b4'
select * from t1,t2 where t1.id = t2.id
END OF STMT
PARSE #381342464:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1838229974,tim=4113882450
WAIT #381342464: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4113882660
WAIT #381342464: nam='SQL*Net message from client' ela= 443 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4113883160
CLOSE #381342464:c=0,e=10,dep=0,type=1,tim=4113883235
PARSE #381342464:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1838229974,tim=4113883301
EXEC #381342464:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1838229974,tim=4113883384
WAIT #381342464: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4113883469
WAIT #381342464: nam='SQL*Net message from client' ela= 1785 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4113885309
WAIT #381342464: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4113886168
WAIT #381342464: nam='SQL*Net more data to client' ela= 127 driver id=1413697536 #bytes=8102 p3=0 obj#=-1 tim=4113886456
FETCH #381342464:c=0,e=1138,p=0,cr=10,cu=0,mis=0,r=100,dep=0,og=1,plh=1838229974,tim=4113886559
STAT #381342464 id=1 cnt=100 pid=0 pos=1 obj=0 op='HASH JOIN (cr=10 pr=0 pw=0 time=1229 us cost=6 size=9300 card=100)'
STAT #381342464 id=2 cnt=100 pid=1 pos=1 obj=107979 op='TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=138 us cost=3 size=300 card=100)'
STAT #381342464 id=3 cnt=100 pid=1 pos=2 obj=107980 op='TABLE ACCESS FULL T2 (cr=7 pr=0 pw=0 time=247 us cost=3 size=9000 card=100)'
WAIT #381342464: nam='SQL*Net message from client' ela= 27884 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4113914744
--//可以發現fetch r=100
D:\app\oracle\diag\rdbms\test\test\trace>grep -i "direct path read" test_ora_5844.trc|head -10
WAIT #0: nam='direct path read' ela= 50598 file number=9 first dba=247 block cnt=1 obj#=107981 tim=4113966551
WAIT #0: nam='direct path read' ela= 283 file number=9 first dba=244 block cnt=2 obj#=107981 tim=4113971148
WAIT #0: nam='direct path read' ela= 271 file number=9 first dba=246 block cnt=1 obj#=107981 tim=4113975251
WAIT #0: nam='direct path read' ela= 29137 file number=9 first dba=439 block cnt=1 obj#=107981 tim=4114005099
WAIT #0: nam='direct path read' ela= 426 file number=9 first dba=440 block cnt=2 obj#=107981 tim=4114009080
WAIT #0: nam='direct path read' ela= 284 file number=9 first dba=442 block cnt=2 obj#=107981 tim=4114013304
WAIT #0: nam='direct path read' ela= 322 file number=9 first dba=444 block cnt=2 obj#=107981 tim=4114017174
WAIT #0: nam='direct path read' ela= 289 file number=9 first dba=446 block cnt=2 obj#=107981 tim=4114020944
WAIT #0: nam='direct path read' ela= 31560 file number=9 first dba=448 block cnt=2 obj#=107981 tim=4114056440
WAIT #0: nam='direct path read' ela= 50977 file number=9 first dba=498 block cnt=2 obj#=107981 tim=4114111350
D:\app\oracle\diag\rdbms\test\test\trace>grep -i "direct path read" test_ora_5844.trc|wc
101 1515 10936
--//說明一下:執行後必須在data grid中選中text欄位,一條一條移動到最後,不然direct path read記數不會這麼多.
--//我測試快速拖最少45次direct path read.
總結:
--//總之:在一些開發工具toad與sqlplus執行存在不同,sqlplus要訪問lob欄位.而toad下可以做到先不訪問lob段外資料.
--//我覺得資料段內要訪問的.不然lob定位資訊無法獲得.
--//而且sqlplus下fetch是1條1條提取,這樣效率很低,而toad是一次提取許多(我以前測試是第1次1001,第二次1000.)
--//參考:http://blog.itpub.net/267265/viewspace-2152038/=>[20180320]toad環境中一次fetch等於多少.
--//lob型別可以修改屬性(cache 或者cache reads),避免direct path read.
--//另外如果在exadata下全表掃描大表(包含lob欄位),不會出現cell smart table scan等待事件,而是direct path read.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2213630/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- direct path read/read temp等待事件事件
- Oracle direct path read相關隱含引數Oracle
- 【TUNE_ORACLE】等待事件之IO等待“direct path write”Oracle事件
- 【TUNE_ORACLE】等待事件之IO等待“direct path read”Oracle事件
- 【TUNE_ORACLE】等待事件之IO等待“direct path write temp”Oracle事件
- Flutter Path(一) ： Path 與 CustomPainterFlutterAI
- 【LOB】Oracle Lob管理常用sqlOracleSQL
- 【LOB】Oracle lob管理常用語句Oracle
- [20181130]control file sequential read.txt
- direct3d---direct11 rendering pipeline3D
- [20181031]lob欄位與布隆過濾.txt
- 《Node.js》path.resolve與path.join的區別與作用Node.js
- [20210208]lob欄位與查詢的問題.txt
- [20181129]大量的control file sequential read.txt
- firewalld:direct規則
- Direct Prompt Injections Pages
- cookie path路徑與domain域CookieAI
- trigger+lob+ora-22275
- LOB欄位相關概念(自動建立LOB索引段和重建索引方法)索引
- RabbitMQ - SpringBoot 案例 - direct 模式MQSpring Boot模式
- [20231008]bbed探究lob段.txt
- 淺析Vue原始碼（九）——VirtualDOM與pathVue原始碼
- Flutter Path(二) ： Path 進階Flutter
- [namespace hdk] 向量 direct_vectornamespace
- 淺談Microsoft Teams Direct RoutingROS
- 【RabbitMQ】direct type exchange example in golangMQGolang
- ORACLE LOB大欄位維護Oracle
- Parsing Direct3D shader bytecode3D
- [20181020]lob欄位的索引段.txt索引
- [BUG反饋]defined('ADDON_PATH') or define('ADDON_PATH', APP_PATH.'Addon');APP
- crontab on raspberry pi, full path, not relative path, is needed.
- Java網路程式設計與NIO詳解8：淺析mmap和Direct BufferJava程式設計
- [20181214]open file using O_DIRECT.txt
- [20190531]lob型別pctversion 和 retention.txt型別
- MySQL8.0 新特性：Partial Update of LOB ColumnMySql
- Python（Path().name）Python
- Path Sum III
- Leetcode Path SumLeetCode