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?