|
--影响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语句父游标与子游标执行环境不同的地方
@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 | 
