1. 查看数据库中与优化器环境相关的视图

SQL> select table_name from dict where table_name like '%OPTIMIZER%';

TABLE_NAME
------------------------------
DBA_HIST_OPTIMIZER_ENV
V$SYS_OPTIMIZER_ENV
V$SES_OPTIMIZER_ENV
V$SQL_OPTIMIZER_ENV
GV$SYS_OPTIMIZER_ENV
GV$SES_OPTIMIZER_ENV
GV$SQL_OPTIMIZER_ENV

7 rows selected.

注意到以下三个视图：
V$SYS_OPTIMIZER_ENV
V$SES_OPTIMIZER_ENV
V$SQL_OPTIMIZER_ENV

SQL> desc V$SYS_OPTIMIZER_ENV
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(40)
 ISDEFAULT                                          VARCHAR2(3)
 VALUE                                              VARCHAR2(25)
 DEFAULT_VALUE                                      VARCHAR2(25)

SQL> desc V$SES_OPTIMIZER_ENV
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 ID                                                 NUMBER
 NAME                                               VARCHAR2(40)
 ISDEFAULT                                          VARCHAR2(3)
 VALUE                                              VARCHAR2(25)

SQL> desc V$SQL_OPTIMIZER_ENV
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDRESS                                            RAW(4)
 HASH_VALUE                                         NUMBER
 SQL_ID                                             VARCHAR2(13)
 CHILD_ADDRESS                                      RAW(4)
 CHILD_NUMBER                                       NUMBER
 ID                                                 NUMBER
 NAME                                               VARCHAR2(40)
 ISDEFAULT                                          VARCHAR2(3)
 VALUE                                              VARCHAR2(25)

2. 通过视图V$SQL_OPTIMIZER_ENV，可以查看某个sql的优化器环境变量：

SQL> select sql_id,child_number,id,name,isdefault,value from V$SQL_OPTIMIZER_ENV where sql_id ='2ktnqttnp0ff8';

SQL_ID        CHILD_NUMBER         ID NAME                                     ISD VALUE
------------- ------------ ---------- ---------------------------------------- --- -------------------------
2ktnqttnp0ff8            0          2 parallel_execution_enabled               YES true
2ktnqttnp0ff8            0          9 optimizer_features_enable                YES 10.2.0.4
2ktnqttnp0ff8            0         11 cpu_count                                YES 2
2ktnqttnp0ff8            0         12 active_instance_count                    YES 1
2ktnqttnp0ff8            0         13 parallel_threads_per_cpu                 YES 2
2ktnqttnp0ff8            0         14 hash_area_size                           YES 131072
2ktnqttnp0ff8            0         15 bitmap_merge_area_size                   YES 1048576
2ktnqttnp0ff8            0         16 sort_area_size                           YES 65536
2ktnqttnp0ff8            0         17 sort_area_retained_size                  YES 0
2ktnqttnp0ff8            0         24 pga_aggregate_target                     YES 40960 KB
2ktnqttnp0ff8            0         35 parallel_query_mode                      YES enabled
2ktnqttnp0ff8            0         36 parallel_dml_mode                        YES disabled
2ktnqttnp0ff8            0         37 parallel_ddl_mode                        YES enabled
2ktnqttnp0ff8            0         38 optimizer_mode                           YES all_rows
2ktnqttnp0ff8            0         39 sqlstat_enabled                          NO  true
2ktnqttnp0ff8            0         48 cursor_sharing                           YES exact
2ktnqttnp0ff8            0         50 star_transformation_enabled              YES false
2ktnqttnp0ff8            0         66 optimizer_index_cost_adj                 YES 100
2ktnqttnp0ff8            0         67 optimizer_index_caching                  YES 0
2ktnqttnp0ff8            0         70 query_rewrite_enabled                    YES true
2ktnqttnp0ff8            0         71 query_rewrite_integrity                  YES enforced
2ktnqttnp0ff8            0        101 workarea_size_policy                     YES auto
2ktnqttnp0ff8            0        105 optimizer_dynamic_sampling               YES 2
2ktnqttnp0ff8            0        112 statistics_level                         NO  all
2ktnqttnp0ff8            0        114 skip_unusable_indexes                    YES true
2ktnqttnp0ff8            0        165 optimizer_secure_view_merging            YES true

26 rows selected.