Getting the SQL_ID in the cloud or not

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;

 

What’s in a NAME in the cloud or not.

It seems rather simple at first, you have a name you call yourself as you go thru life.  I have a slightly complex issue that my legal first name isn’t Ric.  It’s John.  Same as my father’s and the name goes back from there too.  

 

For most of my life this hasn’t been a big deal, but there were times things were a bit confusing.  Once at the doctor’s office, it was discovered that our records were completely mixed up.  And our credit reports were a bit of mess at one time.  These weren’t trivial things but were worked out rather easily.  

 

It’s now getting worse not better.  The mix up with my Dad isn’t so much the issue.  It’s that I go by Ric and not John.  A friend recently wrote me a check as Ric and it was kicked back by the ATM because it didn’t match my legal name.  My bank records have an AKA for me as Ric, but the automated system doesn’t recognize that.   There were some headaches with a recent refinance revolving around my name. 

 

When I go to take certification exams, I have to remember to put John not Ric on the form.  Since my government IDs don’t have me was Ric.  As such all my certifications are for John not Ric.  Which at least is odd, since everyone knows me as Ric not John. 

 

Also, my last name is a problem for many systems.  It should be Van Dyke.  But many systems wouldn’t take a space in my last name so it becomes Vandyke.  My drivers license has no space in my last name but my passport does have a space.  This has causes lengthy discussions sometimes.  

 

So why do I bring this up in my blog which is nearly exclusively about SQL performance?   

 

Because all of this is our fault.  We have written code over the years that makes assumptions about names.  Like a person can’t have two first names, a legal one and a preferred  one.  And that a name can’t have a space in it.  (I’m assume in Europe this isn’t a problem, since sur names with spaces is rather common.) 

 

Yes, there are a few systems that do have preferred names and some that can take a space, but the norm is not to have these.  

 

The song “I feel like a Number” by Bob Seger comes to mind for me on this.  I feel like I MUST conform to the system and not the other way around.  So much for individuality in our computerized world. 

 

Oh, and I did not carry on the tradition of the first name, my son’s first name is not John.  

 

“Damn it I'm a man! I feel like a number!”

Statistic Numbers in the cloud or not.

Thanks to my buddy Jared Still for pointing this out to me.  The Statistic Number for the consistent gets stat has been several different values over the last few releases (see below).   Which means that many statistic number have also been different over the releases as well. 

 

He put together a nifty little function that makes the script that I’ve been doing testing for timings more portable.  I used this script most recently in my last post (Inserting into multiple tables in the cloud or not). 

 

Here is the cool little function he created:

 

create or replace function get_cg_statnum return pls_integer deterministic

   is

      i_statnum pls_integer;

   begin

      select stname.statistic# into i_statnum

      from v$statname  stname

      where stname.name = 'consistent gets';

      return i_statnum;

   end;

/

 

You can use it like this:

 

select get_cg_statnum from dual;

 

To get the statistics number for consistent gets on your database.  Below I have a modified version of the script I used in the last post to use this function.  

 

Jared was able to pull the statistic number for consistent gets from a few versions:

 

VERSION           STATISTIC#

----------------- ----------

21.0.0.0.0               209

19.0.0.0.0               163

12.1.0.2.0               132

11.2.0.4.0                88

 

 

And one last thing.  Don’t make the rookie mistake I did.  To be able to select from v$statname in a function/procedure you have to be granted the select privilege directly, not thru a role.  If you don’t have it directly, the function will not be able to select from the table.  Duh!

 

Here is the new script:

 

-- setup

drop table emp_10 purge;

drop table emp_20 purge;

drop table emp_30 purge;

drop table emp_xx purge;

create table emp_10 as select * from emp where 1=0;

create table emp_20 as select * from emp where 1=0;

create table emp_30 as select * from emp where 1=0;

create table emp_xx as select * from emp where 1=0;

-- end setup 

 

set serveroutput on;

 

create or replace function get_cg_statnum return pls_integer deterministic

   is

      i_statnum pls_integer;

   begin

      select stname.statistic# into i_statnum

      from v$statname  stname

      where stname.name = 'consistent gets';

      return i_statnum;

   end;

/

 

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

    congets_num  pls_integer :=0; 

