Do PLSQL Procedures And Anonymous Blocks Have A SQL_ID?

Posted on 14-July-2015 by Craig Shallahamer, craig@orapub.com

I read in a forum that Oracle PLSQL will not have a SQL_ID.

That seemed strange to me because I swear I’e seen PLSQL running on the database server and seen it in a library cache dump tracefile. And to be found in the library cache, the object must have a hash value. Hmmm... But perhaps not a SQL_ID.

If I'm wrong, then a blank SQL_ID could be the result of PLSQL code. Looks like it's time to run an experiment! Actually, I needed two experiments. The first experiment focuses on a PLSQL procedure. The second experiment focus on an anonymous PLSQL block.

The result was a very cool experiment; clean, straightforward and it demonstrated exactly what I wanted to confirm.

Why Is This Important?

If you have read my recent postings HERE and HERE you'll recall that when there is a log file sync wait event, the SQL_ID can be blank, empty or null.

What prompted this unusual quest is when I saw something similar to this:

select SQL_ID, 
       count(*)/(select count(*) from v$active_session_history where state='WAITING')
from   v$active_session_history
where  state = 'WAITING'
  and  event = 'log file sync'
order by 2 desc

                     % Samples
SQL_ID                  Waited
-------------------- ---------
                        100.00

But when I was doing some research on why an Oracle SQL_ID can be empty, a forum posted mentioned (with lots of authority), that a SQL_ID is blank when running PLSQL in an Oracle Database. That seemed really strange to me. The only way to disproved this, was to run an experiment.

Here's my thinking: If I can demonstrate that both an anonymous PLSQL block and a PLSQL procedure have hash values and SQL_ID's, then I'll feel pretty certain that when I see a blank SQL_ID, it's not because PLSQL is being run. At least not an anonymous block or a procedure.

The two experiments are very straightforward. Anyone could easily copy and paste what I typed below. Give it a try!

Results: Oracle PLSQL Procedure

Here are the results for the Oracle PLSQL procedure, followed my an explanation of what happened.

[oracle@sixcore ~]$ sqlplus sys/manager as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 13 10:27:30 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size		    2923392 bytes
Variable Size		  218104960 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5464064 bytes
Database mounted.
Database opened.
SQL> create or replace procedure findme_prc
is
  findme_v number;
begin
  findme_v := dbms_random.value(1,10);
end;
/   2    3    4    5    6    7  

Procedure created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size		    2923392 bytes
Variable Size		  218104960 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5464064 bytes
Database mounted.
Database opened.
SQL> exec findme_prc;

PL/SQL procedure successfully completed.

SQL> select prev_SQL_ID from v$session where sid = SYS_CONTEXT('USERENV','SID');

PREV_SQL_ID
-------------
976tyyq3tzzt6

SQL> @sqlgettext SQL_ID 976tyyq3tzzt6

Database: prod40                                                                    13-Jul 10:29am
Report:   sqlgettext.sql                          OSM by OraPub, Inc.               Page         1
                                       SQL Statement Text (SQL_ID=976tyyq3tzzt6)


HASH_VALUE ADDRESS            Line SQL Statement Text
---------- ---------------- ------ ---------------------------------------------------------------
2275409702 000000006435F898      0 BEGIN findme_prc; END;

SQL> 
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@sixcore ~]$

Looking at the above code, you can see I first recycled the Oracle instance, just to ensure their is nothing cached in the library cache that will influence the results. Then I created the "findme_prc" procedure and again recycled the instance to ensure there is nothing cached in the library cache to influence the results. I executed the procedure, retrieved my session's previous SQL_ID and retrieved the related SQL text using my OSM tool, sqlgettext.sql.

As you can clearly see, my procedure has both an associated SQL_ID and hash_value. I'd say that's a pretty good indication that an Oracle Database PLSQL procedure can have an associated SQL_ID.

The "Wrapper" PLSQL Has The SQL_ID...

While the procedure sys.findme_prc has a hash value (so it can be found in the library cache) it does not have a SQL_ID. When run in SQL*Plus, the procedure is wrapped inside a simple "BEGIN" and "END". This is the SQL_ID we see during the procedure execution. You can see in the spool above, SQL*Plus added the "BEGIN" and the "END;"... I did not type that.

If you dump the library cache, you can find both the procedure and the wrapped PLSQL which calls the procedure. To find the procedure do a search for, "Name=SYS.FINDME_PRC" and to find the wrapper PLSQL do a search for, "BEGIN findme_prc". You can dump the library cache by issuing this command:

SQL> alter session set events 'immediate trace name library_cache level 10';

Session altered.

So while the procedure does not by itself have a SQL_ID, it is truly associated with a SQL_ID. This is important, because my real goal is to understand when a SQL_ID can be blank, empty or null. And in my test case, when the procedure is run the SQL*Plus session does in fact have an associated SQL_ID.

Perhaps there is a way to execute the procedure when the calling routine does not have a SQL_ID? Or perhaps Oracle will always wrap the procedure in something with a SQL_ID? But... that's a topic for another post, eh?

But you know...

Perhaps the forum was not referring to a PLSQL procedure. Read on!

Results: Oracle PLSQL Anonymous Block

Perhaps the forum was not referring to an Oracle Database PLSQL procedure, but simply an anonymous PLSQL block. OK, let's modify the experiment slightly to use an anonymous block instead of a procedure and see what happens.

Below are the results for the Oracle PLSQL anonymous block, followed my an explanation of what happened.

[oracle@sixcore ~]$ sqlplus sys/manager as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 13 10:43:42 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size		    2923392 bytes
Variable Size		  218104960 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5464064 bytes
Database mounted.
Database opened.
SQL> declare
  findme_v number;
begin
  findme_v := dbms_random.value(1,10);
end;
/   2    3    4    5    6  

PL/SQL procedure successfully completed.

SQL> select prev_SQL_ID from v$session where sid = SYS_CONTEXT('USERENV','SID');

PREV_SQL_ID
-------------
drr87vg80ra5a

SQL> @sqlgettext SQL_ID drr87vg80ra5a

Database: prod40                                                                      13-Jul 10:44am
Report:   sqlgettext.sql                          OSM by OraPub, Inc.                 Page         1
                                       SQL Statement Text (SQL_ID=drr87vg80ra5a)


HASH_VALUE ADDRESS            Line SQL Statement Text
---------- ---------------- ------ -----------------------------------------------------------------
3490425002 000000006555CF60      0 declare   findme_v number; begin   findme_v := dbms_random.value
3490425002 000000006555CF60      1 (1,10); end;

SQL> 

Looking at the above code, you can see I first recycled the Oracle instance... just to ensure their is nothing cached in the library cache that will influence the results. I executed the anonymous PLSQL block, retrieved my session's previous SQL_ID and finally retrieved the related SQL text using my OSM tool, sqlgettext.sql.

As you can clearly see, my anonymous PLSQL block has both an associated SQL_ID and hash_value. I'd say that's a pretty good indication that an Oracle Database anonymous PLSQL block can have an associated SQL_ID.

What Did I Confirm/Learn?

This was a pretty cool experiment; clean, straightforward and it demonstrated exactly what I wanted to confirm. That is, do Oracle procedures and anonymous PLSQL blocks have empty SQL_IDs? Here are my take-aways:

It's important to understand that I did NOT prove a procedure or an anonymous block will NEVER have an empty SQL_ID.

Thanks for reading and enjoy your work!

Craig.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

Gotta Love Oracle Database Latch Classes I Have Lots Of Oracle Database Server Power But Performance Is Slow/Bad Application/SQL Focus: Log File Switch Checkpoint Incomplete