Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is APEX optimization behaves differently than SQL Developer?

Kinjan BhavsarNov 21 2020 — edited Nov 24 2020

Hi All,
I have come across one issue for one of my SQL queries.
When I run that page using LEVEL9 debugging, it gives me explain plan and in that one of table is showing as FULL SCAN and when I run same query with same parameters in SQL Developer, it uses the index and gives results in milliseconds.
Has someone faced similar issue or is there any way/steps to follow so the query uses proper index in APEX?
I am using APEX 18.2 and I have attached examples of explain plan from both APEX and SQL Dev.
From the screenshot you can see that in APEX it is showing cost as 3190 and in SQL Developer it is showing 24. I further found by checking each step of explain plan that one of my database table is doing a full scan in APEX and using proper index in SQL Developer so I am getting confused why would such thing happen.
I tried rebuilding all indexes of that table but it has same issue in APEX.
Can someone suggest what can I do to resolve this issue.

Comments

fac586

I have come across one issue for one of my SQL queries.
When I run that page using LEVEL9 debugging, it gives me explain plan and in that one of table is showing as FULL SCAN and when I run same query with same parameters in SQL Developer, it uses the index and gives results in milliseconds.
Has someone faced similar issue or is there any way/steps to follow so the query uses proper index in APEX?
I am using APEX 18.2 and I have attached examples of explain plan from both APEX and SQL Dev.
From the screenshot you can see that in APEX it is showing cost as 3190 and in SQL Developer it is showing 24.
I can't see any useful information there and visually impaired users can see nothing at all. DO NOT post inaccessible screenshots of textual information.
Post the complete explain plans as suitably formatted text.
I further found by checking each step of explain plan that one of my database table is doing a full scan in APEX and using proper index in SQL Developer so I am getting confused why would such thing happen.
I tried rebuilding all indexes of that table but it has same issue in APEX.
Can someone suggest what can I do to resolve this issue.
It depends. Where, when, and why is the query being used? If it is used as a region source, state the region type. If it is a report region, provide the pagination settings.
Post the full query as entered, and that shown in the debug trace at runtime as it is likely to have been wrapped in additional SQL to support APEX UI features and internal processing.

InoL

These kind of questions regularly pop up, and it's usually one of these "issues":
SQLDeveloper does not take paging into account (rows x-y)
SQLDeveloper does not take total number of rows into account (rows x-y of z)
SQLDeveloper does not take LOV lookups into account
SQLDeveloper does not take an order by into account

1 person found this helpful
fac586

The query appears to be incomplete and there is no SQL Developer execution plan for comparison.

fac586

Still not complete. The posted query is missing the termination of the APEX-generated d and i inline views.
You don't appear to be comparing the same thing. Specifically, the SQL Developer execution plan is missing the WINDOW NOSORT STOPKEY operation we would expect an APEX pagination filter to produce.
Get a SQL Developer plan for the actual query executed by APEX as shown in the debug log, not the region source entered.

1 - 4

Post Details

Added on Nov 21 2020
#apex, #apex-discussions, #database, #performance, #sql-developer
4 comments
297 views