Solved

Oracle forms LOV slow performance with bind variables

Posted on 2014-11-21
7
564 Views
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?
OR
Is option 3 the only option?

Cheers
0
Comment
Question by:beeker270806
  • 4
  • 3
7 Comments
 
LVL 34

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?
0
 

Author Comment

by:beeker270806
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.

Cheers
0
 
LVL 34

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.
0
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

by:beeker270806
ID: 40460393
Hi there,

The application is Oracle E-Business suite.

Cheers
0
 
LVL 34

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.
0
 

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:beeker270806
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.
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

705 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

18 Experts available now in Live!

Get 1:1 Help Now