[20180813]sqlplus arraysize設定與SDU.txt
[20180813]sqlplus arraysize設定與SDU.txt
--//別人問的問題如何設定arraysize大小,實際上這個沒有統一標準,設定太小,全表掃描操作邏輯讀很增加.
--//我個人認為:正常標準資料塊8K,一般最大容納的記錄數是736(實際上僅僅734),設定很大完全沒有必要.
--//因為跨塊邏輯讀就會增加.arraysize設定很大邏輯讀不會減少,我個人設定200,而且設定很大,會導致
--//SDU填滿,這樣會出現大量等待事件: SQL*net more data to client.
--//另外我以前在toad 與PLSQLDev下測試fetch與sqlplus不同:
--//連結:http://blog.itpub.net/267265/viewspace-2152038/=>[20180320]toad環境中一次fetch等於多少.txt
--//大致總結一下:
--//1.sqlplus可以這麼理解先提取1行,不輸出.然後提取N行,輸出N行,保留1行在輸出快取.如此迴圈.這裡N=arraysie.
--//2.toad先提取1001行,然後輸出1000行,然後提取1000行,輸出1000行,如此迴圈.
--//3.PLSQLDev先提取100行,然後輸出100行,然後提取100行,輸出100行,如此迴圈.
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> create table t as select * from all_objects order by DBMS_RANDOM.random;
Table created.
2.測試:
--//預設SDU=8192.
SCOTT@test01p> show arraysize
arraysize 200
--//測試前先執行select * from t;減少一些遞迴操作.
@ 10046on 12
select * from t;
@ 10046off
--//檢查轉儲檔案內容:
*** 2018-08-13 21:12:11.398
WAIT #174817080: nam='SQL*Net message from client' ela= 2051025 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431244692
CLOSE #174817080:c=0,e=35,dep=0,type=1,tim=2431245161
=====================
PARSING IN CURSOR #174802008 len=15 dep=0 uid=109 oct=3 lid=109 tim=2431249623 hv=1134051363 ad='7ff23997360' sqlid='89km4qj1thh13'
select * from t
END OF STMT
PARSE #174802008:c=15600,e=4307,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1601196873,tim=2431249619
EXEC #174802008:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=2431279382
WAIT #174802008: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431279560
WAIT #174802008: nam='direct path read' ela= 15529 file number=9 first dba=2963 block cnt=13 obj#=107848 tim=2431295627
--//竟然走direct path read.關係不大.感覺是取樣,忘記分析表了.
FETCH #174802008:c=0,e=16298,p=28,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=1601196873,tim=2431295959
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #174802008: nam='SQL*Net message from client' ela= 10577 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431306712
WAIT #174802008: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431307122
WAIT #174802008: nam='SQL*Net more data to client' ela= 191 driver id=1413697536 #bytes=8103 p3=0 obj#=107848 tim=2431307681
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
WAIT #174802008: nam='SQL*Net more data to client' ela= 173 driver id=1413697536 #bytes=8102 p3=0 obj#=107848 tim=2431308229
FETCH #174802008:c=0,e=1298,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=2431308363
WAIT #174802008: nam='SQL*Net message from client' ela= 35316 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431343824
WAIT #174802008: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431344242
WAIT #174802008: nam='SQL*Net more data to client' ela= 195 driver id=1413697536 #bytes=8109 p3=0 obj#=107848 tim=2431344678
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
WAIT #174802008: nam='SQL*Net more data to client' ela= 222 driver id=1413697536 #bytes=8116 p3=0 obj#=107848 tim=2431345133
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
FETCH #174802008:c=0,e=1144,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=2431345210
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #174802008: nam='SQL*Net message from client' ela= 19881 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431365158
WAIT #174802008: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431365499
WAIT #174802008: nam='SQL*Net more data to client' ela= 539 driver id=1413697536 #bytes=8107 p3=0 obj#=107848 tim=2431366434
WAIT #174802008: nam='SQL*Net more data to client' ela= 491 driver id=1413697536 #bytes=8095 p3=0 obj#=107848 tim=2431367675
FETCH #174802008:c=0,e=2554,p=0,cr=5,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=2431367926
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//我設定arraysize=200,重新分析表可以發現發現平均行長115.這樣 200*115 = 23000,超過SDU=8192限制,fetch=200需要3次
--//SQL*Net more data to client等待事件.
SCOTT@test01p> @ ev_name 'SQL*Net more data to client'
old 1: select * from v$event_name where lower(name) like lower('%&&1%')
new 1: select * from v$event_name where lower(name) like lower('%SQL*Net more data to client%')
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS DISPLAY_NAME CON_ID
------ ---------- --------------------------- ---------- ---------- ---------- ------------- ----------- ---------- --------------------------- -------
373 554161347 SQL*Net more data to client driver id #bytes 2000153315 7 Network SQL*Net more data to client 0
--//P2引數是位元組: 可以發現大約都是81XX.
--//ela = 191+195+222 = 608 (單位微妙)
--//這樣導致buffer pin時間增加,對資料庫影響並不是很大.因為讀讀模式是共享的.即使是寫也影響不大.
--//如果設定arraysize更大,在一次fetch中遇到SQL*Net more data to client等待事件次數會越多.
D:\app\oracle\diag\rdbms\test\test\trace>grep "SQL\*Net more data to client" test_ora_5288.trc |wc
898 13470 111416
3.測試:
--//修改sqlnet.ora
DEFAULT_SDU_SIZE=32768
--//重複前面測試:
@ 10046on 12
select * from t;
@ 10046off
--//檢查轉儲檔案內容:
=====================
PARSING IN CURSOR #183256856 len=15 dep=0 uid=109 oct=3 lid=109 tim=3516832814 hv=1134051363 ad='7ff23997360' sqlid='89km4qj1thh13'
select * from t
END OF STMT
PARSE #183256856:c=0,e=4934,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1601196873,tim=3516832811
EXEC #183256856:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=3516833461
WAIT #183256856: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3516833727
WAIT #183256856: nam='Disk file operations I/O' ela= 601 FileOperation=2 fileno=9 filetype=2 obj#=107848 tim=3516834941
WAIT #183256856: nam='direct path read' ela= 20202 file number=9 first dba=2963 block cnt=13 obj#=107848 tim=3516855802
FETCH #183256856:c=0,e=22124,p=28,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=1601196873,tim=3516856006
WAIT #183256856: nam='SQL*Net message from client' ela= 4794 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516860893
WAIT #183256856: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516861016
FETCH #183256856:c=0,e=328,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=3516861319
WAIT #183256856: nam='SQL*Net message from client' ela= 25029 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516886434
WAIT #183256856: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516886906
FETCH #183256856:c=0,e=310,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=3516887167
WAIT #183256856: nam='SQL*Net message from client' ela= 44761 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516932043
WAIT #183256856: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516932438
FETCH #183256856:c=15600,e=246,p=0,cr=5,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=3516932653
--//在一次fetch=200中,SDU=32768,不再遇到SDU填滿的情況,也就是不再出現SQL*Net more data to client等待事件
D:\app\oracle\diag\rdbms\test\test\trace>grep "SQL\*Net more data to client" test_ora_6856.trc |wc
0 0 0
--//可以發現沒有出現SQL*Net more data to client等待事件.可以發現設定合理設定就ok.arraysize=200僅僅是個人的經驗值.
--//大多數應用不會修改client端的SDU大小.這個引數要協商,以客戶端,伺服器最小值作為標準.
--//如果3層應用,建議還是修改中間伺服器,伺服器我個人建議SDU=32768.
--//另外設定arraysize很大,一定程度減少SQL*Net roundtrips to/from client.
SCOTT@test01p> set autot traceonly
SCOTT@test01p> set array 200
SCOTT@test01p> select * from t;
89888 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 89888 | 9M| 421 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 89888 | 9M| 421 (1)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1957 consistent gets
1511 physical reads
0 redo size
7754897 bytes sent via SQL*Net to client
5483 bytes received via SQL*Net from client
451 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
89888 rows processed
SCOTT@test01p> set array 400
SCOTT@test01p> select * from t;
89888 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 89888 | 9M| 421 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 89888 | 9M| 421 (1)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1736 consistent gets
1511 physical reads
0 redo size
7711922 bytes sent via SQL*Net to client
3008 bytes received via SQL*Net from client
226 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
89888 rows processed
--//SQL*Net roundtrips to/from client 從451->226.但是總bytes sent via SQL*Net to client是基本不變的.
--//在vage的<oracle揭秘> 有很好的說明,arraysize很大,也不會過度消耗pga,因為填滿SDU就傳輸,只不過一直
--//持有buffer bin,但是對資料庫影響不大.
--//設定DEFAULT_SDU_SIZE=32768,會不會導致使用者uga或者pga消耗,我自己也沒注意觀察.
總結:
1.我個人建議設定DEFAULT_SDU_SIZE=32768.
2.預提取大小設定200足以.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2199981/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20170506]fetch sqlplus arraysize.txtSQL
- 測試SQLPLUS的ARRAYSIZE對效能的影響SQL
- Arraysize的設定以及對查詢效能的影響
- 常用sqlplus設定SQL
- [20190215]sqlplus set arraysize.txtSQL
- Sqlplus設定列寬SQL
- sqlplus環境設定SQL
- 設定sqlplus環境SQL
- arraysize/TDU/SDU的設定對sql執行代價的影響SQL
- SQLPLUS之set常用設定SQL
- sqlplus的常見設定SQL
- 修改sqlplus預設環境設定SQL
- sqlplus下設定歸檔模式SQL模式
- ORACLE sqlplus 提示符設定OracleSQL
- 設定sqlplus中的退格鍵SQL
- sqlplus中提示符的設定SQL
- sqlplus 中設定命令回顯功能SQL
- 設定sqlplus顯示字元 防止誤操作SQL字元
- [20190306]共享服務模式與SDU.txt模式
- sqlplus顯示規格的各種設定SQL
- 設定oracle sqlplus中的Edit編輯模式OracleSQL模式
- Oracle sqlplus設定顯示格式命令詳解OracleSQL
- [20120109]sqlplus 與set longchunksize 設定問題.txtSQLGC
- sqlplus登陸環境設定（glogin.sql）SQL
- Oracle arraysize 和 fetch size 引數 與 效能優化 說明Oracle優化
- 【OH】SET System Variable Summary SQLPLUS 系統變數設定SQL變數
- Oracle sqlplus引數autocommit（自動提交）的設定OracleSQLMIT
- 關於設定SQLPLUS提示符樣式的方法SQL
- 在SQLPLUS中設定預設編輯器(Linux/Unix平臺)SQLLinux
- 【Oracle】arraysize的研究（存在疑問）Oracle
- [20200824]12c sqlplus rowprefetch arraysize 顯示行數量的關係.txtSQL
- oracle linux實現自啟動及sqlplus環境變數的設定，rlwrap的設定OracleLinuxSQL變數
- [20161021]關於sqlplus sqlprompt設定.txtSQL
- sqlplus與空行.txtSQL
- Arraysize 對consistent get的影響
- Autotrace的設定與使用
- ORACLE_HOME設定錯誤導致本地sqlplus無法登陸OracleSQL
- JMeter定時器設定延遲與同步JMeter定時器