Forum Stats

  • 3,837,271 Users
  • 2,262,245 Discussions
  • 7,900,244 Comments

Discussions

Connection Pool Issue

We are having issues with our ORDS environment where one long running service (query) is causing the connection pool to fill up. It almost seems like things are being single threaded through ORDS. Are there any session specific settings we should look into that may help out our situation?

We are on version 21.4.2.r0621806

Tagged:

Comments

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,687 Employee

    each request gets a connection

    each connection will be occupied while the db work is being taken care of (your SQL or PL/SQL)

    if that takes too long, as more requests come in, the pool will either have to grow, or you'll run out of connections

    Ideally your fix would be to tune your query. Increasing the pool size may provide relief, but this shouldn't be the go-to solution.

  • Chad Boom
    Chad Boom Member Posts: 141 Blue Ribbon

    That is what we are thinking is going to be our answer, we are just having a hard time pinpointing which queries/processes are causing the issues. We make ~2.3 million REST calls from our application daily. We've been able to find a few user searches that have been running without the correct parameters but haven't been able to find any silver bullet answer to our issue. Are there any queries you have to look at session-related tables that would help us identify what's consuming the connection pool? The below seems somewhat useful but is only a sampling of every minute of active sessions and doesn't really show the contents of the connection pool.

    SELECT a.sample_time

       ,a.sql_opname

       ,a.sql_exec_start

       ,a.program

       ,a.client_id

       ,b.sql_text

     FROM dba_hist_active_sess_history a

       ,dba_hist_sqltext       b

     WHERE a.sql_id = b.sql_id

     ORDER BY a.sample_time ASC;

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,687 Employee
    edited Jul 5, 2022 7:01PM

    if the code is less than good with that kind of load, the bad stuff should float to the top even if it's only sampling activity

    Make sure you're keeping an eye on blocking sessions, you should have locks all over the place if you have traffic queuing up waiting for resources.

    if you want more tips/help, please email me at [email protected]