??? 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語句執行環境