Introduction

SqlNet OCI

This blog covers the following topics:

  • SqlNet latency across OCI regions
  • SqlNet latency across VCNs within a region
  • Edge Caching for lower SQL latency

 

Part 1 of this blog showed you how to enable SqlNet connectivity between VCNs in a region and across regions.

Part 3 of this blog will cover the following:

  • How the SQL resultset size affects SqlNet latency
  • How the SQL statement complexity affects the SqlNet latency
  • How the OCI compute shape [small VM vs bare metal] affects the SqlNet latency

 

 

 

 

SqlNet latency across OCI regions

Once you have network connectivity across OCI regions, you will be able to verify that the network round trip time is proportional to the distance between the regions.

Regions used

I tested SqlNet latency using the above 18 regions of the possible 42 Oracle Cloud regions.  

 

 

SqlNet latency across OCI regions

My Oracle 19c database was in Phoenix, Arizona in the USA and my Oracle SqlNet clients were in 17 remote regions.

 

Region distances

  • I tried to choose OCI regions which were evenly spaced out across the world
  • The large gap between Montreal [Canada] and Newport [Wales] was due to the Atlantic ocean

 

 

Auckland to Santiago

 

  • My distances between OCI regions are approximations.  I used the distance between the major airports in those regions
  • The length of the fibre optic cables used to connect OCI regions will tend to be longer than the airport to airport distances that I used
  • For example, consider the distance from Auckland, New Zealand to Santiago, Chile 

 

Auckland to Santiago via San Diego

 

 

  • There are no under-sea cables from New Zealand to Chile, so network messages need to go via the USA
    • Auckland to Santiago = 6005 miles
    • Auckland to San Diego = 6502 miles
    • San Diego to Santiago = 5477 miles
    • Auckland to Santiago via San Diego = 11979 miles
    • So the cable distance is almost twice the flight distance for this example
  • I used the flight distances as they are easy to understand and easy to obtain

 

World-wide SqlNet latency

The SqlNet round trip time latency is roughly linear.  ie the greater the [flight] distance that the SqlNet client region was from my Oracle database in Phoenix [USA], the greater the round trip time.

The following regions had a higher latency than expected:

  • Newport [Wales]
  • London [England]
  • Frankfurt [Germany]
  • Sao Paulo [Brazil]
  • Mumbai [India]

Whether the higher than expected latency for those five regions was due to greater cable length, more traffic in those regions or something else is unknown.

 

The SQL statements used to measure the round trip time were deliberately simple:

  • They used the primary key for SQL lookups and updates
  • They minimized the SQL request and response size
SELECT directory_nb, last_calling_party, desc FROM vpn_users WHERE vpn_id = :1 and vpn_nb = :2;
Update vpn_users SET last_calling_party = :1 WHERE vpn_id = :2 and vpn_nb = :3;

 

There were 10 million rows in the VPN_USERS table.

VPN_USERS table

 

The logical SqlNet request and reply messages were small:

  • Less than 100 bytes for each of the SQL statements
  • About 120 bytes for the resultset for the query

 

The SqlNet latency was measured via a C program [tptbmOCI] which used the Oracle Call Interface. This program was used to execute the queries and updates to the VPN_USERS table.

SqlNet clients

These trivial SQL query and update statements minimize the request and reply bytes used for the SqlNet round trip while also minimizing the SQL processing on the Oracle database.

Part 3 of this blog investigates more complex SQL statements and large resultsets.

 

SqlNet latency

The SqlNet latency was quite stable:

  • The average of five latency tests was used for both the queries and the updates
  • At least 1000 iterations of the queries and updates were used for each test
  • Each test was run for at least 90 seconds
  • The results had little variance and seemed to be stable as I got similar results on different days with different VMs and VCNs in the same regions

 

 

 

SqlNet latency within a region

Now we can look at latencies within a region.

SqlNet latency within a region

The above picture shows the following:

  • The Oracle 19c database was provisioned in the Phoenix region, in Availability Domain AD3 on Virtual Cloud Network VCN1
  • If the tptbmOCI benchmark was run on the same machine as the Oracle database, then it had the lowest SqlNet latency of 89 microseconds [red box]
  • If the tptbmOCI benchmark was run on an E4 Flex VM in the same AD and same VCN then the latency was 333 microseconds [brown box]
  • If the tptbmOCI benchmark was run on an E4 VM in the same AD, but in VCN2 via a Local Peering Gateway, then the latency was 340 microseconds [yellow box]
  • If the tptbmOCI benchmark was run on an E4 VM in the same AD, but in VCN2 via a Dynamic Routing Gateway, then the latency was 365 microseconds [purple box]
  • If the tptbmOCI benchmark was run on a E4 flex VM in a different AD on the same VCN, then the latency was 941 microseconds [black box]
  • If the tptbmOCI benchmark was run in the closest region [San Jose, CA], then the latency was 20 milliseconds [20,000 microseconds, gold box]

