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