I took over an instance of Oracle that is running on an Azure VM, there have been some issues with reported “slowness” I want to verify the memory has been configured correctly, both within the instance and the server itself, below are the vitals:
OS - Windows Server 2019 Datacenter
Installed RAM: 32 GB
Virtual Memory: No paging file set on data drive or C:\ drive, however ~10 GB has been enabled on another drive.
SGA & PGA values:
SHOW SGA;
Total System Global Area 17179865192 bytes
Fixed Size 23294056 bytes
Variable Size 2751463424 bytes
Database Buffers 14394851328 bytes
Redo Buffers 10256384 bytes
SELECT name,value/1024/1024 "SGA (MB)" FROM v$sga;
NAME SGA (MB)
-------------------- ----------
Fixed Size 22.2149429
Variable Size 2624
Database Buffers 13728
Redo Buffers 9.78125
SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga;
TOTAL SGA (MB) 16383.9962
SELECT name,value/1024/1024 "SGA (MB)" FROM v$sga;
pga_aggregate_target integer 2750414848
SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga;
pga_aggregate_limit integer 9594470400
Are these configs optimal for this setup? I'm curious about the SGA/PGA, how much should be left for the windows OS at 32 GB of RAM and what about “virtual memory” on the data drive itself.