ORA-4031
Date: Tue, 22 Oct 2024 07:26:29 +0000 (UTC)
Message-ID: <1206106421.2282055.1729581989488_at_mail.yahoo.com>
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-lReceived on Tue Oct 22 2024 - 09:26:29 CEST