sql已优化，执行计划的变更如何人为调整执行路径 ？

Yong Huang

> 在不做统计更新的计划下，这种执行计划的变更有没办法人为优化后的执行计划(主要是执行路径)？

I think you missed a word in this sentence. We have to guess what you mean. If you mean, you want the actually used plan to change without gathering stats, then as others have said, it's not worth it. The best solution is as you did: gather stats, and (if the plan hasn't changed), run a DDL to force re-parse. Relying on dynamic sampling is not a good idea and should only be used temporarily.

> In fact, we should flush shared pool

Most of the time, the whole database is not dedicated to one application. Flushing shared pool has too much impact. Even if the database runs this app only, unless too many SQLs need to be re-parsed, flushing shared pool is still not as good as pinpointing at only the bad SQLs.

Beginning with 10.2.0.4, there's a way to purge a specific cursor, further minimizing impact.

Yong Huang

xiaowg

DBMS_STATS.GATHER_TABLE_STATS ( . . .  no_invalidate=>false)

Yong Huang

> DBMS_STATS.GATHER_TABLE_STATS ( . . .  no_invalidate=>false)

Nothing is perfect. The little problem with setting no_invalidate to false is that, if the table has indexes, then the time the stats gathering is done may significantly differ among indexes and between the indexes and the table, depending on the size. Setting no_invalidate to true gives you an opportunity to invalidate the cursors at one single point in time (the time you run a DDL). Even if you set cascade to true, the completion time of stats gathering is still different.

I haven't studied the case with partitioned tables. Each partition may have different completion time for stats gathering too.

Yong Huang

sundog315

如果预先都可以“固定”下来执行计划，那还要CBO干什么？不就回归了RBO了

secooler

固定执行计划也可以考虑使用Oracle的OUTLINE技术。

请参考《【OUTLINE】使用Oracle Outline技术暂时锁定SQL的执行计划》（http://space.itpub.net/519536/viewspace-673010）。

secooler

lfree

10,11g,可以食用sql profile。

gaowestlake

"原因可能是统计信息更新了，但没有更新share pool里的执行计划。手工做了一把grant"

system 还没有权限去查看那个表的信息？

统计信息收集后会自动应用，不是每次都是手工去刷新share pool。

steven_xiao

select /*+ no_merge(b) */distinct '20100101' as begindate,
                '20110523' as enddate,
                a.MAN_NO,
                b.num
  from zzlocal.imp_highcus_info a,
       (select MAN_NO, count(MSISDN) as num
          from zzlocal.imp_highcus_info
         group by MAN_NO) b
where a.MAN_NO = b.MAN_NO
   and a.MAN_NO is not null
   and (filter_flag != 1 or filter_flag is null)
   and to_char(a.imp_date, 'yyyymmdd') >= 20100101
   and to_char(a.imp_date, 'yyyymmdd') <= 20110523;

经测试，通过no_merge hint也达到了同样的效果。