Oracle forms LOV slow performance with bind variables

Posted on 2014-11-21
Last Modified: 2014-12-02
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?

Question by:beeker270806
  • 4
  • 3
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40457578
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?

Author Comment

ID: 40457628
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.

LVL 35

Expert Comment

by:Mark Geerlings
ID: 40457666
"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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 40460393
Hi there,

The application is Oracle E-Business suite.

LVL 35

Expert Comment

by:Mark Geerlings
ID: 40462067
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.

Accepted Solution

beeker270806 earned 0 total points
ID: 40469647
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.

Author Closing Comment

ID: 40475500
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.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Bulk insert into global temporary table 2 59
Oracle Pivot 2 43
Oracle DATE Column Space 11 61
Check if there are any  duplicate claims paid in a year for each member 25 69
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now