![]() ![]() Last wait for 'enq: TX - row lock contention' blocking sess=0x33968B60 seq=4150 wait_time=2999941 seconds since wait started=4 Session 143: obj - rowid = 0000CE31 - AAANCFAApAAACcCAAZġ0g provides the additional information showing the last wait events:Īpplication name: SQL*Plus, hash value=3669949024 ![]() Session 43: obj - rowid = 0000CE33 - AAANCHAArAAAAOmAAM Resource Name process session holds waits process session holds waits ![]() SELECT XXX FROM YYY WHERE ZZZ LIKE 'AAA%' FOR UPDATE I tried this on 8i and 10g, with real-world and clean room scenarios, and the same pattern appears repeatedly. The trace files generated seem to indicate that the deadlock occurs on row lock contention. These are the ONLY statements issued during each respective session. The deadlock seems to be due to the query execution plan Oracle selected for servicing the statements. I've frequently encountered situations where two sessions issue record locking (UPDATE or SELECT FOR UPDATE) statements against a table simultaneously, and these statements repeatedly and predictably result in a deadlock. ITL contention), should I expect to see deadlocking occur when two UPDATE or SELECT FOR UPDATE statements execute simultaneously, simply as a result of the query execution plan being used? All non-row contention deadlocking scenarios aside (e.g. I'm seeing deadlocking behavior that surprised me, and I want to get your opinion of whether or not I should expect to receive an ORA-00060 error in this case. The soft parse % is 93, which you are probably going to tell me is bad. SELECT "NTH_RPT_GLOBAL_CONTROLS"."FREQU 255 514 SELECT * FROM NTH_STEP_INSTANCE_ISSUES W 211 362 SELECT * FROM NTH_USERS WHERE USER_ID IN 197 406 SELECT DISTINCT A.* FROM NTH_VIEW_CONTRO 104 175 SELECT A.* FROM NTH_AR_ORG_UNITS A WHERE 50 95 SELECT DISTINCT SRCDB, SRCOWNER, SRCTBL, 38 47 SELECT DISTINCT BGROUP FROM hyp.BRIOSECG 37 46 SQL> SELECT SUBSTR(sql_text, 1, 40) "SQL",ĭELETE FROM NTH_FLUX_RUNTIME_DATA_MAP WH 32 32 So far I found this on metalink (while looking up the ORA-4031's we're getting) Do you have a query laying around to query the shared pool to determine which statements are not using binds that does not require ddl changes so I can run it on production? ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |