Wednesday, March 2, 2022

Comments in the cloud or not.

SQL is great and to make it more readable comments are wonderful.  Most code need more comments, some a lot more.  I know you're saying “Hey this code was hard to write, so it should be hard to read!”  Yea, no.  I tend write comments for me, not you.  I might have to maintain this code in the future and I’d like to at least give myself some idea of why I did certain things.

But let’s talk about HOW to do your comments.  Please use the /*  */ syntax more than not.

Why?  Take a look at this fictional code bock:

-- Getting base information for XTLOP processing select col1, --col2, col3 from mycooltable mct, othercooltable oct where  mct.id=oct.id and oct.name in (‘TIM’, ‘KELLYN’,--‘JEFF’,’RICHARD’) -- and mct.abc_id = 42 and mct.working = 101;

So, what is and what isn’t’ a comment?  Yea not exactly clear. Is the whole statement commented out? Was JEFF and RICHARD commented out of the list or just JEFF?  Are both the last two where conditions commented out? 

But Ric, the code wouldn’t look like that!  Oh yes it can.  Depending on how you are getting the code it very well may be just a block of code where line integrity is lost.  I’ve had to deal with this time and again when working on a system that I have limited access to in particular.  Sometime you can make pretty good guesses as to what the code looks like.  

But what if the code had been like this:

/*Getting base information for XTLOP processing*/ select col1, /*col2,*/ col3 from mycooltable mct, othercooltable oct where  mct.id=oct.id and oct.name in (‘TIM’, ‘KELLYN’,/*‘JEFF’,’RICHARD’*/)  /*and mct.abc_id = 42*/ and mct.working = 101;

Now it’s crystal clear what is and what is not commented out.  Yes, it is a bit harder to do the comments with the /* to start and */ to end it, but it does make for much more readable code. 

The example used here is pretty simple and likely not too hard to figure out for the most part. I’ve had to deal with this sort of thing but with hundreds of lines of code.  Not trivial to “parse” something like that.   Comments are good, use them more than not.  And when you do, please use the block comment style with /* and */ rather than the line comment style with the --.  

Now I need to talk with Jeff Smith about changing how SQL Developer comments lines.   Maybe if I send him some muffins, he’ll take interest in my suggestion. 






Tuesday, February 1, 2022

SELECT * in the cloud or not


I was in a recent Tweeter debate about the use of SELECT * within CTEs (Common Table Expressions) in particular.  But really this goes beyond just using them in CTEs. 

 

Sure the * (star or splat as some folks say) is a nice short cut to get all the columns from a table (or set of tables).  But it can be a problem.  

 

For example, take this simple query:

 

select * from emp, dept where emp.deptno = dept.deptno;

 

This works just fine in SQL*Plus (or SQLcl).  There is a duplicate column name in the two tables DEPTNO.  SQL*Plus handles this just fine showing the same column twice.  SQLcl names the second occurrence of the column DEPTNO_1, below output is from SQL*Dev.  Knowing which column is from which table isn’t too hard to figure out (the columns are in order as defined in the tables) but it isn’t necessarily obvious to the casual observer. 

 

 


 

But put that same query in a CURSOR in a PL/SQL block and you got trouble:

 

declare 

   CURSOR EMP_CUR is

   select * from emp, dept where emp.deptno = dept.deptno;

begin

   FOR EMP_REC in EMP_CUR

   LOOP

   DBMS_OUTPUT.PUTLINE('Name: '||EMP_REC.ENAME);

   END LOOP;

end;

/

 

Error report -

ORA-06550: line 5, column 19:

PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names

ORA-06550: line 5, column 4:

PL/SQL: Statement ignored

 

PL/SQL doesn’t like duplicate column names and will not run.  Bummer.  This can be a huge problem when working on a query and you’re embedding it into a testing script that is calling the SQL within a PL/SQL block.  And imagine a query that is 100s of lines with many subqueries.  This can be a nightmare to find the duplicate columns.  

 

Yes, I’ve had this exact problem in the past.  

 

More than once.  

 

Not fun. 

 

Then there is the issue of CTEs.  Why not have CTEs with SELECT * FROM? Even if we don’t have the duplicate column issue, this can make the code harder to debug.  It can be difficult to figure out “where did this column come from?” Think of a set of CTEs that are, in effect, processed in a sequential fashion.  When each one has SELECT * FROM  as its main select list and has a prior CTE (or more than one) in the FROM.  It can be quite a trick to figure out where a column enters into the mix.

 

Also, this certainly can be wasteful, and might even be a problem.  The issue is memory.  Each CTE is often materialized, meaning it becomes a structure that is akin to a temporary table.  This table like structure takes up space in the PGA (Program Global Area, not Professional Golf Association). 

 

It may not seem like much but each column takes up space within the row, and that space is multiplied by the number of rows in the return set.  Sure, when I say do a SELECT * FROM the good old EMP table with only 9 columns and 14 rows, it doesn’t really make a difference if I were to leave out some columns.  

 

But we are rarely optimizing a query with tables that small, either in columns or rows.  Say you have a table with 80+ columns, some are the very popular VARCHAR2(4000), and many have at least some data in them, and to top it off, you don’t use any of them in your query.  With a CTE returning 100s of thousands into the millions of rows, that’s a lot of wasted space.  And that’s just one table. 

 

Also remember that it’s unlikely that many of our queries are just run by one person.  Take that query and have a couple 100 (or 1000) folks running it at more or less then same time.  This makes that extra (unnecessary) memory usage a problem. 

 

And just to add to the fun, there is the problem of someone altering the table to add a column (or columns) that your query wasn’t expecting which could break your code.  Typically, this will be an output issue even if the query still executes without error.  And how about they add a column that now causes a duplicate column name that wasn’t there before! Ouch! 

 

Of course dropping a column from a table that is used in your query is a problem whether naming the columns or using the star. 

 

But Ric! It’s just so easy to use SELECT * FROM!  Yes, I understand it many take a bit of work and time to get that column list tweaked just right. But it sure can make working on the query later easier and can save on memory which can be seriously helpful in the running of the SQL on the system over time.


I know you may be thinking something like "this code I'm writing is a short simple query, it's no big deal to use the  SELECT * FROM."  That might be true, but this is also about the habit, like using your blinkers on your car, best to get in the right habit of using the blinkers every time you make a turn.  It's better for everyone.  


Monday, January 10, 2022

I’m back! (I never really left.)



Pretty much my entire professional career has been Oracle related.  The first time I heard of Oracle as a company I was in college.  The professor of the RDMS class I was taking mentioned that he read about Oracle and thought they had a good product.  (We studied Ingres in class, I think it’s still around.)  

 

My first use of Oracle was version 4 or 5 (not real sure) doing some in house training at a small consulting company.  Then a few years later I went in full on with Oracle 6 at Ford.  I also supported a 5 database at the time.  From then on, it was Oracle stuff.  

 

I started working at Oracle Education (Now Oracle University) as an instructor in 1995.  I did several other gigs in Oracle including Education Manger, Curriculum Developer and Technical Manger in consulting.  I went to Hotsos in 2005 doing mostly teaching but also some consulting.  Hotsos went out of business in December 2018, a dark day indeed.  I went to Zione Solutions as a consultant doing several gigs there.  

 

And now I’m back at Oracle as a Sr. Principal Software Engineer.  (Hum, do I get a cool Railroad Engineer hat for this? I’ll have to check on that.)

 

It’s been a heck of a ride so far, and more to come! 

Wednesday, January 5, 2022

NO_DATA_FOUND in the cloud or not.

 


This is pretty basic for PL/SQL but worth a quick review.   

 

When you do a SELECT … INTO in a PL/SQL block, if it returns no rows the exception NO_DATA_FOUND is raised.    

 

Why is this important?  Consider this block of code:

 

DECLARE

    today DATE;

BEGIN

    SELECT

        sysdate

    INTO today

    FROM

        dual

    WHERE

        1 = 2;

    IF today IS NULL THEN

        RETURN;

    END IF;

    dbms_output.put_line(today);

END;

/

 

This is a simplified example of something I just ran into.  The idea was that the select was getting a value from the table in a variable.  If nothing was returned, exit out of the procedure, otherwise continue processing.  Of course, the predicate wasn’t “1=2” in the code I was reviewing, this is to simulate the data not existing in the table. 

 

Important Note: The column being selected is a NOT NULL column, so the returned value will never be null so long as the condition was TURE.  It would only be “NULL” if the condition was FALSE, as in, no rows returned.

 

What does happen when this is run, is that the unhandled exception NO_DATA_FOUND is returned to the calling environment.  The IF checking for the NULL is not run when there is no data returned, the exception is raised and the execution immediately returns to the calling environment in an error state. 

 

ORA-01403: no data found

ORA-06512: at line 4

01403. 00000 -  "no data found"

*Cause:    No data was found from the objects.

*Action:   There was no data from the objects which may be due to end of fetch.

 

Not likely the intent of the coder. Maybe, but given that IF right after the SELECT … INTO it seems highly unlikely that this was the intention.  If it was the intention to have it exit in error, then the IF shouldn’t be there. 

 

How to fix this? The most direct way is to handle the exception locally like this.

 

DECLARE

    today DATE;

BEGIN

    BEGIN

        SELECT

            sysdate

        INTO today

        FROM

            dual

        WHERE

            1 = 2;

 

    EXCEPTION

        WHEN no_data_found THEN

            RETURN;

    END;

 

    dbms_output.put_line(today);

END;

/

 

The double “BEGIN” in this example looks odd, the code I was working with this was a large procedure (3200+ lines long) so it was just a small part of the code.  

 

The intent here with surrounding the SELECT … INTO with an anonymous block is to handle the exception locally.  Now the block runs with:

 

PL/SQL procedure successfully completed.

 

Which is likely what the original coder was intending to have happen.   There are other things to consider, you could have an exception handler at the bottom of the code to handle all NO_DATA_FOUND exceptions rained, assuming you want to do the same thing if any of the SELECT … INTO statements.  

 

The advantage of the technique I show here, you can handle each one on its own and decided what to do for each one separately.  Which might be nothing.  To do nothing,  just put in NULL; in place of the RETURN; in the example I have above. 


Have a great 2022 everyone!! 

Friday, October 15, 2021

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;

 

Monday, September 20, 2021

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!

Thursday, September 2, 2021

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;