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

An interesting article about performance titled "Thinking Clearly About Performance"

I just happened to read an interesting article related to performance titled "Thinking Clearly About Performance". It is worth spending sometime in it. Click here to access the same. Enjoy reading.

Cheers,

Thiru

Web Site Performance Tips and Tools

I have found an interesting basic article about performance in this week SunFlash news letter. Same is available in link here. Enjoy reading.

Thanks,

Thiru

Oracle Column Selectivity – An Performance Impact

Lot of times I hear developer saying what is the big difference it is going to make when we execute “select * from tab1 where c1=1” instead of “select c2 from tab1 where c1=1”. (Even though they need only c2).  I bet, performance impact will  be big.

Benefits of selecting only required columns are listed below (Below list may not exclusive):

1. Network round trip between application server and database server.

2. Application server’s record set size will consume more memory which adds overhead to JVM’s GC engine.

3. Database server IO (Quite obvious IO will be having big impact on OLTP systems).

First 2 points are simple, straight and it is easy to understand. Regarding 3rd point, see my example below which shows IO impact it has because of bad query(selecting all columns).

SQL> create table tab1 as select * from all_objects;

Table created.

SQL> desc tab1

 Name                                      Null?    Type

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

 OWNER                                     NOT NULL VARCHAR2(30)

 OBJECT_NAME                               NOT NULL VARCHAR2(30)

 SUBOBJECT_NAME                                     VARCHAR2(30)

 OBJECT_ID                                 NOT NULL NUMBER

 DATA_OBJECT_ID                                     NUMBER

 OBJECT_TYPE                                        VARCHAR2(19)

 CREATED                                   NOT NULL DATE

 LAST_DDL_TIME                             NOT NULL DATE

 TIMESTAMP                                          VARCHAR2(19)

 STATUS                                             VARCHAR2(7)

 TEMPORARY                                          VARCHAR2(1)

 GENERATED                                          VARCHAR2(1)

 SECONDARY                                          VARCHAR2(1)

 NAMESPACE                                 NOT NULL NUMBER

 EDITION_NAME                                       VARCHAR2(30)

SQL> create index ind1 on tab1(object_id,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats( user, 'TAB1');

PL/SQL procedure successfully completed.

SQL> select * from tab1;

53762 rows selected.

Execution Plan

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

Plan hash value: 2211052296

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 53762 |  4882K|   228   (1)| 00:00:03 |

|   1 |  TABLE ACCESS FULL| TAB1 | 53762 |  4882K|   228   (1)| 00:00:03 |

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

Statistics

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

          1  recursive calls

          0  db block gets

       4352  consistent gets

          0  physical reads

          0  redo size

    6269625  bytes sent via SQL*Net to client

      39840  bytes received via SQL*Net from client

       3586  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      53762  rows processed

SQL> select object_id,object_name from tab1;

53762 rows selected.

Execution Plan

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

Plan hash value: 4126477464

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

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |      | 53762 |  1522K|    87   (0)| 00:00:02 |

|   1 |  INDEX FAST FULL SCAN| IND1 | 53762 |  1522K|    87   (0)| 00:00:02 |

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

Statistics

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

          1  recursive calls

          0  db block gets

       3887  consistent gets

          0  physical reads

          0  redo size

    1845080  bytes sent via SQL*Net to client

      39840  bytes received via SQL*Net from client

       3586  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      53762  rows processed

SQL> select * from tab1 where object_id=2001

  2  ;

Execution Plan

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

Plan hash value: 2086140937

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

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time   |

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

|   0 | SELECT STATEMENT            |      |     1 |    93 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1 |     1 |    93 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND1 |     1 |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=2001)

Statistics

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

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1349  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> select object_name from tab1 where object_id=2001;

Execution Plan

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

Plan hash value: 3743434205

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

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |      |     1 |    29 |     2   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IND1 |     1 |    29 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("OBJECT_ID"=2001)

Statistics

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

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        428  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> 

If we carefully look in our above example, query "select * from tab1" requires 4882K bytes whereas "select object_id,object_name from tab1" requires only 1522K bytes.

