FAQ
Greetings,

Oracle 8.1.7.4

I have taken a Block dump,

Where do I look for missing commit?

Search Discussions

  • Anjo Kolk at Sep 18, 2006 at 10:36 pm
    I am sorry to be such a pain, but may be explaining what the problem is and
    what you want to do, will help me and others on this list.

    Anjo.
    On 9/18/06, BN wrote:

    Greetings,


    Oracle 8.1.7.4

    I have taken a Block dump,

    Where do I look for missing commit?



    --
    Regards & Thanks
    BN
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • BN at Sep 18, 2006 at 10:45 pm

    On 9/18/06, Anjo Kolk wrote:

    I am sorry to be such a pain, but may be explaining what the problem is
    and what you want to do, will help me and others on this list.

    Anjo.

    On 9/18/06, BN wrote:

    Greetings,


    Oracle 8.1.7.4

    I have taken a Block dump,

    Where do I look for missing commit?



    --
    Regards & Thanks
    BN


    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888
    Greetings

    Thank you Anjo.

    I am seeing an intermittent locing issue. One session blocking others
    I have identified the Blocker, Table
    from v$session I got the info:

    ROW_WAIT_OBJ# "OBJ#",ROW_WAIT_FILE# "FILE#",ROW_WAIT_BLOCK#

    "BLOCK#",ROW_WAIT_ROW# "ROW#"

    I dont think its a missing commit. The Blocker takes a while to complete
    making others to wait.

    Dont know from here what to do.

    For Example:

    SID SERIAL# USERNAME LOCKWAIT STATUS OSUSER PROCESS
    MACHINE PROGRAM Logon Time
    ------ ---------- --------- ---------------- -------- ---------- ---------
    ------------ -------------------- -----------------
    PID SPID OBJ# FILE# BLOCK# ROW#
    ------ --------- ---------- ---------- ---------- ----------
    726 32164 APP_USER1 C000000033D645E8 ACTIVE unixusr1 16533
    vrfhvp08 ABCD@ 09-17-06 09:51:19
    78 16718 76663 40 29125 6

    756 34355 APP_USER1 ACTIVE unixusr1 27139
    vrfhvp08 XYZ_at_v 09-18-06 18:19:29
    248 27155 -1 40 29125 6

    I know that sid 726 and other sids are waiting for the same
    obj3,file#,block#, row#

    I want to continue my investigation from here...
  • David Sharples at Sep 19, 2006 at 8:03 am
    why not just look at dba_blockers and dba_waiters - why are you doing block
    dumps?

    Find the blocking session and either kill it or call him and and ask him to
    commit the changes
    On 18/09/06, BN wrote:


    On 9/18/06, Anjo Kolk wrote:


    I am sorry to be such a pain, but may be explaining what the problem is
    and what you want to do, will help me and others on this list.

    Anjo.

    On 9/18/06, BN wrote:

    Greetings,


    Oracle 8.1.7.4

    I have taken a Block dump,

    Where do I look for missing commit?
    --
    http://www.freelists.org/webpage/oracle-l
  • BN at Sep 19, 2006 at 1:35 pm

    On 9/19/06, David Sharples wrote:
    why not just look at dba_blockers and dba_waiters - why are you doing
    block dumps?

    Find the blocking session and either kill it or call him and and ask him
    to commit the changes

    On 18/09/06, BN wrote:


    On 9/18/06, Anjo Kolk wrote:


    I am sorry to be such a pain, but may be explaining what the problem
    is and what you want to do, will help me and others on this list.

    Anjo.

    On 9/18/06, BN wrote:

    Greetings,


    Oracle 8.1.7.4

    I have taken a Block dump,

    Where do I look for missing commit?
    Greetings,

    Like I said I am able to identify this info from v$lock (request, lmode,
    block) and link it
    to v$session to get the object details.

    Initially I was thining that it was a missing "COMMIT" some where in the
    app, Later I saw a pattern.

    The Blocker (doing a TXN (DML) spanning many tables) was holding the lock a
    little longer, 10 secs are more. Being a very busy OLTP, this was causing
    others issues.

    I wasn't sure where the Blocker loosing his time, Later in the evening, I
    noticed that there were many ST enqueue locks (no, we are not using LMTS -
    For all new tables I am pushing LMTS). I am assuming that this could be an
    issue. Bumped up the next extent size. The tables were large and extents
    were in 1000s.

    I am also monitoring v$session_wait. Its intermittent and fast. The users
    complain that they are stuck and if they retry they are back to normal.

    I have been sitting on this all day yesterday, took some Level 12 traces and
    fixed some expensive queries, not sure if they helped or not. Those
    querues are running faster now.

    Again back to monitoring today ...

    Appreciate your ides/thoughts ....
  • Mark W. Farnham at Sep 19, 2006 at 2:22 pm
    HAVING many many extents is rarely a problem, even with dictionary managed
    tablespaces (with the notable exception of online drop, and pathological
    conditions such as having a huge number of extents each smaller than the
    multiblock read size on tables scanned frequently enough to be a problem and
    infrequently enough to age out of cache.)



    Making extents frequently, on the other hand, could be your entire problem
    here.



    If you create a table with columns like dba_extents plus an as_of_date
    column, and track your dictionary based objects as they change in size
    (perhaps once or twice a day is probably enough), you will be able to
    quickly identify objects which have an inappropriate next size. Now
    rebuilding those objects is usually a time waster unless they exhibit empty
    front (ie. Many empty blocks at the beginning of a table or cluster that is
    significantly scanned before the block that contains the first row or a
    large honeycomb factor that is an artifact of non-recurring past behavior.)
    But changing the next size on the identified quickly extending objects to
    something that will cause an extent allocation about once a day (or less,
    some folks shoot for once a quarter or less) should eliminate the
    possibility that piling up on allocating extents is your problem.



    Now while I usually advocate finding the actual individual problem and
    solving that (see Hotsos, Oak table, etc.), it sounds to me as if in this
    case you have a storm of obfuscation that can be quickly and easily
    eliminated. Then, if the systemic noise was not your entire problem, you
    should return to resolving the actual individual problems by wait analysis.



    Rightsizing, Inc. used to maintain and sell a product called "extmon"
    designed to track object growth as an aid to capacity planning. Since it was
    clear text sql, unauthorized free distribution soon made sales small enough
    to discontinue the product (it was only $500). You can probably build it
    yourself in a few hours and make it fast in a bit longer. Or you can
    probably find it, a derivative, or an independently created similar script
    set laying around the net somewhere.



    Good luck. Just please don't misconstrue this advice as suggesting you
    should spend your life rebuilding objects.





    From: oracle-l-bounce_at_freelists.org
    On Behalf Of BN
    Sent: Tuesday, September 19, 2006 9:35 AM
    To: David Sharples
    Cc: Anjo Kolk; _oracle_L_list
    Subject: Re: Block dump - Uncommitted TXN - Help Urgent





    On 9/19/06, David Sharples wrote:

    why not just look at dba_blockers and dba_waiters - why are you doing block
    dumps?



    Find the blocking session and either kill it or call him and and ask him to
    commit the changes



    On 18/09/06, BN wrote:



    On 9/18/06, Anjo Kolk wrote:

    I am sorry to be such a pain, but may be explaining what the problem is and
    what you want to do, will help me and others on this list.

    Anjo.



    On 9/18/06, BN wrote:

    Greetings,

    Oracle 8.1.7.4 <http://8.1.7.4/>

    I have taken a Block dump,

    Where do I look for missing commit?


    Greetings,

    Like I said I am able to identify this info from v$lock (request, lmode,
    block) and link it
    to v$session to get the object details.

    Initially I was thining that it was a missing "COMMIT" some where in the
    app, Later I saw a pattern.

    The Blocker (doing a TXN (DML) spanning many tables) was holding the lock a
    little longer, 10 secs are more. Being a very busy OLTP, this was causing
    others issues.

    I wasn't sure where the Blocker loosing his time, Later in the evening, I
    noticed that there were many ST enqueue locks (no, we are not using LMTS -
    For all new tables I am pushing LMTS). I am assuming that this could be an
    issue. Bumped up the next extent size. The tables were large and extents
    were in 1000s.

    I am also monitoring v$session_wait. Its intermittent and fast. The users
    complain that they are stuck and if they retry they are back to normal.

    I have been sitting on this all day yesterday, took some Level 12 traces and
    fixed some expensive queries, not sure if they helped or not. Those
    querues are running faster now.

    Again back to monitoring today ...

    Appreciate your ides/thoughts ....
  • BN at Sep 19, 2006 at 4:06 pm

    On 9/19/06, Mark W. Farnham wrote:
    HAVING many many extents is rarely a problem, even with dictionary
    managed tablespaces (with the notable exception of online drop, and
    pathological conditions such as having a huge number of extents each smaller
    than the multiblock read size on tables scanned frequently enough to be a
    problem and infrequently enough to age out of cache.)



    Making extents frequently, on the other hand, could be your entire problem
    here.



    If you create a table with columns like dba_extents plus an as_of_date
    column, and track your dictionary based objects as they change in size
    (perhaps once or twice a day is probably enough), you will be able to
    quickly identify objects which have an inappropriate next size. Now
    rebuilding those objects is usually a time waster unless they exhibit empty
    front (ie. Many empty blocks at the beginning of a table or cluster that is
    significantly scanned before the block that contains the first row or a
    large honeycomb factor that is an artifact of non-recurring past behavior.)
    But changing the next size on the identified quickly extending objects to
    something that will cause an extent allocation about once a day (or less,
    some folks shoot for once a quarter or less) should eliminate the
    possibility that piling up on allocating extents is your problem.



    Now while I usually advocate finding the actual individual problem and
    solving that (see Hotsos, Oak table, etc.), it sounds to me as if in this
    case you have a storm of obfuscation that can be quickly and easily
    eliminated. Then, if the systemic noise was not your entire problem, you
    should return to resolving the actual individual problems by wait analysis.



    Rightsizing, Inc. used to maintain and sell a product called "extmon"
    designed to track object growth as an aid to capacity planning. Since it was
    clear text sql, unauthorized free distribution soon made sales small enough
    to discontinue the product (it was only $500). You can probably build it
    yourself in a few hours and make it fast in a bit longer. Or you can
    probably find it, a derivative, or an independently created similar script
    set laying around the net somewhere.



    Good luck. Just please don't misconstrue this advice as suggesting you
    should spend your life rebuilding objects.




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

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *BN
    *Sent:* Tuesday, September 19, 2006 9:35 AM
    *To:* David Sharples
    *Cc:* Anjo Kolk; _oracle_L_list
    *Subject:* Re: Block dump - Uncommitted TXN - Help Urgent





    On 9/19/06, *David Sharples* wrote:

    why not just look at dba_blockers and dba_waiters - why are you doing
    block dumps?



    Find the blocking session and either kill it or call him and and ask him
    to commit the changes



    On 18/09/06, *BN* wrote:



    On 9/18/06, *Anjo Kolk* wrote:


    I am sorry to be such a pain, but may be explaining what the problem is
    and what you want to do, will help me and others on this list.

    Anjo.



    On 9/18/06, *BN* wrote:

    Greetings,


    Oracle 8.1.7.4

    I have taken a Block dump,

    Where do I look for missing commit?




    Greetings,

    Like I said I am able to identify this info from v$lock (request, lmode,
    block) and link it
    to v$session to get the object details.

    Initially I was thining that it was a missing "COMMIT" some where in the
    app, Later I saw a pattern.

    The Blocker (doing a TXN (DML) spanning many tables) was holding the lock
    a little longer, 10 secs are more. Being a very busy OLTP, this was causing
    others issues.

    I wasn't sure where the Blocker loosing his time, Later in the evening, I
    noticed that there were many ST enqueue locks (no, we are not using LMTS -
    For all new tables I am pushing LMTS). I am assuming that this could be an
    issue. Bumped up the next extent size. The tables were large and extents
    were in 1000s.

    I am also monitoring v$session_wait. Its intermittent and fast. The users
    complain that they are stuck and if they retry they are back to normal.

    I have been sitting on this all day yesterday, took some Level 12 traces
    and fixed some expensive queries, not sure if they helped or not. Those
    querues are running faster now.

    Again back to monitoring today ...

    Appreciate your ides/thoughts ....
    --
    Regards & Thanks
    BN
    Greetings,

    I was thinking of INITRANS, is there a way to track that from v$lock,
    I remember once Steve Adams answering some body looking at v$lock looking
    at Request Column, not sure though
  • John Kanagaraj at Sep 19, 2006 at 6:11 pm
    BN,
    I wasn't sure where the Blocker loosing his time, Later in the evening, I
    noticed that there were many ST enqueue locks (no, we are not using LMTS -
    For all new tables I am pushing LMTS). I am assuming that this could be an
    issue. Bumped up the next extent size. The tables were large and extents
    were in 1000s.
    I am also monitoring v$session_wait. Its intermittent and fast. The users
    complain that they are stuck and if they retry they are back to normal.
    Is your "TEMP" tablespace LMT or Dict? Is it of type temporary or
    normal? I ask this because you mention ST enqueue and this is held for
    creating/dropping extents in a dict managed tbs.... You might be
    mistaking a 'enqueue' wait for a user lock when it is actually waiting
    for a "system" lock (on FET$ and UET$)....

    --
    John Kanagaraj <><
    DB Soft Inc
    Phone: 408-970-7002 (W)

    Disappointment is always inevitable; Discouragement is invariably optional

    ** The opinions and facts contained in this message are entirely mine
    and do not reflect those of my employer or customers **
    --
    http://www.freelists.org/webpage/oracle-l
  • BN at Sep 19, 2006 at 6:32 pm

    On 9/19/06, John Kanagaraj wrote:
    BN,
    I wasn't sure where the Blocker loosing his time, Later in the evening
    , I
    noticed that there were many ST enqueue locks (no, we are not using LMTS -
    For all new tables I am pushing LMTS). I am assuming that this could be an
    issue. Bumped up the next extent size. The tables were large and extents
    were in 1000s.
    I am also monitoring v$session_wait. Its intermittent and fast. The
    users
    complain that they are stuck and if they retry they are back to normal.
    Is your "TEMP" tablespace LMT or Dict? Is it of type temporary or
    normal? I ask this because you mention ST enqueue and this is held for
    creating/dropping extents in a dict managed tbs.... You might be
    mistaking a 'enqueue' wait for a user lock when it is actually waiting
    for a "system" lock (on FET$ and UET$)....

    --
    John Kanagaraj <><
    DB Soft Inc
    Phone: 408-970-7002 (W)

    Disappointment is always inevitable; Discouragement is invariably optional

    ** The opinions and facts contained in this message are entirely mine
    and do not reflect those of my employer or customers **
    Greetings John

    TBS STATUS CONTENTS EXTENT_MAN ALLOCATIO

    --------------- --------- --------- ---------- ---------
    TEMP ONLINE TEMPORARY LOCAL UNIFORM
  • Christian Antognini at Sep 19, 2006 at 8:45 pm

    I was thinking of INITRANS, is there a way to track that from
    v$lock,
    I remember once Steve Adams answering some body looking at v$lock
    looking at Request Column, not sure though
    Hi

    Here an example of sessions waiting for a transaction slot:

    SQL> SELECT event FROM v$session_wait WHERE sid = 12;

    EVENT

    enqueue

    SQL> SELECT type, id1, id2, lmode, request FROM v$lock WHERE sid = 12;

    TY ID1 ID2 LMODE REQUEST

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

    TM 34522 0 3 0
    TX 393247 9877 0 4

    As you can see the request mode is S (4).

    HTH

    Chris
  • BN at Sep 20, 2006 at 12:34 pm

    On 9/19/06, Christian Antognini wrote:

    I was thinking of INITRANS, is there a way to track that from
    v$lock,
    I remember once Steve Adams answering some body looking at v$lock
    looking at Request Column, not sure though
    Hi

    Here an example of sessions waiting for a transaction slot:

    SQL> SELECT event FROM v$session_wait WHERE sid = 12;

    EVENT
    -----------
    enqueue

    SQL> SELECT type, id1, id2, lmode, request FROM v$lock WHERE sid = 12;

    TY ID1 ID2 LMODE REQUEST
    -- ---------- ---------- ---------- ----------
    TM 34522 0 3 0
    TX 393247 9877 0 4

    As you can see the request mode is S (4).


    HTH
    Chris
    --
    http://www.freelists.org/webpage/oracle-l

    Greetings,

    Yes I saw Request=4 for quite a few sessions. I ahve Identified the Tables
    that needs INITRANS Bump. Is there a way to figureout how much should I
    bump for each table in question
  • Goran bogdanovic at Sep 20, 2006 at 12:50 pm
    Hi,

    The inittrans should correspond to your max active concurrent dml sessions
    on that table

    goran
    On 9/20/06, BN wrote:


    On 9/19/06, Christian Antognini wrote:


    I was thinking of INITRANS, is there a way to track that from
    v$lock,
    I remember once Steve Adams answering some body looking at v$lock
    looking at Request Column, not sure though
    Hi

    Here an example of sessions waiting for a transaction slot:

    SQL> SELECT event FROM v$session_wait WHERE sid = 12;

    EVENT
    -----------
    enqueue

    SQL> SELECT type, id1, id2, lmode, request FROM v$lock WHERE sid = 12;

    TY ID1 ID2 LMODE REQUEST
    -- ---------- ---------- ---------- ----------
    TM 34522 0 3 0
    TX 393247 9877 0 4

    As you can see the request mode is S (4).


    HTH
    Greetings,

    Yes I saw Request=4 for quite a few sessions. I ahve Identified the
    Tables that needs INITRANS Bump. Is there a way to figureout how much
    should I bump for each table in question

    --
    Regards & Thanks
    BN
    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Sep 20, 2006 at 1:57 pm
    That's a bit high in some cases. How about the minimum of (maximum number of
    rows in a single block, maximum number of concurrent transactions on the
    table).



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of goran bogdanovic
    Sent: Wednesday, September 20, 2006 8:51 AM
    To: bnsarma_at_gmail.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Block dump - Uncommitted TXN - Help Urgent



    Hi,

    The inittrans should correspond to your max active concurrent dml sessions
    on that table

    goran

    On 9/20/06, BN < bnsarma_at_gmail.com > wrote:


    On 9/19/06, Christian Antognini > wrote:

    I was thinking of INITRANS, is there a way to track that from
    v$lock,
    I remember once Steve Adams answering some body looking at v$lock
    looking at Request Column, not sure though
    Hi

    Here an example of sessions waiting for a transaction slot:

    SQL> SELECT event FROM v$session_wait WHERE sid = 12;

    EVENT

    enqueue

    SQL> SELECT type, id1, id2, lmode, request FROM v$lock WHERE sid = 12;

    TY ID1 ID2 LMODE REQUEST

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

    TM 34522 0 3 0
    TX 393247 9877 0 4

    As you can see the request mode is S (4).

    HTH

    Chris

    --
    http://www.freelists.org/webpage/oracle-l

    Greetings,

    Yes I saw Request=4 for quite a few sessions. I ahve Identified the Tables
    that needs INITRANS Bump. Is there a way to figureout how much should I
    bump for each table in question

    --
    Regards & Thanks

    BN

    --
    http://www.freelists.org/webpage/oracle-l
  • BN at Sep 20, 2006 at 2:14 pm

    On 9/20/06, goran bogdanovic wrote:
    Hi,

    The inittrans should correspond to your max active concurrent dml sessions
    on that table

    goran

    On 9/20/06, BN < bnsarma_at_gmail.com> wrote:



    On 9/19/06, Christian Antognini
    wrote:

    I was thinking of INITRANS, is there a way to track that from
    v$lock,
    I remember once Steve Adams answering some body looking at v$lock
    looking at Request Column, not sure though
    Hi

    Here an example of sessions waiting for a transaction slot:

    SQL> SELECT event FROM v$session_wait WHERE sid = 12;

    EVENT
    -----------
    enqueue

    SQL> SELECT type, id1, id2, lmode, request FROM v$lock WHERE sid = 12;


    TY ID1 ID2 LMODE REQUEST
    -- ---------- ---------- ---------- ----------
    TM 34522 0 3 0
    TX 393247 9877 0 4

    As you can see the request mode is S (4).


    HTH
    Greetings,

    Yes I saw Request=4 for quite a few sessions. I ahve Identified the
    Tables that needs INITRANS Bump. Is there a way to figureout how much
    should I bump for each table in question

    --
    Regards & Thanks
    BN
    Greetings

    Yes, I understand, is there a view in the Datadictionary to check how much
    each table has reached, other than tracking/sampling v$lock or v$transaction
  • Bobak, Mark at Sep 20, 2006 at 4:13 pm
    Note that if you see a TX enqueue waiting on mode 4, that's not
    necessarily ITL slot shortage. If the waiting statement is an INSERT
    and the object is a heap table, then it's definitely not ITL shortage.
    (If there's ITL shortage on a table block, INSERT will go to another
    block on the free list, if it's on the index, INSERT will do a block
    split.)


    The other cause TX enqueue mode 4 waits is concurrent inserts colliding
    on a unique index, PK, or UK. (i.e. session 1 inserts key value 1,
    session 2 inserts value 2, session 1 attempts to insert value 2, session
    2 attempts to insert value 1)


    Hope that helps,


    -Mark



    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    "A human being should be able to change a diaper, plan an invasion,
    butcher a hog, conn a ship, design a building, write a sonnet, balance
    accounts, build a wall, set a bone, comfort the dying, take orders, give
    orders, cooperate, act alone, solve equations, analyze a new problem,
    pitch manure, program a computer, cook a tasty meal, fight efficiently,
    die gallantly. Specialization is for insects." --Robert A. Heinlein

    ________________________________

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of BN
    Sent: Wednesday, September 20, 2006 10:15 AM
    To: goran bogdanovic
    Cc: oracle-l_at_freelists.org
    Subject: Re: Block dump - Uncommitted TXN - Help Urgent

    On 9/20/06, goran bogdanovic wrote:

    Hi,

    The inittrans should correspond to your max active concurrent
    dml sessions on that table

    goran

    On 9/20/06, BN < bnsarma_at_gmail.com >
    wrote:

    On 9/19/06, Christian Antognini <
    Christian.Antognini_at_trivadis.com
    wrote:
    I was thinking of INITRANS, is there a way to
    track that from
    v$lock,
    I remember once Steve Adams answering some
    body looking at v$lock
    looking at Request Column, not sure though
    Hi

    Here an example of sessions waiting for a
    transaction slot:

    SQL> SELECT event FROM v$session_wait WHERE sid
    = 12;

    EVENT
    -----------
    enqueue

    SQL> SELECT type, id1, id2, lmode, request FROM
    v$lock WHERE sid = 12;

    TY ID1 ID2 LMODE REQUEST
    -- ---------- ---------- ---------- ----------
    TM 34522 0 3 0
    TX 393247 9877 0 4

    As you can see the request mode is S (4).

    HTH
    Chris
    --
    http://www.freelists.org/webpage/oracle-l

    Greetings,

    Yes I saw Request=4 for quite a few sessions. I ahve
    Identified the Tables that needs INITRANS Bump. Is there a way to
    figureout how much should I bump for each table in question

    --
    Regards & Thanks

    BN

    Greetings

    Yes, I understand, is there a view in the Datadictionary to check how
    much each table has reached, other than tracking/sampling v$lock or
    v$transaction

    --
    Regards & Thanks
    BN

    --
    http://www.freelists.org/webpage/oracle-l
  • BN at Sep 20, 2006 at 5:03 pm

    On 9/20/06, Bobak, Mark wrote:
    Note that if you see a TX enqueue waiting on mode 4, that's not
    necessarily ITL slot shortage. If the waiting statement is an INSERT and
    the object is a heap table, then it's definitely not ITL shortage. (If
    there's ITL shortage on a table block, INSERT will go to another block on
    the free list, if it's on the index, INSERT will do a block split.)

    The other cause TX enqueue mode 4 waits is concurrent inserts colliding on
    a unique index, PK, or UK. (i.e. session 1 inserts key value 1, session 2
    inserts value 2, session 1 attempts to insert value 2, session 2 attempts to
    insert value 1)

    Hope that helps,

    -Mark



    *--*
    *Mark J. Bobak*
    *Senior Oracle Architect*
    *ProQuest Information & Learning*

    "A human being should be able to change a diaper, plan an invasion,
    butcher a hog, conn a ship, design a building, write a sonnet, balance
    accounts, build a wall, set a bone, comfort the dying, take orders, give
    orders, cooperate, act alone, solve equations, analyze a new problem, pitch
    manure, program a computer, cook a tasty meal, fight efficiently, die
    gallantly. Specialization is for insects." --Robert A. Heinlein



    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *BN
    *Sent:* Wednesday, September 20, 2006 10:15 AM
    *To:* goran bogdanovic

    *Cc:* oracle-l_at_freelists.org
    *Subject:* Re: Block dump - Uncommitted TXN - Help Urgent


    On 9/20/06, goran bogdanovic wrote:

    Hi,

    The inittrans should correspond to your max active concurrent dml
    sessions on that table

    goran

    On 9/20/06, BN < bnsarma_at_gmail.com> wrote:



    On 9/19/06, Christian Antognini < Christian.Antognini_at_trivadis.com >
    wrote:

    I was thinking of INITRANS, is there a way to track that from
    v$lock,
    I remember once Steve Adams answering some body looking at v$lock
    looking at Request Column, not sure though
    Hi

    Here an example of sessions waiting for a transaction slot:

    SQL> SELECT event FROM v$session_wait WHERE sid = 12;

    EVENT
    -----------
    enqueue

    SQL> SELECT type, id1, id2, lmode, request FROM v$lock WHERE sid =
    12;

    TY ID1 ID2 LMODE REQUEST
    -- ---------- ---------- ---------- ----------
    TM 34522 0 3 0
    TX 393247 9877 0 4

    As you can see the request mode is S (4).


    HTH
    Greetings,

    Yes I saw Request=4 for quite a few sessions. I ahve Identified the
    Tables that needs INITRANS Bump. Is there a way to figureout how much
    should I bump for each table in question

    --
    Regards & Thanks
    BN
    Greetings

    Yes, I understand, is there a view in the Datadictionary to check how
    much each table has reached, other than tracking/sampling v$lock or
    v$transaction


    --
    Regards & Thanks
    BN
    Greetings
    Mark, Thank you.

    Right, I read that from the Metalink note: 62354.1

    ST Enque and Request=4 is what I have noticed.
  • Diego Cutrone at Sep 20, 2006 at 2:38 pm
    Hi BN

    They way I do this is by taking a block dump on the
    table blocks (all of them if possible - or the ones
    closer to the HWM otherwise) and check for the ITC
    value. This will report the maximun ammount of
    concurrent transactions that the block suffered. I'm
    sure you can then go from there and set up an accurate
    figure for the 'inittrans' value.

    LINUX:oracle10:TEST10:/u01/app/oracle/admin/TEST10/udump>
    grep itc test10_ora_9715.trc
    seg/obj: 0x1d72 csc: 0x00.454cc itc: 3 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.5058b itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.5058b itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50711 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50711 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50711 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50711 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50715 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50715 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50715 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50715 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50715 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50716 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50716 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.50716 itc: 2 flg: -
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.5071a itc: 2 flg: O
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.5071a itc: 2 flg: O
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.5071a itc: 2 flg: O
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.5071a itc: 2 flg: O
    typ: 1 - DATA
    seg/obj: 0x1d72 csc: 0x00.5071a itc: 2 flg: O
    typ: 1 - DATA

    Hope this helps
    Regards
    diego
    ----- Original Message -----
    From: BN
    To: Christian.Antognini_at_trivadis.com
    Cc: oracle-l_at_freelists.org
    Sent: Wednesday, September 20, 2006 9:34 AM
    Subject: Re: Block dump - Uncommitted TXN - Help
    Urgent

    On 9/19/06, Christian Antognini
    wrote:
    I was thinking of INITRANS, is there a way to track that from
    v$lock,
    I remember once Steve Adams answering some body
    looking at v$lock
    looking at Request Column, not sure though
    Hi

    Here an example of sessions waiting for a transaction
    slot:

    SQL> SELECT event FROM v$session_wait WHERE sid = 12;

    EVENT

    enqueue

    SQL> SELECT type, id1, id2, lmode, request FROM v$lock
    WHERE sid = 12;

    TY ID1 ID2 LMODE REQUEST

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

    TM 34522 0 3 0
    TX 393247 9877 0 4

    As you can see the request mode is S (4).

    HTH

    Chris

    --
    http://www.freelists.org/webpage/oracle-l

    Greetings,

    Yes I saw Request=4 for quite a few sessions. I ahve
    Identified the Tables that needs INITRANS Bump. Is
    there a way to figureout how much should I bump for
    each table in question

    --
    Regards & Thanks
    BN

    __________________________________________________
    Preguntá. Respondé. Descubrí.
    Todo lo que querías saber, y lo que ni imaginabas,
    está en Yahoo! Respuestas (Beta).
    ¡Probalo ya!
    http://www.yahoo.com.ar/respuestas

    --
    http://www.freelists.org/webpage/oracle-l
  • Christian Antognini at Sep 21, 2006 at 3:03 pm
    Hi

    Yes I saw Request=4 for quite a few sessions. I ahve Identified the
    Tables that needs INITRANS Bump. Is there a way to figureout how
    much should I bump for each table in question
    If you really experience ITL waits I'm not aware of THE method to
    correctly size these stuffs... I'll probably do it in this way...

    I would find out if the problem is due to MAXTRANS or to free space.
    In fact Oracle should dynamically allocate additional ITL slots if
    necessary, provided you have free space in the blocks *and* MAXTRANS has
    not been reached.

    2a) If the problem is MAXTRANS, i.e. if the number of ITL slots has not
    been (unnecessarily) limited, then there are two possibilities:

    Increase MAXTRANS.
    Increase INITRANS.

    If there are few ITL waits (few means that the ratio between the number
    of DML and the waits is small, e.g. few percent) the former is probably
    better.
    If there are lots of ITL waits, i.e. INITRANS is way too small, the
    latter is probably better.

    2b) If the problem is free space, then there are two possibilities:

    Increase PCTFREE.
    Increase INITRANS.

    To choose between these two the same method as before can be used.

    Note: IMHO is often better to set MAXTRANS to a high value and increase
    PCTFREE of 1 or 2 percent instead of setting INITRANS to very high
    values.

    HTH

    Chris
  • Bobak, Mark at Sep 21, 2006 at 3:24 pm
    Also, note that possibly in 10.1, definitely in 10.2, MAXTRANS is
    desupported. (It's there, but it doesn't actually limit the number of
    ITL slots, only free space limits how many slots you can have.)

    -Mark

    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    "A human being should be able to change a diaper, plan an invasion,
    butcher a hog, conn a ship, design a building, write a sonnet, balance
    accounts, build a wall, set a bone, comfort the dying, take orders, give
    orders, cooperate, act alone, solve equations, analyze a new problem,
    pitch manure, program a computer, cook a tasty meal, fight efficiently,
    die gallantly. Specialization is for insects." --Robert A. Heinlein

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Christian Antognini
    Sent: Thursday, September 21, 2006 11:03 AM
    To: BN
    Cc: oracle-l_at_freelists.org
    Subject: RE: Block dump - Uncommitted TXN - Help Urgent

    Hi
    Yes I saw Request=4 for quite a few sessions. I ahve Identified the
    Tables that needs INITRANS Bump. Is there a way to figureout how much
    should I bump for each table in question
    If you really experience ITL waits I'm not aware of THE method to
    correctly size these stuffs... I'll probably do it in this way...

    1) I would find out if the problem is due to MAXTRANS or to free space.
    In fact Oracle should dynamically allocate additional ITL slots if
    necessary, provided you have free space in the blocks *and* MAXTRANS has
    not been reached.

    2a) If the problem is MAXTRANS, i.e. if the number of ITL slots has not
    been (unnecessarily) limited, then there are two possibilities:

    - Increase MAXTRANS.
    - Increase INITRANS.

    If there are few ITL waits (few means that the ratio between the number
    of DML and the waits is small, e.g. few percent) the former is probably
    better.
    If there are lots of ITL waits, i.e. INITRANS is way too small, the
    latter is probably better.

    2b) If the problem is free space, then there are two possibilities:

    - Increase PCTFREE.
    - Increase INITRANS.

    To choose between these two the same method as before can be used.

    Note: IMHO is often better to set MAXTRANS to a high value and increase
    PCTFREE of 1 or 2 percent instead of setting INITRANS to very high
    values.

    HTH
    Chris
  • Oracle-l-bounce_at_freelists.org at Sep 21, 2006 at 3:35 pm
    Hi Mark
    Also, note that possibly in 10.1, definitely in 10.2, MAXTRANS is
    desupported.
    It is deprecated since 10.1.

    Cheers,
    Chris

    PS: I didn't mention it because the OP works with 8.1.7.4.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 18, '06 at 9:36p
activeSep 21, '06 at 3:35p
posts20
users10
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase