Re: ORA-4031

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