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);

/* create a homogeneous session pool */
OCISessionPoolCreate(envhp, errhp,
spoolhp, /* session pool handle */
. . .,
OCI_SPC_HOMOGENEOUS|
OCI_SPC_STMTCACHE); /* modes */

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.