Long Parse Time for a big Statement

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Mon, 17 Jan 2022 13:59:14 +0100
Message-ID: <1fd43692-973f-57d7-7f1f-107df61a051e_at_bluewin.ch>



Hi,

At one customer site we see generated statements, actually reports. The parsetime for such a statement is over an hour, if it finishes at all. It is possible we see "ORA-04031:" when we run out of memory in the shared pool.
How big these statements are is hard to tell, since it depends on formatting. With sql developer formatting i get in one typical example > 130000 lines.
The statements are constructed relatively simple. It seems to be a kind of change report where columns from different tables are retrieved.
At the beginning is a big case statement where a meaningful name is generated for a value followed by this values. I counted 7400 case entries as per statement in one case.
I addition we haveĀ  a number of big inlists. All this is running against a union view of 55 Tables. In other words: If i want to stress the parser I would construct a statement exactly like this.
However, one hour seems to be a unrealistically long parse time. Even though that statement needs to be rewritten, but this will take time. I want to know if there is any quick fix like increasing the shared pool a lot. (Which I can't test unfortunately any time soon due lack of memory). Any ideas how to speed up the parse time?

Database version is 19.7. Shared Pool size is 20GB

Thanks

Lothar

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 17 2022 - 13:59:14 CET

Original text of this message