Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 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
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 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
