session cached cursors and the significance of PL/SQL (kksParseCursor)

Years pass by and I think that the more I’m trying to understand the Oracle RDBMS – the less I know. Recently I started to examine the behavior of session cursor cache and I noticed an interesting thing.

But let’s start from the beginning like we should 🙂
The documentation says:

About the Session Cursor Cache
A session cursor represents an instantiation of a shared child cursor, which is stored in the shared pool, for a specific session. Each session cursor stores a reference to a child cursor that it has instantiated.

Oracle Database checks the library cache to determine whether more than three parse requests have been issued on a given statement. If a cursor has been closed three times, then Oracle Database assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache.

Subsequent requests to parse a SQL statement by the same session search an array for pointers to the shared cursor. If the pointer is found, then the database dereferences the pointer to determine whether the shared cursor exists. To reuse a cursor from the cache, the cache manager checks whether the cached states of the cursor match the current session and system environment.

http://docs.oracle.com/database/121/TGDBA/tune_shared_pool.htm#TGDBA565

OK. So in my understanding, the case is simple – if I execute a statement more than 3 times from my session, it will be held in my session cursor cache. So there will be no parsing whatsoever and no library cache scans.

So riddle me this:

SQL> select executions, parse_calls
  2  from v$sql
  3  where sql_text='select count(1) from employees e1';

no rows selected

SQL> /

EXECUTIONS PARSE_CALLS
---------- -----------
       195	   195

According to the theory above, I should have more executions then parse calls. Well, maybe the next part of the documentation will explain it:

Reuse of a cached cursor still registers as a parse, even though it is not a hard parse.

But if I’ll execute a query from inside a PL/SQL procedure (using EXECUTE IMMEDIATE, cursor or any other type of SQL processing from inside a PL/SQL) I can see something like this:

SQL> ;
  1  select executions, parse_calls
  2  from v$sql
  3* where sql_text='select count(department_id) from employees'
SQL> /

EXECUTIONS PARSE_CALLS
---------- -----------
       200	     1

OK, so now it looks like something we have expected from the beginning!

I started to dig a little deeper to find a C function(s), responsible for parsing.
I’ve decided to focus on:

• kksParseCursor
• kxsSqlHash
• kxsPrepareCursorForCache
• kgscCacheCursor

As a test, I executed this query from SQL*Developer

select first_name, last_name, salary
from employees
where employee_id=100;

And I’ve checked the associated process server with gdb and those breakpoints:

[root@rico ~]# cat parse_cursor_cache
break kksParseCursor
  commands
    printf "r8 = %s\n", (char*)$r8
    printf "r12 = %d\n", $r12
    c
  end
break kxsSqlHash
  commands
    c
  end
break kxsPrepareCursorForCache
  commands
    c
  end
break kgscCacheCursor
  commands
    c
  end
set pagination off

In kksParseCursor the register r8 contains address of the memory, where the text of parsed SQL is being held and r12 contains some kind of id for cursor type – all cursor executed from SQL*Plus or SQL*Developer have id 164 while cursors executed from inside PL/SQL have id 66146.

This was the result of the first execution of new query from a session without hard parsing:

Breakpoint 1, 0x000000000cb704d0 in kksParseCursor ()
r8 = select first_name, last_name, salary
from employees
where employee_id=100
r12 = 164

Breakpoint 2, 0x000000000cbdd2f0 in kxsSqlHash ()

The second execution and each next:

Breakpoint 3, 0x000000000cbdc8f0 in kxsPrepareCursorForCache ()

Breakpoint 4, 0x000000000ce02eb0 in kgscCacheCursor ()

Breakpoint 1, 0x000000000cb704d0 in kksParseCursor ()
r8 = select first_name, last_name, salary
from employees
where employee_id=100
r12 = 164

Breakpoint 2, 0x000000000cbdd2f0 in kxsSqlHash ()

So guessing by the names of the functions used, we can assume that something is actually going on in the area of session cached cursors – but whole time the kksParseCursor is being executed indicating, that the parsing occurs. And when we check the V$SQL we can see it also:

