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
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.
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.
s.address = wa.address
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
set lines 2000;
upper(sql_text) like '%MY_TABLE_NAME%'
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.
decode(optimal_executions, null, null,
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
------------ -------- ---- --------- --------
---------- ---------- ----
SORT GROUP BY 4582
8 16 16 26/0/0
HASH JOIN SEMI 4582
5976 5194 2187 16/0/0
TABLE ACCESS FULL ORDERS
TABLE ACCESS FUL LINEITEM
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.