Simulating network latency on Linux

If you want to run test cases that are sensitive to network latency, it’s important to also test them under the effect of different latencies. For example you want to know what it means if you move an application currently running on the same server as the database to a different server. Or you are doing some small tests locally on your computer and want to know how they would behave in a real environment where client and server are on different machines, maybe even with long geographical distances between them.

I usually use the method described here when I want to run some tests on my laptop where I have an Oracle Database running in a Virtualbox VM.

Linux provides a kernel module called NetEm (Network Emulator). With NetEm you can add a delay to all packages leaving (outgoing) a certain network interface. You can do a lot more things with NetEm like duplicating, corrupting or reordering packages and more. Check the man page or the links at the end of this post to find out more about it.

NetEm is an enhancement of the Linux traffic control facilities and can therefore be controlled with tc.

Using the tc command line tool to add a delay

Add a delay of 10ms to eth1

tc qdisc add dev eth1 root netem delay 10ms

Change an already configured delay

tc qdisc change dev eth1 root netem delay 5ms

Show configuration of eth1

tc qdisc show dev eth1

Remove a configured delay

tc qdisc del dev eth1 root netem delay 4ms

Example

I’m just going to ping my virtual machine and add a delay after a couple of pings.

Ping my virtual machine from my local machine

ping oracle-database-183

Add delay of 10ms on eth1 on my virtual machine. This means the packages leaving eth1 (in this examples the answers to the ping) will be delayed by 10ms.

tc qdisc add dev eth1 root netem delay 10ms

Output of ping

PING oracle-database-183 (192.168.56.202): 56 data bytes
64 bytes from 192.168.56.202: icmp_seq=0 ttl=64 time=0.255 ms
64 bytes from 192.168.56.202: icmp_seq=1 ttl=64 time=0.376 ms
64 bytes from 192.168.56.202: icmp_seq=2 ttl=64 time=0.329 ms
64 bytes from 192.168.56.202: icmp_seq=3 ttl=64 time=0.336 ms
64 bytes from 192.168.56.202: icmp_seq=4 ttl=64 time=0.310 ms
64 bytes from 192.168.56.202: icmp_seq=5 ttl=64 time=10.870 ms
64 bytes from 192.168.56.202: icmp_seq=6 ttl=64 time=10.992 ms
64 bytes from 192.168.56.202: icmp_seq=7 ttl=64 time=10.910 ms
64 bytes from 192.168.56.202: icmp_seq=8 ttl=64 time=10.900 ms
64 bytes from 192.168.56.202: icmp_seq=9 ttl=64 time=10.790 ms

The output of ping clearly shows the effect of adding the delay with tc.

References

Comment

Hide SQL statement text from DBMS_XPLAN output

The dbms_xplan.display* functions have a lot of options to format the execution plan output via the format parameter. What often annoys me is the output of the SQL statement text. If, for example, you want to display all execution plans of a cursor with many child cursors and large SQL statement texts, the SQL text is returned for each child cursor and clutters your screen. Unfortunately there is no way to turn off this output with a format parameter.

Luckily we can just filter the rows returned by dbms_xplan.display* functions with a custom query. This blog post show you how. If you are just interested in how the final query looks like, click here.

Build the query

First, let’s have a look at a sample output of dbms_xplan.display_cursor for a cursor with 2 child cursors.

Query

select * from table(
  dbms_xplan.display_cursor('&sqlId', null, 'TYPICAL')
);

Output

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  934ur8r7tqbjx, child number 0
-------------------------------------
SELECT DBID FROM V$DATABASE

Plan hash value: 735420252

----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
|   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))

SQL_ID  934ur8r7tqbjx, child number 1
-------------------------------------
SELECT DBID FROM V$DATABASE

Plan hash value: 735420252

----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
|   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))

The highlighted lines in the output above should always occur in the output, no matter what format options are defined. They are also very easy to identify with a simple regex, so the idea is to filter out everything in between them, with some exceptions (like the Plan hash value line or the header of the execution plan table).

A couple of ways to do that come to my mind:

  • Create a PL/SQL function which accepts the output of the DBMS_XPLAN.DISPLAY_CURSOR function and process it procedurally row by row. I don’t want to create any objects in the database, but in 12c you could define the function in the WITH clause. As I want a solution also working on older Oracle version I don’t follow this approach.
  • The pattern matching functionality in 12c is very powerful and could to this job, but again, I don’t want to depend on 12c.
  • There must be a way to do this with analytic functions. They are available since Oracle 8i, so I’m able to use this solution on all environments I need.

First step: Use last_value analytic function

