ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 优化器相关环境变量

优化器相关环境变量

原创 Linux操作系统 作者:lsq_008 时间:2009-07-30 14:45:30 0 删除 编辑

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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-610830/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    324
  • 访问量
    1228371