Automatic memory management

3.6 Automatic memory management

Queries used in business intelligence applications typically need a significant amount of memory for sorts, hash joins, bitmap operations, and so on. There are various initialization parameters, such as SORT_AREA_SIZE, HASH_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, and BITMAP_ MERGE_AREA_SIZE, that must be tuned to get good performance. The ideal values for these parameters may vary from query to query and may depend on the load on the system. It is too time consuming for a DBA to tune these parameters accurately.

Oracle 9i introduced the automatic memory management feature to ease this burden. With this feature, the DBA only needs to specify the total amount of memory available for the instance by setting the PGA_ AGGREGATE_TARGET parameter. The optimizer determines the best way to utilize it for various SQL operations across all the database server processes running on that instance. To enable automatic memory management the initialization parameter WORKAREA_SIZE_POLICY must be set to AUTO (setting it to MANUAL will revert back to manual memory management).

The amount of memory allocated and used by each process can be seen in the V$PROCESS view, as follows:

 select spid, pga_used_mem, pga_alloc_mem, pga_max_mem from v$process; SPID         PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM ------------ ------------ ------------- ----------- 340                132552        198880      198880 341                135056       3371624     3371624 343               4349880       7570468     7570468 ... 

The work area sizes used by each SQL statement are available in the V$SQL_WORKAREA. You can join to V$SQL to get the sql_text for the statements. (We have edited the following output to show only part of the sql_text for lack of space.)

 SELECT sql_text, operation_type,        estimated_optimal_size estsize, last_memory_used FROM v$sql_workarea w, v$sql s WHERE w.address = s.address AND parsing_schema_id = USERENV('SCHEMAID') ; SQL_TEXT        OPERATION_TYPE   ESTSIZE LAST_MEMORY_USED --------------- --------------   ------- ---------------- SELECT t.month, GROUP BY (SORT)    56320            49152 SELECT t.month, HASH-JOIN         874496           628736 ... 

You can monitor the work area size in use while the query is executing in the V$SQL_WORKAREA_ACTIVE view, as follows:

 -- shows the current workarea usage during an execution SELECT operation_type, work_area_size, expected_size,        actual_mem_used FROM v$sql_workarea_active; OPERATION_TYPE WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED -------------- -------------- ------------- --------------- HASH-JOIN             1087488       1086464          628736