Oracle forms LOV slow performance with bind variables

Hi experts,

We have an oracle forms LOV that is very slow (can run up to an hour on customer site).

The LOV is based on a record group that includes bind variables from other fields in the forms blocks.

If we run the record group SQL in SQL*Plus or developer tool replacing the bind variables with the literal values - it is very quick (less than a second)

We cannot reproduce the issue back at our site on our environments as we don't have the same data volumes as our customer.

I have this question:

1) On site the trace file reveals that the bind variables create a PL/SQL block that is issued to the database rather than a straight SQL statement - does anybody know why?

This PL/SQL block produces a plan that is very differant from the standard SQL with literal values so we will be trying to resolve the issue using these techniques:

1) provide the hint BIND_AWARE in the record group query
2) provide hints in the record group query for the indexes we want the statement to use
3) Rewrite the record group so that it is programmatically created with the literal values when the LOV is to be used to avoid using the bind variables *** we would prefer not to use this option unless absolutely necessary because of re-test and deployment complications

So i suppose another question to be answered is:

Do you think options 1 or 2 will work?
Is option 3 the only option?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
Or option 4: calculate statistics on the table(s) and index(es) that this LOV references.

Do you know if the customer regularly re-calculates statistics on the tables this application uses?
beeker270806Author Commented:
Hi markgeer,

Thanks for the comment - i can confirm that statistics and indexes are up to date.
The exact same SQL runs rapidly outside the form LOV context.

Mark GeerlingsDatabase AdministratorCommented:
"1) On site the trace file reveals that the bind variables create a PL/SQL block that is issued to the database rather than a straight SQL statement - does anybody know why?"

I would say that is not exactly accurate.  Bind variables cannot create PL\SQL blocks.  Applications can create PL\SQL blocks.  What kind of application is this?

Then, what exactly do you mean by "straight SQL statement"?  Do you mean a statement with literal values, and no bind variables?  That is *NOT* what Oracle databases are optimized for.  Oracle databases are optimized for SQL statements that include bind variables.  These allow the database to re-use previously-processed statements and save a lot of parsing overhead (is/are the table name(s) valid; are the column names valid; does this user have permission to query this/these table(s); are there indexes available; if yes which index(es) should be used, etc.)

It is common in Oracle for statements with hard-coded literal values to pick a different plan than similar statements that use bind variables.  One disadvantage of bind variables is the fact that Oracle doesn't know in advance whether the value of a particular bind variable will be a common value in the table or a very unique one.  If the table is large and if this column is indexed, one plan or another may perform very differently with different bind values expecially if they differ in the number of times they occur in the table.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

beeker270806Author Commented:
Hi there,

The application is Oracle E-Business suite.

Mark GeerlingsDatabase AdministratorCommented:
The Oracle E-Business Suite is a very complex application.  It is designed for maximum flexibility, so Oracle can sell it to almost any organization in the world and claim that it has the features that almost any business needs.  It is *NOT* designed for maximum performance!  When customers use E-Business Suite for a while and add lots of records, some parts of the application that used to perform acceptably will get slower.  When this happens, Oracle would like the customer to add CPUs to the database server, or add database servers (RAC) or buy Exadata.  All of those options result in more database license revenue for Oracle.

Your other options include:
1. Make sure that table and index statistics are up-to-date.
2. Add custom indexes to help particular queries/reports.
3. Archive (if necessary) then purge some of the older data, and rebuild the tables and indexes to get performance back to an acceptable level.
4. Customize the application to avoid the performance problem.

Or, if you are fortunate you may be able to:
Configure the application differently via profile values to avoid the problem.
beeker270806Author Commented:
Have solved it by forcing hints and dynamically creating the record group at runtime.

Did lots of research into bind variable peeking which was handy also.

Thanks for your comments.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
beeker270806Author Commented:
The options i presented in my original post were what i used in the end.

The issue of why the bind aware functionality is not being used still remains un-resolved but i have to move on now and may come back to that when time permits.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.