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@xxxxxxxxx> 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@xxxxxxxxx> 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) |
===========================================================================================================================================================================================================================================================
