I will take them as 3 different question. Lets see:
Which portion in AWR report will show that SGA or memory allocation in our database is good, or enough, or correct?
-- You need to refer to the "SGA target advisory" section or you can simply run the Memory Advisor manually any time. Based on the values you need to decide the improvement of performance. For e.g. if 1.25 SGA shows less Physical reads, then you need to definitely increase. Similarly for higher values too.
Can AWR show which hour of the day was there peak usage in Memory/CPU in our EBS database?
-- No. Usually most DBAs know it but you can start comparing the DB times for hourly AWR or use SYSSTAT related views.
Is there an estimate or recommended size for SGA/Memory for EBS R12?
-- It purely depends on the environment and each environment differs. The answer for 1st question applies here as well.
You can go through (Doc ID 396009.1) and go to the last page.
Section 9: Database Initialization Parameter Sizing
Example It says 500 users x SGA size.
It just give you a ballpark figure.
Hope this helps!
To answer you may go through "SGA Target Advisory" (AWR report).
1. To start with.... You can go through (Doc ID 396009.1) and Section 9: Database Initialization Parameter Sizing
2. To tune further.... You may go through "SGA Target Advisory" (AWR report)
>> If the "Est DB Time (s)" decreases significantly as the "Size Factor" increases then increasing the SGA will significantly reduce the physical reads and improve performance.
Look at the note Kanda -Oracle suggested:
Go through the list. It is different for both 11g and 12c. Look up the parameters for each.
There is also a bde script you can run but it is basic and the results may be lacking. it's best to check the note.
bde_chk_cbo.sql - EBS initialization parameters - Healthcheck ( Doc ID 174605.1 )
Our database is 12c,
Is bde_chk_cbo.sql - EBS initialization parameters - Healthcheck still applicable in 12c?
We have EBS Linux Server with 32Gb and 8 CPU cores.
Both apps & db are in one node.
Can you share me your existing SGA parameter size for similar config with 100 concurrent users (or any number of users).
So I can use it as template model for reference.
Is below memory sizing still applicable in 12c? Or should I use the automatic memory allocation?
Is AMM recommended ins EBS? I have not seen docs that recommend AMM in EBS.
What is the command again to check if we enabled or disabled AMM?
Which is better to use ? disabled AMM or enabled AMM?
Have you used AMM in your EBS instance?
You can't use huge pages if you use amm.
- Oracle Database instance(s) are up and running
- Oracle Database 11g Automatic Memory Management (AMM) is not setup (See Note 749851.1)
- The shared memory segments can be listed by command "ipcs -m"
- Oracle Linux
- Package 'bc' installed
To check if you have it set,
check for the db parameters:
MEMORY_MAX_TARGET and MEMORY_TARGET
show parameter memory_target
if it = 0 then it's not set.
Here is a note: