📋

Description

The ORA-29270 error in Oracle Database indicates that your PL/SQL code has exceeded the allowed limit for concurrent HTTP requests. This typically occurs when using the `UTL_HTTP` package to make numerous web service calls without properly closing connections.

💬

Error Message

ORA-29270: too many open HTTP requests

🔍

Known Causes

3 known causes

⚠️

Unclosed HTTP Connections

Connections opened with `UTL_HTTP.BEGIN_REQUEST` are not closed with `UTL_HTTP.END_REQUEST`, leading to resource exhaustion. ⚠

⚠️

High Concurrency

The application attempts to make too many simultaneous HTTP requests exceeding Oracle's limits. 💻

⚠️

Resource Limits Exceeded

The Oracle server's resource limits for HTTP connections are insufficient for the application's needs. ⚙

🛠️

Solutions

3 solutions available

1. Reduce Concurrent HTTP Requests medium

Limit the number of simultaneous HTTP requests being made by the Oracle database to external services.

1

Identify the PL/SQL or Java code that is making HTTP requests. This might involve searching your codebase for calls to `UTL_HTTP` or Java classes like `java.net.HttpURLConnection`.

2

Implement a mechanism to control the concurrency. This could involve using a queueing system, a semaphore, or simply introducing delays between requests. For `UTL_HTTP`, you can modify your PL/SQL code to explicitly manage the number of open requests. Consider using a counter and waiting if it exceeds a certain threshold.

DECLARE
  l_req utl_http.req;
  l_resp utl_http.resp;
  l_url  VARCHAR2(2000) := 'http://example.com/api';
  l_buffer VARCHAR2(32767);
  l_request_count NUMBER := 0;
  l_max_concurrent NUMBER := 5; -- Adjust this value
BEGIN
  -- Simulate a loop for multiple requests
  FOR i IN 1..10 LOOP
    -- Wait if the maximum concurrent requests are reached
    WHILE l_request_count >= l_max_concurrent LOOP
      DBMS_LOCK.SLEEP(1); -- Sleep for 1 second
      -- Re-evaluate l_request_count (this would require tracking active requests)
      -- For simplicity, this example doesn't actively track, but in a real scenario, you'd need to.
    END LOOP;

    l_request_count := l_request_count + 1;

    BEGIN
      l_req := utl_http.begin_request(l_url, 'GET');
      l_resp := utl_http.get_response(l_req);

      -- Process response here...
      LOOP
        utl_http.read_text(l_resp, l_buffer, 32767);
        DBMS_OUTPUT.put_line(l_buffer);
      END LOOP;
      utl_http.end_response(l_resp);

    EXCEPTION
      WHEN OTHERS THEN
        -- Handle exceptions, ensure response is closed if opened
        IF l_resp IS NOT NULL THEN
          utl_http.end_response(l_resp);
        END IF;
        RAISE;
    FINALLY
      -- Decrement count when request is finished or failed
      l_request_count := l_request_count - 1;
    END;
  END LOOP;
END;
/

3

If you're using Java Stored Procedures, implement similar concurrency controls within your Java code, potentially using `ExecutorService` with a fixed thread pool size.

import java.net.HttpURLConnection;
import java.net.URL;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

public class HttpRequester {
    private static final int MAX_CONCURRENT_REQUESTS = 5;
    private static final ExecutorService executor = Executors.newFixedThreadPool(MAX_CONCURRENT_REQUESTS);

    public void makeRequest(String urlString) {
        executor.submit(() -> {
            try {
                URL url = new URL(urlString);
                HttpURLConnection connection = (HttpURLConnection) url.openConnection();
                connection.setRequestMethod("GET");
                // ... process response ...
                connection.disconnect();
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
    }

    public void shutdownExecutor() {
        executor.shutdown();
        try {
            if (!executor.awaitTermination(60, TimeUnit.SECONDS)) {
                executor.shutdownNow();
            }
        } catch (InterruptedException e) {
            executor.shutdownNow();
        }
    }

    public static void main(String[] args) {
        HttpRequester requester = new HttpRequester();
        for (int i = 0; i < 10; i++) {
            requester.makeRequest("http://example.com/api?id=" + i);
        }
        requester.shutdownExecutor();
    }
}

2. Increase `UTL_HTTP` Connection Pool Size (if applicable) medium

Adjust the maximum number of concurrent `UTL_HTTP` connections the database can maintain.

1

This parameter is controlled by the `UTL_HTTP.MAX_CONNECTIONS` initialization parameter. You need to modify your Oracle instance's initialization parameter file (e.g., `init.ora` or `spfile`).

2

Log in to your database as a user with DBA privileges.

3

Check the current value of `UTL_HTTP.MAX_CONNECTIONS`.

SHOW PARAMETER utl_http_max_connections;
-- OR --
SELECT name, value FROM v$parameter WHERE name = 'utl_http_max_connections';

4

If you are using an SPFILE, dynamically alter the parameter. If you are using an init.ora file, edit the file and restart the database.

ALTER SYSTEM SET utl_http_max_connections = 10 SCOPE=SPFILE;
-- Then restart the database instance.

5

If using init.ora, edit the file and add or modify the line:
`utl_http_max_connections = 10` (replace 10 with your desired value).
Then, restart the database instance.

6

After restarting, verify the new value.

SHOW PARAMETER utl_http_max_connections;

3. Review and Optimize External HTTP Calls advanced

Analyze the efficiency and necessity of the HTTP calls being made.

1

Use Oracle's tracing and diagnostic tools to pinpoint which specific calls are contributing to the high load. This might involve enabling SQL Trace (tkprof) for the sessions making the calls or using Enterprise Manager for performance monitoring.

EXEC DBMS_SESSION.SET_SQL_TRACE (TRUE);
-- Perform the operations that cause the error
EXEC DBMS_SESSION.SET_SQL_TRACE (FALSE);
-- Then analyze the trace file using tkprof.

2

Examine the frequency of these calls. Can they be batched? Is there redundant information being fetched? Can some calls be eliminated entirely?

3

Consider caching responses from external services if the data doesn't change frequently. This can significantly reduce the number of actual HTTP requests.

4

If the external service itself is slow or unresponsive, this can exacerbate the problem. Investigate the performance of the target HTTP endpoints.

Oracle ORA-29270: Too Many Requests