When working on a performance issue, you may reach the point where you want to introduce one or more hints into a SQL statement. It is often tempting to simply add the hints to the source code. Doing so may appear to be simpler. That is often the case with Application Engine programs, however, it is much more difficult in PeopleSoft COBOL programs.
The bind step makes an association between the placeholder name and the address of the program variables. The bind also indicates the datatype and length of the program variables, as illustrated in Figure 5-1. Bind variables are not an Oracle'ism. Is the following approach right Kulguru, December 27, 2001 - 3:20 pm UTC Tom I observed that my predecessor developers,have.
A strong argument against such code change is that having made it, you have also to functionally test the change and push it through the entire release management process to get it into production. Then, should you ever want to change or remove the hints, you have to go through the entire process again.
Oracle provides several mechanisms to define a database object containing a hint or set of hints and to apply them to matching SQL statements. These mechanisms work best with SQL that uses bind variables rather than literals. If an otherwise identical SQL statement has different literal values then it has a different SQL_ID and is treated by the database as a new statement. SQL Plan Baselines, SQL Patches and SQL Profiles match to the specific text of a SQL statement. Different literal values will prevent matching and defeat these mechanisms. These techniques must still be tested and migrated in a controlled manner, but they have no functional impact and so only testing of performance is needed.
SQL Profiles can also perform forced matching, where statements that are similar except for literal values are matched. However, note that they also require licencing of Tuning Pack.
Some parts of PeopleTools and the PeopleSoft applications are better than others at producing sharable SQL with bind variables. Most of the SQL generated by the component processor uses bind variables. In Application Engine, if the ReUse Statement property is not set, which it is not by default, the %BIND fields are substituted with their literal values in the SQL statement. However, if the property is set then %BIND fields become bind variables. Over the years much more PeopleSoft code has been delivered with this attribute enabled. Doing so has significant performance benefits (see Performance Benefits of ReUse Statement Flag in Application Engine).
Where, under normal circumstances, I might use a baseline or patch to inject a hint or profile of hints into a particular SQL statement (i.e. where I am dealing with a single SQL_ID), if the statement has literal values that change, then each statement has a different SQL_ID. I have experimented with setting CURSOR_SHARING to FORCE at session-level for a specific scheduled process, but I have always had very poor experiences with that approach. It invariably causes more problems than it solves. Instead, I use force matched SQL Profiles.
The PeopleTools documentation sets out situations where ReUse Statement cannot be set. This includes dynamically generated code where %BIND(…,NOQUOUTES) is used to embed a piece of SQL held in a variable. This is a very common technique in PeopleSoft; often dynamically generated code is driven by the application configuration.
We also see a similar design in PeopleSoft's COBOL programs. Static statements are loaded from the stored SQL statements table (PS_SQLSTMT_TBL) and do use bind variables, but dynamic statements are assembled at runtime from static fragments in the COBOL code and any variable values are embedded as literals rather than using bind variables.
Forced matching will allow a SQL profile to match a statement that is the same except for different literal values. However, dynamic SQL statements can change in ways that are beyond that, including:
- Different instances of working storage tables can be used by different instances of the same process.
- Different columns can be used in select and group by clauses.
- Different criteria can be introduced.
- A different number of terms in an IN() condition.
Occasionally, and I really mean very occasionally when I have exhausted other alternatives, I have dynamically created groups of SQL Profiles (still with forced matching) to cover every permutation of the variations of the dynamic SQL statement.
Example
Here is a dynamic statement from such a COBOL process, FSPCCURR. This General Ledger Revaluation process adjusts the base currency value of the account balances by creating adjusting entries for the accounts being revalued. It creates corresponding entries for any gain or loss that results from the revaluation. It was captured by AWR, and I have extracted it with DBMS_XPLAN.DISPLAY_AWR.
The ledger table (PS_LEDGER) is joined to a working storage table of tree selectors (PS_TREE_SEL10_R001) and working storage table (PS_CURR_WRK_RT001) and the result is put into another working storage table (PS_CURR_WRK_TBL001).
However there are several copies of each of these working storage tables, and different concurrent instances of this process may be allocated different copies. There is also an in clause that lists the accounting periods to be processed.
So the statement can vary. Here is another version of what is essentially the same statement with different literal values and different tables and for a different accounting period (this time period 5). The parts in bold a the ones that vary from statement to statement that are not literal valuesIf I want to use SQL Profiles to introduce hints to control the execution plan, then I will need a different profile for every possible permutation.
I start by using Carlos Sierra's coe_xfr_sql_profile.sql script. This is a part of Oracle's SQLTEXPLAIN (SQLT) tool. It generates a SQL script that generates a SQL profile to reproduce a given execution plan for a given SQL statement that was captured by AWR.
From there is not a big jump to add a SQL statement to generate all the permutations of the variations in the SQL (other than for bind variables) and create a profile inside a loop. The exact details will vary depending on the behaviour of the program. However, in this particular example I need:
- Different SQL profiles will be needed for each accounting period because there will be a different list of accounting periods in the IN() condition. Subquery factors n and n1 produce a list of accounting periods.
- Lists of the various tables used for working storage can be queried from the PeopleSoft data dictionary, PSRECDEFN. I can see that there are 5 versions of the current work table that the process can choose from. Note that these are ordinary tables, so there are 5 different records in PSRECDEFN.
Bind In Peoplesoft Oracle
- However, if I was working on a temporary record used in an Application Engine program, I would need to look up the number of instances of that record.
- The number of non-shared temporary record tables is the sum of all the instances defined on each application engine program to which the record is allocated, plus the number of global instances, up to a maximum of 99. Instance 0 is the shared instance. The number can be altered in development and the additional tables built by Application Designer. This can require additional SQL Profiles be built.
- In this particular example, I know that every permutation of all three tables could occur in all accounting period, so I simply Cartesian join all the subquery factors.
- In other cases, only some permutations may occur. This must be handled in the code that is written. Literal values do not need to be considered because the profile will be created with force matching.
- In Application Engine, although you often see the same instance of different temporary records used in the same process, there is nothing to prevent different instances of different records being used, and so all permutations must be considered.
- I will also concatenate the ID for each table, and also the accounting period to produce an ID string that I can use in the name of the SQL profile.
The profile generated by coe_xfr_sql_profile.sql will contain the complete profile of hints for the SQL statement captured by AWR. That is officially the only way to guarantee a particular execution plan. However, as in this example, I could specify the just that I want to introduce in the statement, effectively treating a SQL Profile as if it was a SQL Patch. See also:
- Nigel Bayliss: Adding and Disabling Hints Using SQL Patch.
- Kerry Osborne: Oracle Support Sanctions Manually Created SQL Profiles!
Here is the modified script. Note the sections in bold.
- The SQL statement in the FOR clause returns all the permutations of the variations in the SQL statement in an implicit cursor.
- Table names are concatenated into the SQL text from the columns in the implicit cursor.
- Single quotation marks are doubled so that the string contains the single quotation mark.
- It is important not to add or remove any spaces when introducing these changes.
- Profiles are dropped and created inside the loop. Force Matching is enabled.
When I implemented this particular example, it created 900 SQL profiles. Each one has a different force matching signature. Having this many SQL Profiles is not a problem for the database because they are looked up by the signature.
However, managing that many profiles could become a cause for concern by the DBA. Therefore, I think it is important that they have a sensible naming convention so that it is obvious to what they relate.
Further reading
- Go-Faster Oracle Blog: Hints, Patches, Force Matching and SQL Profiles. This post also demonstrates dynamically generating SQL Profiles, on this occasion for nVision queries.
- Jonathan Lewis: Hacking Profiles
If bind variables are so grand, why not enable them by default, everywhere?
The problem lies in what is referred to as bind peeking.When Oracle encounters a statement with bind variables for the very first time, it looks at the literals supplied, checks the histogram (if available), and then fixes the execution plan.
In itself this seems reasonable, right?When data is highly skewed that may become an issue.Let’s go back to our
fridge
table and fill it in accordance with the appetite of many developers: beer, beer, and some more beer.Because we have a few guests over tonight we also buy some white wine, salad, and avocados; please, don’t ask why.We have created a histogram too: 95% of the rows are related to beer, whereas the remaining 5% are split among the three newly acquired products.When we send our friendly household robot to run to the kitchen to get beer, and the query contains a bind variable for product, a full table scan will be used.The next time we send it to get white wine and it still uses a full table scan even though an index lookup would be much faster.Why does it do a full table scan?For the first execution our database robot peeked at the bind variable’s value, and because its value was
'Beer'
, the execution plan was fixed with a full table scan.The second execution did not care about the fact that we wanted 'WhiteWine'
since the robot had already decided that the execution plan involved a full table scan.Bind In Peoplesoft Email
The reverse situation is also possible.An index scan is used based on the initial execution of the request for something other than beer, even though a full table scan is much more advantageous when we desire beer.Thus, as stated by Arup Nanda, “smart SQL coders will choose when to break the cardinal rule of using bind variables, employing literals instead.”