Re: Long Parse Time for a big Statement

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Tue, 18 Jan 2022 10:11:40 +0100
Message-ID: <2cf0beeb-5d2e-a2b8-a369-5b363b5ad26c_at_bluewin.ch>

Hi Stefan,

1.) No, I have not, I would have if I had access to the DB server. I did construct a test case but right now I  need to find a server with enough memory to test it.
2.) Yep,  there is both. Shared_pool seems to run out first.

   Event waited on                             Times   Max. Wait  Total Waited

   ----------------------------------------   Waited  ---------- 

---

   PGA memory operation                          147 0.00          0.00
   latch: shared pool                              9 0.00          0.00
   SGA: allocation forcing component growth       27 2.51          2.51

..
ORA-04031: 80 Byte des Shared Memorys konnten nicht zugewiesen werden ("shared pool","explain plan for SELECT * FR...","TCHK^4b493f9a","logdef: qcopCreateLog")

Thanks

Lothar

Am 18.01.2022 um 10:00 schrieb Stefan Koehler:
> Hello Lothar,
> sorry, if I missed some already explained details in the reply chain.
>
> 1) Have you already sampled the parsing (let's say with 20 CPU samples per second for 2 or 3 minutes or so) and created a flame graph afterwards?
> 2) Are you sure that you are running out of memory in shared pool and not also in PGA? Parsing allocates memory in both areas depending on what is happening.
>
> 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
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: http://www.soocs.de
> Twitter: _at_OracleSK<
>
>> Lothar Flatz <l.flatz_at_bluewin.ch> hat am 17.01.2022 13:59 geschrieben:
>>
>> 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
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 18 2022 - 10:11:40 CET