Home > Oracle, Performance > Soft parse and session_cached_cursors

Soft parse and session_cached_cursors


session_cached_cursors is one of the neglected parameter in oracle environment which specifies the number cursor cached in the PGA of a session.  Any SQL executed multiple times will be cached and executed with a softer soft parsing.  There can be 3 scenarios for any parse request.

SQL parsing first time:  No shared cursor available in SGA.  Optimizer request for a hard parse and execute the SQL

Shared cursor is available but not cached in Private SQL Area No hard parse is required.  Under a ‘Library cache pin’ latch  and ‘cursor: mutex’, the shared plan will be copied to the private SQL area and execute the cursor  - call it a soft parse.

Cursor is available both in SGA and Private SQL area: Just bind and execute the SQL, no latch is required.

What makes the cursor stay in the Private SQL area?  It is restricted with the parameter session_cached_cursors which defaults to 50 from Oracle 11g onwards.  If the number of repeated cursor goes above 50, the cached cursors will be flushed out from the private SQL area to make room for the new incoming cursor.  Even though this is a soft parse, but the it can cause problem on high concurrency and lead into performance issues – just because you have set the session_cached_cursors to a low value.

My test table is just one row table from DBA_OBJECTS.  Since I am running only 5 SQLs repeated, set the  session_cached_cursors value to 3.


SQL> create table b as select * from dba_objects  where rownum < 2;

Table created.

SQL> show parameter session_cached_cursors

NAME                                 TYPE                             VALUE

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

session_cached_cursors               integer                          3

SQL> show parameter  cursor_sharing

NAME                                 TYPE                             VALUE

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

cursor_sharing                       string                           FORCE

My test script SELECT this one row table into a local variable.


declare

l_cnt number;

l_owner varchar2(50);

l_object_name varchar2(50);

l_status varchar2(5);

l_DATA_OBJECT_ID number;

begin

for i in 1 ..100000

loop

select object_id into l_cnt from b;

select owner into l_owner from b ;

select object_name into l_object_name from b ;

select status into l_status  from b ;

select DATA_OBJECT_ID into l_DATA_OBJECT_ID from b;

end loop;

end;

/

Exit

Running the above code in a single user session did not create much issues, still the total parse time was more than the execute time with no major waits.


SELECT OBJECT_ID

FROM

B

call     count       cpu    elapsed       disk      query    current        rows

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

Parse    99999      1.11       1.03          0          0          0           0

Execute  99999      0.86       0.83          0          0          0           0

Fetch    99999      1.47       1.44          0     299997          0       99999

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

total   299997      3.46       3.31          0     299997          0       99999

Rows     Execution Plan

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

0  SELECT STATEMENT   MODE: ALL_ROWS

1   TABLE ACCESS (FULL) OF 'B' (TABLE)

********************************************************************************

SQL ID: 65fn676fmwz4n Plan Hash: 1911541843

&nbsp;

SELECT OBJECT_NAME

FROM

B

call     count       cpu    elapsed       disk      query    current        rows

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

Parse   100000      1.08       1.06          0          0          0           0

Execute 100000      0.80       0.82          0          0          0           0

Fetch   100000      1.46       1.46          0     300000          0      100000

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

total   300000      3.35       3.35          0     300000          0      100000

Because I set the session_cached_cursors set to 3, and the number SQL statements run in my session was 5, force to flush out the cached cursor each time to make room for the new statement.  Because of this reason session cache hit was never happened, ended up a soft parse.  It took 1.06 seconds to parse and 0.82 seconds to execute.  There were no wait events.

I tried the same test concurrently with another 10 sessions running the same statement.  The results very high elapsed time on parsing because of the soft parsing under 10 users concurrency.


SELECT OBJECT_ID

FROM

B

call     count       cpu    elapsed       disk      query    current        rows

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

Parse    99999      1.45       6.01          0          0          0           0

Execute  99999      0.88       1.73          0          0          0           0

Fetch    99999      1.49       3.50          0     299997          0       99999

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

total   299997      3.83      11.26          0     299997          0       99999

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited

----------------------------------------   Waited  ----------  ------------

