Investigating the horrible performance of a form with the debugger I noticed the form plug-in executes a bizarre query. The form works on a view and I would expect the following query

SELECT * FROM (SELECT CUSTOMER_SITE_ID COL1,CATALOGUE_ADDRESS_1 COL2,CATALOGUE_ADDRESS_2 COL3,CUSTOMER_NAME COL4,CATALOGUE_ADDRESS_3 COL5,STORE_NAME COL6,CATALOGUE_POSTCODE COL7,CUSTOMER_CODE COL8,v('P10_FILLER_01') COL9,CUSTOMER_SITE_CODE COL10,STORE COL11,ADDRESS_1 COL12,ADDRESS_2 COL13,ADDRESS_3 COL14,POSTCODE COL15,ALLOWED_BRANDS COL16,v('P10_FILLER_02') COL17,CUSTOMER_ID COL18,ORGANIZATION_ID COL19 FROM DS_CUSTOMER_SITE_SALES_VW) WHERE COL1 = :P10_CUSTOMER_SITE_ID

What is actually being executed is

SELECT * FROM ( SELECT CUSTOMER_SITE_ID COL1,CATALOGUE_ADDRESS_1 COL2,CATALOGUE_ADDRESS_2 COL3,CUSTOMER_NAME COL4,CATALOGUE_ADDRESS_3 COL5,STORE_NAME COL6,CATALOGUE_POSTCODE COL7,CUSTOMER_CODE COL8,v('P10_FILLER_01') COL9,CUSTOMER_SITE_CODE COL10,STORE COL11,ADDRESS_1 COL12,ADDRESS_2 COL13,ADDRESS_3 COL14,POSTCODE COL15,ALLOWED_BRANDS COL16,v('P10_FILLER_02') COL17,CUSTOMER_ID COL18,ORGANIZATION_ID COL19,lead(CUSTOMER_SITE_ID) over(ORDER BY CUSTOMER_SITE_ID) AS fx#lead1,lag(CUSTOMER_SITE_ID) over(ORDER BY CUSTOMER_SITE_ID) AS fx#lag1,NULL AS fx#last1,NULL AS fx#first1 FROM DS_CUSTOMER_SITE_SALES_VW) WHERE COL1 = :P10_CUSTOMER_SITE_ID

As a result the view is executed as a full scan and then filtered with the predicate. Is this an artefact from FOEX or APEX, and if from FOEX any way preventing this superfluous horror.

asked 17 Jan, 00:05

Ray's gravatar image

Ray
200316
accept rate: 11%

edited 17 Jan, 08:59

FOEX-SUPPORT's gravatar image

FOEX-SUPPORT
3.7k15


The behaviour you are reporting is caused by the "Form Pagination" i.e. you have added an order by clause to your Form plugin. If we were to not use analytic functions then you would find that we would be using multiple queries to identify the next/previous records.

In this particular case we can see you have added a filter that negates the need for the lead/lag requirement. Unfortunately when we write a generic solution we are not able to know when this type of situation occurs. The question I would be asking is why the optimizer is favouring a full table scan and whether you are missing statistics or indexes in your view.

The quick fix is to disable form pagination, i.e. remove the "order by" value from the form plugin settings.

link

answered 17 Jan, 01:07

FOEX-SUPPORT's gravatar image

FOEX-SUPPORT
3.7k15
accept rate: 32%

I had no "order by" in the form plugin settings and there never was. It was the first thing I checked. Yes I checked all stats and indexes etc. So I tried creating the Form on this page again from scratch and low and behold this time it is all ok. No crazy sql statement. I understand that you apply the analytical functions for next/prev records. But I never use that option. Anyway it is all good now, but it left me baffled.

(17 Jan, 01:28) Ray Ray's gravatar image

Yes we are a bit baffled too as to why this would be enabled when the "order by" is not set. We will check the code for any clues.

(17 Jan, 01:42) FOEX-SUPPORT FOEX-SUPPORT's gravatar image

Ok I can now reproduce the problem. The form is the customer row and I have then a grid with Contacts. The moment I add a Master-Detail DA it adds the analytical function to the form with the customer row.

(17 Jan, 02:12) Ray Ray's gravatar image

All Master-Detail DA convert the master query to a new query with the analytical function. The problem is that the optimizer processes the inline view first and then performs the filter. This is not an issue created by missing statistics etc. CUSTOMER_SITE_ID is the primary key. I tested a master-detail which where the master and detail are a table (not views) and the same behavior occurs. Oracle will simply not push the predicate up into the inline view.

SELECT * FROM ( SELECT CUSTOMER_SITE_ID COL1,CATALOGUE_ADDRESS_1 COL2,CATALOGUE_ADDRESS_2 COL3,CUSTOMER_NAME COL4,CATALOGUE_ADDRESS_3 COL5,STORE_NAME COL6,CATALOGUE_POSTCODE COL7,CUSTOMER_CODE COL8,v('P10_FILLER_01') COL9,CUSTOMER_SITE_CODE COL10,STORE COL11,ADDRESS_1 COL12,ADDRESS_2 COL13,ADDRESS_3 COL14,POSTCODE COL15,ALLOWED_BRANDS COL16,v('P10_FILLER_02') COL17,CUSTOMER_ID COL18,ORGANIZATION_ID COL19,lead(CUSTOMER_SITE_ID) over(ORDER BY CUSTOMER_SITE_ID) AS fx#lead1,lag(CUSTOMER_SITE_ID) over(ORDER BY CUSTOMER_SITE_ID) AS fx#lag1,NULL AS fx#last1,NULL AS fx#first1 FROM DS_CUSTOMER_SITE_SALES_VW) WHERE COL1 = :P10_CUSTOMER_SITE_ID

If I make the inline view the query as in

SELECT CUSTOMER_SITE_ID COL1,CATALOGUE_ADDRESS_1 COL2,CATALOGUE_ADDRESS_2 COL3,CUSTOMER_NAME COL4,CATALOGUE_ADDRESS_3 COL5,STORE_NAME COL6,CATALOGUE_POSTCODE COL7,CUSTOMER_CODE COL8,v('P10_FILLER_01') COL9,CUSTOMER_SITE_CODE COL10,STORE COL11,ADDRESS_1 COL12,ADDRESS_2 COL13,ADDRESS_3 COL14,POSTCODE COL15,ALLOWED_BRANDS COL16,v('P10_FILLER_02') COL17,CUSTOMER_ID COL18,ORGANIZATION_ID COL19,lead(CUSTOMER_SITE_ID) over(ORDER BY CUSTOMER_SITE_ID) AS fx#lead1,lag(CUSTOMER_SITE_ID) over(ORDER BY CUSTOMER_SITE_ID) AS fx#lag1,NULL AS fx#last1,NULL AS fx#first1 FROM DS_CUSTOMER_SITE_SALES_VW) WHERE CUSTOMER_SITE_ID = :P10_CUSTOMER_SITE_ID

the query performs as expected (fast)

link

answered 17 Jan, 03:39

Ray's gravatar image

Ray
200316
accept rate: 11%

Ok thanks for reporting, we are going to investigate further.

(17 Jan, 03:42) FOEX-SUPPORT FOEX-SUPPORT's gravatar image

Technically pushing the predicate into the inline view would be wrong as the result would be different. In this particular case it would work as only 1 row would be returned either way as it works on the primary key column.

(17 Jan, 04:00) Ray Ray's gravatar image

I found a workaround for the performance problem. It only works if you don't use the order by clause in the form plug-in. I added to the where clause of the form plug-in "customer_site_id = :P10_CUSTOMER_SITE_ID". This pushes the predicate into the inline view.

...Execute Statement: SELECT * FROM ( SELECT CUSTOMER_SITE_ID COL1,CATALOGUE_ADDRESS_1 COL2,CATALOGUE_ADDRESS_2 COL3,CUSTOMER_NAME COL4,STORE_NAME COL5,CATALOGUE_ADDRESS_3 COL6,CUSTOMER_CODE COL7,CATALOGUE_POSTCODE COL8,v('P10_FILLER_01') COL9,CUSTOMER_SITE_CODE COL10,STORE COL11,ADDRESS_1 COL12,ADDRESS_2 COL13,ADDRESS_3 COL14,POSTCODE COL15,ALLOWED_BRANDS COL16,CUSTOMER_ID COL17,lead(CUSTOMER_SITE_ID) over(ORDER BY CUSTOMER_SITE_ID) AS fx#lead1,lag(CUSTOMER_SITE_ID) over(ORDER BY CUSTOMER_SITE_ID) AS fx#lag1,NULL AS fx#last1,NULL AS fx#first1 FROM DS_CUSTOMER_SITE_SALES_VW WHERE (customer_site_id = :P10_CUSTOMER_SITE_ID) ) WHERE COL1 = :P10_CUSTOMER_SITE_ID

Execution time drops from 1.92 seconds to 0.0039 Of course it is only temporary hack

link

answered 17 Jan, 04:26

Ray's gravatar image

Ray
200316
accept rate: 11%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×130
×1

Asked: 17 Jan, 00:05

Seen: 226 times

Last updated: 17 Jan, 08:59

FOEX Links