Re: ORA-4031
Date: Wed, 23 Oct 2024 09:29:37 +0000 (UTC)
Message-ID: <1261383966.8515726.1729675777104_at_mail.yahoo.com>
Thanks to Stefan and Laurentiu for their suggestions.Stefan pointed also to his presentations: http://soocs.de/public/talk/161115_DOAG2016_Hacking_Oracles_Memory_About_Internals_Troubleshooting_PPT.pdf http://soocs.de/public/talk/161115_DOAG2016_Hacking_Oracles_Memory_About_Internals_Troubleshooting_PDF.pdfÂ
Best Regards,Petr
Am Mittwoch, 23. Oktober 2024 um 08:40:12 MESZ hat Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> Folgendes geschrieben:
understood, so this suggests your query is the victim of a high level of fragmentation ... in theory. So probably you need to focus on why you get to this point. There is a very good oracle note:
| ORA-04031 Memory Errors with Argument KGLH0^ (Doc ID 2096561.1) |
contains detailed tracing (using oradebug) , I remember it helped me understand a strange library cache issue a long time ago, have a look maybe will help you. În mar., 22 oct. 2024 la 12:23, Petr Novak <novak_petr_at_yahoo.de> a scris:
Complete message:
ORA-04031: 56 Byte des Shared Memorys konnten nichtzugewiesen werden ("shared pool","selecto.owner#,o.name,o.nam...","SQLA^2f3140c8","idndef*[]:qkexrPackName")
Â
Best Regards,Petr
Am Dienstag, 22. Oktober 2024 um 10:46:30 MESZ hat Petr Novak <dmarc-noreply_at_freelists.org> Folgendes geschrieben:
Hallo Laurentiu,
correct, important, I forgot it - 56 Bytes
Best Regards,Petr
Am Dienstag, 22. Oktober 2024 um 10:42:27 MESZ hat Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> Folgendes geschrieben:
Might be good to add some details about the error, to know how big was the allocation that failed then you can check if you had a free chunk with this size. A probable workaround for this is to tune SHARED_POOL_RESERVED_SIZE for your specific DB.
ÃŽn mar., 22 oct. 2024 la 10:27, Petr Novak <dmarc-noreply_at_freelists.org> a scris:
Hallo,
19.19. multitenant RAC DB, 2 nodes, one PDB.
DB ParameterÂ
NAME              SID       MB------------------------------ ---------- --------db_cache_size         *      61.440pga_aggregate_target      *      30.720sga_target           *      122.880shared_pool_size        *      16.384We have got ORA-4031. There was 10G memory free in 7 subpools, but one subpool was very fragmented.
==============================================TOP 20 MEMORY USES ACROSS SGA HEAP 1 - 7----------------------------------------------"free memory          "  10 GB 31%"SQLA            0003" 6504 MB 19%"KGLH0           0003" 3825 MB 11%"gcs resources       0001" 3174 MB 9%"gcs shadows        0001" 1827 MB 5%"KGLHD           0003"  837 MB 2%"gc name table       0001"  640 MB 2%"SQLA            0001"  532 MB 2%"init_heap_kfsg       0001"  515 MB 2%"db_block_hash_buckets   0001"  512 MB 1%"KGLDA           0003"  365 MB 1%"KJSC rnb slots       0003"  278 MB 1%"KQR X SO          0003"  277 MB 1%"gcs dynamic resources   0001"  247 MB 1%"KQR X PO          0003"  242 MB 1%"ksunfy_meta 1       0001"  230 MB 1%"KGLH0           0001"  202 MB 1%"gcs dynamic resources for 0001"  195 MB 1%"ASH buffers        0001"  192 MB 1%"SO private sga       0001"  162 MB 0%TOTALS ---------------------------------------Total free memory          10 GBTotal memory alloc.         23 GBGrand total             34 GBFree memory Chunks Subpools 1 bis 7
   Allocation Name         Size     Max Size  Chunks___________________________    _____________  __________ ______"free memory          "  1670295552  1955931976   300"free memory          "  2664376384  2703709512 572103 - 2540M free Memory in 572103 Chunks "free memory          "   900934224  1442204728   319"free memory          "   750861976  1165225320   197"free memory          "  1463963256  1709569760   174"free memory          "  1738843384  1856281472  22979"free memory          "  1813593240  1813675120 147735Before the error, the DB did not try to reduce the DB cache and increase shared pool. Hard Parses are very moderate, about 150 Hard Parses per hour in average.Is the some possibility to reduce such fragmentation , except flushing whole shared pool ?Is the DB usually able internally to coalesce such fragmentation ? Patch , hidden parameter ? Best Regards,Petr
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 23 2024 - 11:29:37 CEST