你看你的top sql里全是动态采样的sql，默认10g以后optimizer_dynamic_sampling参数为level 2，一般为缺失统计信息会造成每次使用动态采样，虽然动态采样会在表频繁发生大批量改变时，一般可以生成更好的执行计划，但是也不往往是这样，所以看看 “ASSP2”."ASSP_CLOB_VOUCHER_STAMP_2020"表为什么相关SQL都走动态采样？
下面为该参数级别解释Mos(Doc ID 336267.1)：
The parameter OPTIMIZER_DYNAMIC_SAMPLING controls the level of sampling performed by the optimizer.
In simplified form, the Levels have the following effects:
Level 0: Disables dynamic sampling.
Level 1: Sample all tables that have not been analyzed that meet certain criteria.
Level 2: Apply dynamic sampling to all unanalyzed tables. (Default from 10g)
Level 3: As per Level 2, plus all tables for which standard selectivity estimates used a guess for some predicate that is a potential dynamic sampling predicate.
Level 4: As per Level 3, plus all tables that have complex predicate expressions (for example single-table predicates that reference 2 or more columns or non-equality where clause predicates on two correlated columns).
Level 5-9: As per Level 4 with a larger sample size each time.
Level 10: Use dynamic sampling for all statements sampling All blocks
扫一扫
4534
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