So within a region, latency is proportional to distance and Local Peering Gateways are slightly faster than Dynamic Routing Gateways

 

 

Lower Latency with Edge Caching

To minimize application latency, you want your application to be as close to your data as possible:

  • You usually cannot have your application running on the same machine as your Oracle database
  • Your application may be on different VCNs, ADs or even different regions than your Oracle database 

edge caching SQL APIs

You can choose to use Edge Caching to enable low latency for SQL reads and writes, even if your Oracle database is in different VCNs, ADs, regions or even on premises.

The free Oracle TimesTen XE or Licensed Oracle TimesTen In-Memory Database can be used as an Edge Cache for your SQL data:

  • TimesTen can run on the same machine as your application
  • TimesTen can run on bare metal, Virtual Machines or containers managed by Kubernetes
  • TimesTen can also be on a different machine than your application via the TimesTen client server protocol using TCP sockets or TCP over TLS
  • Cache writes from your application can be asynchronous.
    • Your application does not need to wait for the SqlNet round trip time
  • Updates to the TimesTen cache from the Oracle database are asynchronous to your application
    • Your application only sees transactionally consistent and does not need to wait for cache updates
  • Reads and writes to the TimesTen cache are not affected by how far away the Oracle database is
  • Cache reads and writes take a small / constant amount of time, even if your Oracle database is thousands of miles away

 

tptbmOCI with TimesTen cache

The exact same tptbmOCI program that was used to benchmark the Oracle SqlNet latency was used with TimesTen:

  • The tptbmOCI program did not need to be re-compiled or re-linked
  • The tptbmOCI program uses the shared library from the Oracle 19c Instant Client when communicating with both the Oracle database and TimesTen
  • tnsnames.ora defined TNS service names for both TimesTen and the Oracle database

 

SqlNet latency

The above tables show:

  • The Oracle SqlNet latency within a region is dependent on distance
    • The closer the application [eg tptbmOCI] is to the data, the lower the latency
  • The TimesTen latency is also dependent on distance
    • The closer the application [eg tptbmOCI] is to the data, the lower the latency
  • The TimesTen TCPI/IP Client Server protocol was faster than SqlNet using TCP/IP when both the application and database were on the same machine. eg 
    • 43 microseconds vs 89 microseconds for reads
    • 52 microseconds vs 881 microseconds for writes
  • The TimesTen Direct Linked connections were significantly faster than SqlNet using TCP/IP when both the application and database were on the same machine. eg
    • 2 microseconds vs 89 microseconds for reads
    • 5 microseconds vs 889 microseconds for writes

 

 

Edge caching benefit

 

So whether you are measuring SqlNet latency within a region or across regions, the SqlNet latecy is proportional to the distance.

Local edge caching can be significantly faster than remote SqlNet access and is un-affected by how far away the Oracle database is.

Edge caching vs remote SqlNet latency

  • Whether you are measuring SqlNet latency within a region or across regions, the Oracle SqlNet latecy is proportional to the distance
  • Local edge caching is significantly faster than remote SqlNet access and is un-affected by how remote the Oracle database is

 

 

 

Summary

  • Oracle SqlNet latency is proportional to distance
  • The closer your data is to your application, the lower the SqlNet latency
    • Running your application on the same machine as your Oracle database will tend to give the lowest latency for SqlNet
    • Running your application in the same VCN is faster than having your application in a separate VCN
    • Local Peering Gateways and Dynamic Routing Gateways enable Sqlnet connectivity between VCNs within a region
    • Local Peering Gateways are slightly faster than Dynamic Routing Gateways
    • Dynamic Routing Gateways are needed for SqlNet connectivity between OCI regions
  • Edge caching enables the lowest possible SQL latency
    • Oracle TimesTen cache enables edge caching
    • TimesTen cache enables significantly lower latency than using SqlNet
  • Edge Caching needs to be local 
    • Run TimesTen cache on the same machine as your application for the lowest possible latency
    • If required, run TimesTen cache in the same VCN and the same AD as your applications
  • Edge caching hides the SqlNet latency from your applications via asynchronous cache writes

 

Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.