I think you can safely set SGA_MAX_SIZE high as the unused parts will be paged.
Thing is I'm currently at 56 GB / 54 GB, so I have to resize the `SGA_MAX_SIZE` anyway. But yes, I'd then increase the `SGA_TARGET` in small steps and see what happens.
it's a lot if that 1/10th is accessed much more frequently than the other 80% — but the target advice is only an estimate. You can bump it up by a smaller amount and see what gain you get, then rinse/repeat until you've got all the low hanging fruit.
So the instance is caching a approx. 1/20th of the Oracle instance in SGA (currently) which will increase to 1/10th after I change the SGA parameter.
SQL> show parameters pga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 22272M pga_aggregate_target big integer 11136M SQL>`
If the DB_TIME_FACTOR is only marginal, then I think it probably isn't related to I/O (speed-wise)
Time factor is minimal, yes, but the Physical_Reads gains is down to 1/10 th of the original reads, which is pretty impressive IMO
aren't you looking at a very marginal gain if you do? (`ESTD_DB_TIME_FACTOR=.9912`)
The instance (oracle.exe) is currently using 58 GB total RAM according to Task Manager.
SQL> select * from v$sga_target_advice order by sga_size; SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE ---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- 6912 .125 45077321 .9867 494965089 49408 6144 13824 .25 46123506 1.0096 916823834 9728 2688 20736 .375 46022999 1.0074 835055602 14592 4992 27648 .5 45808280 1.0027 604253381 24320 2816 34560 .625 45758027 1.0016 565250132 29184 4992 41472 .75 45735184 1.0011 526444869 38912 2816 48384 .875 45703205 1.0004 511744406 43776 4992 55296 1 45684931 1 494965089 49408 6144 62208 1.125 45652952 .9993 461950918 58368 4992 69120 1.25 45575287 .9976 373352167 68096 3072 76032 1.375 45502191 .996 308363250 72960 5376 82944 1.5 45378842 .9933 172693320 82688 3072 89856 1.625 45301178 .9916 101269857 87552 5376 96768 1.75 45282904 .9912 66424315 92416 6144 103680 1.875 45282904 .9912 43903403 97280 6144 110592 2 45282904 .9912 43903403 97280 6144 16 rows selected. SQL> show parameters sga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ allow_group_access_to_sga boolean FALSE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 56G sga_min_size big integer 0 sga_target big integer 54G unified_audit_sga_queue_size integer 1048576 SQL>
so it might be a choioce between a bigger block cache and a bigger working area for hash joins for example
It's basically a balance between PGA_AGGREGATE_TARGET and SGA_TARGET to fit in your physical memory
Yes the docs aren't much use, but in fairness, I'm not sure there are any useful general rules — it all depends on your workload.
I'm searching for hints on when to resize the SGA parameters. The `V$SGA_TARGET_ADVICE` is a start, but I was hoping for more information from Oracle. However, they only provide generic formulas and some generalised explanations.
Ah, but I'm not doing AMM I'm doing ASMM where you set `SGA_MAX_SIZE` and `SGA_TARGET`. `MEMORY_TARGET` and `MEMORY_MAX_TARGET` have been set to `0`. We had issues with **AMM** so we switched to **ASMM**.
> [You cannot enable automatic memory management if the LOCK_SGA initialization parameter is TRUE](https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN11199)
Is `lock_sga` a good option? Seems to be the equivalent of SQL Server's `lock page in memory` setting.
_When configuring memory, size the memory caches appropriately based on the application's needs._
Does anybody have any good sources for SGA ASMM parameter tuning? The Oracle documentation isn't providing heaps of motivational insights.
> The [following commands](https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat) for SQL Server 2019 points to the RHEL 8 repository. RHEL 8 does not come preinstalled with python2, which is required by SQL Server. Before you begin the SQL Server install steps, execute the command and verify that python2 is selected as the interpreter 🤮