Re: flush share pool for one sql to force new plan

On Feb 22, 1:13 pm, "Ben" <bal..._at_comcast.net> wrote:
> On Feb 22, 12:24 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > Yes, on 9i, plans are invalidated when statistics are collected for
> > objects that are referenced in the plans.
>
> > How do you know that Oracle is not generating a new plan, and still
> > deciding that a full table scan costs less than an index lookup? How
> > you checked a 10053 trace of the session?
>
> > You can force Oracle to reparse a query by adding a space, or by
> > changing the capitalization of one or more characters in the SQL
> > statement. You can force Oracle to use an index by using a hint, but
> > it would probably be best to determine why Oracle believes that an
> > index lookup is more expensive.
>
> > What have you set for optimizer_features_enable and compatible? Have
> > you explicitly disabled bind variable peeking?
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> I guess I should have been a little more clear in my explanation. The
> statement in question is called from our ERP system and I can't add a
> space or change capitalization of the statement. It's always going to
> be the same select with a bind variable.
>
> Select *>From schema.table
>
> where col1 = :key
>
> if I use explain plan this is what I get before and after the analyze.
>
> BEFORE
>
> SQL> explain plan for
> 2 select *
> 3 from proddta.f47027
> 4 where szedsp = ' ';
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> --------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 134K| 156M| 10291 |
> | 1 | TABLE ACCESS FULL | F47027 | 134K| 156M| 10291 |
> --------------------------------------------------------------------
>
> SQL> explain plan for
> 2 select *
> 3 from proddta.f47027
> 4 where szedsp = :key1;
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> --------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 134K| 156M| 10291 |
> | 1 | TABLE ACCESS FULL | F47027 | 134K| 156M| 10291 |
> --------------------------------------------------------------------
>
> AFTER
>
> SQL> explain plan for
> 2 select *
> 3 from proddta.f47027
> 4 where szedsp = ' ';
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> ---------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost |
> ---------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 458 | 548K|
> 104 |
> | 1 | TABLE ACCESS BY INDEX ROWID| F47027 | 458 | 548K|
> 104 |
> | 2 | INDEX RANGE SCAN | F47027_11 | 458 |
> | 5 |
> ---------------------------------------------------------------------------
>
> SQL> explain plan for
> 2 select *
> 3 from proddta.f47027
> 4 where szedsp = :key1;
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> --------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 134K| 156M| 10291 |
> | 1 | TABLE ACCESS FULL | F47027 | 134K| 156M| 10291 |
> --------------------------------------------------------------------
>
> our optimizer_features_enable is set to 9.2.0 but our compatible
> parameter is still sitting on 8.1.0 does that invalidate bind
> peeking?- Hide quoted text -
>

I was prepared to state that having compatible set at 8.1.0 will affect bind variable peeking - when I was running 8.1.7.3, compatible was at first set to 8.0.5, and I was not able to access a couple features that required compatible to be at least 8.1.7.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10265835652030 "compatible only affects 'disk based structures' really -- it makes it so that if we come up with some new format for persisting data -- we won't use it (cause you cannot downgrade if we did). features like bind variable peeking will work."

"explain plan can lie, especially with bind variable peeking."

Even though the SQL statement is being executed by an ERP system, you should still be able to execute the SQL statement using SQLPlus, and generate 10046 and 10053 traces. The 10046 AND 10053 traces will show the actual execution plan, and a 10053 trace should show any bind variables that were peeked during a hard parse.

I am not certain if Oracle 9.2 supports this syntax, but you should be able to execute something like the following after executing a query (no need to execute EXPLAIN PLAN):
SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); On Oracle 10.2.0.2, if statistics_level is set to ALL, the output might look like this for a SQL statement: | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |

|*  1 |  FILTER 	      |      |	    1 |        |     12 |00:00:00.01
|	    14 |       |       |	  |
|   2 |   MERGE JOIN CARTESIAN|      |	    1 |     36 |     12 |
00:00:00.01 |	    14 |       |       |	  |
|*  3 |    VIEW 	      |      |	    1 |      6 |      2 |00:00:00.01
|	     7 |       |       |	  |
|   4 |     COUNT	      |      |	    1 |        |      6 |00:00:00.01
|	     7 |       |       |	  |
|   5 |      TABLE ACCESS FULL| T2   |	    1 |      6 |      6 |
00:00:00.01 |	     7 |       |       |	  |
|   6 |    BUFFER SORT	      |      |	    2 |      6 |     12 |
00:00:00.01 |	     7 |  9216 |  9216 |     1/0/0|
|   7 |     TABLE ACCESS FULL | T1   |	    1 |      6 |      6 |
00:00:00.01 |	     7 |       |       |	  |

Note that both estimated and actual rows are returned.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Feb 22 2007 - 13:04:34 CST