Re: ORA-4031

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 nicht zugewiesen werden
> ("shared pool","select o.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.440
> pga_aggregate_target * 30.720
> sga_target * 122.880
> shared_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 GB
> Total memory alloc. 23 GB
> Grand 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