Locking issue

[gcxp2001]

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

[tamilselvan]

Having a cache on the sequence and a higer freelists on the table will solve your problem.

[gcxp2001]

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

[tamilselvan]

Query from v$session_wait and v$system_event and post the result here.

Tamil

[gcxp2001]

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

[tamilselvan]

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