Locking issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Locking issue

  1. #1
    Join Date
    Nov 2002
    Posts
    31

    Locking issue

    Hi

    Is there any way to find out if we have locking issue on sequence or table level. Or Latch issue ?

    we have 15 batch process inserts data into table. before they insert , then generate the key from sequence.

    i am generating perfstat reports

    thanks
    gcx

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Having a cache on the sequence and a higer freelists on the table will solve your problem.

  3. #3
    Join Date
    Nov 2002
    Posts
    31

    catching contention

    I do have 1000 sequence cached & freelist 4 ( Equal to Number of cpu )

    Any idea , how can i see if i am really getting contention on that table or sequence

    gc

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Query from v$session_wait and v$system_event and post the result here.

    Tamil

  5. #5
    Join Date
    Nov 2002
    Posts
    31

    v$system_event o/p

    EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
    Null event 1 1 0 0
    latch free 15934893 11621873 5421190 .3402087481855071132263015509423251226099
    pmon timer 286162 217250 15534052 54.28411878586255337885533369210447229192
    inactive session 1 1 0 0
    process startup 16 0 0 0
    rdbms ipc reply 1046 71 796 .760994263862332695984703632887189292543
    rdbms ipc message 7834899 588844 131351727 16.76495472373032504949968085102309551151
    enqueue 108094 190 13546 .1253168538494273502692101319222158491683
    imm op 1035 1 4 .003864734299516908212560386473429951690821
    io done 12419449 3167160 7699878 .6199854760062221762012147237771981671651
    slave wait 11254523 1898314 54232953 4.81876957379713027375749287641955149943
    reliable message 1 0 0 0
    control file sequential read 1324874 0 1844 .00139183046840680698692856830158943416506
    control file parallel write 232640 0 176605 .7591342847317744154057771664374140302613
    refresh controlfile command 262093 2 2896 .0110495129591404577764381345552914423506
    free buffer waits 64407 54269 1510295 23.44923688418960671976648500939338891735
    local write wait 23 22 0 0
    checkpoint completed 9 8 0 0
    buffer deadlock 2820 2816 54 .0191489361702127659574468085106382978723
    write complete waits 2005 1923 22351 11.1476309226932668329177057356608478803
    buffer busy waits 656645 5998 305943 .4659184186280257978055113493592428176564
    log file sequential read 534 0 47 .0880149812734082397003745318352059925094
    log file single write 1042 0 202 .1938579654510556621880998080614203454894
    log file parallel write 8848292 0 2053439 .2320717941948570413363392618598029992681
    LGWR wait for redo copy 1288745 873340 349578 .2712545926463342243810839227310290243609
    log buffer space 400904 49 417187 1.04061570849879272843373974816913774869
    log file switch completion 5195 21 23193 4.46448508180943214629451395572666025024
    log file sync 7288368 391 2288398 .3139794807287447615158839399986389271233
    db file sequential read 26378980 0 5557177 .2106668642987712185990512142622648790818
    db file scattered read 36795230 0 1624556 .044151266346208462346885724046296218287
    db file single write 21 0 0 0
    db file parallel write 147846 1 267960 1.81242644373199139645306602816444137819
    db file parallel read 2719 0 2625 .9654284663479220301581463773446119897021
    direct path read 3862402 0 384012 .099423105104025940334537937791042983097
    direct path write 590578 0 422884 .716051055068085841328325809630565310594
    undo segment extension 83 83 0 0
    sort segment request 82 75 1331 16.23170731707317073170731707317073170732
    instance state change 2 0 0 0
    smon timer 2461 2201 15510680 6302.592442096708655018285249898415278342
    row cache lock 70 7 8 .1142857142857142857142857142857142857143
    library cache pin 20 0 0 0
    library cache lock 64 26 1284 20.0625
    library cache load lock 6 0 0 0
    file identify 1069 0 9 .008419083255378858746492048643592142188962
    file open 680588 0 37894 .0556783252129041358354834349121641874379
    single-task message 8 0 12 1.5
    SQL*Net message to client 677662597 0 91792 .000135453838542014146311221010180675502148
    SQL*Net message to dblink 306 0 0 0
    SQL*Net more data to client 682026 0 919 .001347455962089421810898704741461469210851
    SQL*Net message from client 677662567 0 86905227 .1282426258022895928970501922382264328317
    SQL*Net more data from client 156015924 0 815767 .005228741907140196791706979859312309684491
    SQL*Net message from dblink 306 0 0 0
    SQL*Net more data from dblink 48 0 0 0
    SQL*Net break/reset to client 81743490 0 2877356 .035199818358624032323552615627250561482

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Your system has a high number of waits on redo log files.
    What is the redo log file size?

    If the redo log file size is small, then drop and recreate with a higher size (may be 100 MB). If arch is enabled, then place your redo logs under different controllers/disks.

    Are you sure that 15 batch jobs concurrently inserting rows in to only one table? If yes, then increase freelists to 8. Also, look for partitioning the table, and ensure that all partitions are used during the insert process.

    Periodically query from v$lock view to check "enqueue lock"

    Tamil
    Last edited by tamilselvan; 09-17-2003 at 04:04 PM.

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