 |
|
Using the v$sql_plan and
the v$sql_workarea views
Oracle Tips by Burleson
|
Starting in Oracle, Oracle offers an exciting
set of new dictionary views. The v$sql_plan view keeps track of
all SQL statements that are in the library cache, and the
v$sql_workarea gives important statistics about the behavior of
SQL statements.
The information in v$sql_workarea
provides valuable information about the amount of internal Oracle
resources that are consumed by a query. The following code finds the
top 20 work areas requiring most cache memory. For more details on
this technique, see Chapter 9.
top_work_areas.sql
select
*
from
( select
workarea_address,
operation_type,
policy,
estimated_optimal_size
from
v$sql_workarea
order by
estimated_optimal_size
)
where
rownum < 20;
The following example finds the cursors with one or more
work areas that have been executed in one or more passes. This often
indicates a RAM memory shortage within the Oracle SGA.
multipass_work.sql
select
sql_text,
sum(onepass_executions),
sum(multipass_executions)
from
v$sql s,
v$sql_workarea wa
where
s.address = wa.address
group by
sql_text
having
sum(onepass_executions + multipass_executions) > 0;
To locate a specific SQL statement, you can run the
get_sql.sql script, specifying the name of the table you are
seeking:
get_sql.sql
set lines 2000;
spool sql_list.lst
select
address,
sql_text,
from
v$sqlarea
where
upper(sql_text) like '%MY_TABLE_NAME%'
order by
disk_reads desc
;
spool off
Once you have gathered the address of the SQL statement
that you want to investigate, the following query can be executed to
get the details from the v$views. The following SQL statement will
retrieve the execution plan for the SQL: and also display information
about the status of the SQL work areas.
exp_work.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='xxxxxx';
Here is a sample listing from this script. Note that in
addition to the execution plan, we also see the amount of RAM work
area associated with each operation.
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 is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
