Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Explain Plan vs Actual Execution Plan

RE: Explain Plan vs Actual Execution Plan

From: Cary Millsap <>
Date: Thu, 20 Mar 2003 15:08:50 -0600
Message-Id: <>

I think you can trust the STAT lines in a SQL trace file to give you accurate information about what execution plan *did* take place. The test I would suggest is to compare the information in these lines to what EXPLAIN PLAN shows you. You'll need to look at the raw trace data, though, because tkprof doesn't always convert STAT lines into a correct execution plan.  

Cary Millsap
Hotsos Enterprises, Ltd.

Upcoming events:
- Hotsos Clinic 101, Apr 8-10 Chicago

-----Original Message-----
From: [] On Behalf Of Odland, Brad Sent: Thursday, March 20, 2003 2:24 PM
To: Multiple recipients of list ORACLE-L Subject: Explain Plan vs Actual Execution Plan


Heres a question to ponder. While tuning a SQL statment for a user I noticed that the explain plan from SQL Analyze was not the same plan that was found when I used OEM Top Sessions ( upon executing to collect execution stats. Database is on HP/UX 11 version Oracle 8.1.7

The stats were not "stale" yet. Monitoring is on for the tables in the query. The query would actually never return. I suspected that the stats were a bit off so I ran new ones and then SQL Analyze displayed a different explain plan and the plan reteived from top sessions while the SQL was running matched.

My question is is the Explain Plan and estimate or is the actual plan. I suspect that when an explain plan is created it uses statistics and the optimizer to determine the estimated plan and cost. However when the SQL is actually executed I suspect that a different plan may be generated as actual execution begins....or am I just wacked.

Either way the statistics when run created a proper plan that worked fine. But I wonder why the difference in plans...

Brad O.


Please see the official ORACLE-L FAQ:

Author: Odland, Brad

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Mar 20 2003 - 15:08:50 CST

Original text of this message