Row Cache Lock - An interesting case

Suddenly today morning i was reported that an Import is hanging.The dumpfile was of 1Gb  containing few small tables.Immediately, i checked what all are the sessions belonging to impdp and what is session wait for them.Knowing the session wait makes things easier to analyze.

As the import was started by 'SYSTEM'

SELECT NVL(a.username, '(oracle)') AS username,
       a.osuser,
       a.sid,
       a.serial#,
       d.spid AS process_id,
       a.wait_class,
       a.seconds_in_wait,
       a.state,
       a.blocking_session,
       a.blocking_session_status,
       a.module,
       TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session a,
       v$process d
WHERE  a.paddr  = d.addr
AND    a.status = 'ACTIVE'
AND    a.username='SYSTEM'
ORDER BY 1,2;

USERNAME        OSUSER                 SID    SERIAL# PROCESS_ID   WAIT_CLASS                                                   SECONDS_IN_WAIT STATE                BLOCKING_SESSION BLOCKING_SE MODULE          LOGON_TIME
--------------- --------------- ---------- ---------- ------------ ---------------------------------------------------------------- --------------- ------------------- ---------------- ----------- --------------- --------------------
SYSTEM          oracle                1047      16533 32474        Idle                                                     129 WAITING                               UNKNOWN     Data Pump Master 05-MAY-2010 21:28:04

SYSTEM          oracle                1054       1278 32496        Idle                                                     403 WAITING                               UNKNOWN     Data Pump Worker 05-MAY-2010 21:28:07

SYSTEM          oracle                1013       1766 32488        Concurrency                                                     1096 WAITING                               UNKNOWN     Data Pump Worker 05-MAY-2010 21:28:06

I think the best script to find the session wait, which i always use is the one provided by Tanel Poder sw.sql

So, lets dig in some more,

SQL> set lines 170
SQL> @sw 1054
old  35:        sid IN (&1)
new  35:        sid IN (1054)

    SID STATE   EVENT                                          SEQ#  WAIT_TIME SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
   1054 WAITING wait for unread message on broadcast cha        106          0         132 7928361520 8043052960          0

SQL>
SQL> @sw 1013
old  35:        sid IN (&1)
new  35:        sid IN (1013)

    SID STATE   EVENT                                          SEQ#  WAIT_TIME SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
   1013 WAITING row cache lock                                  286          0        1208          8          0          3

‘row cahe lock’ wait.So what is Row Cache Lock?

In order for DDL to execute, it must acquire a row cache lock to lock the Data Dictionary information. The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows. The locks on the data dictionary rows are called row cache enqueue locks.

But how and why row lock cache?Digging some more inside

 Lets see what is the enqueue type from v$rowcache

SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event='row cache lock';

P1TEXT       P1   P2TEXT   P2 P3TEXT     P3
---------------------------------------------------------------- ----------
cache id       8    mode      0  request        3


SQL> select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=8;

PARAMETER                             COUNT       GETS  GETMISSES MODIFICATIONS
-------------------------------- ---------- ---------- ---------- -------------
dc_objects                              939     537060      25206          3955
dc_object_grants                        180     116117       1784             0

Some of the common Enqueue Types are:-

DC_TABLESPACES
Probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.

DC_SEQUENCES
Check for appropriate caching of sequences for the application requirements.

DC_USERS
Deadlock and resulting “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!” can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.

DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock and thus block online activity.

DC_SEGMENTS
This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

In my case enqueue type is “DC_OBJECTS”.Need to check for some object compilation activity.

I had the information that before this import activity some tables were created which used DB-LINK between the target and the source database.

As the tables were created using db-link, i was sure CTAS (create table as select) would have been used.I took a new session and try to create the table using CTAS and db-link and my session was also waiting of row cache lock.Investigation turned over to db-link usage.One of the best way to determine is,who’s using a dblink .It was very helpful to me.

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
substr(
   decode(bitand(ksuseidl,11),
      1,'ACTIVE',
      0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
      2,'SNIPED',
      3,'SNIPED',
      'KILLED'
   ),1,1
) "S",
substr(w.event,1,10) "WAITING"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where  g.K2GTDXCB =t.ktcxbxba
and   g.K2GTDSES=t.ktcxbses
and  s.addr=g.K2GTDSES
and  w.sid=s.indx
and s2.sid = w.sid
/


