Soft Parses wait in Oracle

Soft Parses wait in Oracle

Soft Parse
Session executes a statement that exists in shared pool is refered to Soft Parse.

Note: General high “parse call” (> 10/sec.) indicates that your system has many incoming unique SQL statements,
and your SQL is not using bind variables.

Note: Compare Parses and EXECUTES columns

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.0                0.0        0.00        0.00
       DB CPU(s):                0.0                0.0        0.00        0.00
       Redo size:           11,589.0            2,407.8
   Logical reads:            1,320.2              274.3
   Block changes:               56.6               11.8
  Physical reads:              457.9               95.1
 Physical writes:                3.0                0.6
      User calls:              106.8               22.2
          Parses:             2326.2              345.4
     Hard parses:               13.5                2.8
W/A MB processed:                0.3                0.1
          Logons:                0.2                0.1
      Executes:             2426.3               455.5
       Rollbacks:                0.1                0.0
    Transactions:                4.8

ADDM Report:
Finding Soft Parse:
Impact is 1.1 active sessions, 25.59% of total activity.
-------------------------------------------------------
Soft parsing of SQL statements was consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 1.1 active sessions, 25.59% of total activity.
------------------------------------------------------------------
Action: I
Investigate application logic to keep open the frequently used cursors.
Note: that cursors are closed by both cursor close calls and session disconnects.


Cause:
Excessive Soft Parsing Lack of Statement Caching.

Solution
1. At application level you can use statement caching.
•Keeps frequently used session cursors open
•Reduces soft parses on the Server
•Cuts code path in driver/application tier

2. If not from application then you have option to fixed it at Database level with parameter session_cached_cursors.

-- Change the value of session cached cursors its effect on next startup
ALTER SYSTEM SET session_cached_cursors = 300 scope=spfile;

Note: Oracle Database Default value is 50.

Example of Statement Caching

JAVA

Statement Caching in Java
// Obtain a connection connection = dataSource.getConnection();
// Enable statement caching ((OracleConnection)connection).setStatementCacheSize(20);
((OracleConnection)connection).setImplicitCachingEnabled(true);


OCI

Initialize the OCI Session Pool with statement cache
ub4 stmt_cachesize = 20;
/* set the statement cache size for all sessions in the pool */
OCIAttrSet(spoolhp, OCI_HTYPE_SPOOL, &stmt_cachesize, 0,
OCI_ATTR_SPOOL_STMTCACHESIZE, errhp);

•Use new flavors of prepare/release calls
•OCIStmtPrepare2(), OCIStmtRelease()