Ask The Real Tom

Trailing Nulls

First we want to see how much space we can save if we place columns that frequently contain nulls at the end of the row.
Remember, if the column length is more than 250 bytes, the row requires 3 bytes per column to store the information about the length. If the column length is smaller, then it requires 1 byte.
For trailing nulls no column length data ist stored in the row. The shorter the row, the more you benefit from trailing nulls.

We create a table with the following columns.

col1 varchar2(1 byte)
col2 varchar2(1 byte)
col3 varchar2(1 byte)
col4 varchar2(1 byte)
col5 varchar2(1 byte)

In col4 and col5 we only have nulls, no data.
I have chosen 1 byte column length to keep the rowlength very short.

First we create a table (table1) with the columns in the following order:
col1, col4, col2, col5, col3
In this case we have no trailing nulls

In the second case we create the table (table2) with the following column order:
col1, col2, col3, col4, col5

Now we have trailing nulls in col4 and col5

OK lets create the tables:

create table stewie.table1 (
col1 varchar2(1 byte),
col4 varchar2(1 byte),
col2 varchar2(1 byte),
col5 varchar2(1 byte),
col3 varchar2(1 byte));

create table stewie.table2 (
col1 varchar2(1 byte),
col2 varchar2(1 byte),
col3 varchar2(1 byte),
col4 varchar2(1 byte),
col5 varchar2(1 byte));

I populated both of the tables with 1 million rows and in both tables the columns col4 and col5 have no values.

OK let’s calculate how many MBs table2 would be smaller:
There are 1 million rows and we save 2×3 bytes each row, makes 5MB in total.

SQL> select sum(bytes)/1024/1024 MB from dba_segments where owner='STEWIE' and segment_name='TABLE1';
MB
----------
15

SQL> select sum(bytes)/1024/1024 MB from dba_segments where owner='STEWIE' and segment_name='TABLE2';
MB
----------
13

Oh, only 2MB saved. This is because there is an info in the row, that says that the last 2 columns in the previous row containing trailing nulls.

Ok in this case we saved 13% of the space, but this amount would decrease the larger the rowlength is.

Column position

I will write about that tomorrow.

In the second part of this blog, I will show how a analysed the gathered data from previews blog.
Since Microsoft Excel is crap, because it just can handle about 65K rows and a chard can have just 32K data points, I used oracle for analysis.
I created an external table and created a query with some analtics functions.
The data is sampled in mostly a 1 second interval.
Just a word to the oracle analytics function “lag”. It just provide the value of the row before. Because in this case the order of the rows matters, there has to be a sort criteria.

External table DDL

-- drop table netstat;
CREATE TABLE netstat (cdate date, recv number, sent number)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY ext
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(cdate DATE "YYYYMMDDHH24MiSS",
recv,
sent)
)
LOCATION ('oracle_traffic_20080613_sum.txt')
)
PARALLEL
REJECT LIMIT 0;


base query

alter session set nls_date_format='DD.MM.YYYY HH24:Mi:ss';
select cdate,
trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) interval_sec,
(recv-(lag(recv) over (order by cdate)))/(trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) )/1024 drev_kb,
(sent-(lag(sent) over (order by cdate) ))/(trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) )/1024 dsent_kb
from netstat
order by cdate
/


Time used with throughput below 800k.

select sum(interval_sec) from
(select cdate,
trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) interval_sec,
(recv-(lag(recv) over (order by cdate)))/(trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) )/1024 drev_kb,
(sent-(lag(sent) over (order by cdate) ))/(trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) )/1024 dsent_kb
from netstat
order by cdate
)
where drev_kb > 800 or dsent_kb > 800
/
SUM(INTERVAL_SEC)
-----------------
7091

-> ca 2h


Time used with more than 4000K througput.
select sum(interval_sec) from
(select cdate,
trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) interval_sec,
(recv-(lag(recv) over (order by cdate)))/(trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) )/1024 drev_kb,
(sent-(lag(sent) over (order by cdate) ))/(trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) )/1024 dsent_kb
from netstat
order by cdate
)
where drev_kb > 4000 or dsent_kb > 4000
/
SUM(INTERVAL_SEC)
-----------------
802
-> 13 min


Max value

select max(drev_kb+dsent_kb) from
( select cdate,
trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) interval_sec,
(recv-(lag(recv) over (order by cdate)))/(trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) )/1024 drev_kb,
(sent-(lag(sent) over (order by cdate) ))/(trunc(((cdate-(lag(cdate) over (order by cdate))))*24*60*60) )/1024 dsent_kb
from netstat
order by cdate
)
/
MAX(DREV_KB+DSENT_KB)
---------------------
12321.7627
-> 12 Mb.


With this analysis we can just say that splitting the application and database to different server will be no risk on 1G network. An that is has a small impact on 100MB networks. It is dependent as well from network utilization as well.

The application and the db are running on the same server. Now we want to move the application to a different server. Therefor we identified the network as possible bottleneck.

The only way to get the traffic seem to be a sampling of the values from v$sysstat.

select value from v$sysstat where name like 'bytes received via SQL*Net from client';
select value from v$sysstat where name like 'bytes sent via SQL*Net to client';

To meassure the traffic between application and database I made a small script.

#!/bin/ksh
#
# Author: Thomas Rieder
# meassure oracle net traffic
#
while true; do
DATE=$(date +%Y%m%d%H%M)
sqlplus -S / as sysdba <<_EOF
set serveroutput on size 1000000
alter session set nls_date_format='YYYYMMDDHH24MiSS';
spool oracle_traffic_$DATE.log
declare
v_timestamp date;
v_recieved number;
v_send number;
begin
for c in 1..900 loop -- 15 min
select
sysdate,
(select value from v\$sysstat where name like 'bytes received via SQL*Net from client') recieved,
(select value from v\$sysstat where name like 'bytes sent via SQL*Net to client') sent
INTO v_timestamp,v_recieved,v_send
from dual;
begin
dbms_output.put_line(v_timestamp||';'||v_recieved||';'||v_send);
end;
DBMS_LOCK.sleep(1);
end loop;
end;
/
spool off
_EOF
done;


It should log every 15 minutes a the time;recieved bytes; send bytes over sqlnet.