We hit a performance problem which is a consequence of anti join on a range predicate.<\/p>\n
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”.<\/p>\n
Why a blog post for that?<\/p>\n
First, I was curious about a very unusual execution plan. Second, I’m highlighting some options for improving the application process.<\/p>\n
The query in focus is as follows:<\/p>\n
SELECT \/*+ task_atrx_exp_sync_mgr *\/ MAX(ATRX_SEQ_NR<\/span>) \n FROM ( \n SELECT \/*+ INDEX_DESC(AX (ATRX_SEQ_NR)) *\/ AX.ATRX_SEQ_NR \n\t FROM ATRX AX<\/span> WHERE AX.ATRX_SEQ_NR <= :B2<\/span> AND AX.ATRX_SEQ_NR >= :B1<\/span> AND \n\t NOT EXISTS \n ( SELECT NULL \n\t\t FROM OBJ_CONT_INVDT CI<\/span>\n\t\t\t WHERE AX.ATRX_SEQ_NR<\/span> BETWEEN CI.FROM_ATRX_SEQ_NR AND CI.TO_ATRX_SEQ_NR<\/span> ) \n\t ORDER BY AX.ATRX_SEQ_NR DESC ) \n\t WHERE ROWNUM <= 1 ;<\/code><\/pre>\nThe 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.<\/p>\n
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<\/span> doesn’t fall in the interval defined by the OBJ_CONT_INVDT.FROM_ATRX_SEQ_NR<\/span> and OBJ_CONT_INVDT.TO_ATRX_SEQ_NR<\/span>.<\/p>\nThe SQL Plan Monitor and the predicate section revealed immediately where the time goes:<\/p>\n
SQL Plan Monitoring Details (Plan Hash Value=320913352)\n=============================================================================================================================================================================\n| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |\n| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |\n=============================================================================================================================================================================\n| 0 | SELECT STATEMENT | | | | | | 1 | | | | . | | |\n| 1 | SORT AGGREGATE | | 1 | | | | 1 | | | | . | | |\n| 2 | COUNT STOPKEY | | | | | | 1 | | | | . | | |\n| 3 | VIEW | | 6485 | 3148 | | | 1 | | | | . | | |\n| 4 | FILTER | | | | | | 1 | | | | . | | |\n| -> 5 | MERGE JOIN ANTI | | 6485 | 3145 | 605 | +7 | 1 | 0 | | | . | | |\n| -> 6 | INDEX RANGE SCAN DESCENDING | ATRX#P<\/span> | 649K | 815 | 605 | +7 | 1 | 343K<\/span> | 133 | 1MB | . | | |\n| -> 7 | FILTER | | | | 607 | +5 | 343K<\/span> | 343K | | | . | 13.06 | Cpu (79) |\n| 8 | SORT JOIN<\/span> | | 28453 | 40 | 608 | +1 | 343K | 4G | | | 1MB | 86.94 | Cpu (526) |\n| 9 | INDEX FAST FULL SCAN | OBJ_CONT_INVDT#I#1<\/span> | 28453 | 30 | 1 | +7 | 1 | 29971 | 55 | 4MB | . | | |\n=============================================================================================================================================================================<\/code><\/pre>\n 7 - filter(\"AX\".\"ATRX_SEQ_NR\"<=\"CI\".\"TO_ATRX_SEQ_NR\")\n 8 - access(INTERNAL_FUNCTION(\"AX\".\"ATRX_SEQ_NR\")>=INTERNAL_FUNCTION(\"CI\".\"FROM_ATRX_SEQ_NR\"))\n filter(INTERNAL_FUNCTION(\"AX\".\"ATRX_SEQ_NR\")>=INTERNAL_FUNCTION(\"CI\".\"FROM_ATRX_SEQ_NR\"))\n 9 - filter((\"CI\".\"FROM_ATRX_SEQ_NR\"<=:B2 AND \"CI\".\"TO_ATRX_SEQ_NR\">=:B1))<\/code><\/pre>\nMost of the time was spent on the SORT JOIN<\/span> operation.<\/p>\nBy 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<\/span> gets executed for every row returned by the step 6.<\/p>\nIn other words, SORT JOIN<\/span> on OBJ_CONT_INVDT<\/span> gets executed for rows from ATRX<\/span>.<\/p>\nThis interpretation yield the following improvement ideas:
\n– reducing the number of query executions by selecting more than one ATRX_SEQ_NR<\/span> (ROWNUM <= n instead of ROWNUM <= 1),
\n– reducing the number of SORT JOIN<\/span> operations by narrowing the filter defined by bind variables :B2<\/span> and :B1<\/span>,
\n– improving the efficiency of a single SORT JOIN operation by introducing a filter on OBJ_CONT_INVDT<\/span><\/p>\nLast but not least, I built a model to reproduce the critical subset of the execution plan for further experimentation:<\/p>\n
drop table t1 ;\ndrop table t2 ;\n\ncreate table t1 ( n1 number ) ;\ninsert into t1 select level from dual connect by level <= 1000 ;\n\ncreate table t2 ( low_bound number, upper_bound number ) ;\ncreate index ix_t2 on t2 (upper_bound, low_bound) ;\ninsert into t2 select 400,500 from dual connect by level <= 100 ;\n\ncommit ;\n\nexec dbms_stats.gather_table_stats(null,'T1') ;\nexec dbms_stats.gather_table_stats(null,'T2') ;\n\nselect \/*+ monitor *\/ n1 \n from t1 \n where not exists ( \n select null \n from t2 \n where t1.n1 between t2.low_bound and t2.upper_bound \n ) ;\n \nPlan hash value: 1610866028\n \n-----------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-----------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 700 | 7000 | 5 (40)| 00:00:01 |\n| 1 | MERGE JOIN ANTI | | 700 | 7000 | 5 (40)| 00:00:01 |\n| 2 | SORT JOIN | | 1000 | 4000 | 3 (34)| 00:00:01 |\n| 3 | TABLE ACCESS FULL| T1 | 1000 | 4000 | 2 (0)| 00:00:01 |\n|* 4 | FILTER | | | | | |\n|* 5 | SORT JOIN | | 100 | 600 | 2 (50)| 00:00:01 |\n| 6 | INDEX FULL SCAN | IX_T2 | 100 | 600 | 1 (0)| 00:00:01 |\n-----------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 4 - filter(\"T1\".\"N1\"<=\"T2\".\"UPPER_BOUND\")\n 5 - access(INTERNAL_FUNCTION(\"T1\".\"N1\")>=INTERNAL_FUNCTION(\"T2\".\"LOW_\n BOUND\"))\n filter(INTERNAL_FUNCTION(\"T1\".\"N1\")>=INTERNAL_FUNCTION(\"T2\".\"LOW_\n BOUND\"))<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"Performance issue in the Avaloq backend\/frontend interface Continue Reading