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
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'
a.options, sum(b.executions) executions, c.bytes,
v$sql_Plan a, v$sqlarea b, dba_segments c
(a.object_owner=c.owner and a.object_name=c.segment_name)
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')
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
set pages 20
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.