| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: flush share pool for one sql to force new plan
On Feb 22, 2:04 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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:1...
> "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.- Hide quoted text -
>
> - Show quoted text -
After viewing the link from asktom, I put together my own test with
sql_trace=true and then using sqlplus setting the bind variable and
then tkprof to see what I get.
I'm not for certain but it looks to me like it generated two plans.
SQL> alter session set sql_trace=true;
Session altered.
SQL> set autotrace traceonly statistics
SQL> exec :key1 := ' '
PL/SQL procedure successfully completed.
SQL> select * from proddta.f47027 where szedsp = :key1;
no rows selected
Statistics
0 recursive calls
0 db block gets
141 consistent gets
0 physical reads
0 redo size
6397 bytes sent via SQL*Net to client
232 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> exec :key1 := 'Y'
PL/SQL procedure successfully completed.
SQL> select * from proddta.f47027 where szedsp = :key1;
41891 rows selected.
Statistics
0 recursive calls
0 db block gets
14987 consistent gets
10003 physical reads
0 redo size
9887130 bytes sent via SQL*Net to client
19817 bytes received via SQL*Net from client
2794 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
41891 rows processed
SQL> set autotrace off
Session altered.
And here is the tkprof output....
select *
from
proddta.f47027 where szedsp = :key1
call count cpu elapsed disk query current rows
Parse 2 0.01 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2795 3.81 42.80 10003 15128 0 41891
total 2799 3.82 42.81 10003 15128 0 41891
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID F47027
0 INDEX RANGE SCAN F47027_11 (object id 744882)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'F47027'
********************************************************************************
I'm a little confused here as to why the Row Source Operation shows an index scan and the Execution Plan shows a fts. Received on Thu Feb 22 2007 - 14:06:05 CST
 |
 |