Cursor’s role in sql query execution in oracle

A Private SQL area stores data (like bind variables) and Query Execution status and It belongs to specific session hence stored in PGA/UGA depending on server mode. However, shared SQL area can be used by several session so stored in  Library cache.

In shared SQL area, each SQL statement has one Parent cursor and one or more child cursors. Parent cursor contains sql text to be processed, when two statements are identical word-by-word, they will share the same parent cursor. Child cursor have ‘execution environment’, ‘execution plan’ and also decides whether a query will undergo a hard parse or a soft parse.

Tip: check VERSION_COUNT in v$sqlarea and V$SQL_SHARED_CURSOR to know better on child cursor.

Life Cycle of a cursor:

Open Cursor: Private sql area allocated in PGA/UGA which referred by client side handle.

Parse Cursor: Shared sql area is loaded in SGA by parsed information of SQL and execution plan  i.e Library cache

Define Output variables: If sql returns Data ,variable returning it must be defined.

Bind Input variables- If bind variables used, passes the value.

Execute Cursor: SQL statement is executed.

Fetch Cursor: Used for sql statement’s return data.

Close Cursor: Resource associated with private handle and private sql area is freed so other cursor can use it.  But shared sql area/library cache keep holding values so can use for future.

Now lets talk about parsing, it consist to syntax and semantic checks, hash code generation and query goes to optimizer for hard parsing or skipped in case of soft parsing. Below is the flow chart,

Parsing:

1) Include VPD: If virtual private DB is in use for any table ,predicate generated on basis of security Policies.

2) Check syntax, semantics and access rights: SQL’s syntax checked also checked if object accessed by sql exist or not.

3) Store parent cursor in shared SQL area: if parent cursor not available new memory is allocated and new parent cursor is stored.

4) Generate Execution Plan: Plan is generated at this phase.

5) Store child cursor: Child cursor associated with Parent cursor is stored.

When shareable parent and child cursor available only first 2 steps are executed aka soft parsing.

It’s pretty obvious that we need to avoid hard parsing as generation of execution Plan is CPU-Intensive operation which also causes memory allocation for each new parent-child cursor, causing,

• Shared Pool shared over all sessions.
• Memory allocation shared pool is serialized.
• So latch should be obtained first to allocate memory in Shared Pool.
• So if application doing lot of HARD PARSES it may face SHARED_POOL Latch contention.

I believe, from 11G onwards most latches are replaced by MUTEX.

Example of Hard Parsing:

Create a table

SQL> CREATE TABLE JITEN AS SELECT rownum AS n, rpad(‘*’,100,’*’) AS pad FROM dual CONNECT BY level <= 1000;

SQL> execute dbms_stats.gather_table_stats(ownname=>SYS, tabname=>'JITEN')

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

SQL> ALTER SESSION SET cursor_sharing = 'EXACT';

Even though the following SQL statements perform exactly the same operation, four different parent cursors are created. This is because only two of them have the same text.

SQL> SELECT * FROM JITEN WHERE n = 1234;
SQL> select * from JITEN where n = 1234;
SQL> SELECT     *       FROM  JITEN  WHERE  n=1234;
SQL> SELECT * FROM JITEN WHERE n = 1234;
SQL> SELECT * FROM JITEN WHERE n = 01234;

SQL> SELECT sql_id, sql_text, executions FROM v$sqlarea WHERE sql_text LIKE ‘%1234’;

SQL_ID 		SQL_TEXT        	 	        EXECUTIONS
-------------	--------------------------------	----------
6vdany43w59xn   SELECT * FROM JITEN WHERE n = 01234		1
2254m1487jg50   select * from JITEN where n = 1234		1
g9y3jtp6ru4cb   SELECT * FROM JITEN WHERE n = 1234		2 
7n8p5s2udfdsn   SELECT  *  FROM  JITEN WHERE  n=1234	1

ALTER SESSION SET cursor_sharing = 'FORCE';

SQL> SELECT * FROM JITEN WHERE n = 1001;
SQL> SELECT * FROM JITEN WHERE n = 2001;
SQL> SELECT * FROM JITEN WHERE n = 3001;
SQL> select * from JITEN where n = 3001;

SQL> SELECT sql_id, sql_text, executions FROM v$sqlarea WHERE upper(sql_text) LIKE ‘SELECT * FROM JITEN WHERE N = '%SYS_B_%';

SQL_ID     	SQL_TEXT                        	        EXECUTIONS
-------------	----------------------------------------	-----------
8pr6586w64tfr   SELECT * FROM JITEN WHERE n = :"SYS_B_0"		3
9q3dmdaj3kg9d  	select * from JITEN where n = :"SYS_B_0"		1

As shown above, examples for Hard Parsing we Used CURSOR_SHARING PARAMETER=EXACT and FORCE for Soft parsing.

Simply put, CURSOR_SHARING determines what kind of SQL statements can share the same cursors. This feature is work around to avoid too many HARD PARSES. If application executes SQL statement containing literals and cursor sharing is enabled i.e. FORCE. Database automatically replace literals with BIND variables to give soft parsing a preference. Mostly hard parsing happens, when Application generates sql dynamically or bind Variables are not used.

Tip: Application user says query taking long time, when you run on SQLPLUS it runs fine. Consider checking BIND peeking.

What’s Bind peeking, will share a blog on that soon. Stay tuned.