We hit a performance problem which is a consequence of anti join on a range predicate.
The problem arose in the Avaloq banking application; particulary, in the interface between backend and frontend. It went something along the lines of “a huge backlog of documents that are being transfered between two subsystems”.
Why a blog post for that?
First, I was curious about a very unusual execution plan. Second, I’m highlighting some options for improving the application process.
The query in focus is as follows:
SELECT /*+ task_atrx_exp_sync_mgr */ MAX(ATRX_SEQ_NR)
FROM (
SELECT /*+ INDEX_DESC(AX (ATRX_SEQ_NR)) */ AX.ATRX_SEQ_NR
FROM ATRX AX WHERE AX.ATRX_SEQ_NR <= :B2 AND AX.ATRX_SEQ_NR >= :B1 AND
NOT EXISTS
( SELECT NULL
FROM OBJ_CONT_INVDT CI
WHERE AX.ATRX_SEQ_NR BETWEEN CI.FROM_ATRX_SEQ_NR AND CI.TO_ATRX_SEQ_NR )
ORDER BY AX.ATRX_SEQ_NR DESC )
WHERE ROWNUM <= 1 ;The query above was permanently executed. Based on the naming convention and the problem description, I assumed that this query was determining the next document to process. Notice that this query doesn’t do any “real” work, rather, it just determines the next document to process. But the execution time had kept increasing until it finally reached ~600s, although such queries normally take miliseconds to complete.
I started with a semantic explanation of the query. It returns the highest ATRX.ATRX_SEQ_NR within the boundaries defined by the bind variables :B2 and :B1. At the same time, ATRX.ATRX_SEQ_NR doesn’t fall in the interval defined by the OBJ_CONT_INVDT.FROM_ATRX_SEQ_NR and OBJ_CONT_INVDT.TO_ATRX_SEQ_NR.
The SQL Plan Monitor and the predicate section revealed immediately where the time goes:
SQL Plan Monitoring Details (Plan Hash Value=320913352)
=============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |
=============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | | | 1 | | | | . | | |
| 2 | COUNT STOPKEY | | | | | | 1 | | | | . | | |
| 3 | VIEW | | 6485 | 3148 | | | 1 | | | | . | | |
| 4 | FILTER | | | | | | 1 | | | | . | | |
| -> 5 | MERGE JOIN ANTI | | 6485 | 3145 | 605 | +7 | 1 | 0 | | | . | | |
| -> 6 | INDEX RANGE SCAN DESCENDING | ATRX#P | 649K | 815 | 605 | +7 | 1 | 343K | 133 | 1MB | . | | |
| -> 7 | FILTER | | | | 607 | +5 | 343K | 343K | | | . | 13.06 | Cpu (79) |
| 8 | SORT JOIN | | 28453 | 40 | 608 | +1 | 343K | 4G | | | 1MB | 86.94 | Cpu (526) |
| 9 | INDEX FAST FULL SCAN | OBJ_CONT_INVDT#I#1 | 28453 | 30 | 1 | +7 | 1 | 29971 | 55 | 4MB | . | | |
============================================================================================================================================================================= 7 - filter("AX"."ATRX_SEQ_NR"<="CI"."TO_ATRX_SEQ_NR")
8 - access(INTERNAL_FUNCTION("AX"."ATRX_SEQ_NR")>=INTERNAL_FUNCTION("CI"."FROM_ATRX_SEQ_NR"))
filter(INTERNAL_FUNCTION("AX"."ATRX_SEQ_NR")>=INTERNAL_FUNCTION("CI"."FROM_ATRX_SEQ_NR"))
9 - filter(("CI"."FROM_ATRX_SEQ_NR"<=:B2 AND "CI"."TO_ATRX_SEQ_NR">=:B1))Most of the time was spent on the SORT JOIN operation.
By comparing the number of rows retuned by the step 6 with the number of executions of the steps 7 and 8, it becomes obvious that the SORT JOIN gets executed for every row returned by the step 6.
In other words, SORT JOIN on OBJ_CONT_INVDT gets executed for rows from ATRX.
This interpretation yield the following improvement ideas:
– reducing the number of query executions by selecting more than one ATRX_SEQ_NR (ROWNUM <= n instead of ROWNUM <= 1),
– reducing the number of SORT JOIN operations by narrowing the filter defined by bind variables :B2 and :B1,
– improving the efficiency of a single SORT JOIN operation by introducing a filter on OBJ_CONT_INVDT
Last but not least, I built a model to reproduce the critical subset of the execution plan for further experimentation:
drop table t1 ;
drop table t2 ;
create table t1 ( n1 number ) ;
insert into t1 select level from dual connect by level <= 1000 ;
create table t2 ( low_bound number, upper_bound number ) ;
create index ix_t2 on t2 (upper_bound, low_bound) ;
insert into t2 select 400,500 from dual connect by level <= 100 ;
commit ;
exec dbms_stats.gather_table_stats(null,'T1') ;
exec dbms_stats.gather_table_stats(null,'T2') ;
select /*+ monitor */ n1
from t1
where not exists (
select null
from t2
where t1.n1 between t2.low_bound and t2.upper_bound
) ;
Plan hash value: 1610866028
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 700 | 7000 | 5 (40)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 700 | 7000 | 5 (40)| 00:00:01 |
| 2 | SORT JOIN | | 1000 | 4000 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1000 | 4000 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 100 | 600 | 2 (50)| 00:00:01 |
| 6 | INDEX FULL SCAN | IX_T2 | 100 | 600 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."N1"<="T2"."UPPER_BOUND")
5 - access(INTERNAL_FUNCTION("T1"."N1")>=INTERNAL_FUNCTION("T2"."LOW_
BOUND"))
filter(INTERNAL_FUNCTION("T1"."N1")>=INTERNAL_FUNCTION("T2"."LOW_
BOUND"))