| |
 |
|
Oracle Tips by Burleson Consulting
|
Viewing RAM memory usage for
specific SQL statements
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.
plan_mem.sql
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.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
