When the Wrong Index Works Better

This week I saw a case where a poor choice of index may have actually improved performance…. temporarily.

An application with performance problems had been investigated and a new index had been implemented which reportedly improved reduced the relevant query’s execution time from 40 seconds to between 6-15 seconds.  However, there were still complaints about the variable length of the execution time, so I began to review the situation.

From V$SQL I could see hundreds of examples of the query that had run recently – after the index had been created.  The elapsed time for these took up to 103 seconds to complete.  The number of rows returned and physical/logical block gets also varied greatly.  I began to doubt the accuracy of the description of the new index’s impact, so I needed to verify that the new index was the best choice.

The queries were similar to this (altered for simplicity and confidentiality):

SELECT some_columns
FROM  dummy_table
WHERE FOREIGN_KEY = :b1
AND AMOUNT1 + AMOUNT2 <>0  
AND to_char(TIME_STAMP, 'yyyy-mm-dd HH24:MI:SS') <= '2009-08-18 17:03:54'
/

Each query had a literal for the TIME_STAMP comparison.
The first thing I noticed was the common developer error of putting the function on the column instead of the constant.  It should have been rewritten as:

TIME_STAMP <= to_date('2009-08-18 17:03:54','yyyy-mm-dd HH24:MI:SS');
This would allow Oracle to only convert between char and date once on the constant, rather than on the column for each row processed.  Also, it would allow the TIME_STAMP value to be used to access an index, if a suitable one existed.
These were the relevant indexes:
                                                   DIST KEYS       LEAF BLKS LAST ANALYZED
INDEX_NAME                                         (SELCTY%)   BPK (DEPTH)   (ESTIMATE %)   COLUMN (BYTES ASC/DESC)
-------------------------------------------------- ---------- ---- --------- -------------- ---------------------------------------
DUMMY_TABLE_IDX1                                   34K (0)    #### 243K (3)  15/08/09 (1)   FOREIGN_KEY (22 ASC)

DUMMY_TABLE_IDX2                                   346K (1)    108 283K (3)  15/08/09 (1)   TIME_STAMP (7 ASC)

DUMMY_TABLE_IDX3                                   25M (48)      1 192K (3)  15/08/09 (7)   FOREIGN_KEY (22 ASC)
                                                                                            TIME_STAMP (7 ASC)

DUMMY_TABLE_PK (U)                                 51M (100)     1 274K (2)  15/08/09 (1)   PRIMARY_KEY (22 ASC)
 DUMMY_TABLE_IDX3 was the newly created index that was supposed to improve performance.
Although the to_char function prevented the TIME_STAMP column from being used to access the index, it could still be used to filter rows before accessing the DUMMY_TABLE to get the rest of the columns.  The DUMMY_TABLE was big and could never reside completely in the cache, so avoiding unnecessary table accesses would boost performance.  But would filtering rows based on the TIME_STAMP achieve this aim?
I am always suspicious of date_column < :date_time_value because it is hardly ever very selective.  In this case I noticed that the date and time compared to the TIME_STAMP column was always within a second or two of the actual execution time.  As one would expect from the name, the TIME_STAMP column has only past times in it.
Select count(1) from dummy_table where time_stamp > sysdate;
...returned no rows.  So, the TIME_STAMP column is of no use in an index to improve the performance of this query.
So why was there an impression that the index was beneficial?  One possible answer is that the new index is compact (dense) because it hasn't had much/any data deleted from it, where as the index that would have previously been used by the query (DUMMY_TABLE_IDX1) had become sparse after a lot of data was deleted.  The index information above shows that the old index is 26% larger than the new index (confirmed by DBA_SEGMENTS) despite the new index having an extra column!  At first the smaller index (DUMMY_TABLE_IDX3), with less empty blocks would have been more efficient than the old one (see this post about index performance).  But, after time passes and data is deleted, DUMMY_TABLE_IDX3 will become larger and perform worse because it stores an extra column (TIME_STAMP) which adds nothing for performance because it doesn't filter any rows.
Simply coalescing the index would have provided more benefit than adding the new index, without the overhead.  Compressing it would have improved it even more.
An alternative to the above:

