Donald K. Burleson Oracle Tips

Viewing RAM memory usage for specific SQL statements in Oracle

Oracle now has the ability to display RAM memory usage along with execution plan information.  To get this information you need to gather the address of the desired SQL statement from the v$sql view.  For example, if you have a query that operates against the NEW_CUSTOMER table, you can run the following query to get the address:

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
   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.

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                                     

Here we see the details about the execution plan along with specific memory usage details. This is an exciting new advance in Oracle and gives the Oracle DBA the ability to have a very high level of detail about the internal execution of any SQL statement.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

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