execution plan does not change after gathering stats
627138Sep 19 2010 — edited Mar 17 2011hi all
A SQL executed poorly because the execution plan is not good, I gathered the statistics of the table in question to expect that ORACLE can produce a better exctuion plan by using DBMS_STATS.GATHER_TABLE_STATS with 5 sample percent. But the exection plan is not changed until I flush the share pool.
As far as i know,gathering the statistics about a table will invalidate all the depentent objects,like curosrs,so the ORACLE will re-produce the execution plan by using the new statsticsts gathered. In my case, this is not true,
the sql still use the old execution plan instead of producing a new execution plan,why and how to work around it so that new execution plan will be used.
The database is a 10.2.0.4 two-node RAC database running on HP-UX with psu3 patched.
Thanks!
Edited by: KevinMao on Sep 19, 2010 8:20 PM