Oracle: Network Waits
来源:互联网 发布:彩虹秒赞源码2017破解 编辑:程序博客网 时间:2024/06/09 13:35
Introduction
Unfortunately, what Oracle calls "Network Waits" have little to do with Network but and almost exclusively to do with the time it takes to pack messeges for the network before they are sent.
Client = you, the tool, sqlplus, application
the shadow process is communicating to the client
SQL*Net message to client - time to pack a message (no network time included) possibly tune SDUSQL*Net more data from client - possible network issues, possibly tune SDUSQL*Net more data to client - time to pack a message (no network time included) possibly tune SDU
The same events exist, but where the client is the shadow process and another database plays the roll of shadow process:
SQL*Net message to dblinkSQL*Net more data from dblink - possible network issues, possibly tune SDUSQL*Net more data to dblink
SQL*Net Wait Events
SQL*Net message from client
Idle EventWaiting for work from ClientIncludes network transmission times for messages coming from shadowTypically indicative of Client “think time” or “processing time”Example from Egor Starostin, http://oracledba.ruFrom a 10046 trace=====================PARSING IN CURSOR #1 len=43 dep=0 uid=0 oct=3 lid=0 tim=1304096237hv=2707617103 ad='89a03e18'select * from all_objects where rownum < 20END OF STMTPARSE #1:c=0,e=143,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1304096209EXEC #1:c=0,e=744,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1304097036WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232#bytes=1 p3=0 obj#=-1 tim=1304097096FETCH #1:c=10000,e=6903,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,tim=13041040571->WAIT #1: nam='SQL*Net message from client' ela= 721 driverid=1650815232 #bytes=1 p3=0 obj#=-1 tim=1304104865 # [non-idle]WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232#bytes=1 p3=0 obj#=-1 tim=1304105319FETCH #1:c=0,e=627,p=0,cr=21,cu=0,mis=0,r=15,dep=0,og=1,tim=13041055242->WAIT #1: nam='SQL*Net message from client' ela= 253 driverid=1650815232 #bytes=1 p3=0 obj#=-1 tim=1304105818 # [non-idle]WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232#bytes=1 p3=0 obj#=-1 tim=1304105867FETCH #1:c=0,e=63,p=0,cr=6,cu=0,mis=0,r=3,dep=0,og=1,tim=13041059003->WAIT #1: nam='SQL*Net message from client' ela= 1960753 driverid=1650815232 #bytes=1 p3=0 obj#=-1 tim=1306066946 # [idle]=====================PARSING IN CURSOR #1 len=21 dep=0 uid=0 oct=3 lid=0 tim=1306069444hv=2200891488 ad='89913b50'select user from dualEND OF STMTPARSE #1:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1306069440...The first two "SQL*Net message from client' are in the middle of cursor processing and are considered non-idle waits.The third "SQL*Net message from client" is between cursors and considered an idle event, ie we are waiting for the next command from the client.
SQL*Net message to client
Time it takes to pack a message to be sent to the clientDoesn’t include network timingsee Tanel Poder's analysis of SQL*Net message to client
SQL*Net more data to client
Same as SQL*Net message to client except this is for data that spans SDU packets.Wait represents the time it takes to pack data.Doesn’t include network timing
SQL*Net more data from client
The only SQL*Net wait that can indicate a possible NETWORK problemClient is sending data to shadow that spans packets (think large data inserts, possibly large code blocks, large SQL statements)Shadow waits for next packet.Can indicate network latency.Can indicate a problem with the client toolHere is an example with ASHMON where the application server died mid-stream on inserts. The shadow processes were left waiting for completion of the message. You can see the regular load on the database on the left, then just past the middle the load crashes, and all that's left is waits on "SQL*Net more data from client"
Possibly set SDU=32768 as well as setting RECV_BUF_SIZE and SEND_BUF_SIZE to 65536.
SQL*Net break/reset to client
Error in sql statementControl CUsually highlights and error in applicationExample:
CREATE TABLE T1 (C1 NUMBER);ALTER TABLE T1 ADD(CONSTRAINT T1_CHECK1 CHECK (C1 IN ('J','N')));ALTER SESSION SET EVENTS'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';INSERT INTO T1 VALUES (1);
Trace File
PARSING IN CURSOR #2 len=25 dep=0 uid=0 oct=2 lid=0 tim=5009300581224 hv=981683409 ad='8e6a7c10'INSERT INTO T1 VALUES (1)END OF STMTPARSE #2:c=0,e=2770,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=5009300581220BINDS #2:EXEC #2:c=0,e=128,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5009300581418ERROR #2:err=1722 tim=512952379WAIT #2: nam='SQL*Net break/reset to client' ela= 31 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=5009300581549WAIT #2: nam='SQL*Net break/reset to client' ela= 92 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=5009300581662
Unfortunately Oracle doesn't give much information about debugging unless you are trace. If you don't trace, the SQL won't be captured because from Oralce's point of view the problem statement isn't an acceptable SQL statement so there is no SQL ID to track down.
DBLINK SQL*Net Waits
These waits are the same asSQL*Net message to dblinkSQL*Net more data from dblinkSQL*Net more data to dblinkSQL*Net break/reset to dblink
Analysis and Tuning
There isn't much to do on the Oracle side for tuning. You can try optimizing the SDU and SEND_BUF_SIZE and RECV_BUF_SIZE.
For actually getting information on network speeds you will have to use something like
- ping
- tnsping
- network sniffer
SDU
The default SDU can be set in the sqlnet. oraIf it's not set, the default is 2048The max is 32768The default,or the value in sqlnet.ora, can be overridden in the tnsnames. ora and the listener.ora. The client and server negotiate the size aggreeing on the smaller of the two settings.(TDU – Transmission Data Unit – see note 44694.1 The TDU parameter has been deprecated in the Oracle Net v8.0 and beyond and is ignored. It is only mentioned here for backward compatibility.)tnsnames.ora
V10G = (DESCRIPTION =(SDU=32768)(ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = v10g)) )
listener.ora
SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SDU=32768)(SID_NAME = v10g)(ORACLE_HOME = /export/home/oracle10)))
Tracing
sqlnet.oratrace_level_client=16trace_directory_client=/tmptrace_file_client=client.trctrace_unique_client = truetrace_level_server=16trace_directory_server=/tmptrace_file_server=server.trc
client.trcclient_3582.trc:[12-JAN-2008 11:37:39:237] nsconneg: vsn=313, gbl=0xa01, sdu=32768, tdu=32767
more from Jonathan Lewis at http://www.jlcomp.demon.co.uk/sdu.html
RECV_BUF_SIZE and SEND_BUF_SIZE
see: http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/performance.htm (or local copy)The recommended size for these buffers (from Oracle's docs) is at leastNetwork bandwidth * roundtrip = buffer min sizeFor example if the network bandwidth is 100mbs and the round trip time (from ping) is 5ms then100,000,000 bits 1 byte 5 seconds
---------------- x ------ x --------- = 62,500 bytes
1 second 8 bits 1000
tnsnames.ora
V10G = (DESCRIPTION =(SEND_BUF_SIZE=65536)(RECV_BUF_SIZE=65536)(ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = v10g)) )
listener.ora
SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SEND_BUF_SIZE=65536)(RECV_BUF_SIZE=65536)(SID_NAME = v10g)(ORACLE_HOME = /export/home/oracle10)))
sqlnet.ora
RECV_BUF_SIZE=65536SEND_BUF_SIZE=65536
.
.
.
Subpages (2): Oracle: Network Configuration SQL*Net message from client
0 0
- Oracle: Network Waits
- Oracle: Network Waits
- Oracle buffer busy waits
- oracle buffer busy waits
- Oracle 11gR2 enqueue waits
- Causes of Oracle Buffer Busy Waits
- Oracle Waits event:DB File Scattered Read
- Oracle waits event:DB File Sequential Read
- 转 -- Oracle Interested Transaction List (ITL) Waits
- oracle 模拟出 buffer busy waits 事件
- Oracle wait event --- buffer busy waits
- 从buffer busy waits看oracle内存使用机制
- oracle 重要的等待事件--buffer busy waits
- oracle下network配置
- enqueue waits
- Selenium - Waits
- Database Performance: How to reduce "library cache: mutex X" waits for scalability in the Oracle dat
- Oracle Ports for Network Services
- Linux系统:an error occurred during the file system check 错误解决方法
- 个人博客开启(导语篇)
- 试卷分析
- hdu 5448 Marisa’s Cake(几何+凸包)
- DES加密解密算法
- Oracle: Network Waits
- ios9 设置开机画面隐藏状态栏
- CentOS7下Firewall防火墙配置用法详解
- 基于AStar算法的RCP布线优化
- IMAP Tools
- [转载]MAC 下将libpomelo连接到cocos2d-x
- ios上 更改 状态栏(UIStatusBar)的颜色,你值得一看、收藏
- 有关﹤![CDATA[ ]]> 说明
- HDU 1754 I Hate It(线段树单点替换/区间最值)