Re: ORA-4031

Hello Petr,

> Is the DB usually able internally to coalesce such fragmentation ?

Yes, as soon as the heap manager detaches some objects (from LRU) and marks them as free, it also checks if the newly freed chunks have adjacent free chunks. If so, it coalesces them before attaching them to free lists.

> Is the some possibility to reduce such fragmentation

Finding out what causes these allocations. It should be relatively easy to determine (with SystemTap or eBPF) if they all have the same chunk size.

In general, are you sure that you are not using durations as well?

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: www.soocs.de
Twitter: _at_OracleSK

> Petr Novak <dmarc-noreply_at_freelists.org> hat am 22.10.2024 09:26 CEST geschrieben:
>
>
> 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