Oracle as a company is always adding cool stuff to the database, and often these things are just small enough to get missed by many of us. Here are two things that I have happened to miss in some recent releases.
This first one came out in 11 timeframe, so not super new. It was enhanced in 12 and is quite cool. One of the real headaches as a SQL Optimizer is PL/SQL blocks of code. When you run a block (a procedure for example) that has several SQL statements in it, how do you get the SQL_ID for each of those statements? It’s not trivial to figure it out. The new PLScope setting makes it much easier.
PLScope is intended to be used in a development like environment. Using it can slow down compile time for a program unit in particular since it’s recording information about the program unit that isn’t normally tracked.
Turning it on is pretty simple:
ALTER SESSION SET plscope_settings = 'IDENTIFIERS:ALL,STATEMENTS:ALL';
This gets “everything”. The Identifiers setting has several options, look in the documentation for the full list. With ALL you get all the source code identifiers. These will be things like procedure/function names and variable names. The Statements setting is either ALL or NONE. This is the one of interest to me for SQL optimization, this gets information about the statements in the PL/SQL code. Key here is the SQL_ID.
The data is stored in tables that are viewable with the typical Oracle views, DBA/USER/ALL then either STATEMENTS or IDENTIFIERS. As in DBA_STATEMENTS and DBA_IDENTIFIERS.
Some sample output of these here (some code will be at the end):
DBA_STATMENTS:
SQL_ID OBJECT_TYPE TYPE TEXT
------------- ------------ ----------------- ----------------------------------------
5w0tcp062c753 PROCEDURE SELECT SELECT COUNT(STATUS) FROM BIG_TAB WHERE
OWNER = :B1 AND STATUS = 'VALID'
fxu1q76gwaqm0 PROCEDURE SELECT SELECT DISTINCT OWNER FROM BIG_TAB WHERE
OWNER IN ('OP','HR','PUBLIC','BI') ORDE
R BY OWNER
PROCEDURE EXECUTE IMMEDIATE
b2nuzkxjcqntj PROCEDURE SELECT SELECT COUNT(STATUS) FROM BIG_TAB WHERE
OWNER = :B1 AND STATUS = 'INVALID'
g4ucahfccny0q PROCEDURE SELECT SELECT COUNT(DISTINCT OBJECT_TYPE) FROM
BIG_TAB WHERE OWNER = :B1
aft61pqqpr284 PROCEDURE SELECT SELECT COUNT(1) FROM BIG_TAB WHERE OWNER
= :B1 AND SHARING = 'NONE'
c8d7w66f75fcb PROCEDURE SELECT SELECT COUNT(DISTINCT OBJECT_NAME) FROM
BIG_TAB WHERE OWNER = :B1
Notice that the one statement that is an EXECUTE IMMEDIATE type doesn’t have a SQL_ID. That is because it’s a dynamic SQL statement that will be parsed and run at run time. The SQL_ID can’t be computed now since the statement is built on the fly. Keep in mind, this information is recorded when the block is compiled, not when it run.
For Identifiers you can get some cool output like this:
IDENTIFIER_USAGE_CONTEXTS
-------------------------------------------------------------
Load_Table_Demo1.... procedure declaration
Load_Table_Demo1.. procedure definition
Owntab.......... nested table declaration
Big_Tab....... table reference
Owner....... column reference
Bt_Own.......... variable declaration
Owntab........ nested table reference
V_Obj_Nm_Cnt.... variable declaration
Number........ number datatype reference
V_Obj_Typ_Cnt... variable declaration
Number........ number datatype reference
V_Vld_Cnt....... variable declaration
Number........ number datatype reference
V_Invld_Cnt..... variable declaration
Number........ number datatype reference
V_S_Non_Cnt..... variable declaration
Number........ number datatype reference
Sql_Stmt........ variable declaration
Varchar2...... character datatype reference
I............... iterator declaration
Bt_Own........ variable reference
Bt_Own........ variable reference
Sql_Stmt...... variable assignment
This is some very good information that can be quite handy while developing code and I think the statement level stuff in particular could be useful for run time debugging and optimization as well.
The other new thing is also about getting the SQL_ID. This one gets at the age-old question; how do I get the SQL_ID of the statement I just ran? Well, this isn’t that hard to do, but it’s even easier now. In SQL Plus (along with SQLcl and SQL Developer) you can now set FEEDBACK to give you the SQL_ID! This is new as of 18. This is really simple:
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.2.0.0.0
SQL> set feedback on sql_ID
SQL> select to_char(sysdate, 'DD-MON-YYYY hh24:mi') "Now" from dual;
Now
--------------------------
12-OCT-2021 17:39
1 row selected.
SQL_ID: 7w778r58td0nv
SQL>
SQL> select count (*) from ric.emp;
COUNT(*)
----------
14
1 row selected.
SQL_ID: 33qh87w6f8rcd
Boom! The SQL_ID right after each SQL statement. Man, I wish I had had this for the 100’s of years I’ve been working with Oracle.
Normally I’d give all the code for examples in my post. For the PLScope the code is rather long. I’m just providing here the queries I used to get the output you saw above. If someone really-really wants the code for the table and procedure I used in my test case, send me an email and I’ll get it to you as soon as I can.
Code for select to get the statement information:
SELECT
sql_id, object_type, type, text
FROM
DBA_statements
WHERE
object_name = upper('load_table_demo1');
Code for the identifier information (this is from the documentation originally):
WITH ID_CTE AS (
SELECT
Line, Col, INITCAP(NAME) Name, LOWER(TYPE) Type,
LOWER(USAGE) Usage, USAGE_ID, USAGE_CONTEXT_ID
FROM DBA_IDENTIFIERS
WHERE Object_Name = upper('load_table_demo1')
)
SELECT
RPAD(LPAD(' ', 2*(Level-1)) ||
Name, 20, '.')||' '||
RPAD(Type, 20)||
RPAD(Usage, 20)
IDENTIFIER_USAGE_CONTEXTS
FROM ID_CTE
START WITH USAGE_CONTEXT_ID = 0
CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
ORDER SIBLINGS BY Line, Col;
