In this example, the value of the PGA_AGGREGATE_TARGET parameter should be set to at least 500 MB, and as close to 3 GB as possible. The following query shows the percentage of times work areas are executed in optimal, one-pass, or multi-pass size since startup. The V$SQL_WORKAREA_HISTOGRAM view shows the number of work areas executed with optimal, one-pass, and multi-pass memory size since instance startup. Only a server process can access the PGA. In determining the sessions and processes to abort or terminate, Oracle Database treats parallel queries as a single unit. The available work area sizes include: Optimal size is when the size of a work area is large enough that it can accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. 8.0 2,741,061.8 272,980.1 91.0 0, ------------------------------------------------------------------------. 1.2 2,741,061.8 294,467.8 90.0 0, 2,008 The cache hit percentage is calculated using the following formula: In this example, the cache hit percentage is 99.03%. A system configured with a reasonable amount of PGA memory should not need to perform multiple passes over the input data. By default, Oracle Database uses 20% of the SGA size for this value. Therefore, the cache hit percentage is almost 100%, because the extra pass over 1 MB represents a tiny overhead. In Example 16–8, if you have 10 GB to dedicate to PGA, set the value of the PGA_AGGREGATE_TARGET parameter to 3 GB and dedicate the remaining 7 GB to the SGA. In Figure 16-1, this happens when PGA_AGGREGATE_TARGET reaches 3 GB. 0.1 2,741,061.8 1,671,995.0 62.0 42,214, 359 By default, PGA memory management is enabled. How to move datafile to another location? Example 16-6 Querying the V$SQL_WORKAREA View. Filed under DB The default value is 20% of the SGA size. Oracle Audit Vault and Database Firewall come in different installation media. May 31, 2011 For example, if an Oracle database instance is configured to run on a system with 4 GB of physical memory, and if 80% (or 3.2 GB) of the memory is dedicated to the Oracle database instance, then initially set PGA_AGGREGATE_TARGET to 640 MB for an OLTP system, or 1,600 MB for a DSS system. If one of the small sorts runs one-pass, an extra pass over 1 MB of input data is performed. This is due to an increase in the number of optimal or one-pass work areas and a decrease in the number of multi-pass executions. Change ), You are commenting using your Twitter account. DBAs often debate whether they should use Oracle Scheduler or the Linux/Unix cron utility for scheduling and automating tasks. In this case, Oracle Database cannot honor the PGA_AGGREGATE_TARGET value, and extra PGA memory must be allocated. This statistic indicates the number of bytes processed by memory-intensive SQL operators since instance startup. To set the initial value for PGA_AGGREGATE_TARGET: Determine how much of the total physical memory to reserve for the operating system and other non-Oracle applications running on the same system. Typically, some work areas run one-pass or even multi-pass, depending on the overall size of the PGA memory. This statistic indicates how much allocated PGA memory can be freed. 6.0 2,741,061.8 272,980.1 91.0 0, 11,472 Therefore, any query executed against the V$SQL_WORKAREA_HISTOGRAM view can be used on this view, with an additional predicate to select the desired value of the PGA_AGGREGATE_TARGET parameter. This value reflects that only one of the small sort operations performed an extra pass, while all other sort operations were able to run in optimal size. The answer depends on how much of total memory (SGA+PGA) can be allocated for this database instance on the machine, taking into account memory needs of other database instances on the same machine, non-Oracle software and the OS itself. For backward compatibility, automatic PGA memory management can be disabled by setting the value of the PGA_AGGREGATE_TARGET initialization parameter to 0. For each server process there is one PGA allocated. The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. PGA is a memory region, that contains data and control information for a server process. This reduces the cache hit percentage in proportion to the size of the input data and the number of extra passes performed. This tutorial provides a guideline for tuning the value of the PGA_AGGREGATE_TARGET parameter using the various views discussed in this chapter. Too much memory allocated wastes memory, and too less memory allocated causes possible performance issues in the Oracle environment. This area indicates that the value of the PGA_AGGREGATE_TARGET parameter is too small to meet the minimum PGA memory requirements. Statistics are segmented by work area size, because the performance impact of running a work area in optimal, one-pass or multi-pass size depends mainly on the size of the work area. When sizing the work area, the goal is to have most work areas running with optimal size (more than 90%, or even 100% for pure OLTP systems), and only a small number of them running with one-pass size (less than 10%). Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. Multi-pass size is when the size of the work area is below the one-pass threshold and multiple passes over the input data are needed. The simulation process happens in the background and continuously updates the workload history to produce the simulation result. Example 16-3 Querying the V$SQL_WORKAREA_HISTOGRAM View: Non-Empty Buckets. - Norton Juster, The Phantom Tollbooth Oracle has provided views to assist in managing the SGA and PGA settings when you’re not using Automatic Memory Management (AMM) and a view to manage memory_target when you are using AMM. Let’s see the total picture, how are they distributed: select b.name,sum(a.value) value This statistic shows the maximum size of a work area executed in automatic mode. The V$PGA_TARGET_ADVICE view predicts how changing the value of the PGA_AGGREGATE_TARGET initialization parameter will affect the following statistics in the V$PGASTAT view: Figure 16-1 illustrates how the result of the this query can be plotted. Ensure that enough PGA memory remains for work areas running in automatic mode. When the work area is deallocated—or when the execution of its associated SQL operator is complete—it is automatically removed from this view. ( Log Out / It is therefore meaningless to set a value of the PGA_AGGREGATE_TARGET parameter in that zone. If over-allocation occurs, increase the value of the PGA_AGGREGATE_TARGET parameter using the information provided by the V$PGA_TARGET_ADVICE view, as described in "Using the V$PGA_TARGET_ADVICE View". In automatic PGA memory management mode, Oracle Database attempts to adhere to the PGA_AGGREGATE_TARGET value by dynamically controlling the amount of PGA memory allotted to work areas. Example 16-1 shows how extra passes affect the cache hit percentage metric. In the example output this happens at value 99,935.0. Over-allocating PGA memory can happen if the PGA_AGGREGATE_TARGET value is too small to accommodate the PGA other component and the minimum memory required to execute the work area workload. For information about setting this parameter, see "Setting the Initial Value for PGA_AGGREGATE_TARGET". Oracle Database enables you to control and tune the sizes of work areas. Oracle Database automatically derives the total amount of PGA memory available to active work areas from the PGA_AGGREGATE_TARGET initialization parameter. In this example, 3 GB is the optimal value for the PGA_AGGREGATE_TARGET parameter. MULTIPASSES_EXECUTIONS-number of times, this work area used temporary tablespace in multiple times to get it finished. Oracle Database then assigns the resulting PGA memory to individual active work areas based on their specific memory requirements. For complex queries (such as decision support queries), a big portion of the run-time area is dedicated to work areas allocated by memory intensive operators, including: Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions, Write buffers used by bulk load operations. However, this setting may be too low for a large DSS system. This metric is computed by Oracle Database to reflect the performance of the PGA memory component. Example 16-4 Querying the V$SQL_WORKAREA_HISTOGRAM View: Percent Optimal. 1. v$PGASTAT –displays PGA memory usage statistics. Linkedin: https://www.linkedin.com/in/mariami-kupatadze-01074722/. Run a representative workload on the database instance and monitor its performance, as described in "Monitoring Automatic PGA Memory Management". col estd_pga_cache_hit_percentage format 999.99 heading "Estd PGA|Hit Pct" col estd_overalloc_count format 999,999 heading "Estd|Overalloc" set pagesize 1000 set lines 100 set echo on SQL>SELECT ROUND(pga_target_for_estimate / 1048576) pga_target_mb, Oracle recommends initially dedicating 50% of the available memory to the PGA, and 50% to the SGA. If the work area is less than 40 MB, then the sort operation must perform several passes over the input data. group by b.name; The result above shows that PGA size is not properly set because number of onepass and multipass are considerable. If this value is small compared to the PGA_AGGREGATE_TARGET value, then most of PGA memory is used by other system components (such as PL/SQL or Java) and little is left for work areas. The values of the PGA_AGGREGATE_TARGET parameter are derived from fractions and multiples of its current value to assess possible higher and lower values. In this example, the output shows that increasing the value of the PGA_AGGREGATE_TARGET parameter by a factor of 2 will enable all work areas under 16 MB to execute in optimal size. and b.name like '%workarea executions – %' Example 16-8 shows a query of the V$SYSSTAT view that displays the total number and the percentage of times work areas were executed in these three sizes since the instance was started: In this example, the output shows that 5,395 work area executions (or 95%) were executed in optimal size, and 284 work area executions (or 5%) were executed in one-pass size. When you go down or up the advisory section from the row with 'Size Factr' = 1.0, you get estimates for Disk usage - column 'Estd Extra W/A MB Read/Written to Disk ' - for bigger or smaller settings of PGA_AGGREGATE_TARGET.