Databases - TopAnswers

Andriy M

Morning

John aka hot2use

hi

kus

hi guys :)

FoggyFinder replying to Anonymous 1846

hi

Anonymous 1846

hi

Josh Darnell

Morning

Andriy M

Morning

Josh Darnell

Morning

John aka hot2use

Morning

Jack Douglas replying to John aka hot2use

I think you can safely set SGA_MAX_SIZE high as the unused parts will be paged.

John aka hot2use

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.

Jack Douglas replying to John aka hot2use

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.

John aka hot2use

_Which in hindsight isn't that much..._

John aka hot2use

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.

John aka hot2use

And the Oracle Database (instance) is nearing 1 TB

John aka hot2use

I think that 22 GB should be enough (20% of SGA)

John aka hot2use

    SQL> show parameters pga;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    pga_aggregate_limit                  big integer 22272M
    pga_aggregate_target                 big integer 11136M
    SQL>`

John aka hot2use

If the DB_TIME_FACTOR is only marginal, then I think it probably isn't related to I/O  (speed-wise)

John aka hot2use

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

Jack Douglas

is IO the real bottleneck?

Jack Douglas

is there any pressure on the PGA?

Jack Douglas replying to John aka hot2use

aren't you looking at a very marginal gain if you do? (`ESTD_DB_TIME_FACTOR=.9912`)

John aka hot2use

_did I mention it was running on Windows Server?_

John aka hot2use

The instance (oracle.exe) is currently using 58 GB total RAM according to Task Manager.

John aka hot2use

Give it enough room to play.

John aka hot2use

Thinking of taking the instance to 128 GB `SGA_MAX_SIZE`.

John aka hot2use

    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>

John aka hot2use replying to Jack Douglas

2 Socket 32 Core CPU (per Socket = total 64 cores)

John aka hot2use

It's physical hardware with 768 GB RAM but for multiple instances.

John aka hot2use replying to Jack Douglas

Hi Jack. Nope standard OLTP

Jack Douglas

is it a data warehouse?

Jack Douglas

so it might be a choioce between a bigger block cache and a bigger working area for hash joins for example

Jack Douglas

It's basically a balance between PGA_AGGREGATE_TARGET and SGA_TARGET to fit in your physical memory

Jack Douglas replying to John aka hot2use

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.

John aka hot2use replying to Jack Douglas

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.

John aka hot2use

12.2.2

John aka hot2use replying to Jack Douglas

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**.

Josh Darnell replying to PeterVandivier

Haha yeah, that's pretty gross.

PeterVandivier replying to Josh Darnell

Yesssssssssss

Josh Darnell replying to PeterVandivier

Is that reaction because of the Python *version*?

Jack Douglas

what are you trying to tune for exactly?

Jack Douglas replying to John aka hot2use

> [You cannot enable automatic memory management if the LOCK_SGA initialization parameter is TRUE](https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN11199)

John aka hot2use

Where's Jack when you need one?

John aka hot2use

Is `lock_sga` a good option?  Seems to be the equivalent of SQL Server's `lock page in memory`  setting.

John aka hot2use

Indeed?

John aka hot2use

_When configuring memory, size the memory caches appropriately based on the application's needs._

John aka hot2use

Does anybody have any good sources for SGA ASMM parameter tuning? The Oracle documentation isn't providing heaps of motivational insights.

John aka hot2use

good morning

PeterVandivier

(┛ಸ_ಸ)┛彡┻━┻

PeterVandivier

> 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
    
🤮