How to set the ASH buffer size

How to set the ASH buffer size

Ash Buffer is a memory area used in a loop in SGA to store the active session information captured by MMON. MMNL dumps the content of Ash buffer to AWR every 60 minutes after filtering, the corresponding view is DBA_HIST_ACTIVE_SESS_HISTORY to free up the Ash buffer space for subsequent collection. The size of the Ash Buffer is determined when the instance is started and cannot be dynamically adjusted during the instance running. What determines the size of the Ash Buffer?
The following table describes the size of the ASH buffer MOS 243132.1.
Size of ASH Circular Buffer = Max [Min [# CPUs * 2 MB, 5% of Shared Pool Size, 30 MB], 1 MB]

### View the parameters related to ash buffer in the actual environment
SQL> show parameter cpu_count

NAME TYPE VALUE
-----------------------------------------------------------------------------
Cpu_count integer 16

SQL> show parameter shared_pool_size
NAME TYPE VALUE
-----------------------------------------------------------------------------
Shared_pool_size big integer 1312 M

--- Calculate the size of the ash buffer.
Max [Min [16*2 MB, 5% * 1312 M, 30 MB], 1 MB] = 30 MB

--- The actual Ash buffer size can be obtained from v $ sgastat and v $ ash_info, both of which are 32 MB. The difference is slightly different from the formula. Select 32 MB as the Ash Buffer.
Select * from v $ sgastat where name = 'ash Buffers ';
POOL NAME BYTES
------------------------------------------------
Shared pool ASH buffers 33554432

Select total_size, fixed_size, sampling_interval from v $ ash_info;
TOTAL_SIZE FIXED_SIZE SAMPLING_INTERVAL
-------------------------------------
33554432 33554432 1000

### Change cpu_count
Alter system set cpu_count = 8 scope = spfile;

Startup force

Show parameter cpu_count
NAME TYPE VALUE
-----------------------------------------------------------------------------
Cpu_count integer 8

--- Calculated based on the formula Size of ASH Circular Buffer = Max [Min [# CPUs * 2 MB, 5% of Shared Pool Size, 30 MB], 1 MB]
Ash buffer = Max [Min [8*2 MB, 5% * 1312 M, 30 MB], 1 MB] = 16 MB

--- The following query results fulfill the calculation above
Select total_size, fixed_size, sampling_interval from v $ ash_info;
TOTAL_SIZE FIXED_SIZE SAMPLING_INTERVAL
-------------------------------------
16777216 16777216 1000

Select * from v $ sgastat where name = 'ash Buffers ';
POOL NAME BYTES
------------------------------------------------
Shared pool ASH buffers 16777216

### "_ Ash_size" implicit parameter control the ash buffer size
Formula Size of ASH Circular Buffer = Max [Min [# CPUs * 2 MB, 5% of Shared Pool Size, 30 MB], 1 MB] the default value of _ ash_size is 1 MB.
--- _ Ash_size the default value is 1 MB.
Select ksppinm, ksppstvl from x $ ksppi x, x $ ksppcv y where (x. indx = y. indx) and ksppinm like '_ ash_size %'

KSPPINM KSPPSTVL
------------------------------------------------------------
_ Ash_size 1048618

--- Change _ ash_size to 64 MB.
Alter system set "_ ash_size" = 67108864 scope = spfile;

Startup force

--- Calculate the Ash buffer value at this time.
ASH Circular Buffer = Max [Min [8*2 MB, 5% * 1315 M, 30 MB], 64 MB] = 64 MB

--- The following query results fulfill the calculation above
SQL> select total_size, fixed_size, sampling_interval from v $ ash_info;

TOTAL_SIZE FIXED_SIZE SAMPLING_INTERVAL
-------------------------------------
67108864 67108864 1000

SQL> select * from v $ sgastat where name = 'ash Buffers ';

POOL NAME BYTES
------------------------------------------------
Shared pool ASH buffers 67108864

Therefore, the formula for calculating the Ash Buffer can be changed
Size of ASH Circular Buffer = Max [minute [# CPUs * 2 MB, 5% of Shared Pool Size, 30 MB], _ ash_size]
To adjust the ash buffer Size, modify the _ ash_size parameter and make sure that _ ash_size> Min [# CPUs * 2 MB, 5% of Shared Pool Size, 30 MB]

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian