#.1 sql*net kyle hailey http//ashmasters.com/ [email protected]

16
#.1 SQL*Net Kyle Hailey http://oraclemonitor.com http//ashmasters.com/ [email protected]

Upload: augusta-hunter

Post on 17-Dec-2015

221 views

Category:

Documents


2 download

TRANSCRIPT

Page 1: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.1

SQL*Net

Kyle Hailey

http://oraclemonitor.com

http//ashmasters.com/

[email protected]

Page 2: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.2Copyright 2006 Kyle Hailey

Network

RedoRedo Lib Lib CacheCache

Buffer Buffer CacheCache

IOIO

LocksLocks

NetworkNetwork

Page 3: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.3Copyright 2006 Kyle Hailey

SQL*Net Waits

Session

Shadow

Log BufferLog Buffer

Buffer CacheBuffer CacheLog Log BufferBuffer

Buffer Buffer CacheCache

SGASGALibrary Library CacheCache

Host MachineHost MachineSQL*PlusSQL*Plus

OCIOCI

SQL*FormsSQL*Forms

Java ApplicationJava Application

etcetc

Client Client NetworkNetwork

Page 4: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.4Copyright 2006 Kyle Hailey

SQL*Net Waits P1 and P2Idle (or not?) SQL*Net message from client

Packing and Network: SQL*Net message to client SQL*Net more data to client SQL*Net more data from client

Error SQL*Net break/reset to client

Dblink equivalents SQL*Net message to dblink SQL*Net more data to dblink SQL*Net more data from dblink SQL*Net break/reset to dblink

For Network Timings Use

Ping

Tnsping

Network sniffers

Page 5: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.5

SQL*Net message from client

Idle Event Waiting for work from Client Includes network transmission times for messages

coming from shadow Indicative of Client “think time” or “processing time”

Session

Shadow

““idle” waiting for workidle” waiting for workfrom clientfrom client

Client Client ““idle” idle” or

““workingworking” ” or“waitingwaiting”

???

orasrp.ruorasrp.ru

Page 6: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.6Copyright 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 for for Message Message PackingPacking

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

Client Client WaitingWaiting

Page 7: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.7Copyright 2006 Kyle Hailey

SQL*Net more data to client

Client sends information that spans SDU packets

Time it takes to pack Might include network timing - unclear

Session

Shadow

Client Client WaitingWaiting for for Message Message PackingPackingWaitingWaiting

Page 8: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.8Copyright 2006 Kyle Hailey

SQL*Net more data from client Client is sending data to shadow that spans

packets Shadow waits for next packet Could indicate

network latency? Problem with the client tool

Session

Shadow

WaitingWaitingClient Client WorkingWorking

Page 9: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.9Copyright 2006 Kyle Hailey

SQL*Net more data from client

“OEM-lite”Written in TCL/TKOnly

OverviewSQL Text

Application Server CrashedApplication Server Crashed

Page 10: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.10Copyright 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

dblink

Shadow

““Client” Client”

Page 11: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.11Copyright 2006 Kyle Hailey

SDU: More Data

SDU : Session Data Unit Message size

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 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.

Page 12: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.12Copyright 2006 Kyle Hailey

SDU 32KV10G = (DESCRIPTION =V10G = (DESCRIPTION =

(SDU=(SDU=3276832768)) (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=(SDU=3276832768)) (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=sdu=3276832768, tdu=32767, tdu=32767

Page 13: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.13Copyright 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

Client Client

Page 14: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.14Copyright 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);

Page 15: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.15Copyright 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

Page 16: #.1 SQL*Net Kyle Hailey  http//ashmasters.com/ Kyle.hailey@embarcadero.com

#.16Copyright 2006 Kyle Hailey

Summary

Network/Client/Tool issues More data from client More data from dblink

Try SDU , RECV_BUF_SIZE and SEND_BUF_SIZE 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

For Network Timings Use

Ping

Tnsping

Network sniffers