Wednesday, March 14, 2012

Collection Types in table columns may cause performance problems

As this is not one of the relational database rules supported by Boyce-Codd, in Oracle Database table columns can be defined as NESTED TABLES or COLLECTION TYPES. This functionality seems to be handy in some cases as you can fetch all the values listed in one column but there may be some consequences especially in performance.

To demonstrate the collection and nested table types, i will create three different users table. First table will use a COLLECTION/VARRAY TYPE as the column type, the second one will use the NESTED TABLE and the third table will use built-in VARCHAR type to hold the demo users phone numbers. After all, i will insert some random data to query and to examine the execution plans and statistics information.


--create the test array type
create type TYP_PHONE_VARRAY as varray(5) of varchar2(15); 

--create the test table type
create type TYP_PHONE_TABLE as table of varchar2(15); 


--create table which uses the varray type
drop table T_USER_VARRAY;
create table T_USER_VARRAY
(
username varchar2(25),
fullname varchar2(25),
phone TYP_PHONE_VARRAY default null
);

--create table which uses the nested table
drop table T_USER_TABLE;
create table T_USER_TABLE
(
username varchar2(25),
fullname varchar2(25),
phone TYP_PHONE_TABLE default null
)
NESTED TABLE phone STORE AS nt_t_user_table_phone;

--create table which uses the built-in varchar as column type
drop table U_EPEKER.T_USER_STR;
create table U_EPEKER.T_USER_STR
(
username varchar2(25),
fullname varchar2(25),
phone1 varchar2(15) default null,
phone2 varchar2(15) default null,
phone3 varchar2(15) default null,
phone4 varchar2(15) default null,
phone5 varchar2(15) default null
);



After creating the types and the tables, some random data would be very useful to query and examine the execution plans of the queries. And of course i should not forget to gather statistics, also on the nested table.



--fill the varray typed table with the test data.
declare

  i number;
  v_name varchar2(10);
  v_surname varchar2(10);
  v_username varchar2(25);
  v_phone varchar2(15);
  
begin

  execute immediate ('truncate table T_USER_VARRAY');
  i:=0;
 
  while i<10 loop
    v_name := DBMS_RANDOM.STRING('u', 10);
    v_surname := DBMS_RANDOM.STRING('u', 10);
    v_username := substr(v_name,1,1) || '_' || v_surname;
    v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) || 
                        round(DBMS_RANDOM.VALUE(1000000,9999999)); 
    
    insert into t_user_varray
    values
    (
    v_username,
    v_name || ' ' || v_surname,
    TYP_PHONE_VARRAY(v_phone)
    );
  i:=i+1;
  end loop;
  
  commit;
  
end; 


--fill the nested table with the test data.
declare

  i number;
  v_name varchar2(10);
  v_surname varchar2(10);
  v_username varchar2(25);
  v_phone varchar2(15);
  
begin

  execute immediate ('truncate table T_USER_TABLE');
  i:=0;
 
  while i<10 loop
    v_name := DBMS_RANDOM.STRING('u', 10);
    v_surname := DBMS_RANDOM.STRING('u', 10);
    v_username := substr(v_name,1,1) || '_' || v_surname;
    v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) || 
                        round(DBMS_RANDOM.VALUE(1000000,9999999)); 
    
    insert into t_user_table
    values
    (
    v_username,
    v_name || ' ' || v_surname,
    TYP_PHONE_TABLE(v_phone)
    );
  i:=i+1;
  end loop;
  
  commit;
  
end; 


--fill the conventional table with the test data.
declare

  i number;
  v_name varchar2(10);
  v_surname varchar2(10);
  v_username varchar2(25);
  v_phone varchar2(15);
  
begin

  execute immediate ('truncate table T_USER_STR');
  i:=0;
 
  while i<10 loop
    v_name := DBMS_RANDOM.STRING('u', 10);
    v_surname := DBMS_RANDOM.STRING('u', 10);
    v_username := substr(v_name,1,1) || '_' || v_surname;
    v_phone := '+31' || round(DBMS_RANDOM.VALUE(60,70)) || 
                        round(DBMS_RANDOM.VALUE(1000000,9999999)); 
    
    insert into t_user_str
    (
    username,
    fullname,
    phone1
    )
    values
    (
    v_username,
    v_name || ' ' || v_surname,
    v_phone
    );
  i:=i+1;
  end loop;
  
  commit;
  
end; 

--gather the statistics of the filled tables
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_STR', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_VARRAY', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'T_USER_TABLE', estimate_percent=>100, cascade=>true);
exec sys.dbms_stats.gather_table_stats(ownname=>'U_EPEKER', tabname=>'NT_T_USER_TABLE_PHONE', estimate_percent=>100, cascade=>true);


After the preparation of the tables and filling them with some test data we can examine the costs of the same identical queries on these tables.

Selecting all five of the columns and only the one column has both 8 bytes of "consistent gets" but the returned amount of data differs as expected. As the developer cannot guess how many phone number exists for an individual user, probably in the code all columns of the phones be selected to be sure of it.

But when selecting from the table which has the VARRAY type as the "phone" column there is an unexpected amount of bytes and "consistent gets" in the first look. As it is also explained in the Oracle Documentation columns and variables which are defined as VARRAY types are objects which should be instantiated once in the memory. This is the most probable reason of this excessive consistent gets which is almost 5 times more than the conventinal VARCHAR column. Even there is only one phone number stored in the VARRAY, read operation results as if there are five values in the list because the object instantiated as it is defined before.

On the other hand, when i examine the query on the table which uses the NESTED TABLE type for the phone column there is a considerable difference in the "consistent gets" and "bytes read" when comparing with the VARRAY type. Most probably this result indicates that NESTED TABLE types are not instantiated as objects in the memory. They are real tables in the database which are nested in another table and returns the results which they store physically in the database. If you investigate you will find the index and the table segments in the tablespace.


EXPLAIN PLAN FOR
SELECT 
  phone1, phone2, phone3, phone4, phone5 
FROM t_user_str 
where username='M_VKFHWFQOKL';  --1 row 48 bytes

/*
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        810  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/

EXPLAIN PLAN FOR
SELECT 
  phone1
FROM t_user_str 
where username='M_VKFHWFQOKL';  --1 row 26 bytes

/*
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        534  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/

EXPLAIN PLAN FOR
SELECT 
  phone 
FROM t_user_varray 
where username='V_RLQDMEPGBJ';  --1 row 35 bytes

/*
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         44  consistent gets
          0  physical reads
          0  redo size
       4619  bytes sent via SQL*Net to client
       1865  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/


EXPLAIN PLAN FOR
SELECT 
  phone 
FROM t_user_table 
where username='N_RNJASPGQOS';  --1 row 30 bytes

/*
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1675  bytes sent via SQL*Net to client
        800  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/



As a conclusion; if you are not sure that the VARRAY typed column will not get filled properly then, instead of using VARRAY type using NESTED TABLES may be more convenient for the performance of the application. These small decreases of IO and consistent gets may be very valuable in a busy application for an enterprise environment.


Resources:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/objects.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjdes.htm#i448939
http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#i454908


No comments:

Post a Comment