Wait events

[tabreaz]

Good Evening,

Below are the list of wait events with 17.7 days the top wait event "SQL*Net message from client".
How can I reduce it?

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client        4298474    63977240
rdbms ipc message                    87695     9830470
PX Deq: Execution Msg               310670     8080779
pmon timer                            7135     2035552
virtual circuit status                 647     2031403
dispatcher timer                       323     2021496
PX Idle Wait                        279360     1998518
smon timer                              68     1971965
wakeup time manager                    611     1957159
enqueue                             275521      558988
SQL*Net more data from client         3949       44848
PX Deq: Join ACK                    270437       32571
db file sequential read              86321       27344
PX Deq: Table Q Normal               59714       11390
PX Deq: Execute Reply                 1217        6311
db file scattered read               34101        5654
PX Deq Credit: send blkd             28541        5213
latch free                            1328        3208
direct path read                      8744        2229
PX Deq Credit: need buffer           17004        1913
log file sync                        16344        1579
direct path write                     5268        1406
log file parallel write              19410        1148

[davey23uk]

its an idle wait event, from a database point of view you can ignore it

but it means your database is waiting on the client program to send data to it

[PAVB]

Is your application Oracle Forms based?

[tabreaz]

Its Business Objects.

Is there anyway to set some parameters in oracle Network related files such as sqlnet.ora?

[tabreaz]

I run the statspack report even and that is also very much fine, I dont find any problem except with
SQL*Net message from client and PX Deq: Execution Msg events.

Every other things are seems to be fine.

[PAVB]

As Davey stated, from the database point of view this is an idle event however, this is affecting query performance.

You are experiencing too many round-trips between the client and the server.

This is likely not easy to troubleshoot.

You can start by tracing the most used queries and look for "SQL*Net message from client" wait. As a rule of thumbs take into consideration just queries where this event is greater than the sum of all other wait events.

Once you have identified a offending query... tune it up

Good luck!

[tabreaz]

Thanks PAVB, you are right, I will try to do such

[dbasan]

I think setting profiles, resource limits and sqlnet expire time would reduce the wait events from client. check!

[PAVB]

I think setting profiles, resource limits and sqlnet expire time would reduce the wait events from client. check!

Not sure how profiles and resource limits would help when the root cause of the issue is likely to be the application forcing too many round-trips in between the client and the server.

Would you mind to elaborate?

[dbasan]

Server is waiting for the client to feed the data. It may be the user is out off his place or waiting for some thing else to influence, his decision for input,....are the odd things we need to eliminate to fool statspack.

Under these circumstances when you take a stats report it would certainly show "SQL*Net message from client" as idle wait times consuming more resources, which actually not and can be ignored.

On the other hand it can also be a bottleneck from the network or the resource. Need to find them where is the exact problem and then trouble shoot.

Resource limits and profiles indirectly assist you in eliminating unwanted waits, and be sure in your analysis towards resource or network bottleneck.