If this is a common problem, and not susceptible to SQL Patch treatment,
or the addition of /*+ no_or_expand */ hints at the start of the query
pattern, then you could brute force it by setting "_no_or_expansion" to
true if you can identify the sessions that need it, or just set it
system-wide. You should get much faster optimization times - though the 55
way union all would have to be optimized in 55 parts.
Regards
Jonathan Lewis
On Mon, 17 Jan 2022 at 12:59, Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
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
--
//www.freelists.org/webpage/oracle-l
