sql*net & other waits. #.2 copyright 2006 kyle hailey network redo lib cache buffer cache io...
TRANSCRIPT
#.2
Copyright 2006 Kyle Hailey
Network
RedoRedo Lib Lib CacheCache
Buffer Buffer CacheCache
IOIO
LocksLocks
NetworkNetwork
#.3
Copyright 2006 Kyle Hailey
SQL*Net
6 SQL*Net more data from clientUsually OK, reduce data transferred, possible Network problems
10 SQL*Net more data to clientUsually OK, reduce amount of data transferred, possible Network
tuning needed
14 SQL*Net break/reset to clientCheck for errors in sql statement
21 SQL*Net more data from dblinkReduce data transfer, check net response
31 SQL*Net break/reset to dblinkCheck for errors in sql statement sent
#.4
Copyright 2006 Kyle Hailey
SQL*Net Waits
Session
Shadow
Log BufferLog Buffer
Buffer CacheBuffer CacheLog Log BufferBuffer
Buffer Buffer CacheCache
SGASGALibrary Library CacheCache
Host MachineHost Machine
SQL*PlusSQL*Plus
OCIOCI
SQL*FormsSQL*Forms
etcetc
#.5
Copyright 2006 Kyle Hailey
SQL*Net Waits P1 and P2
1. SQL*Net message to client2. SQL*Net more data from client3. SQL*Net more data to client4. SQL*Net more data from dblink
Message and More data P1 = driver id P2 = #bytes
5. SQL*Net break/reset to client6. SQL*Net break/reset to dblink
Break/resets P1= driver id P2 = break
#.6
Copyright 2006 Kyle Hailey
SQL*Net message to client Time it takes to pack a message to be sent to
the client Doesn’t include network timing
Session
Shadow
WaitingWaiting
http://blog.tanelpoder.com/2008/02/10/sqlnet-message-to-client-vs-sqlnet-more-data-to-clienthttp://blog.tanelpoder.com/2008/02/10/sqlnet-message-to-client-vs-sqlnet-more-data-to-client
#.7
Copyright 2006 Kyle Hailey
SQL*Net more data to client Client sends information that spans SDU
packets Time it takes to pack Doesn’t include network timing
Session
Shadow
WaitingWaiting
#.8
Copyright 2006 Kyle Hailey
SQL*Net more data from client Shadow is sending data to client that spans
packets Shadow waits for next packet Could indicate
network latency? Problem with the client tool
Session
Shadow
WaitingWaiting
#.9
Copyright 2006 Kyle Hailey
SQL*Net more data from client
“OEM-lite”Written in TCL/TKOnly
OverviewSQL Text
Application Server CrashedApplication Server Crashed
#.10
Copyright 2006 Kyle Hailey
SQL*Net more data from dblink
Shadow at db link is sending data to client that spans packets
Sends first packet then waits for shadow to respond, so it can send more
Session
Shadow
WaitingWaitingdblink
Shadow
#.11
Copyright 2006 Kyle Hailey
SDU: More Data SDU : Session Data Unit Set in sqlnet.ora
client_187.trc:[12-JAN-2008 11:01:38:209] nsconneg: vsn=313, gbl=0xa01, sdu=2048, tdu=32767
trace_level_client=16trace_level_client=16trace_directory_client=/tmptrace_directory_client=/tmptrace_file_client=client.trctrace_file_client=client.trctrace_unique_client = truetrace_unique_client = truetrace_level_server=16trace_level_server=16trace_directory_server=/tmptrace_directory_server=/tmptrace_file_server=server.trctrace_file_server=server.trc
TDU – Transmission Data TDU – Transmission Data Unit – see note Unit – see note 44694.1 44694.1 The TDU parameter has The TDU parameter has been deprecated in the been deprecated in the Oracle Net v8.0 and Oracle Net v8.0 and beyond and is ignored. It beyond and is ignored. It is only mentioned here for is only mentioned here for backward compatibility.backward compatibility.
#.12
Copyright 2006 Kyle Hailey
SDU 32KV10G = (DESCRIPTION =V10G = (DESCRIPTION =
(SDU=32640)(SDU=32640) (ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = (ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))1522)) (CONNECT_DATA =(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = v10g)(SERVER = DEDICATED) (SERVICE_NAME = v10g) ) )) )
SID_LIST_LISTENER =SID_LIST_LISTENER = (SID_LIST =(SID_LIST = (SID_DESC =(SID_DESC = (SDU=32640)(SDU=32640) (SID_NAME = v10g)(SID_NAME = v10g) (ORACLE_HOME = /export/home/oracle10)(ORACLE_HOME = /export/home/oracle10) ))))
listener.oralistener.ora
tnsnames.oratnsnames.ora
client_3582.trc:[12-JAN-2008 11:37:39:237] nsconneg: vsn=313, client_3582.trc:[12-JAN-2008 11:37:39:237] nsconneg: vsn=313,
gbl=0xa01, gbl=0xa01, sdu=32640sdu=32640, tdu=32767, tdu=32767
#.13
Copyright 2006 Kyle Hailey
SQL*Net break/reset to client
Error in sql statement Control C Usually highlights and error in application
Session
Shadow
WaitingWaiting ErrorError
#.14
Copyright 2006 Kyle Hailey
Example
PARSING IN CURSOR #2 len=25 dep=0 uid=0 oct=2 lid=0 tim=5009300581224 hv=9816834PARSING IN CURSOR #2 len=25 dep=0 uid=0 oct=2 lid=0 tim=5009300581224 hv=9816834
09 ad='8e6a7c10'09 ad='8e6a7c10'INSERT INTO T1 VALUES (1)INSERT INTO T1 VALUES (1)END OF STMTEND OF STMTPARSE #2:c=0,e=2770,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=5009300581220PARSE #2:c=0,e=2770,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=5009300581220BINDS #2:BINDS #2:EXEC #2:c=0,e=128,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5009300581418EXEC #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=512952379ERROR #2:err=1722 tim=512952379WAIT #2: nam='SQL*Net break/reset to client' ela= 31 driver id=1650815232 break?WAIT #2: nam='SQL*Net break/reset to client' ela= 31 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=5009300581549=1 p3=0 obj#=-1 tim=5009300581549WAIT #2: nam='SQL*Net break/reset to client' ela= 92 driver id=1650815232 break?WAIT #2: nam='SQL*Net break/reset to client' ela= 92 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=5009300581662=0 p3=0 obj#=-1 tim=5009300581662
CREATE TABLE T1 (C1 NUMBER);CREATE TABLE T1 (C1 NUMBER);ALTER TABLE T1 ADD ALTER TABLE T1 ADD (CONSTRAINT T1_CHECK1 CHECK (C1 IN ('J','N')));(CONSTRAINT T1_CHECK1 CHECK (C1 IN ('J','N')));ALTER SESSION SET EVENTSALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';INSERT INTO T1 VALUES (1);INSERT INTO T1 VALUES (1);
#.15
Copyright 2006 Kyle Hailey
SQL*Net break/reset to dblink Error in sql statement Control C Usually highlights and error in application
Session
Shadow
WaitingWaitingdblink
Shadow
errorerror
#.16
Copyright 2006 Kyle Hailey
SQL*Net Waits P1 and P2
Network/Client/Tool issues More data from client More data from dblink
Try SDU SQL*Net more data from client SQL*Net more data to client SQL*Net more data from dblink
Application Error SQL*Net break/reset to client SQL*Net break/reset to dblink