Showing posts with label Bugs. Show all posts
Showing posts with label Bugs. Show all posts

9.26.2010

kghfrunp: latch: nowait paralyzes RAC database instances

When I arrived office this morning, I received a complain from a business user that he is unable to log in on one of our business critical RAC database (v10gR2, configured with 2 instances) running on HP Superdome. Nevertheless, after the complain, I was able to establish a connection on the first instance, but, on the second instance, I couldn't, the connection just got hanged. Suspecting the blocking scenario, I then tried to run some queries on the first instance to understand the ongoing situation of the database. Alas, the query too hanged and the cursor didn't return back to the SQL prompt.
The alert.log of the second instance just shows an 'WARNING: inbound connection timed out (ORA-3136)' message. However, the instance alter.log file shows the following information:

ORA-12012: error on auto execute of job 42781
ORA-27468: "EXFSYS.RLM$SCHDNEGACTION" is locked by another process
Errors in file /u00/app/oracle/admin/XXXX/bdump/xxxx1_j000_23982.trc:
ORA-12012: error on auto execute of job 42781
ORA-27468: "EXFSYS.RLM$SCHDNEGACTION" is locked by another process


I then had a closer look at the details given in the DIAG trace file on the second node and found some valuable information, as listed below:

         waiter count=24
          gotten 8128165 times wait, failed first 518198 sleeps 267045
          gotten 83569 times nowait, failed: 232295
        possible holder pid = 24 ospid=14113
      on wait list for c0000001b8cdd6f0
    Process Group: DEFAULT, pseudo proc: c0000001bc2f4d90
    O/S info: user: oracle, term: UNKNOWN, ospid: 14391
    OSD pid info: Unix process pid: 14391, image: oracle@usogp08 (LCK0)
  waiting for c0000001b8cdd6f0 Child row cache objects level=4 child#=16
        Location from where latch is held: kghfrunp: clatch: nowait:
        Context saved from call: 0
        state=busy, wlstate=free
          waiters [orapid (seconds since: put on list, posted, alive check)]:

    ----------------------------------------
    SO: c0000001b5387868, type: 16, owner: c0000001bf2a61c0, flag: INIT/-/-/0x00
    (osp req holder)
Enqueue blocker waiting on 'latch: row cache objects'

After analyzing the text closely, it was loud and clear that the database indeed is suffering from the blocking problem. Instead of aborting the second instance (could be managed through sqlplus -prelim / as sysdba command though) in order to release the lock held on the database, I thought it would be better to find the lock holder session and kill it's process. I manage to get the details about the holder session from the diag file, ' possible holder pid = 24 ospid=14113'. After getting the ospid details of the possible holder session, I found that it was a job queue oracle background process. Without any second thought, I just killed the process using from the OS using  the 'kill -9 processid' command. Once process has been killed, the database was back to the normal functionality. I then took a AWR report of the second instance and it reveals the following facts about the sitaution:

top 5 timed events
latch: row cache objects     420,942 64,707 154   40.3 Concurrency
rdbms ipc reply               32,832 62,721 1,910 39.1 Other
cursor: pin S wait on X    1,898,149 25,440 13    15.8 Concurrency
row cache lock               321,355 10,961 34     6.8 Concurrency
CPU time                              3,953        2.5  


shared KQR L PO 214.04 56.62 -73.55
library cache kqlmbfre: child: in loop 0 66 175
library cache kqlmbfre: child: no obj to free 0 18 258

shared pool kghfrunp: clatch: wait   0 308,002 417,684
shared pool kghfrunp: alloc: wait    0 129,691 128,527
shared pool kghfrunp: clatch: nowait 0 125,485 0
shared pool kghfre                   0   3,043 3,386
shared pool kghalo                   0 258 777
shared pool kgh_next_free            0  79 322
shared pool kghalp                   0  18 48


The ML LCK temporarily stuck waiting for latch 'Child row cache objects' [ID 843638.1] points towards shared pool stress and below is the abstract about the cause of the problem:

The shared pool is stressed and memory need to be freed for the new cursors. As a consequence, the dictionary cache is reduced in size by the LCK process causing a temporal hang of the instance since the LCK can't do other activity during that time. Since the dictionary cache is a memory area protected clusterwide in RAC, the LCK is responsible to free it in collaboration with the dictionary cache users (the sessions using cursors referenced in the dictionary cache). This process can be time consuming when the dictionary cache is big.

After understanding the facts & figures shown in the AWR report, indeed, there was a pressure on the shared pool's dictionary cache memory component.  As suggested in the ML, I need to pay attention to the larger text SQLs and tune or need to apply a patch to avoid the embarrassing situation in the future.
Stay tune, I will post more details in the coming days.

References
BUG:8266531 - LCK STUCK WAITING FOR LATCH 'CHILD ROW CACHE OBJECTS'
BUG:8666117 - LCK0 PROCESS STUCK AT WAITING FOR "LATCH: ROW CACHE OBJECTS"


12.21.2009

Shrink space on a lobsegment of 65G running since more than 5 days

A shrink space operation on a lobsegment of 65G in one of our business critical 2 instance RAC database (10gR2) is running more than five days now. As usual , when I opened a TAR with Oracle support, they coolly informed me that, look mate, you are hitting a bug (5768710), you either need to upgrade the db to v11g or apply a patch (8598989).

The lobsegment contains 8128896 blocks, 1183 extents and the size is around 65GB. We actually truncated the source table and then tried shrinking this lobsegment in order to release the free space.
I did test in development and UAT environments (single instance RAC databases) and it took almost a day and half duration to complete the task. However, the following get stuck on the production:

ALTER TABLE tablename MODIFY LOB (column_name)(SHRINK SPACE) nologging parallel 5;

Due to the bug, I am unable to predict how long this operation would take as no information is listed in  v$session_logops dynamic view as well. Unfortunately, Oracle support too can't confirm me when would this operation finishes. The only saving  point to me is that the other sessions working on the table works fine. Neither there is any performance impact nor any excessive redo generation causing.
Upon analyzing my trace files, 10046 and oradebug short_stack, Oracle support confirms that the operation is not hanged, it is progressing.
Keeping in view about no impact on the database, they requested me to leave it running as killing session may trigger SMON recovery on this table.

My boss says, whatever I am handling ending up hitting the bug, one of very rare unfortunately days of my life.


Happy reading,

Jaffar