Re: ORA-4031

Petr,

there's no way to check CDB level parameters from a PDB connection as far as I know. The values you see are set on the PDB level. So I think you should unset at least shared_pool_size in the PDB and hope for the better.
I've seen more than a few PDBs run without ORA-4031 after such a simple change.

On Thu, Oct 24, 2024 at 2:12 PM Petr Novak <novak_petr_at_yahoo.de> wrote:

>
> Hallo Timur,
> thanks for the link, sorry for the delay. I have only access to the PDB,
> values in my first mail were extracted there.
> I tried to answer your question, but in the PDB I am not able find out if
> some parameter is inherited from CDB or was defined only in PDB or has
> different value than CDB. It is possible ?
>
> Best Regards,
> Petr
> Am Mittwoch, 23. Oktober 2024 um 11:31:15 MESZ hat Timur Akhmadeev <
> timur.akhmadeev_at_gmail.com> Folgendes geschrieben:
>
>
> Hi,
>
> How is memory set up on the PDB level? Check this note
> https://blog.go-faster.co.uk/2024/04/sgapdbcdb.html
>
> On Tue, Oct 22, 2024 at 9:28 AM Petr Novak <dmarc-noreply_at_freelists.org>
> wrote:
>
> 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
>
>
>
>
>
> --
> Regards
> Timur Akhmadeev
>

-- 
Regards
Timur Akhmadeev

--
http://www.freelists.org/webpage/oracle-l