ReUse in App Engines July 31, 2007
Posted by Duncan in Application Engine, PeopleSoft, PeopleTools, SQL.trackback
One of the easiest tricks to improve performance of some App Engines is the ‘ReUse’ setting. It’s useful when you have a SQL object that is called multiple times, for instance when you have a DoSelect that runs a SQL Step repeatedly, substituting different Bind variables in each time.
The default setting for ReUse is ‘No’, which means that each time the SQL Step is called it is recompiled and new execution plan generated, bad news if the SQL has to run a large number of times. If the ReUse setting is ‘Yes’ then any %Binds in the SQL statement are converted into ‘:1’, ‘:2’ etc. This means the statement can just be compiled once and the same plan used each time, resulting in really quite dramatic improvements in processing time.
In addition to the performance gain from the above, some databases also have an area of memory cache where recently used SQL is stored. Oracle definitely has this cache, I’m not sure about other databases. As each SQL statement would contain Binds rather than literals it would be an exact match for the previous SQL Statement enabling the database to use the cached SQL and thus giving a further performance boost.
As always, there are some exceptions. If the %Bind is either part of either a record or column name (using ‘NOQUOTES’) then it shouldn’t be used. If this is the case the record and/or field is changing each time and as a result a new plan would be needed, therefore PeopleSoft converts the Binds to statics.
The performance gains that this gives will vary depending upon your SQL and the data, but a recent opportunity I had to set the ReUse flag to Yes dropped processing time for an AE Section from 15 mins to just under 2. Well worth considering next time you write an App Engine …
Comments
Sorry comments are closed for this entry
The PeopleSoft Tipster Blog 
Thanks for posting this. I applied it to one of my processes and it cut a section down from 20 minutes to about 6. A nice gain! Thanks Again..
ReUse can be very useful, and it is a great pity that PeopleSoft does not make more use of it in the delivered processes. Using binds instead of literals is nearly always a good thing in a database because it reduces hard parse. Similar statements with different literals are different statements and they have to be parsed (unless you use cursor sharing which is a rather blunt instrument and can cause other problems).
If you are using a Unicode on Oracle RDBMS on PeopleSoft applications prior to v9, you will have length checking constraints on all your character columns. The constraints (in sys.cdef$) are not cached in the memory by the database and have to be re-queried during every hard parse. This can produce a huge overhead during AE batch programs. Financials systems are particularly affected. ReUse inside Loops in AE can significantly reduce parse overhead. This problem goes away in v9 apps when character semantics are introduced. But binds variables are still recommended.
However, in Oracle it is sometimes possible to get problems with bind variable peeking (this is when Oracle looks at the bind variable values during the parse phase). If the bind values during parse are not typical you may an unexpected execution plan, and then you carry on using it for all executions of the SQL statement.
However, the problem with introducing this into vanilla code is that enabling ReUse is a customisation, that must be tested and released like any other customisation – and it is your responsibility if there is a problem, no PeopleSoft/Oracle’s.
[…] Shared Pool December 13, 2007 Posted by PeopleSoft Tipster in Oracle, PeopleSoft, SQL. trackback Oracle Databases contain a ’shared pool’, which is a repository for recently run SQL. If you attempt to execute the same piece of SQL repeatedly you’ll probably notice that the execution time decreases, this is because of how Oracle parses the SQL (i.e. it’s a soft parse, not a hard parse) and the execution plan is already calculated for the SQL. I’ve spoken about this before in an App Engine context here. […]
If reuse and bulk insert are so much efficient, then why they are not default properties.