Bind In Peoplesoft

8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries.

If you've read anything about writing OLTP applications that talk to Oracle databases, you will know that bind variables are very important.

Each time a SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found a hard parse is performed, which is a resource intensive process. If the statement is found in the shared pool this step is not necessary and a soft parse is performed. Concatenating variable values into a SQL statement makes the statement unique, forcing a hard parse. By contrast, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable changes.

Why do we care?

Holding many similar SQL statements in the shared pool is a waste of memory.
Filling the shared pool with similar statements will cause well written statements to get paged out of the shared pool quickly, forcing them to be reparsed also.
Parsing SQL statements is a resource intensive process. Reducing the number of hard parses results in reduced CPU usage.
Bind variables protect against SQL Injection.

In the sections below you will see the impact of using literals, substitution variables and bind variables in your code.

I have the below PeopleCode step in an Application Engine program that reads a CSV file using a File Layout and then inserts the data into a table, and I am just trying to get a better understanding of how the the line of code (&SQL1 = CreateSQL('%Insert(:1)');) in the below script gets generated.It looks like the CreateSQL is using a bind variable (:1) inside the Insert statement, but I am.
The%DateIn meta-SQL variable expands into platform-specific SQL syntax for the date. Use%DateIn whenever a date literal or Date bind variable is used in a comparison in the Where clause of a Select or Update statement, or when a Date value is passed in an Insert statement.
Use bind variables that intelligently pick the right plan every time and make sure a new execution plan is perfect before it's used. By now many of you have heard an earful about how using bind variables enhances performance; for those who haven't, let me try to explain the core concepts in as simple manner as I can.
Bind Peeking¶ 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.

Literals

The following example shows the affect of using literals on the shared pool. First the shared pool is cleared of previously parsed statements. Then two queries are issued, both specifying literal values in the WHERE clause. Finally the contents of the shared pool is displayed by querying the V$SQL view.

From this we can see that both queries were parsed separately.

Substitution Variables

Substitution variables are a feature of the SQL*Plus tool. They have nothing to do with the way SQL is processed by the database server. When a substitution variable is used in a statement, SQL*Plus requests an input value and rewrites the statement to include it. The rewritten statement is passed to the database. As a result, the database server knows nothing of the substitution variable. The following example illustrates this by repeating the previous test, this time using substitution variables.

Once again, both statements were parsed separately. As far as the database server is concerned, literals and substitution variables are the same thing.

Exactly the same behavior occurs when scripts contain placeholders to allow parameters to be sent to them from the command line. So for example, imagine a script called 'dummy.sql' containing the following.

This can be called from SQL*Plus like this.

When run, the placeholder '&1' will be replaced by the value 'MyValue'. This is just the same as a substitution variable.

Bind Variables

The following example illustrates the affect of bind variable usage on the shared pool. It follows the same format as the previous examples.

This clearly demonstrates that the same SQL statement was executed twice.

Performance Issues

The following example measures the amount of CPU used by a session for hard and soft parses when using literals. The shared pool is flushed and a new session is started. Dynamic SQL is used to mimic an application sending 10 statements to the database server. Notice that the value of the loop index is concatinated into the string, rather than using a bind variable. The CPU usage is retrieved from the V$MYSTAT view by querying the 'parse time cpu' statistic. This statistic represents the total CPU time used for parsing (hard and soft) in 10s of milliseconds. The statements present in the shared pool are also displayed.

The results show that 630 milliseconds of CPU time were used on parsing during the session. In addition, the shared pool contains 10 similar statements using literals.

The following example is a repeat of the previous example, this time using bind variables. Notice that the USING clause is used to supply the loop index, rather than concatenating it into the string.

The results show that 400 milliseconds of CPU time were used on parsing during the session, less than two thirds the amount used in the previous example. As expected, there is only a single statement in the shared pool.

These simple examples clearly show how replacing literals with bind variables can save both memory and CPU, making OLTP applications faster and more scalable. If you are using third-party applications that don't use bind variables you may want to consider setting the CURSOR_SHARING parameter, but this should not be considered a replacement for bind variables. The CURSOR_SHARING parameter is less efficient and can potentially reduce performance compared to proper use of bind variables.

SQL Injection

Concatenating strings together with user input to form an SQL statement is a classic way to allow an SQL injection attack against your system. If you use bind variables for user input, the statement can't be attacked with an SQL injection attack. The statement is fixed. Only the bind variable value changes.

If for some reason you can't use a bind variable, you must sanitise the user input. This can be done using the DBMS_ASSERT package, described here.

For more information see:

Hope this helps. Regards Tim...

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.

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.

Bind Values In Peoplesoft

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:

Bind In Peoplesoft Oracle

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.

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.

Bind Records In Peoplesoft

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.

Oracle Bind Variable

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

Heyloading