Re: Long Parse Time for a big Statement
Date: Tue, 18 Jan 2022 10:00:03 +0100 (CET)
Message-ID: <1471489336.143593.1642496403138_at_ox.hosteurope.de>
Hello Lothar,
In the past I almost figured out every long parsing problem with CPU samples and flame graphs (if PARSE timings were not available) - so please have a look at the whole C-stack if you are just concerned about the parsing time but if you are more concerned about the memory usage filter on kghalf, kghalo and kghalp while sampling the C-stacks.
Best Regards
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
> Lothar Flatz <l.flatz_at_bluewin.ch> hat am 17.01.2022 13:59 geschrieben:
sorry, if I missed some already explained details in the reply chain.
Stefan Koehler
Twitter: _at_OracleSK<
>
> 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-lReceived on Tue Jan 18 2022 - 10:00:03 CET