V$SES_OPTIMIZER_ENV

In 10g kann man mit Hilfe der View V$SES_OPTIMIZER_ENV die für die einzelnen Sessions gesetzten Parameter ermitteln, die den cbo betreffen:

-- für eine Session wurden die PGA-Settings verändert
SQL> r
1  select *
2    from V$SES_OPTIMIZER_ENV
3*  where name = 'sort_area_size'

SID         ID NAME                           ISD VALUE
----------  -------------------------------   --- ---------
 85         16 sort_area_size                 YES 65536
 86         16 sort_area_size                 YES 65536
 91         16 sort_area_size                 YES 65536
 95         16 sort_area_size                 NO  200000000
101         16 sort_area_size                 YES 65536
107         16 sort_area_size                 YES 65536
122         16 sort_area_size                 YES 65536
125         16 sort_area_size                 YES 65536
127         16 sort_area_size                 YES 65536

SQL> r
1  select *
2    from V$SES_OPTIMIZER_ENV
3*  where sid = 95

SID         ID NAME                          ISD VALUE
---------- ------------------------------    --- ----------
95          2 parallel_execution_enabled     NO  false
95          9 optimizer_features_enable      YES 10.2.0.3
95         11 cpu_count                      YES 4
95         12 active_instance_count          YES 1
95         13 parallel_threads_per_cpu       YES 2
95         14 hash_area_size                 NO  200000000
95         15 bitmap_merge_area_size         YES 1048576
95         16 sort_area_size                 NO  200000000
95         17 sort_area_retained_size        YES 0
95         24 pga_aggregate_target           YES 786432 KB
95         35 parallel_query_mode            YES enabled
95         36 parallel_dml_mode              YES disabled
95         37 parallel_ddl_mode              YES enabled
95         38 optimizer_mode                 YES all_rows
95         48 cursor_sharing                 YES exact
95         50 star_transformation_enabled    YES false
95         66 optimizer_index_cost_adj       YES 100
95         67 optimizer_index_caching        YES 0
95         70 query_rewrite_enabled          YES true
95         71 query_rewrite_integrity        YES enforced
95        101 workarea_size_policy           NO  manual
95        102 _smm_auto_cost_enabled         NO  false
95        105 optimizer_dynamic_sampling     YES 2
95        112 statistics_level               YES typical
95        114 skip_unusable_indexes          YES true
95        165 optimizer_secure_view_merging  YES true

26 Zeilen ausgewählt.