begin

    select get_cg_statnum into congets_num from dual; 

    select value into l_start_cr from v$mystat where STATISTIC# = congets_num;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

        INSERT

            WHEN (deptno=10) THEN

            INTO emp_10 (empno,ename,job,mgr,sal,deptno)

            VALUES (empno,ename,job,mgr,sal,deptno) 

            WHEN (deptno=20) THEN

            INTO emp_20 (empno,ename,job,mgr,sal,deptno)

            VALUES (empno,ename,job,mgr,sal,deptno) 

            WHEN (deptno=30) THEN

            INTO emp_30 (empno,ename,job,mgr,sal,deptno)

            VALUES (empno,ename,job,mgr,sal,deptno)

            ELSE

            INTO emp_xx (empno,ename,job,mgr,sal,deptno) 

            VALUES (empno,ename,job,mgr,sal,deptno)

            SELECT * FROM emp;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('Inserting into 4 tables with one command 100,000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('In hundredths of a second');

    DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.GET_TIME - l_start_time));

    DBMS_OUTPUT.put_line ('**** CPU  - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

    select value into l_end_cr from v$mystat where STATISTIC# = congets_num;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

-- delete data from tables 

delete from emp_10;

delete from emp_20;

delete from emp_30;

delete from emp_xx;

 

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

    congets_num  pls_integer :=0; 

begin

    select get_cg_statnum into congets_num from dual; 

    select value into l_start_cr from v$mystat where STATISTIC# = congets_num;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

        INSERT

            INTO emp_10 (empno,ename,job,mgr,sal,deptno)

            SELECT empno,ename,job,mgr,sal,deptno FROM emp where deptno =10;

        INSERT

            INTO emp_20 (empno,ename,job,mgr,sal,deptno)

            SELECT empno,ename,job,mgr,sal,deptno FROM emp where deptno =20;

        INSERT

            INTO emp_30 (empno,ename,job,mgr,sal,deptno)

            SELECT empno,ename,job,mgr,sal,deptno FROM emp where deptno =30;

        INSERT

            INTO emp_xx (empno,ename,job,mgr,sal,deptno) 

            SELECT empno,ename,job,mgr,sal,deptno FROM emp where deptno is null;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('Inserting into 4 tables with four commands 100,000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('In hundredths of a second');

    DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.GET_TIME - l_start_time));

    DBMS_OUTPUT.put_line ('**** CPU  - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

    select value into l_end_cr from v$mystat where STATISTIC# = congets_num;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

 

-- delete data from tables 

delete from emp_10;

delete from emp_20;

delete from emp_30;

delete from emp_xx;

 

Inserting into multiple tables in the cloud or not.

 

There is more to the INSERT command than most folks are aware.  

 

My buddy Dan Morgan (The man behind Morgan’s Library) remined me recently of this.  For example, you can use one statement to insert into multiple tables at once. 

 

 

The basic syntax looks like this:

 

 

INSERT

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>) 

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

ELSE

INTO <table_name> (<column_list>)

VALUES (<values_list>)

SELECT <column_list> FROM <table_name>;

 

 

You can have many WHEN conditions.  This syntax only would insert into the first table where condition is true.   An advantage of the one statement is the ELSE part.  This is nice to put stuff in a table that just doesn’t match the other conditions.  This could be tricky with multiple statements.  For my test below, it was simple, the DEPNO is one of four values, 10,20, 30 or NULL for the table.  Hence it was easy to come up with four statements to get all the rows into the four separate tables. I think you can image cases where this wouldn’t be as straight forward.

 

 

If you add ALL after the word INSERT, it would insert into any of the tables where the condition is true.  With the ALL version you could have no WHEN conditions at all and it would insert into all the tables in the statement.  This could be useful when you are moving a row from a wide table (many columns) and you are breaking that up into several narrow tables.  For example, you have a table that is not normalized and you want to normalize it into a set of tables.

 

 

This is super cool of course, but as a performance guy, how does it perform?  I set up a test to see at least in one case how it did. This test takes the good old EMP table and populates 4 other tables based on DEPTNO, I do this 100,000 times to have a reasonable amount of activity. (The code at the bottom if you’d like to try it yourself.)  The results are below from both a 21c super cool autonomous database and from a 12c database on one of my old windows laptop. 

 

 

As I do, each test was run multiple times to weed out any noise to get a good idea of what is happening.  In this test I ran the script with the SETUP part once, then commented the SETUP lines and reran it multiple times, this way the tables are already there and have been inserted into to weed out any noise with the high-water mark and such.  The results below are representative of these runs after the first one. 

 

 

For the 21c Database:

 

 

......................................................

Inserting into 4 tables with one command 100,000 times

......................................................

