Oracle Apex Reports causing performance issue

Question

I'm creating an Interactive Grid report.

I've a very complex sql query. It has 3 portions of SQL query and the syntax is like below.

WITH
QUERY1 AS
    -- QUER1 --
QUERY2 AS
    -- QUER1 --
QUERY3 AS
    -- QUER1 --

SELECT
-- COLUMNS --
FROM
(
QUERY1 WHERE NVL(QUERY, 'A') = 'A'
UNION ALL 
QUERY2 WHERE NVL(QUERY, 'B') = 'B' 
UNION ALL
QUERY3 WHERE NVL(QUERY, 'C') = 'C'
)
WHERE
-- CONDITIONS --

It has 57 columns and using 14 tables. Even the size of the sql query is about 30k characters in length. I've already optimized the query. The cost is below 2000 as per post execution plan and below 150 as per explain plan. Only 3 of them causes Table Access Full. And this when parameters or apex-item values are not passed.

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                      |       |       |  1740 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID                     | CD_TBL               |     1 |    24 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN                              | PK_CD_TBL            |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID                     | PLP_TBL              |     1 |    72 |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN                              | PK_PLP_TBL           |     1 |       |     1   (0)| 00:00:01 |
|   5 |  TABLE ACCESS BY INDEX ROWID                     | CD_TBL               |     1 |    24 |     2   (0)| 00:00:01 |
|*  6 |   INDEX UNIQUE SCAN                              | PK_CD_TBL            |     1 |       |     1   (0)| 00:00:01 |
|   7 |  TABLE ACCESS BY INDEX ROWID                     | PTD_TBL              |     1 |    17 |     2   (0)| 00:00:01 |
|*  8 |   INDEX UNIQUE SCAN                              | PTD_TBL_DT           |     1 |       |     1   (0)| 00:00:01 |
|*  9 |  FILTER                                          |                      |       |       |            |          |
|* 10 |   FILTER                                         |                      |       |       |            |          |
|  11 |    NESTED LOOPS                                  |                      |   100 |   260K|  1640   (2)| 00:00:01 |
|  12 |     NESTED LOOPS                                 |                      |   100 |   260K|  1640   (2)| 00:00:01 |
|* 13 |      HASH JOIN RIGHT OUTER                       |                      |   100 |   252K|  1608   (2)| 00:00:01 |
|  14 |       VIEW                                       | VW_ORE_115E4D93      |    96 |  8064 |   393   (1)| 00:00:01 |
|  15 |        UNION-ALL                                 |                      |       |       |            |          |
|* 16 |         FILTER                                   |                      |       |       |            |          |
|  17 |          NESTED LOOPS                            |                      |     1 |    64 |     3   (0)| 00:00:01 |
|  18 |           NESTED LOOPS                           |                      |     1 |    64 |     3   (0)| 00:00:01 |
|* 19 |            TABLE ACCESS BY INDEX ROWID BATCHED   | RD_TBL               |     1 |    48 |     2   (0)| 00:00:01 |
|* 20 |             INDEX RANGE SCAN                     | RD_TBL_I1            |     1 |       |     2   (0)| 00:00:01 |
|* 21 |            INDEX UNIQUE SCAN                     | PK_RH_TBL            |     1 |       |     0   (0)|          |
|* 22 |           TABLE ACCESS BY INDEX ROWID            | RH_TBL               |     1 |    16 |     1   (0)| 00:00:01 |
|* 23 |         FILTER                                   |                      |       |       |            |          |
|* 24 |          HASH JOIN                               |                      |    95 |  6080 |   390   (1)| 00:00:01 |
|* 25 |           TABLE ACCESS FULL                      | RH_TBL               |    71 |  1136 |   177   (2)| 00:00:01 |
|* 26 |           TABLE ACCESS FULL                      | RD_TBL               |     1 |    48 |     3   (0)| 00:00:01 |
|  27 |       VIEW                                       |                      |   100 |   244K|  1214   (2)| 00:00:01 |
|  28 |        UNION-ALL                                 |                      |       |       |            |          |
|  29 |         VIEW                                     | VW_ORE_87C0170C      |    68 | 42636 |   893   (2)| 00:00:01 |
|  30 |          UNION-ALL                               |                      |       |       |            |          |
|* 31 |           FILTER                                 |                      |       |       |            |          |
|  32 |            NESTED LOOPS                          |                      |     1 |   209 |     4   (0)| 00:00:01 |
|  33 |             NESTED LOOPS                         |                      |     1 |   209 |     4   (0)| 00:00:01 |
|  34 |              TABLE ACCESS BY INDEX ROWID BATCHED | AD_TBL               |     1 |   177 |     2   (0)| 00:00:01 |
|* 35 |               INDEX RANGE SCAN                   | AD_TBL_I1            |     1 |       |     2   (0)| 00:00:01 |
|* 36 |              INDEX UNIQUE SCAN                   | PK_AH_TBL            |     1 |       |     1   (0)| 00:00:01 |
|* 37 |             TABLE ACCESS BY INDEX ROWID          | AH_TBL               |     1 |    32 |     2   (0)| 00:00:01 |
|* 38 |           FILTER                                 |                      |       |       |            |          |
|  39 |            NESTED LOOPS                          |                      |    67 | 14003 |   889   (2)| 00:00:01 |
|  40 |             NESTED LOOPS                         |                      |    70 | 14003 |   889   (2)| 00:00:01 |
|  41 |              TABLE ACCESS BY INDEX ROWID BATCHED | AH_TBL               |    10 |   320 |   799   (3)| 00:00:01 |
|* 42 |               INDEX SKIP SCAN                    | PAH_I1               |    10 |       |   789   (3)| 00:00:01 |
|* 43 |              INDEX RANGE SCAN                    | PK_AD_TBL            |     7 |       |     2   (0)| 00:00:01 |
|  44 |             TABLE ACCESS BY INDEX ROWID          | AD_TBL               |     7 |  1239 |     9   (0)| 00:00:01 |
|  45 |         NESTED LOOPS OUTER                       |                      |    30 | 17010 |   298   (1)| 00:00:01 |
|  46 |          NESTED LOOPS OUTER                      |                      |    30 | 16290 |   297   (1)| 00:00:01 |
|  47 |           NESTED LOOPS OUTER                     |                      |    30 | 15570 |   296   (1)| 00:00:01 |
|  48 |            NESTED LOOPS OUTER                    |                      |    30 | 14850 |   295   (1)| 00:00:01 |
|  49 |             VIEW                                 | VW_JF_SET$3641B155   |    30 | 14130 |   294   (1)| 00:00:01 |
|  50 |              UNION-ALL                           |                      |       |       |            |          |
|* 51 |               FILTER                             |                      |       |       |            |          |
|  52 |                NESTED LOOPS                      |                      |    29 |  4669 |   190   (1)| 00:00:01 |
|  53 |                 NESTED LOOPS                     |                      |    29 |  4669 |   190   (1)| 00:00:01 |
|* 54 |                  TABLE ACCESS FULL               | MAH_TBL              |    29 |   783 |   103   (1)| 00:00:01 |
|* 55 |                  INDEX RANGE SCAN                | MAD_TBL_INDEX2       |     1 |       |     2   (0)| 00:00:01 |
|* 56 |                 TABLE ACCESS BY INDEX ROWID      | MAD_TBL              |     1 |   134 |     3   (0)| 00:00:01 |
|* 57 |               FILTER                             |                      |       |       |            |          |
|  58 |                NESTED LOOPS                      |                      |     1 |   161 |   104   (1)| 00:00:01 |
|  59 |                 NESTED LOOPS                     |                      |     1 |   161 |   104   (1)| 00:00:01 |
|* 60 |                  TABLE ACCESS FULL               | MAH_TBL              |     1 |    27 |   103   (1)| 00:00:01 |
|* 61 |                  INDEX RANGE SCAN                | MAD_TBL_INDEX2       |     1 |       |     1   (0)| 00:00:01 |
|* 62 |                 TABLE ACCESS BY INDEX ROWID      | MAD_TBL              |     1 |   134 |     1   (0)| 00:00:01 |
|  63 |             TABLE ACCESS BY INDEX ROWID          | CD_TBL               |     1 |    24 |     1   (0)| 00:00:01 |
|* 64 |              INDEX UNIQUE SCAN                   | PK_CD_TBL            |     1 |       |     0   (0)|          |
|  65 |            TABLE ACCESS BY INDEX ROWID           | CD_TBL               |     1 |    24 |     1   (0)| 00:00:01 |
|* 66 |             INDEX UNIQUE SCAN                    | PK_CD_TBL            |     1 |       |     0   (0)|          |
|  67 |           TABLE ACCESS BY INDEX ROWID            | CD_TBL               |     1 |    24 |     1   (0)| 00:00:01 |
|* 68 |            INDEX UNIQUE SCAN                     | PK_CD_TBL            |     1 |       |     0   (0)|          |
|  69 |          TABLE ACCESS BY INDEX ROWID             | CD_TBL               |     1 |    24 |     1   (0)| 00:00:01 |
|* 70 |           INDEX UNIQUE SCAN                      | PK_CD_TBL            |     1 |       |     0   (0)|          |
|* 71 |         FILTER                                   |                      |       |       |            |          |
|  72 |          NESTED LOOPS OUTER                      |                      |     2 |   542 |    23   (0)| 00:00:01 |
|  73 |           NESTED LOOPS OUTER                     |                      |     2 |   494 |    22   (0)| 00:00:01 |
|  74 |            NESTED LOOPS OUTER                    |                      |     2 |   446 |    21   (0)| 00:00:01 |
|  75 |             NESTED LOOPS OUTER                   |                      |     2 |   398 |    20   (0)| 00:00:01 |
|  76 |              NESTED LOOPS                        |                      |     2 |   350 |    19   (0)| 00:00:01 |
|* 77 |               TABLE ACCESS FULL                  | PAH_TBL              |     4 |   100 |    11   (0)| 00:00:01 |
|* 78 |               TABLE ACCESS BY INDEX ROWID BATCHED| PAD_TBL              |     1 |   150 |     2   (0)| 00:00:01 |
|* 79 |                INDEX RANGE SCAN                  | PAD_TBL_INDEX2       |     1 |       |     1   (0)| 00:00:01 |
|  80 |              TABLE ACCESS BY INDEX ROWID         | CD_TBL               |     1 |    24 |     1   (0)| 00:00:01 |
|* 81 |               INDEX UNIQUE SCAN                  | PK_CD_TBL            |     1 |       |     0   (0)|          |
|  82 |             TABLE ACCESS BY INDEX ROWID          | CD_TBL               |     1 |    24 |     1   (0)| 00:00:01 |
|* 83 |              INDEX UNIQUE SCAN                   | PK_CD_TBL            |     1 |       |     0   (0)|          |
|  84 |            TABLE ACCESS BY INDEX ROWID           | CD_TBL               |     1 |    24 |     1   (0)| 00:00:01 |
|* 85 |             INDEX UNIQUE SCAN                    | PK_CD_TBL            |     1 |       |     0   (0)|          |
|  86 |           TABLE ACCESS BY INDEX ROWID            | CD_TBL               |     1 |    24 |     1   (0)| 00:00:01 |
|* 87 |            INDEX UNIQUE SCAN                     | PK_CD_TBL            |     1 |       |     0   (0)|          |
|* 88 |      INDEX UNIQUE SCAN                           | PK_EM_TBL            |     1 |       |     0   (0)|          |
|* 89 |     TABLE ACCESS BY INDEX ROWID                  | EM_TBL               |     1 |    76 |     1   (0)| 00:00:01 |
|  90 |   NESTED LOOPS                                   |                      |     1 |    27 |     3   (0)| 00:00:01 |
|  91 |    TABLE ACCESS BY INDEX ROWID                   | AU_TBL               |     1 |     9 |     2   (0)| 00:00:01 |
|* 92 |     INDEX UNIQUE SCAN                            | AU_TBL_PK            |     1 |       |     1   (0)| 00:00:01 |
|* 93 |    INDEX RANGE SCAN                              | AULA_TBL             |     1 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------

