Do PLSQL Procedures And Anonymous Blocks Have A SQL_ID?
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:
- Don't believe everything you read... especially if there is no test case.
- An Oracle Database PLSQL procedure can have an associated SQL_ID.
- An Oracle Database anonymous PLSQL block can have an associated SQL_ID.
- During a log file sync wait event, if the associated SQL_ID is blank, empty or null do not expect the associated SQL to be a procedure or anonymous PLSQL block.
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.
Start my FREE 18 lesson Machine Learning For Oracle Professionals E-Course here.
Craig Shallahamer is a long time Oracle DBA who specializes in predictive analytics, machine learning and Oracle performance tuning. Craig is a performance researcher and blogger, consultant, author of two books, an enthusiastic conference speaker a passionate teacher and an Oracle ACE Director. More about Craig Shallahamer...
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
