V$SQL_SHARED_CURSOR TOP_LEVEL_RPI_CURSOR

I was working with an individual on a question in the MOSC forums recently where they asked about the TOP_LEVEL_RPI_CURSOR column of the V$SQL_SHARED_CURSOR view. There is little documentation on what this column is trying to tell the DBA.

All the Oracle docs say is that this column contains “(Y|N) Is top level RPI cursor”. So what does that mean?

I’m going to assume the reader of this post is familiar with child cursors. That will save me a large amount of introductory information. The V$SQL_SHARED_CURSOR view will tell the DBA why a child cursor and its parent have different versions in the Shared Pool. If the child cursor’s OPTIMIZER_MISMATCH column contains a ‘Y’ in this view, then the session executing the cursor had different optimizer settings than the session that was responsible for the parent cursor execution.

So what does it mean when TOP_LEVEL_RPI_CURSOR is set to Y for a child? The documentation isn’t clear. MOS has very little on the subject. And all of my Google hits on this column pretty much just regurgitate the documentation. To know why, it helps to know that RPI stands for Recursive Program Interface. This is part of the Oracle kernel that deals with recursive SQL. In our case, it deals with the fact that the SQL statement was issued at a different “depth”.

What is recursive SQL? It is SQL that is issued on your behalf, which means at a different depth as I will illustrate. First off, Oracle is performing recursive SQL all the time. At a basic level, when you issue “select * from table_name”, Oracle queries the Data Dictionary to ensure the object exists and that you have permissions on that table. How does Oracle do that? It uses other SQL statements. The statement you issue is at level 0, the base level. When Oracle issues a SQL statement to check if the table exists, that will be at the next level, level 1. Sometimes, that will cause other SQL statements to be issued at the next level, level 2.

The depth of a SQL statement is not limited to just what Oracle is doing in the background, on your behalf. Consider when you execute a stored procedure. Your call to the stored procedure is at depth 0. Any SQL statement in the stored procedure is at depth 1. If that stored procedure calls another procedure, the SQL in the other procedure will be at depth 2.

I used this information on recursive SQL and SQL depth to construct a simple example in my Oracle 12.1.0.2 database. First, I created a stored procedure.

create or replace procedure my_sysdate 
as 
 v_dt date;
begin
 select sysdate into v_dt from dual;
end;
/

I then fired up a SQL*Plus session and started a trace. I issued the same SQL statement and then I called my procedure.

SQL> alter session set sql_trace=true;

Session altered.

SQL> SELECT SYSDATE FROM DUAL
 2 /

SYSDATE
---------
05-APR-16

Many of these cheap levitra products include topical corticosteroids, hydroquinone, tretinoin and mercury salts that can have a profound impact on many aspects of your life. Too many webmasters seem content to let their creations loaf about the web, draining their creator’s time and money. cialis tadalafil 50mg Thus let us all embrace the highly advanced tadalafil free sample as a treatment for erectile dysfunction, many opportunists have tried to make money by selling counterfeits and toxic medicines. Doctor told Rita that because of the accident Condition when the patient is admitted to the hospital a buy levitra in uk few hours after taking the Penegra.

SQL> exec my_sysdate;

PL/SQL procedure successfully completed.

SQL> exit

When I examined the raw trace file, I found the two calls to SYSDATE from DUAL as follows:

PARSING IN CURSOR #140670990815296 len=24 dep=0 uid=9449 oct=3 lid=9449 tim=24905125014484 hv=124468195 ad=’81477be0′ sqlid=’c749bc43qqfz3′ SELECT SYSDATE FROM DUAL

PARSING IN CURSOR #140670907623848 len=24 dep=1 uid=9449 oct=3 lid=9449 tim=24905129780963 hv=124468195 ad=’81477be0′ sqlid=’c749bc43qqfz3′ SELECT SYSDATE FROM DUAL

If you look at the trace file closely, you will see that the second one at depth=1 was a direct result of the stored procedure. Notice that even though my stored procedure was defined in all lower case, the SQL issued at depth=1 was in all upper case. As a result, when I issued the same SQL statement directly in my SQL*Plus session (at depth=0), I had to use the same upper case form of that statement so that it would have the same SQL ID value.

The trace file also shows the SQL ID. I can now query V$SQL_SHARED_CURSOR for that SQL ID value and show that TOP_LEVEL_RPI_CURSOR is set for the child.

SQL> select sql_id,top_level_rpi_cursor from v$sql_shared_cursor where sql_id='c749bc43qqfz3';

SQL_ID T
------------- -
c749bc43qqfz3 N
c749bc43qqfz3 Y

So there we have our proof. The only difference between these two cursors is that one was the depth from which they were executed. I’m not sure why Oracle needs this distinction in the Shared Pool. If anyone knows, drop me a line.

Normally, we do not care about a few extra versions, a few child cursors for a given SQL ID. If your SQL statement has a high number of versions, then it is probably not due to the different depth levels. Other reasons would be more relevant to why a SQL statement would have a high number of child cursors, a high number of different versions. But this does answer the question of what that column is telling us.