Home » RDBMS Server » Performance Tuning » Wait event problem (oracle 10.1.0.5.0, Sunsolaris 5.10)
| Wait event problem [message #439129] |
Thu, 14 January 2010 09:47  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
We are monitoring database through veritas I3 performance monitoring tool.
I3 showing high number of OS WAIT for a whole day for the below query.
INSERT INTO SAS_AUDIT_TRAIL_DETAIL
(TRANS_ID, JOB_ID, TRANS_EXIT_CODE , TRANS_STEP, TRANS_REQUEST_XML, TRANS_RESPONSE_XML,TRANS_TIME,
TRANS_CLIENT, TRANS_SERVER, COMMENTS, JEOPARDY_STATUS , JEOPARDY_RES_REMARK)
VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)
The trace file for the above query shows that the below waits are occuring mostly.
WAIT #3: nam='SQL*Net message from client' ela= 219373 p1=1952673792 p2=1 p3=0
WAIT #3: nam='SQL*Net message to client' ela= 6 p1=1952673792 p2=1 p3=0
Why this waits are occuring?
How can I eliminate this?
and Is this the cause of OS wait?
Note: As size of the trace file is big, So information for the above query only taken from the trace file generated and attached the same.
[Updated on: Thu, 14 January 2010 10:14] by Moderator Report message to a moderator |
|
|
|
| Re: Wait event problem [message #439135 is a reply to message #439129] |
Thu, 14 January 2010 10:25  |
 |
Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
The first is just saying Oracle was waiting for you to send some data. You have to improve your skills in typing the command.
The second one is really tiny (ela=6) is just the latency to send you a message.
Both are idle events that is Oracle is not waiting during work.
By the way, elapsed time is in microseconds.
Regards
Michel
[Updated on: Thu, 14 January 2010 10:30] Report message to a moderator |
|
|
|
| Re: Wait event problem [message #439433 is a reply to message #439129] |
Sat, 16 January 2010 15:37 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
And thus is demonstrated the futility of wait tuning.
Ah yes, there were a couple conference papers presented about it, which spawned a couple of books with moderate sales, and several people claim that wait tuning is the best thing going. But those who espouse the virtue of wait tuning are lost in a lack of reality.
Please do not miscontrue my commentary here. Wait tuning does indeed work and work well. It is not just theory; it is a well founded concept that makes sense. But going from an idea that only eggheads can work with, to rubber meets the road ready for mass consumption is another matter.
One cannot be successful in wait tuning with just a layman's comprehension of working technologies. Wait tuning requires significant interdiciplinary skills. To successfully wait tune one must have a deep understanding of I/O SUBSYSTEMS, NETWORK ARCHITECTURE, and ORACLE INTERNALS. Very few of us have this makeup in our skill set, mostly because we simply don't need it to do our jobs.
I work for a very large company, the largest user of Oracle technology in CT. We have 1000 poeple more or less who support oracle systems and having been with the company for fifteen years and knowing most of them I can tell you there is not one among them myself included who would be a good wait tuner. I would suggest there are maybe 100 people across the globe who attempt wait tuning and actually have the needed background to be good at it. It is not a surprise that most of them work for Oracle Corp.
If you are using I3 then I suggest you check out the various RANKING reports it offers. SQL by CPU... SQL by READ/WRITES... SQL by BUFFER GETS... to find the real cluprits, assuming your system is showing signs of some problem. To the OP, I am curious as to what made you think this INSERT statement was a problem?
Make no mistake, I do not consider myself a wait tuner. I have awe for those who can do it (worked with one guy once some ten years ago from Oracle who could do it with Oracle supplied scripts). But wait tuning is not a skill for the MASSES OF THE ASSESS, it is instead an ability reserved for A FEW GOOD MEN (and WOMEN).
I am waiting on a wait tuner who can teach me how to do it and thus prove me wrong. But I offer the simple observation that no one is rushing to provide help to the OP to date, and only one reply has been offered to the OP regarding their initial question and this reply only showed how wait stats are easily misinterpreted.
So what is the purpose of my post? I don't know. Maybe just for me to vent the frustation I feel knowing that wait tuning is a skill I have yet to be able to teach myself. Maybe its just therapy helping me deal with my own limitations. Forgive me.
Kevin
[Updated on: Sat, 16 January 2010 15:44] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon May 13 01:53:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
