Forum Stats

  • 3,734,267 Users
  • 2,246,933 Discussions
  • 7,857,216 Comments

Discussions

Is APEX optimization behaves differently than SQL Developer?

Kinjan Bhavsar
Kinjan Bhavsar Member Posts: 49 Red Ribbon
edited Nov 24, 2020 7:15AM in APEX Discussions

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.

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,676 Black Diamond

    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
    InoL Member Posts: 8,995 Silver Crown

    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
    Kinjan Bhavsar
  • fac586
    fac586 Senior Technical Architect Member Posts: 19,676 Black Diamond

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

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,676 Black Diamond

    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.

Sign In or Register to comment.