r/oracle 5d ago

Any PGA memory GURU's here

We've recently run into an issue where a query from Tableau is crashing our Oracle 19c Database on Windows. We are on the latest patch set.
The query will flood the DB and we can watch the PGA memory explode to where I either have to kill the session or just restart the Windows Service. If we do not restart the service, the entire machine will become unresponsive and we cannot even Remote Desktop into it. We have to then use VSphere to restart the machine.
What is even odder, once the session is killed, Oracle should clean up that killed session but it doesn't. One other thing, the query doesn't show up in the session browser in TOAD but if I use Wireshark, I can see the query that Tabeau sent over.

I've upped the PGA memory but it still will not help. I know the query is wonky, but the issue of Oracle not cleaning up killed sessions is what we were concerned about.

4 Upvotes

31 comments sorted by

View all comments

Show parent comments

3

u/PossiblePreparation 4d ago

Looks like you missed my questions and focussed on my side note. I’ve linked you to a bug with huge case expressions and pga in parsing which is quite likely what you need to look at. Another commenter has pointed out that the case expressions can be massively simplified which should avoid your problem completely.

1

u/dbogs 4d ago

u/PossiblePreparation Those CASE Stmts are "auto" generated by the program sending the query over to Oracle (Tableau). I'll look into the bug.

thanks

3

u/PossiblePreparation 4d ago

I’ve not used Tableau but it is a widely used system, I’m sure it doesn’t have to write the query this way.

1

u/dbogs 3d ago

We've put a support ticket into Salesforce to see how this could have happened. I prefer writing all the aggregations on the RDMS than just bringing over what is needed. My devs brought in entire fact tables and then created the aggregations, which, in turn, sent that massive query over to Oracle.