Performing a Concurrency Test with Python and Oracle Autonomous Database

Conducting a concurrency test is a crucial step in evaluating the performance and scalability of an application. In this blog post, we'll explore how to execute a concurrency test using a Python script to connect to an Oracle Autonomous Database. Oracle Autonomous Database is a fully managed cloud database service that offers high availability, scalability, and automated maintenance. By leveraging Python and Oracle Autonomous Database, we can easily simulate multiple concurrent sessions and measure the response times of each query.

Prerequisites

Before getting started, ensure you have the following prerequisites in place:

1. Python installed on your machine
2. cx_Oracle library installed (python -m pip install cx_Oracle --upgrade )
3. Access to an Oracle Autonomous Database instance

Setting Up the Python Script

Let's begin by creating a Python script that connects to Oracle Autonomous Database, executes queries concurrently, and measures the response times. Here's the script:

import cx_Oracle

#import concurrent.futures

import time

import threading

#dsn = cx_Oracle.makedsn('<hostname>', '<port>', '<service_name>')

username = 'ADMIN'

password = 'Carbon##lalal4'

dsn="krpocadw_low"

# List of queries to be executed

queries = [

    "SELECT  /*+ NO_RESULT_CACHE */ * FROM ssb.customer where rownum < 1000" ,

    "SELECT  /*+ NO_RESULT_CACHE */ * FROM ssb.customer where rownum < 10000",

    "SELECT  /*+ NO_RESULT_CACHE */ * FROM ssb.customer where rownum < 100000",

    "select  /*+ NO_RESULT_CACHE */sum(lo_extendedprice*lo_discount) as revenue from ssb.lineorder, ssb.dwdate where lo_orderdate = d_datekey and d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35",

    """ select  /*+ NO_RESULT_CACHE */sum(lo_extendedprice*lo_discount) as revenue from ssb.lineorder, ssb.dwdate

     where lo_orderdate = d_datekey and d_weeknuminyear = 6 and d_year = 1994 and lo_discount

     between 5 and 7 and lo_quantity between 26 and 35 """

]

# Function to execute queries and measure their response times

# Function to execute queries and measure their response times

def execute_query(queries):

    

    # Establish connection to the Oracle database

    connection = cx_Oracle.connect(username, password, dsn)

    # Create a cursor

    cursor = connection.cursor()

    response_times = []

    for query in queries:

        # Append current date and time to the query

        query_with_time = query + " /* " + time.strftime("%Y-%m-%d %H:%M:%S") + " */"

        

        start_time = time.time()

        # Execute the query

        cursor.execute(query_with_time)

        # Fetch all the rows

        rows = cursor.fetchall()

        end_time = time.time()

        # Calculate the response time

        response_time = end_time - start_time

        response_times.append(response_time)

    # Close cursor and connection

    cursor.close()

    connection.close()

    return response_times

# Function to run queries concurrently and measure average response time per query

# Function to run queries concurrently and measure average response time per query

def run_queries_concurrently(concurrency_level):

    response_times = []

    # Create a list to hold the threads

    threads = []

    # Create and start the threads

    for _ in range(concurrency_level):

        thread = threading.Thread(target=lambda: response_times.extend(execute_query(queries)))

        threads.append(thread)

        thread.start()

    # Wait for all threads to complete

    for thread in threads:

        thread.join()

    return response_times

# Main function

def main():

    # Define the levels of concurrency

    concurrency_levels = [1, 2, 14, 28]

    for level in concurrency_levels:

        print(f"Running queries with concurrency level: {level}")

        # Measure the average response time per query

        response_times = run_queries_concurrently(level)

        for i, query in enumerate(queries):

            average_time = sum(response_times[i::len(queries)]) / len(response_times[i::len(queries)])

            print(f"Query: {query}")

            print(f"Average response time: {average_time:.4f} seconds\n")

# Execute the main function

if __name__ == "__main__":

    main()

Make sure to replace <hostname>, <port>, <service_name>, <username>, and <password> with your actual Oracle Autonomous Database connection details. Update the queries list with your desired