Thanks a lot for your response on this.
vaibhav kansara, November 29, 2021 - 10:32 am UTC
Hello,
Thanks for your response on this.
I'm a little bit new to Oracle so could you please tell me what does columns from the table SGA_TARGET_ADVICE signify ?
And how does that help me determine how much sga size I shoud assign to my DB ?
Below is the sample output from one of my database :
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
18560 .125 4797623 8.8786 82376952 13824 2560 0
27840 .1875 600986 1.1122 82376952 13824 10752 0
37120 .25 554461 1.0261 25397336 27648 7680 0
46400 .3125 553327 1.024 22865771 41472 4608 0
55680 .375 552408 1.0223 22865771 41472 9216 0
64960 .4375 547275 1.0128 16544948 55296 7680 0
74240 .5 540790 1.0008 8088875 69120 6144 0
83520 .5625 540358 1 8088875 69120 7680 0
92800 .625 540358 1 8088066 82944 7680 0
102080 .6875 540358 1 8088066 96768 7680 0
111360 .75 540358 1 8088066 96768 7680 0
120640 .8125 540358 1 8088066 110592 7680 0
December 01, 2021 - 3:02 am UTC
The two ones easiest to focus on are:
ESTD_DB_TIME_FACTOR
ESTD_PHYSICAL_READS
The advisory is saying that with an sga size factor of 0.5625 (ie almost half of your existing) will still result in a DB Time of 1, ie, you would not see any degradation in DB time (ie active work on the database) even if the SGA was ~50% of its current size.
Similarly with physical reads. We're saying that you won't see a jump in physical reads (from its current 8088066 ) until you shrink the SGA to 43% (0.4375) of its current size.
So in a nutshell - your SGA is plenty big enough, and you could even release up to half of it to other services on that server without getting impacted.
But remember - these are advisories - in a production scenario, you would slowly ease back on the allocation and monitor again. Similarly, if nothing else on the server needs that memory, it wont really hurt to have an excess.
The advisor knows too.
Andrew, November 29, 2021 - 9:06 pm UTC
I find this script also to be helpful in getting an idea of how much SGA the database wants. This script is easily modified for PGA, as well.
COLUMN finding_time FOR a20 HEAD 'Finding Time'
COLUMN current_target_mb FOR 999,999 HEAD 'Current SGA|Target (MB)'
COLUMN advised_target_mb FOR 999,999 HEAD 'Advised SGA|Target (MB)'
BREAK ON REPORT
COMPUTE maximum LABEL 'Recommended Min. SGA' OF advised_target_mb ON report
SELECT TO_CHAR( al.execution_start, 'dd Mon yyyy HH24:MI:SS') AS finding_time,
aa.num_attr1/1048576 AS current_target_mb,
aa.num_attr2/1048576 AS advised_target_mb
FROM dba_advisor_actions aa,
dba_advisor_findings af,
dba_advisor_log al
WHERE 1=1
AND al.owner = af.owner
AND al.task_name = af.task_name
AND aa.owner = af.owner
AND aa.task_name = af.task_name
AND aa.execution_name = af.execution_name
AND af.finding_name = 'Undersized SGA'
AND aa.attr1 = 'sga_target'
ORDER
BY al.execution_start
;
clear computes
December 01, 2021 - 3:03 am UTC
Nice input!
great response
vaibhav kansara, December 01, 2021 - 6:54 am UTC
Thanks guys,
I'm glad I learned something new today on this topic. I appreciate you taking your time to answer this.
December 01, 2021 - 8:09 am UTC
glad we could help
Which one give correct PGA in usage for using alerting through OEM
ArunB, February 09, 2023 - 10:00 pm UTC
February 28, 2023 - 1:57 am UTC
I've asked around internally without much success (and also not managed to reproduce this on any of my own databases)
I think the best way forward is log an SR