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?

score 0 · Answer 1 · answered May 24 '23 at 09:25

0

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".

answered May 24 '23 at 09:25

Koen Lostrie

14,938
2
13
19

IG does not have a setting like `X to Y of Z` in pagination. Although I've tried disabling `total row count`. Still having the same performance issue. – Anand Jagtap May 24 '23 at 09:30
Actually it works if I use only single select statement as I've updated in the main description. However we need to show the data as per `:QUERY` value. Which can be null if user wants to see all. – Anand Jagtap May 24 '23 at 10:14
I'm not a tuning specialist. Maybe ask your question on the official oracle forum – Koen Lostrie May 24 '23 at 11:08

score 0 · Answer 2 · answered May 25 '23 at 04:09

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.

Hi Jon, I went through your url, but I can't see all columns from output plan. Like I'm missing `A Rows`, `A Time`, `Buffer`, `Reads` — Anand Jagtap, May 26 '23 at 06:37
@AnandJagtap Maybe you used the wrong SQL_ID or setup the hint incorrectly? — Jon Heller, May 26 '23 at 17:02

Anand Jagtap · Accepted Answer · 2023-05-27T08:00:15.873

0

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.

edited May 27 '23 at 08:00

answered May 25 '23 at 06:14

Anand Jagtap

113
1
9

How many rows total are in the results? Your change likely made the Top N rows return quickly, the but time for the query to retrieve *all* rows may have stayed the same. The explain plans says Oracle thinks there will only be one row returned, but I'm guessing that's way off. If you're lucky, this is just a problem from bad optimizer statistics and you can improve the plan by running `begin dbms_stats.gather_schema_stats('SCHEMANAME'); end;`. If that helps, you may also need a DBA to look into why statistics aren't automatically gathered. – Jon Heller May 27 '23 at 17:03

Oracle Apex Reports causing performance issue

3 Answers3