oracleplsqloracle-apexoracle-apex-19.1interactive-grid
Oracle Apex Reports causing performance issue
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?
Solution
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.
- Table Creating in Toad
- Max and Rank not returning expected results
- Get records for same date from table in Oracle
- C# Nhibernate | PL/SQL Oracle - different length of string when it contains diacritical marks
- Oracle XE installation aborts with no error explanation
- Oracle PL SQL Insert Statement - ORA-01007: variable not in select list
- Is it possible to have a "deferred check constraint" in Oracle?
- plsql procedure to save html in table
- How to process tabular data from Oracle PL/SQL function in JAVA?
- TO_CHAR to format decimal places not working for whole numbers
- How to replace null values with a text?
- Microsoft SSIS - Issues switching from attunity driver to new Microsoft Oracle Connector v1.3
- Writing SQL Query With Same Client ID , same Tax_year, same Tax_effective_date But with Different Tax_id & Payroll_ID in ORACLE SQL
- Difficulty Extracting Value of a Key in CLOB JSON with Unknown Path
- How do you execute SQL from within a bash script?
- How to connect to Oracle 8i using Java
- Convert JSON to table when all JSON field keys are unknown/arbitrary/random
- How to sort all numbers before letters with linguistic sorting?
- Oracle Sql Statement for unique timestamp for each row
- ambiguity error when adding pivot to existing SQL oracle statement
- Within PL/SQL script create table based on Oracle Edition
- Connect to sqlplus in a shell script and run SQL scripts
- Oracle SQL hierarchical query data accumulation
- Does Oracle field contain all letters from a string?
- Oracle JSON_TABLE to PostgreSQL - how to search from the second hierarchical key in a JSON column
- ORA-00902: invalid datatype with EF Core
- SQL query to get the employee who has not been appraised
- TOAD for Oracle, variables for schema/ table names
- ORACLE APEX ADMIN LOGIN ISSUE
- Fetch last row for each month, or previous month if not found