oracle 统计信息 sort,更新统计信息

1、DBMS_STATS.SET_INDEX_STATS的使用。

alter session set event 10053 trace name context forever,level 1;

查看TRACE.

将统计信息取出导入。

可能会引起执行计划发生变化的参数有：

OPTIMIZER_FEATURES_ENABLE = 9.2.0

OPTIMIZER_MODE/GOAL = Choose

_OPTIMIZER_PERCENT_PARALLEL = 101

HASH_AREA_SIZE = 1048576

HASH_JOIN_ENABLED = TRUE

HASH_MULTIBLOCK_IO_COUNT = 0

SORT_AREA_SIZE = 524288

OPTIMIZER_SEARCH_LIMIT = 5

PARTITION_VIEW_ENABLED = FALSE

_ALWAYS_STAR_TRANSFORMATION = FALSE

_B_TREE_BITMAP_PLANS = TRUE

STAR_TRANSFORMATION_ENABLED = FALSE

_COMPLEX_VIEW_MERGING = TRUE

_PUSH_JOIN_PREDICATE = TRUE

PARALLEL_BROADCAST_ENABLED = TRUE

OPTIMIZER_MAX_PERMUTATIONS = 2000

OPTIMIZER_INDEX_CACHING = 0

_SYSTEM_INDEX_CACHING = 0

OPTIMIZER_INDEX_COST_ADJ = 100

OPTIMIZER_DYNAMIC_SAMPLING = 1

_OPTIMIZER_DYN_SMP_BLKS = 32

QUERY_REWRITE_ENABLED = FALSE

QUERY_REWRITE_INTEGRITY = ENFORCED

_INDEX_JOIN_ENABLED = TRUE

_SORT_ELIMINATION_COST_RATIO = 0

_OR_EXPAND_NVL_PREDICATE = TRUE

_NEW_INITIAL_JOIN_ORDERS = TRUE

ALWAYS_ANTI_JOIN = CHOOSE

ALWAYS_SEMI_JOIN = CHOOSE

_OPTIMIZER_MODE_FORCE = TRUE

_OPTIMIZER_UNDO_CHANGES = FALSE

_UNNEST_SUBQUERY = TRUE

_PUSH_JOIN_UNION_VIEW = TRUE

_FAST_FULL_SCAN_ENABLED = TRUE

_OPTIM_ENHANCE_NNULL_DETECTION = TRUE

_ORDERED_NESTED_LOOP = TRUE

_NESTED_LOOP_FUDGE = 100

_NO_OR_EXPANSION = FALSE

_QUERY_COST_REWRITE = TRUE

QUERY_REWRITE_EXPRESSION = TRUE

_IMPROVED_ROW_LENGTH_ENABLED = TRUE

_USE_NOSEGMENT_INDEXES = FALSE

_ENABLE_TYPE_DEP_SELECTIVITY = TRUE

_IMPROVED_OUTERJOIN_CARD = TRUE

_OPTIMIZER_ADJUST_FOR_NULLS = TRUE

_OPTIMIZER_CHOOSE_PERMUTATION = 0

_USE_COLUMN_STATS_FOR_FUNCTION = TRUE

_SUBQUERY_PRUNING_ENABLED = TRUE

_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50

_SUBQUERY_PRUNING_COST_FACTOR = 20

_LIKE_WITH_BIND_AS_EQUALITY = FALSE

_TABLE_SCAN_COST_PLUS_ONE = TRUE

_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE

_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE

_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE

_OPTIMIZER_COST_MODEL = CHOOSE

_GSETS_ALWAYS_USE_TEMPTABLES = FALSE

DB_FILE_MULTIBLOCK_READ_COUNT = 16

_NEW_SORT_COST_ESTIMATE = TRUE

_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE

_CPU_TO_IO = 0

_PRED_MOVE_AROUND = TRUE[@more@]