Wednesday, July 25, 2012

Getting the True Access Path in Oracle


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



  rtrim(a.operation) operation,

  a.options, sum(b.executions) executions, c.bytes,  

  (sum(b.executions)*c.bytes)/(1024*1024) fts_meg


    v$sql_Plan a, v$sqlarea b, dba_segments c


      (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


---------- ---------- ---------------------- ------------ --------- ------------ -------- ----------

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.