[oracle-l] Re: Trace output

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 23 Jan 2004 05:40:49 -0700
Message-Id: <6.0.0.22.2.20040123052931.02bc5860@pop.centrexcc.com>

Even in Oracle 8 the two plans can differ since the explain plan is done at the time you run tkprof. By then things may have changed in the database. One thing you have to be careful with is testing the effectiveness of a new index. Imagine the following scenario:

1. You run a sql with sql_trace on
2. You create a new index
3. you run the sql again with sql_trace on and the new index present - in a

new session to get a separate trace
4. you run tkprof for the two trace files with explain=user/password. The explain plan within tkprof will see the index in both executions and will display the resulting plan in both output. It superimposes the actual rowcounts from the trace onto the theoretical plan which of course make no sense at all then.

Bottom line - ONLY trust the plan with the heading "Row Source Operation"

At 01:39 AM 1/23/2004, you wrote:

>Satheesh,
>
>"Row source operation" is the run time execution plan. The "execution
>plan" is added when you run tkprof and you use parameter
>"explain=user/password Connect to ORACLE and issue EXPLAIN PLAN" and
>this is "theoretical plan". In version 9.2 those two plans can differ. The
>reason for that is how CBO deals with bind variables in explain plan and
>when statement is optimized for real execution. For explain plan you don't
>need to supply any values for bind variables. But when CBO prepares the
>runtime execution plan (here comes the quote from Database Performance
>Tuning Guide and Reference 9.2)
>
><quote> it peeks at the values of user-defined bind variables on the first
>invocation of a cursor. This feature lets the optimizer determine the
>selectivity of any WHERE clause condition, as well as if literals have
>been used instead of bind variables. On subsequent invocations of the
>cursor, no peeking takes place, and the cursor is shared, based on the
>standard cursor-sharing criteria, even if subsequent invocations use
>different bind values.
>
></quote>
>So in "Row source operation" you see the actual plan while in explain plan
>you see only theoretical plan that doesn't take into account the values
>for bind variables. That's the reason why those two plans can differ.
>
>There are many cases when this can cause problems because the existing
>plan is not optimal for subsequent execution.
>
>Regards, Joze

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com Received on Fri Jan 23 2004 - 06:40:49 CST