Error related to hash memory
From: Lok P <loknath.73_at_gmail.com>
Date: Wed, 1 Dec 2021 17:38:48 +0530
Message-ID: <CAKna9VaPSZv-w0t-E7ThdgX2WOFQoUsNZd5gi+cPAhqpoFj_BQ_at_mail.gmail.com>
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.
ORA-32690: Hash Table Infrastructure ran out of memory
| 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% |
| Name | Type | Server# | Elapsed | Cpu | IO |
Application | Concurrency | Cluster | Buffer | Read | Read | Write |
| Id | Operation | Name
Date: Wed, 1 Dec 2021 17:38:48 +0530
Message-ID: <CAKna9VaPSZv-w0t-E7ThdgX2WOFQoUsNZd5gi+cPAhqpoFj_BQ_at_mail.gmail.com>
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-lReceived on Wed Dec 01 2021 - 13:08:48 CET