Locking on bitmap index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Locking on bitmap index

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    Locking on bitmap index

    All, I have a question in bitmap index. It would be great if any one could clarify this... I have two scenario's. In first scenario, it is locking. But the second scenario, it is not locking. I am not sure why it is...

    Scenario 1
    ==========

    Session 1
    =========

    SQL> create table t_bitmap(no number,name varchar2(10),status char(1));

    Table created.

    SQL> ed
    Wrote file afiedt.buf

    1* create bitmap index biidx on t_bitmap(status)
    SQL> /

    Index created.

    SQL>

    SQL> insert into t_bitmap values(1,'Scott','M');

    1 row created.

    SQL>


    Session 2
    =========
    SQL> insert into t_bitmap values(3,'John','F');


    This session is hanging.....



    ================================================================================


    Scenario 2
    ==========

    Session 1
    =========
    SQL> create table t_bitmap(no number,name varchar2(10),status char(1));

    Table created.

    SQL> declare
    2 begin
    3 for i in 1.. 100 loop
    4 insert into t_bitmap values(1,'Scott','F');
    5 end loop;
    6 end;
    7 /

    PL/SQL procedure successfully completed.

    SQL> declare
    2 begin
    3 for i in 1.. 100 loop
    4 insert into t_bitmap values(1,'Scott','M');
    5 end loop;
    6 end;
    7 /

    PL/SQL procedure successfully completed.

    SQL> create bitmap index biidx on t_bitmap(status);

    Index created.

    SQL> insert into t_bitmap values(9999,'Scott','M');

    1 row created.

    SQL>

    Session 2
    ==========
    SQL> insert into t_bitmap values(9999,'Scott','F');

    1 row created.

    SQL>

    Here the session is not hanging...

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    May be the session is hanging due to some other reason. check alert log for any error..

    I tried in my D/B. Did not get any hanging session..
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    Malay, I checked the alert log. I don't see any information. Are you sure that, it is working in your DB? I checked the same in different database. It is hanging...

    Here is my db version.

    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    PL/SQL Release 9.2.0.1.0 - Production
    CORE 9.2.0.1.0 Production
    TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
    NLSRTL Version 9.2.0.1.0 - Production



    Quote Originally Posted by malay_biswal
    May be the session is hanging due to some other reason. check alert log for any error..

    I tried in my D/B. Did not get any hanging session..

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    whats the sessions waiting on?

  5. #5
    Join Date
    Dec 2005
    Posts
    195
    Dav, I believe, you are asking which session is waiting.. The session 2 is waiting in Scenario I. I am sorry if i did not understand your question.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    and what is it waiting on?

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    check v$session_wait..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Dec 2005
    Posts
    195
    Here is the entry in v$session_wait. But i am not able to understand the reason for waiting...

    PHP Code:
    SID    SEQ#    EVENT    P1TEXT    P1    P1RAW    P2TEXT    P2    P2RAW    P3TEXT    P3    P3RAW    WAIT_TIME    SECONDS_IN_WAIT    STATE
    19    80    enqueue    name|mode    1415053316    54580004    id1    655392    000A0020    id2    4,522    000011AA    0    0    WAITING 

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    is your dba not around to help you know what an enqueue is?

  10. #10
    Join Date
    Dec 2005
    Posts
    195
    Thanks Dav. I will take care of it.
    Quote Originally Posted by davey23uk
    is your dba not around to help you know what an enqueue is?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width