Oracle Metric Memory Usage Percent Oracle Tips by Burleson Consulting

The Memory Usage Percent Oracle metric is the physical memory usage percentage for the given process group.  Memory usage percent can also be measured externally (globally) with vmstat, top and glance.  You can also see the amount of RAM used by specific Oracle operations (hash joins) in Oracle9i and Oracle 10g.

Viewing RAM for execution plan operations in Oracle

select
address
from
v$sql
where
sql_text like ‘%NEW_CUSTOMER’;

88BB460C

1 row selected.

Now that we have the address, we can plug it into the following script to get the execution plan details and the PGA memory usage for the SQL statement.


select
   operation,
   options,
   object_name name,
   trunc(bytes/1024/1024) "input(MB)",
   trunc(last_memory_used/1024) last_mem,
   trunc(estimated_optimal_size/1024) opt_mem,
   trunc(estimated_onepass_size/1024) onepass_mem,
   decode(optimal_executions, null, null,
   optimal_executions||'/'||onepass_executions||'/'||
   multipasses_exections) "O/1/M"
from
code depot for full script
   v$sql_plan p,
   v$sql_workarea w
where
   p.address=w.address(+)
and
   p.hash_value=w.hash_value(+)
and
  p.id=w.operation_id(+)
and
  p.address='88BB460C';
 

Here is the listing from this script, showing RAM usage for each execution component:

OPERATION OPTIONS NAME input(MB) LAST_MEM OPT_MEM ONEPASS_MEM O/1/M
------------ -------- ---- --------- -------- ---------- ---------- -
SELECT STATE
SORT GROUP BY 4582 8 16 16 26/0/0
HASH JOIN SEMI 4582 5976 5194 2187 16/0/0
TABLE ACCESS FULL ORDERS 51
TABLE ACCESS FUL LINEITEM 1000

  
This, and many other Oracle performance metrics are discussed in my book "Oracle Tuning" by Rampant TechPress.  You can buy it directly from the publisher and save 30% at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm