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;