Replace DUMMY_TABLE_IDX3 with a compressed functional index on (FOREIGN_KEY, AMOUNT1 + AMOUNT2), because the criterion "AMOUNT1 + AMOUNT2 <> 0" does filter out a lot of rows for some of the queries, and so table accesses (sequential I/O) would be reduced.
	

Reduce Processing Time by Reducing the Index

Rebuilding Oracle indexes is nothing new, but there are contrasting ideas about when this action should be taken.  Some argue that there is usually no real benefit, and others set automatic jobs to rebuild indexes regularly as a rule.

I think that each case should be evaluated on its merits.  Without going into it (the topic is well covered elsewhere) some indexes naturally tend towards 75% capacity and others >90%.  Index capacities well under 75% are usually due to deletions.  The only general rule I’d suggest is that when data is deleted, especially by a few large operations, then the related indexes might be worth checking to see if a reorganisation is warranted.

Compact (dense) indexes mean less disk space, less memory for caching the same information, less I/O and less processing (CPU time).

Rebuilding, coalescing (Enterprise Edition) or shrinking space (10g+) can be big and easy wins.  There are many cases I could describe, but here is the most extreme example.

Purging Performance Problem

An organisation approached me after they been trying to purge years worth of data from an Oracle 9i database, but found that the performance was atrocious, and they were after that elusive go fast switch.

The application had a purging function built in, (rare but so beneficial!), so the code couldn’t be adjusted.  There were no application design changes possible, eg partition by time and efficiently drop partitions instead of deleting.

The application purged data by asking for a period to retain and one to purge, then looping – deleting more recent data in the first iteration followed by incrementally older data in the next iterations.  Here is a simplified example of what I believe it was doing from the description given to me:

delete where data_date between :retention_date-1 and :retention_date;
commit;
delete where data_date between :retention_date-2 and :retention_date;
commit;
delete where data_date between :retention_date-3 and :retention_date;
commit;
....

This was probably designed to reduce undo space requirements.

The data_date key was indexed, which avoided tablescans.  Oracle would handle the first deletion by entering the index at retention_date – N and then (range) scanning the index until it found the retention date, deleting as it went.

The problem is obvious to those who understand how Oracle indexes work.  The deleted index entries result in more and more empty leaf blocks between the data yet to be purged and the retention date.  These empty leaf blocks are not taken out of the index structure until they are reused elsewhere (by an insert or update).  The index range scan had to access more and more of these empty leaf blocks as the purging process continued, requiring more processing time for each subsequent delete.

The solution I suggested was to punctuate the purge cycles with manual index coalesce commands to remove the empty leaf blocks.  The first coalesce of the relevant index took several minutes, but took less time after that. This is the feedback we received when the index coalesce was added to the purging process:

“Wow……..what previously took 1 hour now takes about 25s.  Yes, I can now purge a day off the table in about 25s.   This is great.  A whole week in 1 minute. and a month in 10 minutes.”

In this case, it wasn’t even necessary to access the database to find the solution.  When one does have access to the database, evidence that the index should be rebuilt / shrunk / coalesced can come from:

• a high logical read count for a SQL statement, and
• a high logical read count for an index segment, and
• a low density shown by the results of analyze index .. validate structure, or
  
• a low density shown by the results of the segment advisor (10g), or
• a low estimated density from the key length x number of keys versus actual index size, or
• a tree dump of the index showing many empty (or mostly empty) leaf blocks in the structure

Big Wins are the Easiest

Isn’t that always the way with Oracle tuning?  The really dramatic improvements are often the easiest to find and implement.  Tuning tasks that are highly restrictive, complex and only offer a small scope for performance gains may be the most challenging but the least appreciated.  Finding the “low hanging fruit” (beneficial, cheap and low risk) is justifiably what customers want first.

