Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Power BI Desktop - Oracle 12c database - Direct query - exceeding SESSIONS_PER_USER limit

Power BI Desktop is exceptionally "greedy" with Oracle sessions. Smiley Frustrated It always attempts to create new sessions despite holding onto earlier sessions.  If I had to guess, I'd say someone is not properly closing/disposing of a database connection object in the underlying code.  I currently have a Power BI report in development that uses six (6) separate queries to obtain data.  I would expect it to use six Oracle connections.  But the first time I attempt to refresh, it requests six new sessions, exceeding my employer's very strict SESSIONS_PER_USER limit of ten (10) sessions.  It never releases any of these sessions unless I completely exit the Power BI Desktop program and wait several minutes.

 

Why does Power BI Desktop not re-use existing sessions?

 

select SID, "SERIAL#", AUDSID, "USER#", USERNAME, COMMAND, STATUS, SERVER, PORT, PROGRAM
from v$session
where username like 'FredTheFrog';

SID SERIAL# AUDSID USER# USERNAME COMMAND STATUS SERVER PORT PROGRAM 
--- ------- ------ ----- -------- ------- ------ ------ ---- -------
33 31319 8738532 461 FredTheFrog 0 INACTIVE DEDICATED 54843 msmdsrv.exe 
49 54055 8738529 461 FredTheFrog 0 INACTIVE DEDICATED 54831 msmdsrv.exe 
53 62873 8738534 461 FredTheFrog 0 INACTIVE DEDICATED 54847 Microsoft.Mashup.Container.NetFX45.exe 
65 61497 8738522 461 FredTheFrog 3 ACTIVE DEDICATED 54579 SQL Developer 
640 43822 8738537 461 FredTheFrog 0 INACTIVE DEDICATED 54852 Microsoft.Mashup.Container.NetFX45.exe 
666 59135 8738531 461 FredTheFrog 0 INACTIVE DEDICATED 54840 msmdsrv.exe 
667 57853 8738536 461 FredTheFrog 0 INACTIVE DEDICATED 54849 msmdsrv.exe 
669 44155 8738530 461 FredTheFrog 0 INACTIVE DEDICATED 54836 Microsoft.Mashup.Container.NetFX45.exe 
672 63478 8738533 461 FredTheFrog 0 INACTIVE DEDICATED 54844 msmdsrv.exe 
674 36464 8738535 461 FredTheFrog 0 INACTIVE DEDICATED 54848 msmdsrv.exe

10 rows selected

 

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @FredTheFrog,

 

How many visuals do you add into the report? As I tested, when we refresh the report in Power BI desktop, each visual send a connection to database when refreshing data.  

 

Best Regards,
Qiuyun Yu

FredTheFrog
Advocate I

The report has five visuals on the first report tab, six visuals on the second report tab, and one visual on the third report tab.  Many of these visuals draw from the same SQL queries, so there is not a one-to-one relationship between queries and visuals.  My concern is, Power BI does not appear to re-use existing sessions, nor does it release/dispose of sessions upon query completion.  This is not good behavior.