Introduction
Many times I have been asked how to determine the actual
access paths used by active SQL in the database. In the past we had to use
EXPLAIN PLAN, TKPROF and various scripts or procedures to get the explain plan
for a given statement and then use that to determine access paths. The problem
being that due to the way the SQL was injected into the database (manually, via
JAVA, C or PL/SQL for example) the actual execution plan could differ from what
was show via a manual explain plan. In Oracle9i that all changed with the
V$SQL_PLAN dynamic performance view.
Using the V$SQL_PLAN Dynamic Performance View
The V$SQL_PLAN DPV provides a single place to go and
retrieve information about the execution plans currently being utilized by the
database. For each active SQL in the shared pool, there will be a stored plan
in the V$SQL_PLAN table.
The structure of the V$SQL_PLAN DPV is nearly identical to
that of the PLAN_TABLE used for EXPLAIN PLAN and TKPROF plan display, with the
exception that the V$SQL_PLAN DPV is automatically populated and de-populated
as SQL is placed in the shared pool and aged from it. This allows real-time
viewing of how objects are currently being accessed in the database by current
SQL code.
Querying the V$SQL_PLAN DPV
For individual SQL statements, if you know their SQL
identifier, you can utilize the DBMS_XPLAN package to access their plan data in
the DPV as a standard format explain plan. In versions prior to 10g (9i,
version 9.2.08 and greater to be exact) you had to create a view to allow
DBMS_XPLAN to access the data in the DPV:
create view seeplan as
select sp.*,
to_char(hash_value) statement_id,
sysdate timestamp
from v$sql_plan sp;
Once the view exists any plan can be retrieved when you know its STATEMENT_ID:
SQL> select * from table(DBMS_XPLAN.DISPLAY('SEEPLAN','1683243911'));
In Oracle 10g and above use the DBMS_XPLAN.DISPLAY_CURSOR
with the SQL_ID and child number to get the data directly form the DPV.
However, what if we want summaries of the data?
Summarizing the Data in V$SQL_PLAN
Very often I want to know what objects are using full scans
and a few other access plans that can be detrimental to performance. Now I
could go through the plans one by one in the V$SQL_PLAN DPV, but why when
writing a query that does the work for you is so much easier! Look at the
following SQLPLUS report:
rem fts report
rem based on
V$SQL_PLAN table
col operation format a13
col object_name format a32
col object_owner
format a10
col options format a15
col executions format 999,999,999
col fts_meg format 999,999.999
column dt new_value
today noprint
select
to_char(sysdate,'ddmonyyyyhh24miss') dt from dual;
set pages 55 lines
132 trims on
ttitle 'Full
Table/Index Scans'
spool
fts&&today
select
a.hash_value,a.object_owner,a.object_name,
rtrim(a.operation) operation,
a.options,
sum(b.executions) executions, c.bytes,
(sum(b.executions)*c.bytes)/(1024*1024)
fts_meg
from
v$sql_Plan a, v$sqlarea b, dba_segments c
where
(a.object_owner=c.owner and
a.object_name=c.segment_name)
and a.address=b.address
and a.operation
IN ('TABLE ACCESS','INDEX')
and nvl(a.options,'NULL')
in ('FULL','NULL','FULL SCAN')
and a.object_owner
not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS','DBSNMP')
and b.executions>1
group by
a.hash_value, a.object_owner, a.object_name,
operation, options, c.bytes
order by
a.object_owner,a.object_name,operation, options, executions desc
/
spool off
set pages 20
ttitle off
The report generates an output similar to the following:
Wed Jan 21
page 1
Full Table/Index
Scans
HASH_VALUE OBJECT_OWN
OBJECT_NAME OPERATION OPTIONS
EXECUTIONS BYTES
FTS_MEG
---------- ----------
---------------------- ------------ --------- ------------ -------- ----------
3156122473 EXFSYS RLM$JOINQKEY INDEX FULL SCAN 2
65536 .125
2786539032 FLOWS_0300
WWV_FLOW_MAIL_QUEUE_PK INDEX FULL
SCAN 4 65536 .250
4239944566 TPCC C_ORDER TABLE ACCESS FULL 1,590 19922944 30,210.000
1311609328 TPCC C_ORDER TABLE ACCESS FULL 226 19922944 4,294.000
The report clearly shows what objects are seeing full scan
activity, the SQL responsible (at least its hash value) and the expected
megabyte impact on the database cache area. By using the hash value the SQL can
be quickly retrieved from either the V$SQLTEXT or the V$SQL_PLAN DPVs and
tuning decisions made.
Retrieving the SQL allows you to see what other objects are
involved, the WHERE clauses and allows you to then retrieve the objects indexes
to see if perhaps a better index can be constructed to eliminate any non-needed
full table scans.
