Solved

Oracle forms LOV slow performance with bind variables

Posted on 2014-11-21
7
693 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
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?
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 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.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:beeker270806
ID: 40460393
Hi there,

The application is Oracle E-Business suite.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

737 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