Re: Error related to hash memory

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