使用优化器性能视图获取SQL语句执行环境

使用优化器性能视图获取SQL语句执行环境

Oracle SQL语句的运行环境分为多个不同的层次，主要包括实例级别，会话级别，语句级别，其优先级依次递增。即语句级别的执行环境具有最高的优先权，会话级别次之，实例级别最低。反过来，实例级别的环境设置影响全局，而会话级别的则影响当前会话，语句级别的设置当然也就只影响当前语句。由此可知，运行环境中每一个环节的参数都对最终的数据库性能或所执行的SQL语句有直接的影响。因此在对数据库优化或调试SQL时，获得当前SQL语句运行环境显得尤为重要。为此，Oracle提供了三个重要的视图来获取不同级别的参数信息。

一、优化器性能视图名字

--下面的三个性能视图分别对应到不同的级别
scott@ORCL> select table_name from dict where table_name like 'V$%OPTIMIZER_ENV';

TABLE_NAME
------------------------------
V$SYS_OPTIMIZER_ENV
V$SES_OPTIMIZER_ENV
V$SQL_OPTIMIZER_ENV

--注：上述的3个视图实际上是Oracle 10053事件中参数的一个子集

二、实例级别执行环境(视图V$SYS_OPTIMIZER_ENV)

--提供实例级别的环境执行的信息。Oracle 优化器在为SQL语句生成执行计划时，会根据实例级别参数的值来确定，如 optimizer_mode 用于
--确定优化器的模式，optimizer_index_cost_adj用于确定全表扫描与索引扫描之间成本开销的比值等。
--实例级别的参数可以使用下面的方式来设置

alter system set parameter=value scope=memory | both |spfile;

--我们来看看当前实例级别的运行环境

scott@ORCL> select
  2  name,
  3  value,
  4  isdefault
  5  from
  6  v$sys_optimizer_env
  7  order by 3,1;

NAME                              |VALUE          |ISD
-----------------------------------|---------------|---
statistics_level                  |all            |NO    -->非缺省值在此显示为NO
active_instance_count              |1              |YES
bitmap_merge_area_size            |1048576        |YES
cpu_count                          |1              |YES
cursor_sharing                    |exact          |YES
hash_area_size                    |131072        |YES
optimizer_dynamic_sampling        |2              |YES
optimizer_features_enable          |10.2.0.1      |YES
optimizer_index_caching            |0              |YES
optimizer_index_cost_adj          |100            |YES
optimizer_mode                    |all_rows      |YES
optimizer_secure_view_merging      |true          |YES
parallel_ddl_mode                  |enabled        |YES
parallel_dml_mode                  |disabled      |YES
parallel_execution_enabled        |true          |YES
parallel_query_mode                |enabled        |YES
parallel_threads_per_cpu          |2              |YES
pga_aggregate_target              |59392 KB      |YES
query_rewrite_enabled              |true          |YES
query_rewrite_integrity            |enforced      |YES
skip_unusable_indexes              |true          |YES
sort_area_retained_size            |0              |YES
sort_area_size                    |65536          |YES
star_transformation_enabled        |false          |YES
workarea_size_policy              |auto          |YES

--从上面的查询结果可知，当前系统中仅有statistics_level参数使用了非缺省值，statistics_level参数的缺省值为typical，而此处为all。
--对于实例级别任意参数的修改其影响都是全局的，因此应当慎重。当然，如果某个参数设置的不合理，其影响也是全局的，因此对于实例级别
--参数的合理设置，可以避免成百上千枯燥的SQL语句调整，反之整个数据库性能急剧下降也可能是因为实例级别参数设置不当所致。