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语句调整，反之整个数据库性能急剧下降也可能是因为实例级别参数设置不当所致。
三、会话级别执行环境(视图V$SES_OPTIMIZER_ENV)--与实例级别相对应的会话级别视图是V$SES_OPTIMIZER_ENV。此时session 级别的参数值会优先于实例级别的参数值。
--我们可以通过V$SYS_OPTIMIZER_ENV与V$SES_OPTIMIZER_ENV来获得当前或某个特定session的所有session级别参数值。
scott@ORCL> show parameter cursor_shar
NAME |TYPE |VALUE
------------------------------------|-----------|------------------
cursor_sharing |string |EXACT
scott@ORCL> alter session set cursor_sharing=force;
Session altered.
scott@ORCL> show parameter cursor_shar
NAME |TYPE |VALUE
------------------------------------|-----------|------------------
cursor_sharing |string |FORCE
scott@ORCL> alter session set optimizer_mode=first_rows_100;
Session altered.
scott@ORCL> show parameter optimizer_mode
NAME |TYPE |VALUE
------------------------------------|--------------------------------|--------------------
optimizer_mode |string |FIRST_ROWS_100
scott@ORCL> alter session set db_file_multiblock_read_count=128;
Session altered.
scott@ORCL> select username,sid,serial# from v$session where username='SCOTT';
USERNAME | SID| SERIAL#
------------------------------|----------|----------
SCOTT | 159| 5
scott@ORCL> select name,value,isdefault
2 from v$ses_optimizer_env
3 where sid=&input_sid
4 minus
5 select name,value,isdefault
6 from v$sys_optimizer_env;
Enter value for input_sid: 159
old 3: where sid=&input_sid
new 3: where sid=159
NAME |VALUE |ISD
-----------------------------------|---------------|---
_db_file_optimizer_read_count |128 |NO
cursor_sharing |force |NO
optimizer_mode |first_rows_100 |NO
四、语句级别执行环境(视图V$SQL_OPTIMIZER_ENV)--影响SQL语句运行的执行环境受到实例级别以及会话级别之外，最后的，优先级最高的级别即是语句级别。因此语句级别的设置直接影响最终
--SQL语句执行计划的产生。SQL语句级别的参数设置是通过提示来完成的。
--在下面的例子中，我们通过为同一SQL 语句使用提示来使其运行在不同的环境之中。
scott@ORCL> select /*+ no_hint */ ename,job,sal from emp where empno=7369; -->此SQL语句会根据实例与会话级别的优化器环境
-->的设置来生成SQL执行计划并执行
ENAME |JOB | SAL -->此时的no_hint不是一个有效的提示，被当成注释
----------|---------|----------
Henry |CLERK | 1400
scott@ORCL> select /*+ all_rows */ ename,job,sal from emp where empno=7369; -->此时该SQL语句使用了all_rows提示
-->注，此语句与上一语句尽管得到的相同的结果
ENAME |JOB | SAL -->但运行在不同的优化器环境模式之中
----------|---------|---------- -->两者会有不同的sql_id
Henry |CLERK | 1400
scott@ORCL> show parameter optimizer_index_cost_adj -->查看参数设置
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
optimizer_index_cost_adj |integer |100
scott@ORCL> show parameter cursor_sharing -->查看参数设置
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
cursor_sharing |string |EXACT
scott@ORCL> select avg(sal) from emp; -->聚合运算
AVG(SAL)
----------
2335
scott@ORCL> alter session set cursor_sharing=force; -->基于session级别修改参数
Session altered.
scott@ORCL> alter session set optimizer_index_cost_adj=80; -->基于session级别修改参数
Session altered.
scott@ORCL> select avg(sal) from emp; -->再次聚合运算
AVG(SAL)
----------
2335
-->下面的查询得到同一SQL语句由于不同运行环境的sql_id,child_number等
scott@ORCL> select sql_id, child_number, sql_text,optimizer_env_hash_value oehv,child_address
2 from v$sql where sql_text like '%avg(sal)%' and sql_text not like '%from v$sql%';
SQL_ID |CHILD_NUMBER|SQL_TEXT | OEHV|CHILD_AD
-------------|------------|-----------------------------------|----------|--------
5du23va3p3ad0| 0|select avg(sal) from emp |2196913643|3015F434
5du23va3p3ad0| 1|select avg(sal) from emp |3293423667|2CD868A0
-->获得同一SQL语句父游标与子游标执行环境不同的地方
scott@ORCL> select se0.name,se0.value as value_child_0,se1.value as value_child_1
2 from v$sql_optimizer_env se0
3 inner join v$sql_optimizer_env se1
4 on se0.sql_id=se1.sql_id
5 where se0.sql_id='&input_sql_id'
6 and se0.child_number=0
7 and se1.child_number=1
8 and se0.name=se1.name
9 and se0.value<>se1.value;
Enter value for input_sql_id: 5du23va3p3ad0
old 5: where se0.sql_id='&input_sql_id'
new 5: where se0.sql_id='5du23va3p3ad0'
NAME |VALUE_CHILD_0 |VALUE_CHILD_1
----------------------------------------|-------------------------|-------------------------
cursor_sharing |exact |force
optimizer_index_cost_adj |100 |80
-->获得上一SQL语句运行时优化器的详细信息
-->我们看到父游标与子游标的信息全部被列出来
scott@ORCL> select address, name, value, isdefault,child_number
2 from v$sql_optimizer_env
3 where sql_id = '5du23va3p3ad0'
4 order by child_number, isdefault, name;
ADDRESS |NAME |VALUE |ISD|CHILD_NUMBER
--------|----------------------------------------|-------------------------|---|------------
30177094|sqlstat_enabled |true |NO | 0
30177094|statistics_level |all |NO | 0
30177094|active_instance_count |1 |YES| 0
30177094|bitmap_merge_area_size |1048576 |YES| 0
30177094|cpu_count |1 |YES| 0
30177094|cursor_sharing |exact |YES| 0
30177094|hash_area_size |131072 |YES| 0
30177094|optimizer_dynamic_sampling |2 |YES| 0
30177094|optimizer_features_enable |10.2.0.1 |YES| 0
30177094|optimizer_index_caching |0 |YES| 0
30177094|optimizer_index_cost_adj |100 |YES| 0
30177094|optimizer_mode |all_rows |YES| 0
30177094|optimizer_secure_view_merging |true |YES| 0
30177094|parallel_ddl_mode |enabled |YES| 0
30177094|parallel_dml_mode |disabled |YES| 0
30177094|parallel_execution_enabled |true |YES| 0
30177094|parallel_query_mode |enabled |YES| 0
30177094|parallel_threads_per_cpu |2 |YES| 0
30177094|pga_aggregate_target |59392 KB |YES| 0
30177094|query_rewrite_enabled |true |YES| 0
30177094|query_rewrite_integrity |enforced |YES| 0
30177094|skip_unusable_indexes |true |YES| 0
30177094|sort_area_retained_size |0 |YES| 0
30177094|sort_area_size |65536 |YES| 0
30177094|star_transformation_enabled |false |YES| 0
30177094|workarea_size_policy |auto |YES| 0
30177094|cursor_sharing |force |NO | 1
30177094|optimizer_index_cost_adj |80 |NO | 1
30177094|sqlstat_enabled |true |NO | 1
30177094|statistics_level |all |NO | 1
30177094|active_instance_count |1 |YES| 1
30177094|bitmap_merge_area_size |1048576 |YES| 1
30177094|cpu_count |1 |YES| 1
30177094|hash_area_size |131072 |YES| 1
30177094|optimizer_dynamic_sampling |2 |YES| 1
30177094|optimizer_features_enable |10.2.0.1 |YES| 1
30177094|optimizer_index_caching |0 |YES| 1
30177094|optimizer_mode |all_rows |YES| 1
30177094|optimizer_secure_view_merging |true |YES| 1
30177094|parallel_ddl_mode |enabled |YES| 1
30177094|parallel_dml_mode |disabled |YES| 1
30177094|parallel_execution_enabled |true |YES| 1
30177094|parallel_query_mode |enabled |YES| 1
30177094|parallel_threads_per_cpu |2 |YES| 1
30177094|pga_aggregate_target |59392 KB |YES| 1
30177094|query_rewrite_enabled |true |YES| 1
30177094|query_rewrite_integrity |enforced |YES| 1
30177094|skip_unusable_indexes |true |YES| 1
30177094|sort_area_retained_size |0 |YES| 1
30177094|sort_area_size |65536 |YES| 1
30177094|star_transformation_enabled |false |YES| 1
30177094|workarea_size_policy |auto |YES| 1
52 rows selected.
-->Author : Robinson Cheng
-->Blog : http://blog.csdn.net/robinson_0612
五、总结1、通过三个重要的优化器性能视图v$sys_optimizer_env,v$ses_optimizer_env,v$sql_optimizer_env可以获得SQL语句的优化器相关的信息
2、三个性能视图获得的数据实际上Oracle 10053 事件的一部分，即也可以通过Oracle 10053 事件进行剖析来得到相同的结果
3、为调试SQL语句设置不同级别的优化器相关参数提供了便利
六、更多参考NULL 值与索引(一)
NULL 值与索引(二)
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划
启用 AUTOTRACE 功能
函数使得索引列失效
Oracle 绑定变量窥探
Oracle 自适应共享游标
分享到：
相关推荐
SQL相关资料
用sql语句创建库 表 视图与存储过程 用sql语句创建库 表 视图与存储过程
我自己做网络课堂网站开发过程中锁创建的数据库表及视图。表结构查询太复杂时可以试着创建视图
HANA_SQL语句和系统视图 了解Hana的SQL语句 内存数据库CURD的基本操作
使用诸如内嵌视图和bif扩展提高oracle SQL性能，确定并报告程序库缓存中的SQL语句，调整SQL表访问、完整表描述和平行查询，运行TYPEOF获得SQL跟踪报告，使用oracle线索为oralce sql语句更改执行计划，使用oracle8i...
从数据库管理系统 (DBMS) 的观点来看，视图是数据（元数据）的说明。创建典型视图时，通过 SELECT ...在视图扩展之后，查询优化器会为正在执行的查询编译单个执行计 划。 如果是非索引视图，视图在运行时将被实体化。
SQL语句小结. SQLServer语句小结(SQL语句使用整理) SQLServer语句小结(SQL语句使用整理)
使用sql语句创建视图，多种视图演示示例以及SQL语句实现讲解。
DELPHI 将表、视图、存储过程转换为SQL语句，导出文件时，会自动打开并显示导出文件的内容，目前支持MSSQL2000和MSSQL2005数据库。目前支持视图脚本、存储过程脚本、用户表结构＋数据＋主键＋默认值的提龋对标识也做...
SQL相关资料
使用SQL语句创建视图3.ppt
sqlsever 刷新（更新）数据库中的所有的视图，用于表添加字段，但是视图还是没有字段
7、 掌握使用 SQL 语句创建、删除、查询和更新视图。 二、 实验内容 （一）创建数据库和模式 1、通过 SQL 语句创建图书信息管理数据库，命名为"db_Library" ，数据文件和日志文件放在 D 盘下以自己学号和姓名命名的...
使用语如内嵌视图和BIF扩展提高QraleSQL性能，确定并报告程序库缓存中的SQL语句，调整SQL表访问、完整表扫描和平行查询，运行TKPROF获得SQL跟踪报告，使用Oracle线索为OracleSQL语句更改执行计划，使用Oracle8i优化...
Oracle sql执行流程图_SQL执行过程一、sql语句的执行步骤：1）语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。2） 语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。3）...
PREPARE --为动态执行准备SQL 语句 EXECUTE --动态地执行SQL 语句 DESCRIBE --描述准备好的查询 ---局部变量 declare @id char(10) --set @id = '10010001' select @id = '10010001' ---全局变量 ...
无论多么复杂的语句，都能分析出来（包括SQL各子句中嵌套的SQL语句） 5、数据库视图定义和重建 6、支持将SQL查询语句，替换为插入(Insert into)和更新(Update)语句 7、附属工具内嵌入Delphi IDE（支持Delphi 5和...
软件介绍 常用SQL语句大全 语 句 功 能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 ...Tags： 常用SQL语句大全 SQL语句大全 SQL
LINQ to SQL语句(24)之视图
1、掌握数据更新语句的使用； 2、掌握视图操作的基本方法和应用，理解基于视图的查询和数据更新操作的过程。