Connection pools on Data Warehouse

Issue : Data Warehouse getting inundated with connections 11/11/22

• This caused other people to not be able to connect to the data warehouse

Possibilities/Notes

• Max connections on data warehouse = 300 connections

• Possible:

  • Connections were not closed when done with them?

  • Maybe the issue is in the Oracle OCI layer?

•  

Cause:

This code was run from someone’s local machine, causing many connections to the data warehouse to be established:

Solution:

To make the code work as intended, the following changes were made:

• Look at the V$ tables to see the connections that are present on the warehouse

  • specifically V$ Session

• Bitool has access to V$ tables

  • Covid, DWDATA do not have access

•  

New code looks like this:

Takeaways:

• Use a context manager for connection pools in python

• Set max size of connection pool

• Connection pools behave erratically with failovers

• Maybe we should increase the amount of connections that are allowed in the Data Warehouse