Tuesday, March 13, 2012

To re-create "enq: RO – fast object reuse" problem

create 3 sessions.

Session 1:

SQL> select count(*) from mchbas;

  COUNT(*)
----------
   5943314


SQL> create table mchbastemp as select * from mchbas;
Table created

SQL> select count(*) from mchbastemp;

  COUNT(*)
----------
   5943314

SQL> select * from mchbastemp where mbaprf='999' and rownum<20001;

    MBASEQ MBATKP MBAETF MBATII MBAPRF MBAFRM MBATKT   MBACPN ......         MBAPRH          MBAPLH MBARWI MBARRI MBAOID    MBAEFD     MBAOVD

---------- ------ ------ ------ ------ ------ -------- ------ ------ ------ ------ ------ -------- ------ ------ ------ ------ ......------ ------ --------- ----------

 355728497 PAX    Y      I      999    448    1487413       1 AC     O      A      ATK    08307736 CN     SHA    CA     Q             149    ......201011  20110209  201102            20101122  201011  20110209  201102                                                  M             N                    CU     TU                                                                         0      1                                               N 

Session 2:

SQL> select * from v$mystat where rownum<2;
       SID STATISTIC#      VALUE

---------- ---------- ----------

       377          0          1

SQL> truncate table mchbastemp;

......

Truncate is hanging.

Session 3:

SQL> select event from v$session where sid=377;

EVENT

----------------------------------------------------------------
enq: RO - fast object reuse


topas:
Name            PID  CPU%  PgSp Owner           ServerV3       0   "h" for help

oracle       225602  32.2  52.8 oracle          ClientV3       0   "q" to quit

xmwlm        221686   0.7   0.8 root

topas       1417238   0.5   5.5 oracle

SQL> exec sys.printsql(225602,'SPID');

--------------------------------------------------------------------------------------

The sql text has been aged out from the shared pool.

--------------------------------------------------------------------------------------
The session id is 442
The status is ACTIVE
The sql hash value is 0
The prev hash value is 0
The osuser is oracle
The machine is p690ca
The terminal is UNKNOWN
The program is oracle@p690ca (DBW0)

--------------------------------------------------------------------------------------

alter system kill session '442,1' immediate;

PL/SQL procedure successfully completed


To resolve the problem:
SQL> alter system flush buffer_cache;
System altered.

set "_db_fast_obj_truncate"=FALSE to spfile/init.ora

No comments:

Post a Comment