Hi,

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.

Thanks,

Vishnu

Hi JC

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!

Thanks

N Kandasamy

Hi

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.

Thanks!

Look at the note Kanda -Oracle suggested:

Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

Under Section: Section 5: Release-Specific Database Initialization Parameters for  Oracle 12c Release 1.  There is a Removal list:

Go through the list. It is different for both 11g and 12c. Look up the parameters for each.

5.2 Parameter Removal List for Oracle Database 12c Release 1

hash_area_size

java_pool_size

job_queue_interval

large_pool_size

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  )

You can't use huge pages if you use amm.

Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)

Requirements

• 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: Automatic Memory Management (AMM) on 11g & 12c  (Doc ID 443746.1)