SQL Runtime/Executentime APEX is much higher than in SQLDeveloper

Lars_

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?

Mike Kutz

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