-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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!
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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!
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|