DatabaseVerse: Journey into the World of Databases

Last week someone asked in a forum I joined recently: “An Oracle database has been migrated from AWS to OCI in the same region to Autonomous Database and the customer is complaining about latency. How can I measure or test this?”

And there many variables around that question, right?

• Sizing difference between source and target (cpu, memory, storage etc)
• Architecture
• Version
• etc etc

But let’s suppose that everything is “quite similar” and we want to determine if there is an latency issue, How can we check on this for Autonomous Database?

Oracle has release a tool called adbping that you can use to quickly determine the latency while connecting to a particular service. Typical use cases are:

• Determine which service is better for your workload
• Determine the connection and SQL Execution latency
• Benchmark performance for SQL executions
• etc

So, let’s see how it works!

Prerequisites

Prior to running the adbping tool, please make sure the following pre-requisites are met (this is for oracle linux/red hat 8)

sudo dnf install oracle-instantclient-release-el8 -y // instant client basic
sudo dnf install oracle-instantclient-basic -y // sql plus
sudo dnf install oracle-instantclient-sqlplus -y

if you want to fetch sqlplus connection latency you will need also the java JDK

sudo dnf install java-11-openjdk-devel -y 

Download adbping

You can download the tool from the official note: Connection and latency test tool – adbping (Doc ID 2863450.1)

Syntaxis

adbping -u <user> -p <pwd> -s <service name> -w <wallet location> -i <instantclient home> -j <javahome> -t <threads> -d <duration> -c <client> -l <TLS URL>

So, let’s run couple of examples:

• Duration: 30 secs
• Threads: 20
• sampleSQLFile: samples/sqlplus_client/large_resultset.sql

select d_date,d_month,d_year from ssb.dwdate where rownum<10001;

• Services: low, medium and high

Example of execution:

-w parameter is to specify basically where my wallets and tnsnames.ora are located. Make sure to modify this in your test and download your Autonomous Database connections details from OCI.

Using service low priority output:

+++Test Summary+++
   Test Client: sqlplus
   Number of concurrent threads: 20
   Duration (secs): 300
   Custom SQL executed: samples/sqlplus_client/large_resultset.sql
   Pass: 5760 Fail: 0
   Test start date: 2024-02-18 17:21:45.531324+00:00
   Test end date: 2024-02-18 17:26:45.926044+00:00
   SQL Execution Time(ms) : Min:30 Max:450 Avg:184.826 Median:180 Perc90:270 Perc95:290 Perc99:340
   Connect + SQL Execution Time(ms) : Min:521.054 Max:1290.2 Avg:841.514 Median:845.614 Perc90:923.411 Perc95:945.967 Perc99:997.959

Using service medium priority output:

+++Test Summary+++
   Test Client: sqlplus
   Number of concurrent threads: 20
   Duration (secs): 300
   Custom SQL executed: samples/sqlplus_client/large_resultset.sql
   Pass: 5720 Fail: 0
   Test start date: 2024-02-18 17:27:46.115212+00:00
   Test end date: 2024-02-18 17:32:47.070423+00:00
   SQL Execution Time(ms) : Min:30 Max:530 Avg:182.39 Median:180 Perc90:260 Perc95:290 Perc99:340
   Connect + SQL Execution Time(ms) : Min:468.233 Max:1347.348 Avg:846.231 Median:850.499 Perc90:931.717 Perc95:956.723 Perc99:1105.394

Using service high priority output:

+++Test Summary+++
   Test Client: sqlplus
   Number of concurrent threads: 20
   Duration (secs): 300
   Custom SQL executed: samples/sqlplus_client/large_resultset.sql
   Pass: 5760 Fail: 0
   Test start date: 2024-02-18 17:33:47.261498+00:00
   Test end date: 2024-02-18 17:38:47.536889+00:00
   SQL Execution Time(ms) : Min:30 Max:430 Avg:180.068 Median:180 Perc90:260 Perc95:290 Perc99:330
   Connect + SQL Execution Time(ms) : Min:473.883 Max:1124.592 Avg:838.896 Median:845.112 Perc90:919.794 Perc95:939.839 Perc99:983.859

Interpretation of the results

    1. Pass/Fail count: Indicates the total number of connections passed/failed in defined duration by the defined number of threads.
    2. SQL execution time: Time taken to just execute the SQL. Connection time not included.
       For sqlplus, this would be the elapsed time reported by sqlplus.
    3. Connect + SQL Execution Time: Time taken to connect and execute SQL.
       For sqlplus, this would be the time to connect and run the sql.
       For java, it would be time taken to getConnection() and execute the query.
    4. Java connection pool stats: Reports the time taken to setup the java connection pool and the initial and max size.
       All query executions do a getConnection() and execute the SQL.
    5. Perc90, Perc95, Perc99: This is the percentile value indicating 90%, 95% or 99% of the latencies are below the respective value.

• For all the test cases I didn’t get any error
• SQL execution time was pretty similar (all test cases were launch from same VM)
• Connect + SQL Execution Time(ms) was pretty similar as well (all test cases were launch from same VM)

So, this is useful to run on your application server or a server that is in the same subnet with the same firewall rules, if any, and measure how your connection/latency is behaving. If you have identified a particular query that is being impacted you can save it as a “.sql” file and use the -q option and pass the query as a parameter, adbping will run that statement for you and your measure will be specific.

Conclusion

In conclusion, measuring and testing latency for an Autonomous Database before/after migration is crucial to ensure optimal performance and user experience. Despite the various variables involved in such a migration from different cloud vendors, tools like adbping provided by Oracle offer a comprehensive solution for determining connection and SQL execution latency. The detailed syntax, prerequisites, and examples provided in the post demonstrate the practical application of the tool, including the interpretation of results and their significance. Oracle is evolving their tools so make sure to check the note every time for future versions of adbping tool that might include new features.

Reference

Connection and latency test tool – adbping (Doc ID 2863450.1)