Here are two contrasting examples from my experience:

An Easy Win

We had just taken over support of a customer’s Oracle database from a competitor.  It was a terabyte database on Oracle 8i and Windows (32-bit).  When doing the initial audit, I was shocked to learn that their monthly billing process ran for two weeks solid!  This meant no outages, slow performance for OLTP users, and if something went wrong, they’d only just have time to run it again before the next billing process needed to start.

It was obvious to me that before we could start making other improvements highlighted in our audit, we’d need to get the billing process down to a reasonable length of time.  Sure, the platform was hardly appropriate for the amount of data and processing, but two weeks to process one batch job each month was ridiculous.

It took a few minutes to spot two inefficient SQL statements and to recommend a couple of indexes that ended up reducing the monthly billing run to less than a day.  The only problem was that the companies being billed were surprised to get their bills earlier. 

This kind of miracle tuning is welcomed by the customer and looks great on a CV, but it was embarrassingly easy.

A Hard Win

In complete contrast to the previous example, this one took a lot of work for small but necessary gains.  A different customer had a legacy monthly billing process that was very poorly designed, and was taking longer each month as the data grew.  The core engine could not be altered; only tweaks were possible.  The application was due to be replaced in the new year, but until then, we were tasked with keeping it running within a window of a weekend.  (Users could not be in the system during this monthly billing process, so it had to complete within a weekend).

This application had already been through iterations of tuning, so there were no easy and quick wins left.  It took a lot of analysis and work to save 10% here and 5% there, just to hold a steady run time each month.  We tuned multiple layers: the hardware, storage, OS, Oracle parameters, data density, SQL, indexes and finally the PL/SQL packages.

Although the results were appreciated by the customer, and provided me with job satisfaction, stopping an application from slowing down doesn’t grab attention in the same way as the easy win described above.

How All this Relates to the HP Database Services Blog

Big and easy wins are often not so technically interesting or challenging, and are usually well covered in books and forums and other blogs.  Therefore, it is tempting to just write about more difficult or rare performance problems.  However, “low hanging fruit” are commonly sought after, so I’ll try to keep this in mind for future posts.

Here’s a start in that theme.

The Secret Life of LOBs

Now and then we are passed a problem that other people have been working on for a while, but despite lots of changes, it hasn’t been resolved.  In some cases this makes it impossible to obtain a clear description of the symptoms and history, and may even have introduced other issues.  I much prefer a ‘diagnostic approach’ rather the ‘try the usual suspects first method’.  One might ‘get lucky’ by simply changing a common tuning parameter, but I prefer to consider the risks, costs and testing effort the customer will incur, and so attempt to understand the problem and provide the best solution.

The case I’m going to briefly cover in this post was one of these problems with more rumour than substance to the problem history.  Significant changes had been made to the Oracle database, including changing the optimiser back and forward between cost and rule and cursor sharing between exact and force.  I’m not even sure if the problem I investigated was the original one or a side-effect of the various attempts at a solution.

Obvious Symptoms

The highest component of response time was the enqueue wait event.

There were about ten blocked sessions with the same SQL statement:

UPDATE dummytable SET nextId = nextId + 1 WHERE schemaId = 123;

My first thought was that the locking was due to the application incrementing a number field instead of using a sequence.  Then I noticed that each blocker relinquished the lock after a minute or so, and one of the other ten sessions became the blocker for another minute, and so on.  Assuming the application design hadn’t just changed, it was probably this 60 second delay while holding a lock that was at the heart of the problem.

The Not-so-Obvious

Finding what was happening during that 60 second delay was trickier than usual.  How so?  I’ll start by sharing a portion of the trace file.  Pay attention to the timing parts in blue.

