Thread: same explain plan but different response time between two days

Thanks Charles, I am using Release 10.2.0.4.0 I am using explain plan for (query goes here.....) and select * from table(dbms_xplan.display) to get the explain plan. Actually the query is runing fast. I will trace it with 10046 level 12 when it will performs badly Regards Mohamed

The EXPLAIN PLAN FOR ... syntax may also display inaccurate execution plans. Bind variable peeking does not take place with EXPLAIN PLANs, while it does take place during a normal query parse and execute in 9i and above. Additionally, you could find that all bind variables are treated as VARCHAR2 in an EXPLAIN PLAN. If I recall correctly, EXPLAIN PLAN FOR should (could?) trigger a hard parse of the SQL statement rather than reusing the...

Oracle's bind variable peeking may be at play, here. Have a Google for it - there's good stuff on Jonathan Lewis' site about it. An example: the first query to run one day uses search value "USA" for a query - later, "DCG" is used as the search value. The first query is a lucky one: it leads to an index being used, as "USA" appears very infrequently in the table being queried, and a good plan is...

I think you have answered your own question :- "The only differences are in the numbers of rows, bytes, costs and a new column named TempSpc has been added into the new explain plan." Do all tables and indexes have stats ? Did the two statements use the same bind variables, or different ? E.g select count(*) from employee where gender = &input1; The explain plan may be the same for MALE and FEMALE, but the row counts may be very...

Hi all, I prefer to retreive the execution plan with execution stats. Thus you will be able to compare the estimated rows with the number of rows really processed. ex: SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last')); But you have to add the hint /*+ gather_plan_statistics */ into the sql statement or to set the TIMED_STATISTICS parameter to ALL.

Mohamed Houri wrote: Yes the two statements use the same bind variables. This morning I run the query and it tooks less than 1 seconde to complete. I have just go the time to post my thread and now the same query with the same bind variables are runing very slowly (about 5 minutes to complete). The explain plans of the two statements are now exactly the same. Charles, and others, have pointed out the problems of using "explain plan"...