In hundredths of a second

**** TIME - 3133

**** CPU  - 3105

**** LIO  - 619022

 

 

......................................................

Inserting into 4 tables with four commands 100,000 times

......................................................

In hundredths of a second

**** TIME - 6874

**** CPU  - 6769

**** LIO  - 1230158

 

 

For the 12c Database:

 

 

......................................................

Inserting into 4 tables with one command 100,000 times

......................................................

In hundredths of a second

**** TIME - 1622

**** CPU  - 1111

**** LIO  - 707520

 

 

......................................................

Inserting into 4 tables with four commands 100,000 times

......................................................

In hundredths of a second

**** TIME - 2784

**** CPU  - 2627

**** LIO  - 1308283

 

 

Over all it took a bit more than twice as long to do the task with 4 separate statement rather than one, elapsed time and CPU time.  Not unexpected, I would think that one statement should perform better then several.  But it wasn’t 4 times as long as one might have thought, only about twice as long.   LIOs were just a bit less than twice as many when doing 4 statements rather than one. 

 

 

What does this mean?  It’s likely better to use one statement rather than multiple if you can.  And it might be slightly easier to maintain this code over time.  It does require a slightly different way of thinking about your inserts of course. 

 

 

Code for the test below.  Make sure you use the correct statistic number for the consistent gets statistic.  Its 139 for 12c and earlier, 209 for 21c. 

 

 

-- setup

drop table emp_10 purge;

drop table emp_20 purge;

drop table emp_30 purge;

drop table emp_xx purge;

create table emp_10 as select * from emp where 1=0;

create table emp_20 as select * from emp where 1=0;

create table emp_30 as select * from emp where 1=0;

create table emp_xx as select * from emp where 1=0;

-- end setup 

 

set serveroutput on;

 

 

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    --the consistent gets statistic is #139 in 12c and #209 in 21c 

    --select value into l_start_cr from v$mystat where STATISTIC# = 139;

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

        INSERT

            WHEN (deptno=10) THEN

            INTO emp_10 (empno,ename,job,mgr,sal,deptno)

            VALUES (empno,ename,job,mgr,sal,deptno) 

            WHEN (deptno=20) THEN

            INTO emp_20 (empno,ename,job,mgr,sal,deptno)

            VALUES (empno,ename,job,mgr,sal,deptno) 

            WHEN (deptno=30) THEN

            INTO emp_30 (empno,ename,job,mgr,sal,deptno)

            VALUES (empno,ename,job,mgr,sal,deptno)

            ELSE

            INTO emp_xx (empno,ename,job,mgr,sal,deptno) 

            VALUES (empno,ename,job,mgr,sal,deptno)

            SELECT * FROM emp;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('Inserting into 4 tables with one command 100,000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('In hundredths of a second');

    DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.GET_TIME - l_start_time));

    DBMS_OUTPUT.put_line ('**** CPU  - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

    --select value into l_end_cr from v$mystat where STATISTIC# = 139;

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

-- delete data from tables 

delete from emp_10;

delete from emp_20;

delete from emp_30;

delete from emp_xx;

 

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    --the consistent gets statistic is #139 in 12c and #209 in 21c 

    --select value into l_start_cr from v$mystat where STATISTIC# = 139;

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

        INSERT

            INTO emp_10 (empno,ename,job,mgr,sal,deptno)

            SELECT empno,ename,job,mgr,sal,deptno FROM emp where deptno =10;

        INSERT

            INTO emp_20 (empno,ename,job,mgr,sal,deptno)

            SELECT empno,ename,job,mgr,sal,deptno FROM emp where deptno =20;

        INSERT

            INTO emp_30 (empno,ename,job,mgr,sal,deptno)

            SELECT empno,ename,job,mgr,sal,deptno FROM emp where deptno =30;

        INSERT

            INTO emp_xx (empno,ename,job,mgr,sal,deptno) 

            SELECT empno,ename,job,mgr,sal,deptno FROM emp where deptno is null;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('Inserting into 4 tables with four commands 100,000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('In hundredths of a second');

    DBMS_OUTPUT.put_line ('**** TIME - '||to_char(DBMS_UTILITY.GET_TIME - l_start_time));

    DBMS_OUTPUT.put_line ('**** CPU  - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));

    --select value into l_end_cr from v$mystat where STATISTIC# = 139;

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

 

-- delete data from tables 

delete from emp_10;

delete from emp_20;

delete from emp_30;

delete from emp_xx;