Forum Discussion
Oracle Connector Leaving Open Connections
So I recently started using Power BI with our oracle server to build some reports. What I have noticed and it has caused severe issues is that when I go to refresh my data, Power BI (via "Oracle Connection" and a tnsnames.ora file) are opening about 20-30 new connections on the server (Red Hat with Oracle DB, currently on-prem in process of being moved to AWS). This is not too much of an issue but the connections are not being dropped once the data refresh is done. I do not know too much about Linux but they are sitting in an "ESTABLISHED" status. This obviously presents a problem because 30 new connections will max out most servers within 3-4 refreshes (considering other outside users, connections, etc). Even more odd is that OLE DB connection via Excel on the same machine using the same tnsnames.ora files does not cause this issue. Any help would be greatly appreciated.
23 Replies
To force Power BI gateway to flush out its connections after refreshing, you would need to periodically run a similar script suggested by brendan_w in the following thread to restart gateway.
http://community.powerbi.com/t5/Integrations-with-Files-and/Possible-BUG-Power-BI-Personal-Gateway-Does-not-close-Database/m-p/76767
Replace GWConfig.exe with EnterpriseGatewayConfigurator.exe in the script that brendan_w provides , and the EnterpriseGatewayConfigurator.exe locates in C:\Program Files\On-premises data gateway.Regards,
Lydia Zhang
Thanks for the suggestion, is there any way to apply this to PowerBI Desktop. We do not currently have the gateway setup because of some firewall (and the flushing) issue and your advice will be helpful for down the road. But currently we are just having this issue when we click refresh on the desktop application.
Edit: I also noticed that once I close out of the desktop application, the connection still continues to remain open.
zeel801p,
Consider to add Table.Buffer function in the codes of your advanced editor to reduce the load on the database server, or kill the connection from the Oracle server side after you refresh data in Power BI Desktop.
Regards,
LydiaThanks Anonymous,
I was about to try the Table.Buffer method, but am now noticing that I cannot even do a refresh without PBI opening too many simultaneous connections with even one refresh. Is this just behavir on Oracle connections or is this specific to my situation?
zeel801p,
Where do you add the Table.Buffer method? Do you get any error messages when refreshing in this case?
Regards,
Lydia
We too face open Oracle connections - when PBI reports are scheduled on our PBI Server - any hints how to get them closed automatically - cleaning up from Oralce site is not a solution!
Wobi
Well what i found out about open Oracle connections when using PBI Reportserver Schedules: They stay open -until closed by killing the processes or by reboot - so we do regular reboots of the PBI server - sorry for the short service interruption - but MS is not giving any parameters to get the connections closed otherwise.
We observerd that hanging / not closed mesh container processes keep the connection open - kill the process and the connection @ oracle is closed - are there any options to controll the mesh container prozesses cleanup - would be helpful!
Wobi
We are trying to avoid rebooting the servers (DB server is unthinkable, PBIRS server is not recommended, but not off the table).
We found that manually refreshing, by opening the report in the PBI desktop and refreshing from there, has a different behavior and actually closes the connections as the refresh ends.
We cheched th ODAC driver from our desktops and servers and they are both running the same versoin, so it`s not a problem with the driver version.
Hi there!
Any updates on this issue?
We are facing the same problems, running PBI desktop, Jan 22 version.
Any updates on this issue?
We have the same problems with September 2022 server version.
Microsoft should add a way to turn off connection pooling. Just like you can define it with .NET Connection Strings:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ole-db-odbc-and-oracle-connection-poolingStill having this issue.
Power BI doesn't let me edit the connection string. Is there anyway to disable pooling in the registry?
