The ReUse statement setting on an Application Engine is a great way to increase performance. 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. With the default setting of 'No', a new SQL statement is generated which needs to be parsed and stored in 'Shared Pool' for every row that satisfies the DoSelect loop. This is like thrashing the shared pool literally. You should see some 'wait events' on latches in the shared pool that clearly would mean, the performance is getting degraded. 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.
This is a very simple and low hanging fruit but yet this would result in dramatic performance improvements.
No comments:
Post a Comment