Re: Error related to hash memory
Date: Wed, 1 Dec 2021 12:57:36 -0500
Message-ID: <a16a642f-f804-dc61-32fb-464ed5aa2748_at_gmail.com>
On 12/1/21 07:08, Lok P wrote:
> Hello Listers, This database is on version 11.2.0.4. We are seeing one
> query which is suddenly failing after running over ~2hrs , with
> ORA-32690. And it happened twice till now. Rerun making it finish
> though. We get to see bugs related to the same as below but that seems
> to be related to older versions. So wondering why we are seeing this
> here and how to fix this. We currently have pga_aggregate_taget set as
> ~40GB. Also from dba_hist_pga_stat, we see we have 'total PGA
> allocated' reaching ~30-35GB during peak time (but not ~40GB limit
> though). So wondering if we should increase pga_aggregate_target more
> or should do anything else here to fix it? This query is running with
> a parallel-4 hint.
Try hinting the SQL to do SORT-MERDE join, as my French friends would say. That should avoid creating hash tables in memory. Alternatively, add more memory.
Regards
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 01 2021 - 18:57:36 CET