On SQL developer, if checked for 1 year data, this can load 3M records in just 2secs.

But on apex it is taking more than 10 minutes to load data for a month. I'm using apex 19.1. I've tried all 3 types of apex reports.

The debugger log shows that query itself is taking too long to process.

I've also tried, creating a very plain page. Which does not have any js code, DA. And column types are text / date / number which came by default.

Sometimes I'm also getting, 502 Proxy error.

Now for the fun part, If I comment any two off the below statements then I get results in less than 1 minute for year as well.

(
SELECT * FROM QUERY1 WHERE NVL(:QUERY, 'A') = 'A'
UNION ALL 
SELECT * FROM QUERY2 WHERE NVL(:QUERY, 'B') = 'B' 
UNION ALL
SELECT * FROM QUERY3 WHERE NVL(:QUERY, 'C') = 'C'
)

However even if not commented and passed any value 'A' / 'B' / 'C', still causing same performance issue for a month.

Any thoughts on how to solve this issue?

Answer 1

I found a fix.

There was a custom function used inside all 3 subqueries. I just used it in main select statement.

Though, I'm facing a apex-report download issue. Now data loads in secs but once clicked on download, it starts after 10-15 mins. And between that nothing happens. Even no statements are written in console, debug, network etc.

Answer 2

In most cases, this is due to the pagination setting for the page. If pagination is set to "X TO Y FROM Z" then the apex engine will have to retrieve all rows first (as you can see in the debug) . Change it to "No pagination" or "Rows X to Y".

Answer 3

If you just want to fix the query ASAP, try the simple ROWNUM trick. It's the quickest way to solve the common problem of "these queries run fast independently, but they run slow when combined." Change the queries to something like this:

SELECT * FROM QUERY1 WHERE NVL(:QUERY, 'A') = 'A' AND ROWNUM >= 1
UNION ALL
...

If you want to drill down more, and really get to the bottom of the performance issue, you'll need more powerful tools. You'll need to find the actual run times and cardinalities of each operation to dig a little deeper. The two best ways are usually either the /*+ gather_plan_statistics */ hint or create a SQL Monitor Report.

The explain plan you posted only shows Oracle's estimates. Oracle thinks that every operation the query performs will take 1 second and return at most 100 rows. Since your query is taking minutes, at least one of those estimates is clearly wrong. Most people just try to guess at which part of the plan is slow. With actual numbers, you can narrow down the problem to focus on a small number of operations. But even with this knowledge, solving the problem can still take a long time.