cursor: pin S                                  99        0.02          0.72

cursor: mutex S                                38        0.02          0.38

latch: cache buffers chains                    33        0.03          0.24

cursor: mutex X                              3228        0.02          0.96

library cache: mutex X                         13        0.01          0.12

********************************************************************************

The first SQL took 6.01 seconds for parsing and 1.73 seconds for execution.  It is 6.67 times more than previous run in isolation.  This is just with 10 users, and when the number of users increasing, the elapse time will also increase.  Just for comparison I will take another of SQL.


SELECT OWNER

FROM

B

call     count       cpu    elapsed       disk      query    current        rows

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

Parse   100000      1.47       5.68          0          0          0           0

Execute 100000      0.86       1.90          0          0          0           0

Fetch   100000      1.63       3.73          0     300000          0      100000

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

total   300000      3.97      11.32          0     300000          0      100000

Rows     Execution Plan

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

0  SELECT STATEMENT   MODE: ALL_ROWS

1   TABLE ACCESS (FULL) OF 'B' (TABLE)

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited

----------------------------------------   Waited  ----------  ------------

cursor: mutex S                                35        0.02          0.28

cursor: mutex X                              4348        0.02          0.95

latch: cache buffers chains                    59        0.01          0.37

cursor: pin S                                  76        0.01          0.62

library cache: mutex X                         13        0.02          0.16

********************************************************************************

The increase in elapsed is just because of, the session is not able to cache cursor in their private memory for re-use.  They need to do a soft parse each time.  So, when ever concurrency is increasing, a soft parse is not really soft!  It will kill the application scalability.

I have repeated the test after setting the session_cached_cursors value to 10 with the same way.  The first run in isolation results were

SQL> show parameter session_cached_cursors

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
session_cached_cursors integer 10

 


SELECT OBJECT_ID

FROM

B

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          1          1          0           0

Execute 100000      0.84       0.84          0          0          0           0

Fetch   100000      1.31       1.33          0     300000          0      100000

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

total   200001      2.16       2.18          1     300001          0      100000

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited

----------------------------------------   Waited  ----------  ------------

db file sequential read                         1        0.00          0.00

********************************************************************************

SELECT OWNER

FROM

B

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          1          0           0

Execute 100000      0.83       0.82          0          0          0           0

Fetch   100000      1.35       1.34          0     300000          0      100000

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

total   200001      2.19       2.16          0     300001          0      100000

It parsed just once and executed 100000 times!  Elapsed time for parsing les than 0.01 second!!  As we are running 5 SQL with session cached cursor value 10, help us to re-use the cached cursors and avoided expensive ‘soft’ parse.

Repeated the same test under 10 user concurrency.


SELECT OBJECT_ID

FROM

B

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute 100000      0.88       2.05          0          0          0           0

Fetch   100000      1.36       3.88          0     300000          0      100000

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

total   200001      2.24       5.94          0     300000          0      100000

Wow, even the 10 user concurrency is not affecting the overall execution as the number parsing is just one.  All the concurrent lat waits were disappeared


Rows     Execution Plan

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

0  SELECT STATEMENT   MODE: ALL_ROWS

1   TABLE ACCESS (FULL) OF 'B' (TABLE)

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited

----------------------------------------   Waited  ----------  ------------

latch: cache buffers chains                    70        0.02          0.45

********************************************************************************

10 X 100000 concurrent execution resulted minor CBC latch waits which outside scope of this discussion.   One more sample SQL for comparison.


SELECT OWNER

FROM

B

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute 100000      0.80       2.35          0          0          0           0

Fetch   100000      1.37       4.18          0     300000          0      100000

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

total   200001      2.17       6.53          0     300000          0      100000

Rows     Execution Plan

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

0  SELECT STATEMENT   MODE: ALL_ROWS

1   TABLE ACCESS (FULL) OF 'B' (TABLE)

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited

----------------------------------------   Waited  ----------  ------------

latch: cache buffers chains                    64        0.02          0.44

session_cached_cursors plays an important role avoiding soft parses which maintain the scalability of the application.  Just setting this parameter to a right value sometimes  resolve  your concurrency related performance issues.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: