Re: ORA-4031

From: Petr Novak <"Petr>
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.384

We 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 GB

Free 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  147735

Before 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-l

Received on Wed Oct 23 2024 - 11:29:37 CEST