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

From: Ben <balvey_at_comcast.net>
Date: 22 Feb 2007 10:13:25 -0800
Message-ID: <1172168005.499691.192440@v45g2000cwv.googlegroups.com>

On Feb 22, 12:24 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 22, 11:58 am, "Ben" <bal..._at_comcast.net> wrote:
>
> > Do plans get invalidated when statistics are gathered? If so then CBO
> > should have peeked at this statement and seen that it was using ' '
> > and started using the index.
>
> > If I delete the stats on that column, then that will also delete the
> > histogram on that column, no? I am trying to achieve the use of the
> > histogram so that my selects that bind on that column actually use the
> > index.- Hide quoted text -
>
> > - Show quoted text -
>
> 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? Received on Thu Feb 22 2007 - 12:13:25 CST