Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
SQL Runtime/Executentime APEX is much higher than in SQLDeveloper

Hello everybody,
A page of my APEX Application runs a sql-query which takes about 30 up to 45 minutes (shown in Oracle Enterprise Manager) (Screenshot1).
When I click on the details of this query and copy it into sqldeveloper, the query only takes less than 1 second (Screenshot2).
So the same query takes 30 min (APEX) or 1 second (sqldeveloper). Does anybody have an idea why?
Answers
-
APEX could be using Bind Variable.
I didn't see any Bind Variables in your SQL*Developer statement.
Plan could change between BIND vs no BINDs
APEX has a habit of mucky your SQL statement so that it can do things like Paging.
Are you sure you are comparing the same SQL statements?
Check APEX Debug Logs for actual SQL statement used.
You need to compare how long it takes to fetch an identical number of rows.
Remember, Oracle "streams" the results.
SQL*Developer "returns" after it gets the first few rows. Since Oracle "Streams" results, it could take ~1s to get those values.
For things like "page x of y", APEX needs to process all row just so that it can calculate "y".
If finding the last row take 30 - 45 minutes in APEX, finding the last row in SQL*Developer should take 30 -45 minutes also.
SQL*Developer trick
When in the Table Result Window, you can fetch the last row by hitting ctrl-end.
Bad news: It will cache all that data locally. Be mindful of how big your result is expected to be.
Good new: If all the data is cached locally, "right click -> export" will use that data instead of re-querying the data. This is the trick I use for my hour+ long queries.
That is all I can think of for "why different".
My $0.02
MK