Re: Error related to hash memory

Trying to understand the possible options we have here. Will the "sort group by" in place of 'hash group by" by forcing through hints - "no_use_hash_aggregation" will make the query run longer? Trying to understand the logic behind the failure, can we do anything wrt parallelism, say currently we are using Parallel-4, will increasing it to '6' will make the query finish with a "hash group by" without failure?

On Wed, Dec 1, 2021 at 5:58 PM 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) |
>>
>> ===========================================================================================================================================================================================================================================================
>>
>

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