The idea is to add an additional column to the plan output which has the value 1 if the row should be display or 0 if it shouldn’t be displayed.

I use the CASE expression as a first step to mark a row to either be shown or not. In combination with the LAST_VALUE analytic function we can mark all subsequent rows the same way.

Query

select plan_table_output,show from (
select
  plan_table_output,
  last_value(
    case
      when plan_table_output like '| Id%'
        then 1
      when regexp_like(plan_table_output,
                       'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
        then 0 
      else null
    end) ignore nulls over (order by rownum)
  as show
from table(dbms_xplan.display_cursor('&sqlId',null,'TYPICAL')))
/

Check the output below first, to see what’s returned.

The use of NULL and the IGNORE NULLS clause in the LAST_VALUE function is important. This way, for every NULL returned by the CASE expression, the LAST_VALUE function ignores them and uses the last non-null value, which is either 1 or 0.

Note that the regex also searches for HASH_VALUE and EXPLAINED SQL STATEMENT. This is because, depending on the detail level chosen in the format parameter of the DBMS_XPLAN function, the output looks a little bit different.

Output

SHOW PLAN_TABLE_OUTPUT
---- ----------------------------------------------------------------------
   0 SQL_ID  934ur8r7tqbjx, child number 0
   0 -------------------------------------
   0 SELECT DBID FROM V$DATABASE
   0
   0 Plan hash value: 735420252
   0
   0 ----------------------------------------------------------------------
   1 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
   1 ----------------------------------------------------------------------
   1 |   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
   1 |   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
   1 |*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
   1 |   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
   1 |   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
   1 ----------------------------------------------------------------------
   1
   1 Predicate Information (identified by operation id):
   1 ---------------------------------------------------
   1
   1    2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
   1
   0 SQL_ID  934ur8r7tqbjx, child number 1
   0 -------------------------------------
   0 SELECT DBID FROM V$DATABASE
   0
   0 Plan hash value: 735420252
   0
   0 ----------------------------------------------------------------------
   1 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
   1 ----------------------------------------------------------------------
   1 |   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
   1 |   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
   1 |*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
   1 |   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
   1 |   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
   1 ----------------------------------------------------------------------
   1
   1 Predicate Information (identified by operation id):
   1 ---------------------------------------------------
   1
   1    2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
   1

The column SHOW now correctly contains 0 for the SQL statement section, but we also have some false negatives:

  • The row showing use the SQL_ID
  • The row after the SQL_ID which is kind of a separator
  • The row showing the plan hash value
  • The first row which is part of the execution plan table header

Second step: Refine query

To fix these false negatives, again we use the CASE expression in combination with some regex.

The LAG and LEAD analytic functions are used to decide what value the column has, depending on the next or previous row.

Query

select show,plan_table_output from (
select
  plan_table_output,
  case
    when regexp_like(plan_table_output, 
                    'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
      then 1
    when regexp_like(lag(plan_table_output) over (order by rownum),
                    'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
      then 1
    when (lead(plan_table_output) over (order by rownum)) like '| Id%'
      then 1
    when plan_table_output like 'Plan hash value%'
      then 1
    else last_value(
      case
        when plan_table_output like '| Id%'
          then 1
        when regexp_like(plan_table_output,
                        'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
          then 0 
	else null
    end) ignore nulls over (order by rownum)
  end as show
from table(dbms_xplan.display_cursor('&sqlId', null,'TYPICAL')))
/

Output

SHOW PLAN_TABLE_OUTPUT
---- ----------------------------------------------------------------------
   1 SQL_ID  934ur8r7tqbjx, child number 0
   1 -------------------------------------
   0 SELECT DBID FROM V$DATABASE
   0
   1 Plan hash value: 735420252
   0
   1 ----------------------------------------------------------------------
   1 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
   1 ----------------------------------------------------------------------
   1 |   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
   1 |   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
   1 |*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
   1 |   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
   1 |   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
   1 ----------------------------------------------------------------------
   1
   1 Predicate Information (identified by operation id):
   1 ---------------------------------------------------
   1
   1    2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
   1
   1 SQL_ID  934ur8r7tqbjx, child number 1
   1 -------------------------------------
   0 SELECT DBID FROM V$DATABASE
   0
   1 Plan hash value: 735420252
   0
   1 ----------------------------------------------------------------------
   1 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
   1 ----------------------------------------------------------------------
   1 |   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
   1 |   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
   1 |*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
   1 |   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
   1 |   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
   1 ----------------------------------------------------------------------
   1
   1 Predicate Information (identified by operation id):
   1 ---------------------------------------------------
   1
   1    2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
   1

Final query

This is exactly what we want. All we have to do now, is to remove the projection of the SHOW column and filter the rows with SHOW=0.

Query

select plan_table_output from (
select
  plan_table_output,
  case
    when regexp_like(plan_table_output, 
                    'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
      then 1
    when regexp_like(lag(plan_table_output) over (order by rownum),
                    'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
      then 1
    when (lead(plan_table_output) over (order by rownum)) like '| Id%'
      then 1
    when plan_table_output like 'Plan hash value%'
      then 1
    else last_value(
      case
        when plan_table_output like '| Id%' 
          then 1
        when regexp_like(plan_table_output, 
                        'SQL_ID.*|HASH_VALUE.*|EXPLAINED SQL STATEMENT.*')
          then 0 
	else null
    end) ignore nulls over (order by rownum)
  end as show
from table(dbms_xplan.display_cursor('&sqlId', null,'TYPICAL')))
where show=1
/

Output

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  934ur8r7tqbjx, child number 0
-------------------------------------
Plan hash value: 735420252
----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
|   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))

SQL_ID  934ur8r7tqbjx, child number 1
-------------------------------------
Plan hash value: 735420252
----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     1 (100)|
|   1 |  MERGE JOIN CARTESIAN|          |   100 |  2600 |     0   (0)|
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |    26 |     0   (0)|
|   3 |   BUFFER SORT        |          |   100 |       |     0   (0)|
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 |       |     0   (0)|
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))

The query was tested with various format options and should work equally well under 12c and 11g and probably also on older versions.

I use a variaton of it in a script where I can control whether the SQL statement text is shown or not with when I invoke it.

Comment

Find functions with pragma RESTRICT_REFERENCES

If you want to do parallel DML and use packaged functions in the statement, the functions need to fulfill certain requirements. Otherwise you will get the following note in the plan and PDML is not used:

- PDML disabled because function is not pure and not declared parallel enabled

This means your function must be either pure or declared with the parallel-enable clause.

The parallel enabled requirement is easy to check, you can find it out by querying DBA_PROCEDURES.PARALLEL

Pure functions

The second one is a bit harder, because it is not directly exposed via a database view.

First we need to know what pure means in this case. A function is considered as pure if it is defined with the pragma RESTRICT_REFERENCES and all four constraints RNDS, WNDS, RNPS and WNPS.

This information is encoded in SYS.PROCEDUREINFO$.PROPERTIES. The following query can be used to display which constraints of the pragma are used in a function/procedure:

select o.OBJECT_NAME,
       pi.procedurename,
       decode(bitand(pi.properties, 8388608), 8388608, 'NO', 'YES')
         as pragma_restrict_references,
       decode(bitand(pi.properties, 524288), 524288, 'NO', 'YES') as rnds,
       decode(bitand(pi.properties, 1048576), 1048576, 'NO', 'YES') as wnds,
       decode(bitand(pi.properties, 131072), 131072, 'NO', 'YES') as rnps,
       decode(bitand(pi.properties, 262144), 262144, 'NO', 'YES') as wnps
  from sys.procedureinfo$ pi
  join dba_objects o
    on (o.OBJECT_ID = pi.obj#);

So to finally find out if your function can be used in PDML statements, the following query can be used:

select o.OBJECT_NAME, pi.procedurename
  from sys.procedureinfo$ pi
  join dba_objects o on (o.OBJECT_ID = pi.obj#)
  join dba_procedures p on (o.OWNER = p.OWNER
    and o.OBJECT_NAME = p.OBJECT_NAME
    and pi.procedurename = p.PROCEDURE_NAME)
 where (p.PARALLEL = 'YES' 
   or (bitand(pi.properties, 8388608) <> 8388608 and
       bitand(pi.properties, 524288) <> 524288 and
       bitand(pi.properties, 1048576) <> 1048576 and
       bitand(pi.properties, 131072) <> 131072 and
       bitand(pi.properties, 262144) <> 262144))
   and p.OBJECT_NAME = '&packageName'
   and p.PROCEDURE_NAME = '&procedureName';

Conclusion

The above query provides an easy and fast way to determine if a function can be used in PDML statements. This is especially useful if you have a function for which you don’t own the source.

Example:

We had a DML statement which was using UTL_I18N.STRING_TO_RAW. This function is neither parallel enabled nor does it have the required pragma RESTRICT_REFERENCES. Therefore no PDML was performed.

We simply changed the statement to use UTL_RAW.CAST_TO_RAW instead, which has the pragma RESTRICT_REFERENCE required for PDML.

Of course these two functions do not provide the exact same functionality, but in our case (destination charset was the same as database charset) we were able to swap them without risk.

Comment

Temporal Validity and "wrong" results

Temporal validity is a new feature in Oracle 12c. It makes querying effective date ranges much simpler. For more details refer to the Oracle documentation.

This blog post explains in which situation one gets “wrong” or at least unexpected results when using temporal validity.

To demonstrate the situation let’s create a table with a time dimension and some records:

create table t1 (startdate date, enddate date); 
alter table t1 add period for t1period (startdate, enddate); 

insert into t1 values(null, date'2016-01-10'); 
insert into t1 values(date'2016-01-08', null); 
insert into t1 values(date'2016-01-08', date'2016-01-10'); 

commit;

So if you want to get the records from table T1 which are effective as of the day after tomorrow you can query it with the following select:

select * from t1 as of period for t1period date'2016-01-11';

The returned result is as expected

STARTDATE           ENDDATE
------------------- -------------------
2016-01-08 00:00:00

So what happens if you use a nonexistent time dimension:

select * from t1 as of period for t2period sysdate+2;
              *
ERROR at line 1:
ORA-55603: invalid flashback archive or valid time period command

You get an error, which is also the expected behaviour. But what happens when you delete the only existing time dimension on the table and query the table again.

alter table t1 drop (period for t1period);

select * from t1 as of period for t2period sysdate+2;

STARTDATE           ENDDATE
------------------- -------------------
                    2016-01-10 00:00:00
2016-01-08 00:00:00
2016-01-08 00:00:00 2016-01-10 00:00:00

Oracle successfully executes the query and simply ignores the temporal validity clause. If someone accidentally drops your time dimension your application won’t notice and you get wrong results all the time until you have to analyze it because it became an issue (which will probably be very soon).

In my opinion the correct behaviour in this situation would be returning an error, as it is the case when you have an already existing time dimension on the table but use a non-existing one in the query. I already filed a bug in MOS to ask whether this is a bug or a design decision and I will keep this post updated on the outcome.

Comment

Loading data into index organized tables

Most of the time you don’t have full control of how your data gets loaded into the database, because it is often imposed by business rules. But in the case you have full control, you should take advantage of it.

I recently came across an index organized table which was kind of a denormalized form of other tables and which was truncated and reloaded every day before some heavy batch jobs queried it.

The data was loaded into the IOT with an insert as select statement. Because the data returned by the select was not ordered (or at least not ordered after the primary key columns of the IOT), the insert caused a lot of 50/50 block splits on the IOT.

I added a simple order by clause to the statement with the effect that only 90/10 block splits were done afterwards. This also resulted in a 50% smaller index size.

Here’s a small test case for demonstration:

Create a heap table which is later used to load data into the IOT

The c1 column will be the pk column of the later created IOT. I used a random function so the values for this column are not ordered in any way.

create table t_heap (c1 number, c2 varchar2(100));

INSERT INTO t_heap
SELECT dbms_random.random, lpad('#',100,'#')
FROM dual CONNECT BY rownum <= 100000;

Remove any duplicate values for c1 as this would lead to unique constraint violations later.

declare
 lastValue t_heap.c1%type;
 cursor c_heap is select * from t_heap order by c1 for update;
begin
  for rec in c_heap loop
    if rec.c1 = lastValue then
      delete from t_heap where current of c_heap;
    end if;
  lastValue := rec.c1;
  end loop;
end;
/

commit;

Create the IOT

create table t_iot (c1 number, c2 varchar2(100),
 constraint pk_iot primary key (c1))
 organization index;

Check the index split statistics before inserting

select n.name,s.value from v$mystat s
join v$statname n on (s.statistic# = n.statistic#)
where n.name in ('leaf node splits', 'leaf node 90-10 splits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                          0
leaf node 90-10 splits                                                    0

Insert without order by clause

insert into t_iot select * from t_heap;


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                       2908
leaf node 90-10 splits                                                    0

select bytes/1024/1024 from dba_segments where segment_name='PK_IOT';

BYTES/1024/1024
---------------
             23

About ~3k block splits were done, all of them 50/50. The size of the index is ~23 MB.

Now insert with order by clause

(Open a new database session, so we have “fresh” counters in v$mystat)

truncate table t_iot;
insert into t_iot select * from t_heap order by c1;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                       1458
leaf node 90-10 splits                                                 1458

select bytes/1024/1024 from dba_segments where segment_name='PK_IOT';

BYTES/1024/1024
---------------
             12

The amount of block splits is cut in half and because of the order by clause, only 90/10 block splits were done. The index size is also considerably smaller (12 MB compared to 23 MB).

Comment