Re: Error related to hash memory

Thank you Andy and Jared. This query was existing query and was running fine with same memory config till recently. So it may be a little more volume it's processing now.

So as we are not saturating all the host memory during this error. So may be increasing pga_aggregate_target further won't help . The only option seems here to be forcing sort group by through profile/hints. Please correct me if wrong.

Yes we are soon planning to move to 19C. But at this point want to have some workaround to avoid customer impact. The original query is a big one with group by in it I will post the original query after modifying exact names and values.

And one question still I was wondering of is it's working. Should the hash group by operation uses previously allocated memory(decided during parse may be) and it won't thrash the PGA beyond its set target , even if enough host memory is available?

On Thu, 2 Dec 2021, 2:39 am Andy Sayer, <andysayer_at_gmail.com> wrote:

> The fix would be to upgrade off of ancient 11.2. At some point you're
> going to run out of duct tape to keep supporting this, the more you apply
> now the harder it is going to be to actually upgrade.
>
> Is this query new or has it been running fine for a while and successfully
> using that much memory without breaking?
>
> The hash join is not being executed in parallel so changing the degree
> isn't going to change this.
>
> We can't see your SQL but the plan looks like there may be
> optimizations you can make to reduce the amount of data which needs
> grouping (newer versions would probably do this for you).
>
> Thanks,
> Andrew
>
> On Wed, 1 Dec 2021 at 20:17, Jared Still <jkstill_at_gmail.com> wrote:
>
>> there seem to be some issues at times with hash_group_by.
>>
>> I have seen it (in 11g only) consume all memory and crash, regardless of
>> how much memory it wad given, up to several times the size of the amount of
>> data.
>>
>> Ad suggested in another reply, the fix was to disable hash_group_by.
>>
>>
>> On Wed, Dec 1, 2021 at 04:28 Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> Also see doc 1505491.1. It seems your line number 19, Hash_group_by is
>>> causing the error to popup. And a "sort group by" i.e
>>> no_use_hash_aggregation hint may stop this error from happening.
>>>
>>> On Wed, Dec 1, 2021 at 5:39 PM Lok P <loknath.73_at_gmail.com> 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.
>>>>
>>>> Query crash with ORA-32690 -- Bug 6471770. (Doc ID 960690.1)
>>>>
>>>> Error: ORA-32690
>>>> ------------------------------
>>>> ORA-32690: Hash Table Infrastructure ran out of memory
>>>>
>>>> Global Information
>>>> ------------------------------
>>>> Status : DONE (ERROR)
>>>> Instance ID : 1
>>>> SQL Execution ID : 16777475
>>>> Execution Started : 12/01/2021 03:19:14
>>>> First Refresh Time : 12/01/2021 03:19:14
>>>> Last Refresh Time : 12/01/2021 05:11:57
>>>> Duration : 6763s
>>>>
>>>> Global Stats
>>>>
>>>> =========================================================================================================================
>>>> | Elapsed | Cpu | IO | Application | Concurrency | Cluster |
>>>> Buffer | Read | Read | Write | Write | Cell |
>>>> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) |
>>>> Gets | Reqs | Bytes | Reqs | Bytes | Offload |
>>>>
>>>> =========================================================================================================================
>>>> | 28199 | 6038 | 21457 | 0.05 | 0.59 | 703 |
>>>> 1G | 12M | 106GB | 400K | 46GB | -29.87% |
>>>>
>>>> =========================================================================================================================
>>>>
>>>> Parallel Execution Details (DOP=4 , Servers Allocated=8)
>>>>
>>>> =======================================================================================================================================================================================================
>>>> | Name | Type | Server# | Elapsed | Cpu | IO |
>>>> Application | Concurrency | Cluster | Buffer | Read | Read | Write |
>>>> Write | Cell | Wait Events |
>>>> | | | | Time(s) | Time(s) | Waits(s) |
>>>> Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs |
>>>> Bytes | Offload | (sample #) |
>>>>
>>>> =======================================================================================================================================================================================================
>>>> | PX Coordinator | QC | | 1168 | 1054 | 113 |
>>>> 0.05 | 0.32 | 0.01 | 196K | 48876 | 6GB | 316K | 36GB |
>>>> -85.19% | direct path read temp (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path write temp (67) |
>>>> | p000 | Set 1 | 1 | 6761 | 1240 | 5337 |
>>>> | 0.06 | 183 | 302M | 3M | 26GB | 20758 | 3GB |
>>>> -8.70% | gc buffer busy acquire (177) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc cr disk read (5) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc cr grant 2-way (2) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc remaster (2) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | cursor: pin S wait on X (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | cell single block physical read (5136) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path read temp (16) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path write temp (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | read by other session (42) |
>>>> | p001 | Set 1 | 2 | 6748 | 1262 | 5443 |
>>>> | 0.05 | 43 | 314M | 3M | 25GB | 21766 | 3GB |
>>>> -9.89% | gc buffer busy acquire (36) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc cr grant 2-way (6) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc current block 2-way (3) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc current block 3-way (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc remaster (2) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | cursor: pin S wait on X (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gcs drm freeze in enter server mode (2) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | cell single block physical read (5222) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path read temp (11) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path write temp (5) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | read by other session (27) |
>>>> | p002 | Set 1 | 3 | 6752 | 1231 | 5505 |
>>>> | 0.05 | 16 | 310M | 3M | 25GB | 20882 | 3GB |
>>>> -8.70% | gc buffer busy acquire (10) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc cr disk read (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc current block 2-way (2) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc current block 3-way (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc remaster (2) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | cursor: pin S wait on X (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gcs drm freeze in enter server mode (2) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | cell single block physical read (5313) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path read temp (12) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path write temp (5) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | read by other session (33) |
>>>> | p003 | Set 1 | 4 | 6769 | 1250 | 5058 |
>>>> | 0.11 | 461 | 312M | 3M | 25GB | 20952 | 3GB |
>>>> -8.70% | gc buffer busy acquire (425) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc cr disk read (7) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc cr grant 2-way (3) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | gc remaster (2) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | cursor: pin S wait on X (1) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | cell single block physical read (4858) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path read temp (10) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | direct path write temp (4) |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> | read by other session (51) |
>>>>
>>>> =======================================================================================================================================================================================================
>>>>
>>>> SQL Plan Monitoring Details (Plan Hash Value=1777040360)
>>>>
>>>> ===========================================================================================================================================================================================================================================================
>>>> | Id | Operation | Name
>>>> | Rows | Cost | Time | Start | Execs | Rows | Read
>>>> | Read | Write | Write | Cell | Mem | Temp | Activity |
>>>> Activity Detail |
>>>> | | |
>>>> | (Estim) | | Active(s) | Active | | (Actual) | Reqs
>>>> | Bytes | Reqs | Bytes | Offload | (Max) | (Max) | (%) |
>>>> (# samples) |
>>>>
>>>> ===========================================================================================================================================================================================================================================================
>>>> | 0 | INSERT STATEMENT |
>>>> | | | 4017 | +1 | 1 | 0 |
>>>> | | | | | | | 0.03 | cursor: pin
>>>> S wait on X (7) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | Cpu (2)
>>>> |
>>>> | 1 | LOAD AS SELECT |
>>>> | | | | | 1 | |
>>>> | | | | | | | |
>>>> |
>>>> | 2 | FAST DUAL |
>>>> | 1 | 2 | 1429 | +49 | 2 | 2 |
>>>> | | | | | | | |
>>>> |
>>>> | 3 | FAST DUAL |
>>>> | 1 | 2 | 6695 | +49 | 5M | 5M |
>>>> | | | | | | | |
>>>> |
>>>> | 4 | TABLE ACCESS BY INDEX ROWID | TMFS
>>>> | 1 | 3 | 1265 | +49 | 7 | 7 | 10
>>>> | 81920 | | | | | | |
>>>> |
>>>> | 5 | INDEX UNIQUE SCAN | TMFS_PK
>>>> | 1 | 2 | 1265 | +49 | 7 | 7 | 5
>>>> | 40960 | | | | | | |
>>>> |
>>>> | 6 | TABLE ACCESS BY INDEX ROWID | TMIF
>>>> | 1 | 4 | | | | |
>>>> | | | | | | | |
>>>> |
>>>> | 7 | INDEX UNIQUE SCAN | TMIF_PK
>>>> | 1 | 3 | | | | |
>>>> | | | | | | | |
>>>> |
>>>> | 8 | TABLE ACCESS BY INDEX ROWID | TMFS
>>>> | 1 | 3 | 1265 | +49 | 7 | 7 |
>>>> | | | | | | | |
>>>> |
>>>> | 9 | INDEX UNIQUE SCAN | TMFS_PK
>>>> | 1 | 2 | 1265 | +49 | 7 | 7 |
>>>> | | | | | | | |
>>>> |
>>>> | 10 | TABLE ACCESS BY INDEX ROWID | TMIF
>>>> | 1 | 4 | 1265 | +49 | 13 | 13 | 8
>>>> | 65536 | | | | | | |
>>>> |
>>>> | 11 | INDEX UNIQUE SCAN | TMIF_PK
>>>> | 1 | 3 | 1265 | +49 | 13 | 13 | 7
>>>> | 57344 | | | | | | |
>>>> |
>>>> | 12 | SORT AGGREGATE |
>>>> | 1 | | 6695 | +49 | 5469 | 5469 |
>>>> | | | | | | | |
>>>> |
>>>> | 13 | TABLE ACCESS BY INDEX ROWID | TLSV
>>>> | 1 | 2 | 6695 | +49 | 5469 | 2 |
>>>> | | | | | | | |
>>>> |
>>>> | 14 | INDEX RANGE SCAN | TLSV_IX2
>>>> | 1 | 1 | 6695 | +49 | 5469 | 43752 |
>>>> | | | | | | | |
>>>> |
>>>> | 15 | FAST DUAL |
>>>> | 1 | 2 | 6695 | +49 | 12596 | 12596 |
>>>> | | | | | | | |
>>>> |
>>>> | 16 | FAST DUAL |
>>>> | 1 | 2 | 6695 | +49 | 12596 | 12596 |
>>>> | | | | | | | |
>>>> |
>>>> | 17 | FAST DUAL |
>>>> | 1 | 2 | 1429 | +49 | 2 | 2 |
>>>> | | | | | | | |
>>>> |
>>>> | 18 | FAST DUAL |
>>>> | 1 | 2 | | | | |
>>>> | | | | | | | |
>>>> |
>>>> | 19 | HASH GROUP BY |
>>>> | 1 | 127K | 6716 | +48 | 1 | 0 | 36963
>>>> | 4GB | 316K | 36GB | | 2G | 41G | 1.85 | Cpu (432)
>>>> |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | direct path
>>>> read temp (1) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | direct path
>>>> write temp (67) |
>>>> | 20 | VIEW |
>>>> | 1 | 127K | 6695 | +49 | 1 | 168M |
>>>> | | | | | | | 1.38 | Cpu (372)
>>>> |
>>>> | 21 | PX COORDINATOR |
>>>> | | | 6695 | +49 | 5 | 168M |
>>>> | | | | | | | 0.70 | Cpu (190)
>>>> |
>>>> | 22 | PX SEND QC (RANDOM) | :TQ10003
>>>> | 1 | 127K | 6695 | +48 | 4 | 168M |
>>>> | | | | | | | 1.19 | Cpu (322)
>>>> |
>>>> | 23 | NESTED LOOPS OUTER |
>>>> | 1 | 127K | 6695 | +48 | 4 | 168M |
>>>> | | | | | | | 0.01 | Cpu (3)
>>>> |
>>>> | 24 | NESTED LOOPS OUTER |
>>>> | 1 | 127K | 6695 | +48 | 4 | 168M |
>>>> | | | | | | | 0.10 | Cpu (26)
>>>> |
>>>> | 25 | NESTED LOOPS OUTER |
>>>> | 1 | 127K | 6695 | +48 | 4 | 168M |
>>>> | | | | | | | 0.10 | Cpu (28)
>>>> |
>>>> | 26 | NESTED LOOPS OUTER |
>>>> | 1 | 127K | 6695 | +48 | 4 | 168M |
>>>> | | | | | | | 0.05 | Cpu (14)
>>>> |
>>>> | 27 | HASH JOIN |
>>>> | 1 | 127K | 6741 | +2 | 4 | 168M | 77152
>>>> | 9GB | 77152 | 9GB | | 4G | 10G | 1.61 | Cpu (408)
>>>> |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | direct path
>>>> read temp (11) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | direct path
>>>> write temp (15) |
>>>> | 28 | PX RECEIVE |
>>>> | 357K | 95828 | 33 | +2 | 4 | 34M |
>>>> | | | | | | | 0.06 | Cpu (17)
>>>> |
>>>> | 29 | PX SEND HASH | :TQ10002
>>>> | 357K | 95828 | 28 | +2 | | |
>>>> | | | | | | | 0.10 | Cpu (26)
>>>> |
>>>> | 30 | HASH JOIN |
>>>> | 357K | 95828 | 27 | +3 | | |
>>>> | | | | | | | 0.08 | Cpu (21)
>>>> |
>>>> | 31 | BUFFER SORT |
>>>> | | | | | | |
>>>> | | | | | | | |
>>>> |
>>>> | 32 | PX RECEIVE |
>>>> | 357K | 1869 | | | | |
>>>> | | | | | | | |
>>>> |
>>>> | 33 | PX SEND BROADCAST | :TQ10000
>>>> | 357K | 1869 | 3 | +1 | 1 | 1M |
>>>> | | | | | | | |
>>>> |
>>>> | 34 | TABLE ACCESS STORAGE FULL | TTSFA
>>>> | 357K | 1869 | 3 | +1 | 1 | 332K | 111
>>>> | 84MB | | | 59.02% | 7M | | |
>>>> |
>>>> | 35 | PX BLOCK ITERATOR |
>>>> | 34M | 93934 | | | | |
>>>> | | | | | | | |
>>>> |
>>>> | 36 | TABLE ACCESS STORAGE FULL | TTTD
>>>> | 34M | 93934 | 29 | +2 | | |
>>>> | | | | | | | 0.02 | Cpu (6)
>>>> |
>>>> | 37 | BUFFER SORT |
>>>> | | | 4333 | +36 | 4 | 18M | 12971
>>>> | 1GB | 7206 | 1GB | | 409M | 2G | 0.17 | Cpu (7)
>>>> |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | direct path
>>>> read temp (38) |
>>>> | 38 | PX RECEIVE |
>>>> | 63M | 30721 | 9 | +36 | 4 | 18M |
>>>> | | | | | | | 0.00 | Cpu (1)
>>>> |
>>>> | 39 | PX SEND HASH | :TQ10001
>>>> | 63M | 30721 | 12 | +35 | 1 | 18M |
>>>> | | | | | | | 0.02 | Cpu (5)
>>>> |
>>>> | 40 | TABLE ACCESS STORAGE FULL | TTFE
>>>> | 63M | 30721 | 11 | +36 | 1 | 18M | 2567
>>>> | 1GB | | | 9.09% | 7M | | 0.01 | Cpu (3)
>>>> |
>>>> | 41 | TABLE ACCESS BY INDEX ROWID | TLSV
>>>> | 1 | 2 | 6695 | +48 | 168M | 0 |
>>>> | | | | | | | 1.09 | Cpu (295)
>>>> |
>>>> | 42 | INDEX RANGE SCAN | TLSV_IX2
>>>> | 1 | 1 | 6695 | +48 | 168M | 1G |
>>>> | | | | | | | 1.17 | Cpu (316)
>>>> |
>>>> | 43 | PARTITION RANGE ITERATOR |
>>>> | 1 | 22 | 6662 | +82 | 168M | 2442 |
>>>> | | | | | | | 0.42 | Cpu (113)
>>>> |
>>>> | 44 | INLIST ITERATOR |
>>>> | | | 6666 | +77 | 168M | 2442 |
>>>> | | | | | | | 0.15 | Cpu (40)
>>>> |
>>>> | 45 | TABLE ACCESS BY LOCAL INDEX ROWID | TFPA
>>>> | 1 | 22 | 6634 | +108 | 335M | 2442 | 777
>>>> | 6MB | | | | | | 0.40 | Cpu (108)
>>>> |
>>>> | 46 | INDEX RANGE SCAN | TFPA_IX1
>>>> | 351 | 4 | 6694 | +48 | 335M | 2442 | 2M
>>>> | 18GB | | | | | | 22.08 | gc cr grant
>>>> 2-way (1) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | Cpu (839)
>>>> |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | cell single
>>>> block physical read (5102) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | read by
>>>> other session (21) |
>>>> | 47 | PARTITION RANGE ITERATOR |
>>>> | 1 | 7 | 6666 | +53 | 168M | 0 |
>>>> | | | | | | | 0.43 | Cpu (115)
>>>> |
>>>> | 48 | TABLE ACCESS BY LOCAL INDEX ROWID | TFMA
>>>> | 1 | 7 | 6605 | +105 | 168M | 0 |
>>>> | | | | | | | 0.18 | Cpu (48)
>>>> |
>>>> | 49 | INDEX RANGE SCAN | TFMA_IX1
>>>> | 166 | 3 | 6694 | +48 | 168M | 0 | 240K
>>>> | 2GB | | | | | | 6.52 | gc buffer
>>>> busy acquire (648) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | gc cr disk
>>>> read (13) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | gc cr grant
>>>> 2-way (4) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | Cpu (445)
>>>> |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | gcs drm
>>>> freeze in enter server mode (4) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | cell single
>>>> block physical read (606) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | read by
>>>> other session (41) |
>>>> | 50 | PARTITION RANGE ITERATOR |
>>>> | 1 | 4 | 6695 | +48 | 168M | 162M |
>>>> | | | | | | | 0.33 | Cpu (90)
>>>> |
>>>> | 51 | TABLE ACCESS BY LOCAL INDEX ROWID | TFA
>>>> | 1 | 4 | 6695 | +48 | 168M | 162M | 9M
>>>> | 66GB | | | | | | 51.63 | Cpu (714)
>>>> |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | cell single
>>>> block physical read (13143) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | read by
>>>> other session (84) |
>>>> | 52 | INDEX RANGE SCAN | TFA_IX1
>>>> | 7 | 3 | 6695 | +48 | 168M | 164M | 614K
>>>> | 5GB | | | | | | 8.10 | gc cr grant
>>>> 2-way (6) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | gc current
>>>> block 2-way (5) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | gc current
>>>> block 3-way (2) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | gc remaster
>>>> (8) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | Cpu (482)
>>>> |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | cell single
>>>> block physical read (1678) |
>>>> | | |
>>>> | | | | | | |
>>>> | | | | | | | | read by
>>>> other session (7) |
>>>>
>>>> ===========================================================================================================================================================================================================================================================
>>>>
>>> --
>> Jared Still
>> Certifiable Oracle DBA and Part Time Perl Evangelist
>> Principal Consultant at Pythian
>> Oracle ACE Alumni
>> Pythian Blog http://www.pythian.com/blog/author/still/
>> Github: https://github.com/jkstill
>>
>>
>>

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