Last day...I faced a big issue with latch events on one of our application program.
Program which was supposed to complete in 5 mins ,was running for more than hours.
I checked the session ,sql_id remained the same and wait was Latch: cache buffer and Later free.
sql_id was an insert statement and object waited was a global temporary table.
Once this program uses this temporary table ,and only one program was running,so chances for this block to hot is very less.
Finally developer bypassed that temporary table ,for the that time.
but needs to revert the code.
We bounced the DB now,the issue seems to not replicating.
But would like to know how to resolve this issue without bouncing DB.
Our DB version 184.108.40.206
OS : AIX 6.1
Can anyone help me in this
Only one program was running, however it could've been opening multiple sessions.
The fact of the matter is:
1. You either have a bug
2. Or you had some severe concurrency issues.
Without further information regarding the application process it's impossible for us here at the forum to provide any useful information.
I would suggest you look at your AWR at the time of the problem, look at the latch miss sources and start yout investigation from there.
your case may be an exception to the general rule, but very often when someone is complaining about his or her session being busy on some exotic and/or abnormal waits, this is simply because of a popular misconception. Namely, when the database session is running on CPU (and it should be in that state most or at least much of the time), it's not waiting on anything, so the wait event information displayed in V$SESSION, other V$ views or any GUI tools based on them is displayed not for the current wait event (there is no current event in such case as there is no wait), but for the last completed event before the session went on CPU.
E.g. if your session spends 10 hours on CPU, a few milliseconds on buffer busy waits, and there aren't any other wait events, then most of the time it would look like the session is stuck on buffer busy waits. In order to avoid the confusion, you need to read documentation on OWI very carefully, especially the part about WAIT_TIME (or other columns which help you interpret the rest of OWI information).
It's also possible you are running into some AIX specific bug or misfeature, as it may use slightly different process wait communication than the more front-running OS's. You might try the Oracle provided OS tools, and Tanel's snapper script in addition to the other tools suggested.
Of course, if you can't reliably replicate it, it might not be fixable. On the other hand, the 220.127.116.11 patch set lists a P+ process memory area fix for AIX