Saturday, April 20, 2013

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 2nd 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
UseLocalOracleDB

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

Thanks,
Thiru

6 comments:

Unknown said...

Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital informationOracle performance Tunning Training in Chennai

Ram Niwas said...

Really an interesting post, thanks for sharing .keep it up.

Oracle Performance Tuning Course
Oracle DBA Training

kirankumar said...

Great blog article informative I liked it

We are the best piping design course in Hyderabad, India. Sanjary academy Offers Piping Design Course and Best Piping Design Training Institute in Hyderabad. Piping Design Institute in India Piping Design Engineering.
best Piping Design Course
piping design course with placement
pipe stress analysis course in hyderabad
piping engineering course in hyderabad
piping stress analysis course
best Piping Design institute
best institute of Piping Design Course in India
Piping Design Course
Piping Design Course in India­

sara jenifer said...

You’d outstanding guidelines there. I did a search about the field and identified that very likely the majority will agree with your web page.
DAVV BCOM TimeTable 2020
Delhi University BCOM TimeTable 2020
Matsya University BCOM TimeTable 2020

bestalls said...


Thanks for taking the time to talk about this, I feel strongly about it and really like
mastering more on this subject. If feasible, as you acquire experience,
would you mind updating your blog with much more information and facts?
It is very helpful for me.

Cheapest Electric Pressure Cooker


Entryway Wall Mounted Coat Rack


bestalls

zabulonkabacinski said...

Harrah's Reno Hotel & Casino - Mapyro
Welcome to our newly 군산 출장샵 opened 1,300 room 전주 출장안마 Harrah's Reno Hotel & Casino located in 광양 출장마사지 Reno, Nevada. This hotel 오산 출장마사지 is 5 star rating from 8.6  Rating: 4 · ‎8 reviews 제천 출장안마