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;