SQL> ;
  1  select executions, parse_calls, sql_text
  2  from v$sql
  3  where parsing_schema_name='HR'
  4  and module='SQL Developer'
  5* and lower(sql_text) like '%employees%'
SQL> /

EXECUTIONS PARSE_CALLS SQL_TEXT
---------- ----------- ----------------------------------------------------------------------------------------------------
	25	    25 select first_name, last_name, salary from employees where employee_id=100

The number of executions is equal to number of parse calls.

Now let’s see what will happen with SQLs executed from inside a PL/SQL procedure:

SQL> get test_exec
  1  create or replace procedure test_exec is
  2    vx number;
  3  begin
  4    select count(1) into vx from employees;
  5    select count(salary) into vx from employees;
  6    select count(department_id) into vx from employees;
  7    execute immediate 'select count(department_id) from employees' into vx;
  8* end;
SQL> /

Procedure created.

This was the result of the first execution of this procedure from a session without hard parsing for statements:

(gdb) c
Continuing.

Breakpoint 1, 0x000000000cb704d0 in kksParseCursor ()
r8 = begin
  test_exec;
end;
r12 = 164

Breakpoint 1, 0x000000000cb704d0 in kksParseCursor ()
Cannot access memory at address 0x0
(gdb) c
Continuing.

Breakpoint 1, 0x000000000cb704d0 in kksParseCursor ()
r8 =
r12 = 66146

Breakpoint 1, 0x000000000cb704d0 in kksParseCursor ()
r8 =
r12 = 66146

Breakpoint 1, 0x000000000cb704d0 in kksParseCursor ()
r8 = select count(department_id) from employees
r12 = 546

We have 5 hits in kksParseCursor function – one for PL/SQL block used to call procedure, three for regular selects in our procedure (r12=66146) and one for EXECUTE IMMEDIATE statement (r12 = 546).

And what it looks like for the second and each other execution?

Breakpoint 3, 0x000000000cbdc8f0 in kxsPrepareCursorForCache ()

Breakpoint 4, 0x000000000ce02eb0 in kgscCacheCursor ()

Breakpoint 1, 0x000000000cb704d0 in kksParseCursor ()
r8 = begin
  test_exec;
end;
r12 = 164

The kksParseCursor is executed only to parse the anonymous block and the contents of V$SQL looks like this:

SQL> ed
Wrote file afiedt.buf

  1  select executions, parse_calls, sql_text
  2  from v$sql s, dba_objects o
  3  where parsing_schema_name='HR'
  4  and   s.PROGRAM_ID=o.object_id
  5* and   o.object_name='TEST_EXEC'
SQL> /

EXECUTIONS PARSE_CALLS SQL_TEXT
---------- ----------- ----------------------------------------------------------------------------------------------
       243	     9 SELECT COUNT(SALARY) FROM EMPLOYEES
	10	     2 SELECT COUNT(1) FROM EMPLOYEES
       233	     7 SELECT COUNT(1) FROM EMPLOYEES
       244	     9 SELECT COUNT(DEPARTMENT_ID) FROM EMPLOYEES
       243	     9 select count(department_id) from employees

So we can see multiple executions without unnecessary parsing.
And that’s for me the true meaning of session cached cursors!

It appears to me that SQLs executed from PL/SQL are treated differently at parsing and (maybe) execution level then statements executed from JDBC or other sources.

Just notice the behavior of kksParseCursor – no SQL text is visible at r8 registry, when SQL is being executed from PL/SQL.

After some tests I found out that value at r12 registry is as follows (at least for now):

• 164 – PL/SQL block or regular SQL statement like SELECT, UPDATE,…
• 66146 – for statements executed from PL/SQL
• 66080 – for cursors in PL/SQL
• 546 – for EXECUTE IMMEDIATE statements (it is also called the "native dynamic sql" and as you can see above – kksParseCursor shows the text of the parsed SQL)

But the real question is: "What is the real significance of PL/SQL?" If this simple test showed us, that there is a difference between SQL executed from client like JDBC and PL/SQL procedure/function/package… then how far it goes? What other internal differences can we find?

So many questions, so little whiskey…

Tested on Oracle Database Enterprise Edition 12.1.0.2.