Solved

Oracle forms LOV slow performance with bind variables

Posted on 2014-11-21
7
613 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 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
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 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

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.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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