ORIGIN                GTXID                               LSESSION   USERNAME                       S WAITING
--------------------- ----------------------------------- ---------- ------------------------------ - ----------
gld01-23990        FLXP.9e2e919.112.20.4540          1050.8656  SYS                            A SQL*Net mo

Hmmm…got something…what session is this.What is it doing???Lets check it out

SELECT NVL(s.username, '(oracle)') AS username,
        s.osuser,
        s.sid,
        s.serial#,
        p.spid,
        s.status,
        s.module,
        s.terminal,
        s.machine,
        s.program,
        TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
        v$process p
WHERE  s.paddr  = p.addr
and s.sid=&sid
ORDER BY s.username, s.osuser
/
Enter value for sid: 1050
old  15: and s.sid=&sid
new  15: and s.sid=1050

USERNAME                       OSUSER                                SID    SERIAL# SPID         STATUS   MODULE
------------------------------ ------------------------------ ---------- ---------- ------------ -------- ------------------------------------------------
TERMINAL                       MACHINE                                                          PROGRAM                      LOGON_TIME
------------------------------ ---------------------------------------------------------------- ------------------------------------------------ --------------------
SYS                            oracle                               1050       8656 23991        ACTIVE   sqlplus@gld01 (TNS V1-V3)
                               gld01                                                         sqlplus@gld01 (TNS V1-V3) 05-MAY-2010 11:48:33

The sqlplus session is of ‘SYS’ and is active.The logon time was of a day before.Somebody who tried to create the table did a CTRL+C, but the might be he user’s request didn’t get canceled.Some other reasons might also be possible for this session still to be there.What is this session doing?

SQL>select sql_fulltext from v$sql where (ADDRESS,HASH_VALUE) in (select sql_address,sql_hash_value from v$session where sid= &sid_number); Enter value for sid_number: 1050
old   1: select sql_fulltext from v$sql where (ADDRESS,HASH_VALUE) in (select sql_address,sql_hash_value from v$session where sid= &sid_number)
new   1: select sql_fulltext from v$sql where (ADDRESS,HASH_VALUE) in (select sql_address,sql_hash_value from v$session where sid= 1050)

SQL_FULLTEXT
--------------------------------------------------------------------------------
CREATE TABLE anand.test AS
SELECT b.*, a.creation_date , a.last_modified_date 
  FROM table_name@prd2 a,
          table@prd2 b
 WHERE a.creation_date >= TO_DATE('01-JAN-2010 00:00:00', 'dd-mon-yyyy hh24:mi:s
s')
   AND a.creation_date <= TO_DATE('05-MAY-2010 00:00:00', 'dd-mon-yyyy hh24:mi:s
s')

Ohhhhh….this session is trying to create the table which was in the dumpfile and luckily or unluckily i also tried to create the same table using CTAS with db-link for investigating the row cache lock issue.

Now, the best option was to kill the session and try creating the table using CTAS with db-link and check for the session wait again.

SQL> alter system kill session '1050,8656' immediate;
alter system kill session '1050,8656' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL>SELECT NVL(s.username, '(oracle)') AS username,
        s.osuser,
        s.sid,
        s.serial#,
        p.spid,
        s.status,
        s.module,
        s.terminal,
        s.machine,
        s.program,
        TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
        v$process p
WHERE  s.paddr  = p.addr
and s.sid=&sid
ORDER BY s.username, s.osuser
/
Enter value for sid: 1050
old  15: and s.sid=&sid
new  15: and s.sid=1050

no rows selected

The session has gone.Lets now try to create the table

SQL>CREATE TABLE anand.test AS SELECT b.*, a.creation_date ,a.last_modified_date  FROM table_name@prd2 a,table@prd2 b
 WHERE a.creation_date >= TO_DATE('01-JAN-2010 00:00:00','dd-mon-yyyy hh24:mi:ss') AND a.creation_date 
/

Table created

After this dropped the test table and tried to import the dumpfile.It got imported successfully.It was a nice learning for me

About these ads

2010

05/07

CATEGORY

Oracle

Write comment