Likewise "select * from tab1 where object_id=2001" requires 93bytes whereas "select object_name from tab1 where object_id=2001" requires just 29 bytes.

All of this IO optimization is because of 

• "INDEX FAST FULL SCAN" ==>Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized
  
• "INDEX RANGE SCAN" ==> An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.
  

During any index access, if index has all column required to satisfy an SQL, table will not be accessed at all(avoids extra IO/buffer gets).

Note: Bytes in above examples means "Estimate by the cost-based approach of the number of bytes accessed by the operation".

As a single user, we might not see much performance impact; but at high load this will have huge impact. Whoever developing code, please mark this valuable performance tuning hints.

As usual, your comments are always welcome.

-Thiru

Improving performance with Result Cache in Oracle 11g

Initially in previous versions(before 11g), Oracle Database started off with caching data block in memory, which inturn is used to construct results for user’s request. Even though data blocks are cached in memory, it should construct results from cache which takes from time, resource, locks (latches & enqueues) etc., Oracle has gone to next level of caching in 11g database. Effective from Oracle 11g, Oracle can cache result sets. Oracle can cache the results of both SQL & PL/SQL. In this article, I am going to discuss basics of SQL result cache and I am planning to write about PL/SQL result cache in future articles. Quite obvious, at the end of article, most people will feel that this is the most interesting performance tuning hints while working in Oracle 11g database.

Few basics about result cache

Oracle introduced the memory structure called Result Cache to store the results of SQLs & PL/SQL. The database serves the results for the executed SQL queries and PL/SQL functions from the result cache instead of re-executing the actual query, quite obvious this feature will increase the performance. Memory allocated(can be controlled using RESULT_CACHE_MAX_SIZE parameter) for the result cache is taken from the shared pool. By now one might come to the conclusion that result cache will improve the performance of the repeatly used PL/SQL & SQL. RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. If the value of this parameter is 0, then the result cache feature is disabled.

Here are some of the parameters that control the behaviour of the result cache.

SQL> col name format a30

SQL> col value format a25

SQL> select name,value from v$parameter where name like 'result_cache%';

NAME                           VALUE

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

result_cache_mode              MANUAL

result_cache_max_size          5373952

result_cache_max_result        5

result_cache_remote_expiration 0

Brief description about the above parameters:

RESULT_CACHE_MODE: RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan. If set to FORCE, all the queries are cached if they are qualifed and fit in cache. The default is MANUAL, which indicates that only queries with the hint will be cached. 

RESULT_CACHE_MAX_SIZE: Specified maximum size of the result cache. Remember, result cache is part of shared pool and maximum can be of 75% of shared pool size. Also query result.

RESULT_CACHE_MAX_RESULT: Specifies percentage of RESULT_CACHE_MAX_SIZE that any single query result set can occupy. (Just to prevent the big result set from throwing out all other small resultset).

RESULT_CACHE_REMOTE_EXPIRATION: Specifies the number of minutes that a cached result that accesses a remote object will remain valid. Setting this parameter to 0 implies that results using remote objects should not be cached. Setting this parameter to a nonzero value may produce stale answers (for example, if the remote table used by a result is modified at the remote database). I personally think it should be set to positive value if remote database is read only database.

From the below query result, it is evident that result cache is part of shared pool.

SQL> select * from v$sgastat where pool='shared pool' and name like 'Result%';

POOL         NAME                            BYTES

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

shared pool  Result Cache: Memory Mgr          200

shared pool  Result Cache: Bloom Fltr         2048

shared pool  Result Cache: Cache Mgr           152

Like almost all memory structure in oracle instance, Result cache is also protected by latches. Latches, being serialization devices, are scalability inhibitors. Don’t just to come to conclusion that Latches are evil; afterall we expect database to maintain ACID property.

SQL> select * from v$latchname where name like 'Result Cache%';

    LATCH# NAME                                 HASH

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

       377 Result Cache: Latch            1545889529

       378 Result Cache: SO Latch          986859868

Oracle 11g provides couple of views to see the behavious/statistics of result cache. Click on the below view names to see the details in Oracle documentation.

