OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SQL*Net messages

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Net messages [message #126775]

Wed, 06 July 2005 10:33

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

In my tkprof report I have the following line:

INSERT INTO ENT(ENT_ID, SYS_CREATE_DT, SYS_LSTUPD_DT, ENT_TYPE_ID)
VALUES (:1, :2, :3, :4)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 18859 8.97 10.32 0 252 60676 18859
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18860 8.97 10.32 0 252 60676 18859

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 67

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 18859 0.00 0.02
SQL*Net message from client 18859 0.00 9.87
********************************************************************************
Is this a real delay? I know that we could disregard the "from client" messages but is there something I can look at to optimize the current situation?
Also, how to unsderstand the other sql*net messages like "SQL*Net break/reset to client" or "SQL*Net more data to client" like in this case:
INSERT INTO NAME(NAME_ID, CUL, ACCT_ID, ENt_ID, FIRST_NAME,
HIST_STAT, LAST_NAME, LFN, LMN, MID_NAME, NAME_GEN, NAME_PFX,
NAME_SFX, NAME_TYPE, QC_STAT, SYS_CREATE_DT, SYS_DELETE_DT, SYS_LSTUPD_DT, VALID_FROM_DT, VALID_THRU_DT)
VALUES
(:1, NULL, :2, :3, :4, :5, :6, :7, :8, :9, NULL, NULL, NULL, :10, :11, :12,
NULL, :13, NULL, NULL)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 15484 12.61 14.03 2 846 181511 15475
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15485 12.61 14.03 2 846 181511 15475

Misses in library cache during parse: 1
Misses in library cache during execute: 5
Optimizer mode: FIRST_ROWS
Parsing user id: 67

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 15484 0.00 0.02
SQL*Net message from client 15484 0.03 17.37
db file sequential read 2 0.00 0.00
SQL*Net break/reset to client 18 0.00 0.00
log file sync 4 0.00 0.01
********************************************************************************
This is the moment when my slow downs usually start...
Is this network related and what can I do? I have checked that my TCP/IP packagt is 1500 on both app and db servers.

Thanks a lot for any idea, mj

Report message to a moderator

Re: SQL*Net messages [message #126791 is a reply to message #126775]

Wed, 06 July 2005 12:05

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

First, look into your array size, if it is low you'll want to test it at higher values, maybe test every 100 from 100 to 1000. In sqlplus, for example, you can do show arraysize to see what it is. Basically you want to reduce either the number of or the duration of each round trip between client and server.

You'll want to look into oracle documentation and books regarding wait events.

Cary Millsap writes about it in his:
http://www.amazon.com/exec/obidos/tg/detail/-/059600527X/qid=1120669281

And this is supposed to be a good book, although I don't have it yet:

http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/qid=1120669191

Also the oracle documentation performance tuning guide from this chapter as well as other chapters discusses it:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/instance_tune.htm#15891

Report message to a moderator

Previous Topic:	unable to allocate 4128 bytes of shared memory ("shared pool
Next Topic:	composite index or single column index?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 04:50:37 CDT 2025