By: Juan Carlos Olamendy Turruellas
This is the second article from a series where I’ll be talking about tuning Oracle instances in order to maintain an adequate performance regarding to the established SLA and avoiding the system degradation.
When I talk about performance, I refer specifically to have an adequate response time and throughput (of course, depending of the SLA) by using efficiently the computing resources: RAM, CPU and storage.
So, we’re going to talk about allocating the computing resources affecting the efficiency of the database system.
Oracle database tries to hold data, parsed SQL statement and metadata in memory as much time as possible because it’s faster to get data from memory rather than disk storage. We can easily adjust the allocation of memory for an instance using the initialization parameter MEMORY_TARGET. The underlying value must be adjusted during the system operation. If there is no sufficient memory room, the instance must do costly I/O operations on the storage, but if there is much memory allocated than necessary, then it’s a waste of money. It’s remarkable to say that the node/server must have sufficient physical memory (MEMORY_TARGET should be at least 75% of the system RAM leaving the remaining 25% to the OS) in order to avoiding the swapping operations done by the OS. Today, the best strategy is to set parameter MEMORY_TARGET, so Oracle database can automatically manage the SGA.
One important component of memory management in Oracle databases is the PGA. The PGA is the memory area allocated for each server process serving to client requests. This is a private area (no shared) created when the server process starts and it’s automatically released at the end of the session. The PGA is used mainly for memory-intensive tasks such as sorting, joining, grouping and rollup.
The strategy to use in recent version of Oracle databases is to manage automatically the PGA. We can set the PGA_AGGREGATE_TARGET initialization parameter to automatically manage this work-area memory and the V$PGA_TARGET_ADVICE and V$PGASTAT views to tune/change the value appropriately.
The PGA_AGGREGATE_TARGET initialization parameter (in the init.ora file) sets the maximum limit on the total memory allocated to the PGA, that is, the sum of all individual PGA for each server process. The vendor recommends the following values:
It’s remarkable to say that when we set a value for the SGA_TARGET, then Oracle database takes this value from the actual OS memory as soon as it starts while when we set the value for the PGA_AGGREGATE_TARGET, then Oracle database doesn’t take this value from the OS memory at starting because PGA_AGGREGATE_TARGET is just an upper bound of the total work-area memory of all server processes combined that can be allocated.
We can see the value of the actual PGA_AGGREGATE_TARGET as shown below in the listing 01.
Now we can tune the PGA_AGGREGATE_TARGET value using the V$PGA_TARGET_ ADVICE view. Oracle database populates this view with the result of simulations of different workloads at different PGA values. We can query this view as shown below in the listing 02.
We can monitor the performance of the PGA using the V$PGASTAT view as show below in the listing 03.
The first row (aggregate PGA target parameter) indicates the current value of PGA_AGGREGATE_TARGET parameter. In this case is around 200 MB.
The fifth row (total PGA allocated) indicates the current amount of PGA memory allocated by the Oracle instance. In this case is around 45 MB. The Oracle instance tries to keep this number less than the PGA_AGGREGATE_TARGET value. However, if the work-area workload is increasing rapidly or the PGA_AGGREGATE_TARGET parameter is set to a value that is too low, it is possible for the PGA allocated to exceed this value by a small percentage and for a short time.
And the row (cache hit percentage) is very important because it reflects the performance of the PGA memory component. A value of 100% means that all work areas executed by the system since instance startup are using an optimal amount of PGA memory. Typically, some work-areas run one-pass or even multi-pass, depending on the overall size of the PGA memory. When a work-area cannot run optimally, one or more extra passes are performed over the input data. This reduces the cache hit percentage in proportion to the size of the input data and the number of extra passes performed. In this example, we have 100% cache hit ratio, which is the ideal in order to obtain the best performance of the system.
Another way is to monitor the V$SQL_WORKAREA_HISTOGRAM view. This view contains statistics about how many work-areas have been executed with optimal (without interacting with temporary tablespace), one-pass (need to interact with the temporary tablespace once to get finished) and multi-pass (need to interact with the temporary tablespace more than once to get finished) memory size since the Oracle instance has started. The work-areas are divided into groups, whose optimal requirement varies from 0KB to 1KB, 1KB to 2KB, 2KB to 4KB—and so on. Each group is identified by the values of the LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE columns.
For example, a sorting operation that requires 3MB of memory to run, then the statistics about this work-area is placed in the group: LOW_OPTIMAL_SIZE is 2097152 (2 MB) and HIGH_OPTIMAL_SIZE is 4194303 (4 MB minus 1 byte).
We can see the statistics as shown below in the listing 04.
We can see in this example that all the operations requiring the PGA (such as the sorting operations) are done optimally. That’s why the cache hit ratio is 100%. This Oracle database instance doesn’t use the temporary tablespace for sorting data, and in other words, work-areas are sized optimally. If we find a lot of values in the “>1 Pass” column, then we need to adjust the PGA size.
We can query the V$PROCESS view to monitor the PGA usage for each background processes, specifically what PGA memory in use, how much has been allocated and maximum memory to allocate. The following query shows the information as shown below in the listing 05.
Finally, we can adjust/tune the PGA by consulting the V$PGA_TARGET_ADVICE view. This view offers advices on what would happen if the PGA was increased or decreased in size by looking at the PGA statistics since the instance was last started.
The query is shown below in the listing 06.
We can see that we don’t have to do anything because cache hit ratio is 100% and 0 over-allocation for the current PGA setting which is 100 MB.
In this second article, I’ve talked about tuning Oracle instances in particular about tuning the PGA. Now you can apply these knowledge and techniques to your own Oracle databases.