V$RESULT_CACHE_STATISTICS 

V$RESULT_CACHE_MEMORY

V$RESULT_CACHE_OBJECTS

V$RESULT_CACHE_DEPENDENCY

APIs for the result cache management is available in PL/SQL package DBMS_RESULT_CACHE.

Couple of examples on how to use APIs on DBMS_RESULT_CACHE package.

SQL> set serveroutput on size 99999

SQL> exec  DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE")

R e s u l t   C a c h e   M e m o r y   R e p o r t

[Parameters]

Block Size          = 1K bytes

Maximum Cache Size  = 5248K bytes (5248 blocks)

Maximum Result Size = 262K bytes (262 blocks)

[Memory]

Total Memory = 2400 bytes [0.001% of the Shared Pool]

... Fixed Memory = 2400 bytes [0.001% of the Shared Pool]

....... Cache Mgr  = 152 bytes

....... Memory Mgr = 200 bytes

....... Bloom Fltr = 2K bytes

....... State Objs =  bytes

... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

DBMS_RESULT_CACHE package has lot more functions/procedures and if you are looking for more, login into 11g using SQL* Plus and execute “desc DBMS_RESULT_CACHE” and the documentation for the same can be found here.

Background Process

SQL> select description from V$BGPROCESS where name like 'RC%';

DESCRIPTION

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

Result Cache: Background

Watch my blog to see result set in action.

As usual, comments are always welcome.

-Thiru

Monitoring Oracle database using Perfmon

Performance monitoring is the essential part of performance tuning exercise. Oracle database monitoring is no exception to this rule. As such Oracle database provides various ways to monitor such as statspack, AWR, tracing etc., Some people might be quite interested in seeing numerical values such as hit ratios, DBWR statistics etc., so that they can plot graphs against load and see how the trend is. Oracle running on Windows based Operating System provides features which help to monitor the system via perfmon (click here to know more about perfmon).

By default Oracle database related counters are not exposed to perfmon. But there are couple of ways to make it appear on perfmon. 

1. By using custom installation

2. By using operfcfg utility

3. By using administrative Assist on Windows

4. By adding couple of values in registry

First three ways indirectly adds the values to registry (hides complexity from end user). Oracle recommends user to user operfcfg utility. I am going to explain how to use operfcfg to add Oracle database counters in perfmon.

operfcfg is an Oracle Database tool that you run from the command prompt. Use this syntax:

operfcfg [-U username] [-D TNS_Alias_for_database] [-P password]

username is the username registry parameter value that Oracle Counters for Windows Performance Monitor uses to log in to the database. You must have a DBA privilege on this database.

TNS_Alias_for_database is the net service name that Oracle Counters for Windows Performance Monitor uses to connect to the database. It affects the Hostname registry parameter. The net service name corresponds to the SID of the database that you want to monitor. The -D command can be specified without providing a database name value.

password is the password registry parameter value for username.

If we omit –D & -P, then utility will take the default database on that system and it will interactive asks for password to connect to that user.

Debugging: 

If you are not able to see Oracle database counter after running through operfcfg utility, then

1. If you are using TNS alias then try to ping the database using tnsping utility and check whether you are able to reach the database. Click here to know more about tnsping utility

2. Try to connect to the Oracle database using sqlplus with the username, password provided for operfcfg utility.

3. Make sure the username provided has DBA privilege.

Once you got the counters, then enjoy the performance monitoring & tuning excerise. As usual, you comments are always welcome regarding my performance tuning hints.

Cheers,

Thiru

Safari - Performance

Apple came out with its new browser Safari 4 Beta and as a performance guys, I found something really interesting. Apple claims "Safari’s Nitro Engine executes JavaScript up to 30 times faster than Internet Explorer 7 and more than 3 times faster than Firefox 3. "

Wow! 

As I said earlier here, most of performance testing tools doesn't have ability to report end user response time and one of the reason being different Javascript engine used by different browser vendor.

For more information about Safari and its performance, check here and here

As usual, comments are always welcome.

-Thiru