Wait events

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?

Code:

---

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

---

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

Is your application Oracle Forms based?

Its Business Objects.

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

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.

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!

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

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

Quote:

---

Originally Posted by dbasan

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?

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