Monday, August 12, 2013

12c - first tests

We have the Exalytic machine and created 3 DBs in it: one -11.2.0.3 and two 12.1.0.1 - one in container (CDB) and one non-CDB.
We put all datafiles from all DBs on the same file system.
We aligned all parameter for all DBs - sga_max_size, pga_aggregate_target, processes, redo log file sizes ... and so on.
Codepage was CL8MSWIN1251 in all 3 DBs.
Forcelogging and other v$database params were aligned.

Then we run some tests.

The test for PL/SQL:



SET TIMING ON;    
             
declare a number := 0;               
begin   for i in 1 .. 1e9           
   loop a := a + 1; end loop;                   
end;                           
/
                  
declare a number := 0;    
begin   for i in 1 .. 1e9
   loop a := a + 1/3; end loop;        
end;                
/                   
 

The test for SQL:
- created one table and measured population time (INSERT) and SELECT time with select:



create table test tablespace users as
select ...
from dual connect by rownum <= 2e6;




set timing on
select count(*),sum(client_id)/1e7,sum(length(NAME))/1e7,sum(account)/1e7, min(birthday) from test; -- this run was rejected
select count(*),sum(client_id)/1e7,sum(length(NAME))/1e7,sum(account)/1e7, min(birthday) from test;
select count(*),sum(client_id)/1e7,sum(length(NAME))/1e7,sum(account)/1e7, min(birthday) from test;
select count(*),sum(client_id)/1e7,sum(length(NAME))/1e7,sum(account)/1e7, min(birthday) from test;




Then we updated a number of rows manually and deleted some rows manually and wrote down our results.


The results are:

- PL/SQL +1 run 53-55 sec in 11.2 and 43-44 in 12c.
- PL/SQL +1/3 rim 150-155 sec in 11.2 and 

- SQL 11.2 SELECT time is 1.34-1.38 sec, 12c is 0.56-0.58 sec.
- Update,Delete is 3,32 in 11.2 and 4,08 in 12c.

So, we clearly see the about 15-20% improvement in PL/SQL and about 2 times faster the SELECT and about 30% faster DML.

The DBMS_RANDOM become 3 times slower.
But we hope, it become more random.

Any comments ?

No comments:

Post a Comment

cellcli -e list diskmap

The interesting LIST DISKMAP command in the storage cells links all views to hard disks: PCI bus address as Name 252:5, OS name (/dev/sdh,...