ReUse flag in PeopleSoft AE program – Bind variable issue and its impact on Performance

It has been long since I blogged as I got busy. Personally I became a Father and officially, I moved to PeopleSoft Performance and Benchmark(P&B) team. Now my time is dedicated to handling escalated performance issues from customer and internal PeopleSoft tuning projects.
Recently I got an escalation from dev team that a customer is having an performance issue in Fusion – PeopleSoft co-exist module and they were about to go alive in next couple of days. Asked customer to provide us AE trace (level 135), process scheduler configuration file and few other application architecture details. From the AE trace I found FT_COMP_CHNG taking lot of time(Close to 6 hours).

AE Program: FT_COMP_CHNG

                               C o m p i l e    E x e c u t e    F e t c h        Total          

SQL Statement                  Count   Time     Count   Time     Count   Time     Time   

------------------------------ ------- -------- ------- -------- ------- -------- --------

MAIN.Clear.S                         1      0.0       1    119.9       0      0.0    119.9

MAIN.Extract.S                       1      0.0       1     99.7       0      0.0     99.7

MAIN.Filter.D                        1      0.0       1     11.5   37596      0.5     12.0

xxxxxx.Step01.S                  37595     24.7   37595   5254.7       0      0.0   5279.4

xxxxxx.Step02.S                  37595      3.1   37595   5809.9       0      0.0   5813.0

xxxxxx.Step03.S                  37595      3.1   37595   5099.4       0      0.0   5102.5

xxxxxx.Step01.S                  37595     26.2   37595   5208.1       0      0.0   5234.4

xxxxxx.Step04.S                  37595      3.4   37595     24.7       0      0.0     28.1

xxxxxx.Step01.S                  37595      2.4   37595     27.4       0      0.0     29.9

                                                                                  --------

                                                                                   21718.8

The moment I saw compile count equal to execution count, I did a quick check on the SQL in the associated steps and found they are static with just. Recommended the dev team to set ReUse flag on the steps. Also from the architecture, I found DB server and process scheduler are on same box. So we have recommended customer to set UseLocalOracleDB to 1 instead of 0.

When the recommendation was send to the customer and customer has shared the latest trace as follows:

AE Program: FT_COMP_CHNG

                               C o m p i l e    E x e c u t e    F e t c h        Total          

SQL Statement                  Count   Time     Count   Time     Count   Time     Time   

------------------------------ ------- -------- ------- -------- ------- -------- --------

xxxxxx.Clear.S                         1      0.0       1    142.3       0      0.0    142.3

xxxxxx.Extract.S                       1      0.0       1     93.1       0      0.0     93.1

xxxxxx.Filter.D                        1      0.0       1      7.2   37440     10.8     18.0

xxxxxx.Step01.S                        1      0.0   37439      8.7       0      0.0      8.7

xxxxxx.Step02.S                        1      0.0   37439      5.6       0      0.0      5.6

xxxxxx.Step01.S                        1      0.0   37439     18.0       0      0.0     18.0

xxxxxx.Step03.S                        1      0.0   37439     61.2       0      0.0     61.2

xxxxxx.Step01.S                        1      0.0   37439     11.2       0      0.0     11.2

                                                                                    --------

                                                                                       358.1

Now the program got finished in less than 6 minutes (earlier it was more than 6 hours).

Technicalities behind the scene:

ReUse Flag – Quote from PeopleBook:

One of the key performance features of Application Engine is the ability to reuse SQL statements by dedicating a persistent cursor to that statement.

Unless you select the ReUse property for a SQL action, %BIND fields are substituted with literal values in the SQL statement. The database has to recompile the statement every time it is executed.

However, selecting ReUse converts any %BIND fields into real bind variables (:1, :2, and so on), enabling Application Engine to compile the statement once, dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can result in dramatic improvements to performance.

Impact of ReUse Flag on Oracle database:

 If we don’t the ReUse flag for the static SQLs executing in loop in the AE program, literal values are passed as such to the Oracle database and it will cause the database to hard parse each and every time. Following quote was taken from Expert Oracle database Architecture 2^nd edition written by Thomas Kyte.

If I were to write a book about how to build non-scalable Oracle applications, “Don’t Use Bind Variables” would be the first and last chapter. Not using bind variables is a major cause of performance issues and a major inhibitor of scalability—not to mention a security risk of huge proportions. The way the Oracle shared pool (a very important shared-memory data structure) operates is predicated on developers using bind variables in most cases. If you want to make Oracle run slowly, even grind to a total halt, just refuse to use them.

Links:

ReUse Flag

http://docs.oracle.com/cd/E26239_01/pt851h3/eng/psbooks/tape/book.htm?File=tape/htm/tape05.htm#H3009

UseLocalOracleDB

http://docs.oracle.com/cd/E26239_01/pt851h3/eng/psbooks/tsvt/book.htm?File=tsvt/htm/tsvt07.htm#H4016

As always, your comments are always welcome. Hope this will be good performance tuning hints for atleast few.

Thanks,

Thiru