PARSE #1:c=0,e=980,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=3,tim=31325192050363
EXEC #1:c=0,e=158,p=0,cr=4,cu=1,mis=0,r=0,dep=0,og=3,tim=31325192050680
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=10000,e=64,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=3,tim=31325192050859
WAIT #1: nam='SQL*Net message from client' ela= 381 p1=1413697536 p2=1 p3=0
WAIT #33: nam='db file sequential read' ela= 103 p1=15 p2=1542515 p3=1
WAIT #33: nam='db file sequential read' ela= 317 p1=15 p2=1542520 p3=1
WAIT #33: nam='direct path write (lob)' ela= 178 p1=10 p2=2008755 p3=1
WAIT #0: nam='SQL*Net message to client' ela= 7 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 596 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=4 r=0 w=0 time=44 us)'
STAT #1 id=2 cnt=2 pid=1 pos=1 obj=42798 op='TABLE ACCESS BY INDEX ROWID table_a (cr=8 r=0 w=0 time=97 us)'
STAT #1 id=3 cnt=2 pid=2 pos=1 obj=42799 op='INDEX UNIQUE SCAN table_a_index (cr=6 r=0 w=0 time=57 us)'
=====================
PARSING IN CURSOR #1 len=66 dep=0 uid=23 oct=6 lid=23 tim=31325202616132 hv=4020366925 ad='ff8b078'
UPDATE table_a SET C2 = EMPTY_CLOB() WHERE C1 = '000000003637834'
END OF STMT
PARSE #1:c=0,e=2286,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=3,tim=31325202616118
EXEC #1:c=0,e=670,p=0,cr=7,cu=6,mis=0,r=1,dep=0,og=3,tim=31325202616968
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 313 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE  (cr=7 r=0 w=0 time=475 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=42799 op='INDEX UNIQUE SCAN table_a_index (cr=3 r=0 w=0 time=114 us)'
=====================
PARSING IN CURSOR #1 len=63 dep=0 uid=23 oct=3 lid=23 tim=31325202618985 hv=1434031237 ad='156ec818'
SELECT C2 FROM table_a WHERE C1 = '000000003637834' FOR UPDATE
END OF STMT
PARSE #1:c=0,e=1203,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=3,tim=31325202618977
EXEC #1:c=0,e=217,p=0,cr=4,cu=1,mis=0,r=0,dep=0,og=3,tim=31325202619378
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=90,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=3,tim=31325202619616
WAIT #1: nam='SQL*Net message from client' ela= 505 p1=1413697536 p2=1 p3=0
WAIT #34: nam='db file sequential read' ela= 146 p1=15 p2=1473089 p3=1
*** 2008-11-05 17:14:00.054
WAIT #34: nam='db file sequential read' ela= 950 p1=15 p2=1473090 p3=1
*** 2008-11-05 17:14:14.217
WAIT #34: nam='db file sequential read' ela= 138 p1=15 p2=1473091 p3=1
WAIT #34: nam='SQL*Net more data from client' ela= 110 p1=1413697536 p2=1 p3=0
WAIT #34: nam='direct path write (lob)' ela= 183 p1=10 p2=1595041 p3=1
WAIT #0: nam='SQL*Net message to client' ela= 8 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 579 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=4 r=0 w=0 time=56 us)'
STAT #1 id=2 cnt=2 pid=1 pos=1 obj=42798 op='TABLE ACCESS BY INDEX ROWID table_a (cr=8 r=0 w=0 time=135 us)'
STAT #1 id=3 cnt=2 pid=2 pos=1 obj=42799 op='INDEX UNIQUE SCAN table_a_index (cr=6 r=0 w=0 time=81 us)'
=====================
PARSING IN CURSOR #1 len=66 dep=0 uid=23 oct=6 lid=23 tim=31325238065528 hv=3338015855 ad='165cdab0'

We can see that the blocking session is eating up time:

31325202616132 – 31325192050859 = 10565273, or 10 seconds.

31325238065528 – 31325202619616 = 35445912, or 35 seconds.

Whatever is causing the delay, it hasn’t been assigned a wait event name.  One clue the trace does have for us is that there are reads to a couple of segments during the mysterious delay and just before writes to a LOB.  Using DBA_EXTENTS we can see that these segments are LOB indexes for TABLE_A.

Watching the blocking sessions revealed that they clock up a lot of CPU and consistent gets.  The LOB indexes for TABLE_A have high logical read counts. The buffer cache is also full of copies of the LOB index blocks. The picture is becoming clearer – something must be wrong with the LOB indexes.

Theory, Confirmation, Resolution

My best guess at this point was that this performance issue was caused by a big delete of data in TABLE_A.  The LOB chunks were marked as deleted (not free), so each time there was a new insert Oracle had to find re-usable space, change the deleted chunks to free and update the LOB index, (as well as manage the various bitmap blocks and undo blocks).  According to the Metalink Note:365156.1 this process can take a long time because it “has to traverse the empty LOB INDEX leaf blocks created by all the previous transactions”.  This would explain the high number of logical reads recorded against the LOB indexes during each insert/update.
This Oracle database had a long statspack history, so I was able to write some ad-hoc queries to generate a graph of enqueue waits over time to see when the first big locking problems really started, and I was also able to find some large delete statements on TABLE_A.  The archive log history showed a huge spike at the time of one of these deletes, and so it looked like the theory was right.  This data helped to jog a few memories and we learned that 90% of TABLE_A’s data had been deleted around the time the ‘slowness’ started.

I can hardly do the explanation justice here, but if you know how Oracle LOBS and indexes work in general, then this may make sense:
LOB segments don’t use undo like everything else, but instead mark old chunks of LOB data as re-usable and write over it a later time when an insert or update needs some space.  These re-usable chunks are tracked by the LOB index – the same LOB index that tracks the current chunks of LOB data.  The current data is in the left side of the index and the re-usable/undo/consistent read data is in the right side.  When data is deleted, (or updated), the old LOB locator entries are deleted from the index and new re-usable index rows are entered into the right side of the index.  The emptied leaf blocks on the left side of the index are reclaimed and used for leaf blocks during inserts, while the “re-usable” index entries are removed from the tail of the right half of the index with each insert.  After a big delete (or many small ones in a row) the freelist is full of blocks from the left side of the index… so as data is inserted again over time, a gap opens up with empty leaf blocks between the left and right sides (used and re-usable) of the index.  The empty blocks are on the freelist but not removed from the index structure until they are taken from the freelist.
So… when an insert happens it checks for re-usable space.  The index is range scanned for the first free chunk.  (Re-usable space is indexed chronologically).  Unfortunately, this process takes a lot of CPU time to complete when there are large numbers of empty leaf blocks in the index and multiple LOBs in the same table; over a minute in the example in this blog.  Sometimes the delay can be smaller – just a few seconds, and so even harder to identify.

A dump of the index showed the problem clearly, (although it helped to a dump of a few of the index blocks at key points to be sure where the current data index entries stopped and where re-usable chunk list started).

A rebuild of the LOBs for TABLE_A resolved this problem.    I set the LOBs to cache before rebuilding them to speed the process up.

Moral of the Story

My recommendation to prevent this from happening again was the usual ‘rebuild/repack database segments after mass deletions, but even better is to purge in small amounts regularly’.  This case was an extreme example of why that is important.

A performance tuning lesson emphasised here is to use a multiple diagnostic tools, deductive reasoning and research to identify the real problem so that the best solution can be found and a strategy for preventing its re-occurrence can be designed.  Also, tuning LOB related activity can be tricky for those relying on the standard tuning tools and statistics.  In a future post, I’ll give another example of how LOB activity cause performance problems that aren’t obvious.

Deadlock Investigation Example

DBINST started to display the following error message:

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/DBINST/udump/DBINST_ora_1705.trc.

When the deadlocks occurred, the application was failing and logging errors too.

Oracle Concept Manual: “A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. … Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks. “

The start of the trace files were similar to each other, so it appeared to be the same piece of code causing the problem:

DEADLOCK DETECTED
Current SQL statement for this session:
delete from PORTDETAIL where ID=:1 and version=:2
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000e7bc-00000000        28     132    SX   SSX       25     135    SX   SSX
TM-0000e7bc-00000000        25     135    SX   SSX       28     132    SX   SSX
session 132: DID 0001-001C-0000052F     session 135: DID 0001-0019-00000B29
session 135: DID 0001-0019-00000B29     session 132: DID 0001-001C-0000052F
Rows waited on:
Session 135: obj - rowid = 0000E7DC - AAAOfcAAFAAAAjIAAA
(dictionary objn - 59356, file - 5, block - 2248, slot - 0)
Session 132: obj - rowid = 0000E7DC - AAAOfcAAFAAAAjIAAA
(dictionary objn - 59356, file - 5, block - 2248, slot - 0)
Information on the OTHER waiting sessions:
Session 135:
pid=25 serial=1301 audsid=134658 user: 60/EGSCHEMA
O/S info: user: , term: , ospid: 1234, machine: dbserver.local
program:
Current SQL Statement:
delete from PORTDETAIL where ID=:1 and version=:2
End of information on OTHER waiting sessions.

I looked noticed that this was a bit different than other deadlocks I’ve seen; it was dead locking on table locks instead of row locks (transactions). This made me suspect a foreign key wasn’t indexed. (It could be the result of an application doing something unusual, like issuing lock table commands or DDL even).

So in this case we should ignore the “Rows waited on” section, which is useful when we have blocking transactions (TX).

I checked which table was locked:

TM=table lock (DML lock)
0x0000e7bc=59324

select owner, object_name from dba_objects where DATA_OBJECT_ID=59324;
OWNER
------------------------------
OBJECT_NAME
------------------------------
EGSCHEMA
PORTSERVICEORDER

Note that this isn’t the table in the SQL statement for either session: PORTDETAIL.

So, is there a trigger or a foreign key that could cause a lock on PORTSERVICEORDER when a row from PORTDETAIL is deleted?

@dd
EGSCHEMA
PORTDETAIL
TYPE              NAME                           OWNER                          'SOF 'REFERENCED'  NULL
----------------- ------------------------------ ------------------------------ ---- ------------- ------------------------------
INDEX             PD_PSO_IDX                     EGSCHEMA                       HARD ON_OBJECT
INDEX             PK_PORTDETAIL                  EGSCHEMA                       HARD ON_OBJECT
TABLE             PORTITEM                       EGSCHEMA                       HARD FK_REF_OBJECT FK_PORTITEM_PORTDETAIL
TABLE             PORTSERVICEORDER               EGSCHEMA                       HARD FK_REF_OBJECT FK_PSO_APPROVEDPORTDETAILS
TABLE             PORTSERVICEORDER               EGSCHEMA                       HARD FK_REF_OBJECT FK_PSO_GSPPORTDETAILS
VIEW              VIEW_GC_LC_FOR_COMPLETED_PORTS EGSCHEMA                       SOFT REFERENCED

Yes! Two foreign keys.

Are these foreign keys indexed?

@$DBA_SQLPATH/apt/missing_fk_indexes.sql
TABLE_NAME                                       COLUMN_NAME
------------------------------------------------ ------------------------------
EGSCHEMA.PORTSERVICEORDER                        APPROVEDPORTDETAILS
EGSCHEMA.PORTSERVICEORDER                        GSPPORTDETAILS

No, neither of them are indexed.

My Interpretation

A row is being updated or deleted from the child PORTSERVICEORDER, and then a row is deleted from the parent table PORTDETAIL in the same transaction. There is no index on the foreign key so a lock is taken on the whole PORTSERVICEORDER table. When two sessions attempt these same steps concurrently, they would deadlock because they both hold an SX (row exclusive) lock in PORTSERVICEORDER and both are asking for an SSX (share sub-exclusive) lock on PORTSERVICEORDER.

In general, foreign keys should be indexed unless the parent table has very little DML on it. So… I asked the developers to index these foreign keys and we haven’t had deadlocks since.