This page provides a list of best practices when optimizing an Oracle database with Akamas.
This section provides some guidelines on the most relevant memory-related parameters and how to configure them to perform a high-level optimization of a generic Oracle Database instance.
Oracle DBAs can choose, depending on their needs or expertise, the desired level of granularity when configuring the memory allocated to the database areas and components, and let the Oracle instance automatically manage the lower layers. In the same way, Akamas can tune a target instance with different levels of granularity.
In particular, we can configure an Akamas study so that it simply tunes the overall memory of the instance, leaving Oracle automatically manage how to allocate it between shared memory (SGA) and program memory (PGA); alternatively, we can tune the target values of both of these areas and let Oracle take care of their components, or go even deeper and have total control of the sizing of every single component.
Notice: running the queries in this guide requires a user with the ALTER SYSTEM, SELECT ON V_$PARAMETER, and SELECT ON V_$OSSTAT privileges
Also notice that to define the domain of some of the parameters you need to know the physical memory of the instance. You can find the value in MiB running the query select round(value/1024/1024)||'M' "physical_memory" from v$osstat where stat_name='PHYSICAL_MEMORY_BYTES'. Otherwise, if you have access to the underlying machine, you can run the bash command free -m
This is the simplest of the memory-optimization set of parameters, where the study configures only the overall memory available for the instance and lets Oracle’s Automatic Memory Management (AMM) dynamically assign the space to the SGA and PGA. This is useful for simple studies where you want to minimize the overall used memory, usually coupled with constraints to make sure the performances of the overall system remain within acceptable values.
memory_target: this parameter specifies the total memory used by the Oracle instance.
When AMM is enabled can find the default value with the query select display_value "memory_target" from v$parameter where name='memory_target'. Otherwise, you can get an estimate summing the configured SGA size found running select display_value "sga_target" from v$parameter where name LIKE 'sga_target' and the size of the PGA found with select ceil(value/1024/1024)||'M' "physical_memory" from v$pgastat where name='maximum PGA allocated'.
The explored domain strongly depends on your application and hardware, but an acceptable range goes from 152M (the minimum configurable value) to the physical size of your instance. Over time, Akamas will learn to avoid automatically the configuration with not-enough memory.
To configure the Automatic Memory Management you also need to make sure that the parameters sga_target and pga_aggregate_limit are set to 0 by configuring them among the default values of a study, or manually running the configuration queries.
The following snippet shows the parameter selection to tune the total memory of the instance. The domain is configured to go from the minimum value to the maximum physical memory (7609M in our example).
With the following set of parameters, Akamas tunes the individual sizes of the SGA and PGA, letting Oracle’s Automatic Shared Memory Management (ASMM) dynamically size their underlying SGA components. You can leverage these parameters for studies where, like the previous scenario, you want to find the configuration with the lowest memory allocation that still performs within your SLOs. Another possible scenario is to find the balance in the allocation of the memory available that best fits your optimization goals.
sga_target: this parameter specifies the target SGA size.
When ASMM is configured, you can find the default value with the query select display_value "sga_target" from v$parameter where name='sga_size'.
The explored domain strongly depends on your application and hardware, but an acceptable range goes from 64M (the minimum configurable value) to the physical size of your instance minus a reasonable size for the PGA (usually up to 80% of physical memory).
pga_aggregate_target: this parameter specifies the target PGA size.
You can find the default value with the query select display_value "pga_aggregate_target" from v$parameter where name='pga_aggregate_target'.
The explored domain strongly depends on your application and hardware, but an acceptable range goes from 10M (the minimum configurable value) to the physical size of your instance minus a reasonable size for the SGA.
To tune the SGA and PGA, you also must set the memory_target to 0 to disable AMM by configuring them among the default values of a study, or manually running the configuration queries.
ASMM will dynamically tune all the SGA components whose size is not specified, so set to 0 all the parameters (db_cache_size, log_buffer, java_pool_size, large_pool_size, shared_pool_size, and streams_pool_size) unless you have any specific requirements.
The following snippet shows the parameter selection to tune both SGA and PGA sizes. Each parameter is configured to go from the minimum value to 90% of the maximum physical memory (6848M in our example), allowing Akamas to explore all the possible ways to partition the space between the two areas and find the best configuration for our use case:
The following code snippet forces Akamas to explore configuration spaces where the total memory, expressed in MiB, does not exceed the total memory available (7609M in our example). This allows speeding up the optimization avoiding configurations that won’t work correctly.
With the following set of parameters, Akamas tunes the space allocated to one or more of the components that make the System Global Area, along with the size of the Program Global Area size. This scenario is useful for studies where you want to find the memory distribution that best fits your optimization goals.
pga_aggregate_target: this parameter specifies the size of the PGA.
You can find the default value with the query select display_value "pga_aggregate_target" from v$parameter where name='pga_aggregate_target'.
The explored domain strongly depends on your application and hardware, but an acceptable range goes from 10M (the minimum configurable value) to the physical size of your instance.
db_cache_size: this parameter specifies the size of the default buffer pool.
You can find the default value with the query select * from v$sgainfo where name='Buffer Cache Size'.
log_buffer: this parameter specifies the size of the log buffer.
You can find the default value with the query select * from v$sgainfo where name='Redo Buffers'.
java_pool_size: this parameter specifies the size of the java pool.
You can find the default value with the query select * from v$sgainfo where name='Java Pool Size'.
large_pool_size: this parameter specifies the size of the large pool.
You can find the default value with the query select * from v$sgainfo where name='Large Pool Size'.
streams_pool_size: this parameter specifies the size of the streams pool.
You can find the default value with the query select * from v$sgainfo where name='Streams Pool Size'.
shared_pool_size: this parameter specifies the size of the shared pool.
You can find the default value with the query select * from v$sgainfo where name='Shared Pool Size'.
The explored domains of the SGA components strongly depend on your application and hardware; an approach is to scale both up and down the baseline value by a reasonable factor to define the domain boundaries (eg: from 20% to 500% of the baseline).
To tune all the components set both the memory_target and sga_target parameters to 0 by configuring them among the default values of a study, or manually running the configuration queries.
Notice: if your system leverages non-standard block-size buffers you should consider tuning also the db_Nk_cache_size parameters.
The following snippet shows the parameter selection to tune the size of the PGA and the SGA components. The PGA parameter is configured to go from the minimum value to 90% of the maximum physical memory (6848M in our example), while the domains for the SGA components are configured scaling their default value by approximatively a factor of 10. Along with the constraint defined below, these domains give Akamas great flexibility while exploring how to distribute the available memory space:
The following code snippet forces Akamas to explore configuration spaces where the total memory, expressed in MiB, does not exceed the total memory available (7609M in our example).
You should also add to the equation any db_Nk_cache_size tuned in the study.
In this section, some guidelines on how to choose optimization parameters are provided for the following specific technologies:
These guidelines also provide an example of how to approach the selection of parameters (and how to define the associated domains and constraints) in an optimization study.
When starting a new Kubernetes optimization, the following is a list of recommended parameters. After having selected the parameters, always make sure to add/review the corresponding parameter domains and constraints based on your environment. Please refer to the optimization pack reference page for more information.
cpu_request
cpu_limit
memory_request
memory_limit
j9vm_minFreeHeap
j9vm_maxFreeHeap
j9vm_minHeapSize
j9vm_maxHeapSize
j9vm_gcCompact
j9vm_gcThreads
j9vm_gcPolicy
j9vm_codeCacheTotal
j9vm_compilationThreads
j9vm_aggressiveOpts
The following describes how to approach tuning JVM in the following areas:
The most relevant JVM parameters are the ones defining the boundaries of the allocated heap (j9vm_minHeapSize, j9vm_maxHeapSize). The upper bound to configure for this domain strongly depends on the memory in megabytes available on the host instance or on how much we are willing to allocate, while the lower bound depends on the minimum requirements to run the application.
The free heap parameters (j9vm_minFreeHeap, j9vm_maxFreeHeap) define some boundaries for the free space target ratio, which impacts the trigger thresholds of the garbage collector. The suggested starting ranges are from 0.1 and 0.6 for the minimum free ratio range, and from 0.3 to 0.9 for the maximum.
The following represents a sample snippet of the section parametersSelection in the study definition:
It is also recommended to define the following constraints:
min heap size lower than or equal to the max heap size:
upper bound to be at least 5 percentage points higher than the lower bound
The following JVM parameters define the behavior of the garbage collector:
j9vm_gcPolicy
j9vm_gcThreads
j9vm_gcCompact
The garbage collection policy (j9vm_gcPolicy) defines the collection strategy used by the JVM. This parameter is key for the performance of the application: the default garbage collector (gencon) is the best solution for most scenarios, but some specific kinds of applications may benefit from one of the alternative options.
The number of GC threads (j9vm_gcThreads) defines the level of parallelism available to the collector. This value can range from 1 to the maximum number of CPUs that are available or we are willing to allocate.
The GC compaction (j9vm_gcCompact) selects if garbage collections perform full compactions always, never, or based on internal policies.
The following represents a sample snippet of the section parametersSelection in the study definition:
The following JVM parameters define the behaviors of the compilation:
j9vm_compilationThreads
j9vm_codeCacheTotal
The compilation threads parameter (j9vm_compilationThreads) defines the number available for the JIT compiler. Its range depends on the available CPUs.
The code cache parameter (j9vm_codeCacheTotal) defines the maximum size limit for the JIT code cache. Higher values may benefit complex server-type applications, at the expense of the memory footprint, so should be taken into account in the overall memory requirements.
The following represents a sample snippet of the section parametersSelection in the study definition:
The following JVM parameter defines the behavior of aggressive optimization:
j9vm_aggressiveOpts
Aggressive optimizations (j9vm_aggressiveOpts) enables some experimental features that usually lead to performance gains.
The following represents a sample snippet of the section parametersSelection in the study definition:
pg_max_connections
Keep its value under 1000 connections.
pg_effective_cache_size
75% of physical available memory to PostgreSQL.
pg_maintenance_work_mem
12% of physical available memory to PostgreSQL.
pg_max_wal_senders
Max replicas you expect to have, doubled.
pg_max_parallel_workers
Number of cores divided by 2.
pg_shared_buffers
25% of physical available memory to PostgreSQL.
When starting a new JVM optimization, the following is a list of recommended parameters. After having selected the parameters, always make sure to add/review the corresponding parameter domains and constraints based on your environment. Please refer to the optimization pack reference page for more information.
jvm_maxHeapSize
jvm_minHeapSize
jvm_gcType
jvm_newSize
jvm_survivorRatio
jvm_maxTenuringThreshold
jvm_parallelGCThreads
jvm_concurrentGCThreads
jvm_maxInlineSize
jvm_inlineSmallCode
jvm_useTransparentHugePages
jvm_alwaysPreTouch
When starting a new JVM optimization, the following is a list of recommended parameters to include in your study:
jvm_gcType
jvm_maxHeapSize
jvm_newSize
jvm_survivorRatio
jvm_maxTenuringThreshold
jvm_parallelGCThreads
jvm_concurrentGCThreads