??? Oracle SQL語句的運(yùn)行環(huán)境分為多個不同的層次，主要包括實(shí)例級別，會話級別，語句級別，其優(yōu)先級依次遞增。即語句級別的執(zhí)行環(huán)境具
有最高的優(yōu)先權(quán)，會話級別次之，實(shí)例級別最低。反過來，實(shí)例級別的環(huán)境設(shè)置影響全局，而會話級別的則影響當(dāng)前會話，語句級別的設(shè)置當(dāng)然
也就只影響當(dāng)前語句。由此可知，運(yùn)行環(huán)境中每一個環(huán)節(jié)的參數(shù)都對最終的數(shù)據(jù)庫性能或所執(zhí)行的SQL語句有直接的影響。因此在對數(shù)據(jù)庫優(yōu)化
或調(diào)試SQL時，獲得當(dāng)前SQL語句運(yùn)行環(huán)境顯得尤為重要。為此，Oracle提供了三個重要的視圖來獲取不同級別的參數(shù)信息。

一、優(yōu)化器性能視圖名字
--下面的三個性能視圖分別對應(yīng)到不同的級別 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個視圖實(shí)際上是Oracle 10053事件中參數(shù)的一個子集 二、實(shí)例級別執(zhí)行環(huán)境(視圖V$SYS_OPTIMIZER_ENV)
--提供實(shí)例級別的環(huán)境執(zhí)行的信息。Oracle 優(yōu)化器在為SQL語句生成執(zhí)行計(jì)劃時，會根據(jù)實(shí)例級別參數(shù)的值來確定，如 optimizer_mode 用于 --確定優(yōu)化器的模式，optimizer_index_cost_adj用于確定全表掃描與索引掃描之間成本開銷的比值等。 --實(shí)例級別的參數(shù)可以使用下面的方式來設(shè)置 alter system set parameter=value scope=memory | both |spfile; --我們來看看當(dāng)前實(shí)例級別的運(yùn)行環(huán)境 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 --從上面的查詢結(jié)果可知，當(dāng)前系統(tǒng)中僅有statistics_level參數(shù)使用了非缺省值，statistics_level參數(shù)的缺省值為typical，而此處為all。 --對于實(shí)例級別任意參數(shù)的修改其影響都是全局的，因此應(yīng)當(dāng)慎重。當(dāng)然，如果某個參數(shù)設(shè)置的不合理，其影響也是全局的，因此對于實(shí)例級別 --參數(shù)的合理設(shè)置，可以避免成百上千枯燥的SQL語句調(diào)整，反之整個數(shù)據(jù)庫性能急劇下降也可能是因?yàn)閷?shí)例級別參數(shù)設(shè)置不當(dāng)所致。 三、會話級別執(zhí)行環(huán)境(視圖V$SES_OPTIMIZER_ENV)
--與實(shí)例級別相對應(yīng)的會話級別視圖是V$SES_OPTIMIZER_ENV。此時session 級別的參數(shù)值會優(yōu)先于實(shí)例級別的參數(shù)值。 --我們可以通過V$SYS_OPTIMIZER_ENV與V$SES_OPTIMIZER_ENV來獲得當(dāng)前或某個特定session的所有session級別參數(shù)值。 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 四、語句級別執(zhí)行環(huán)境(視圖V$SQL_OPTIMIZER_ENV)
--影響SQL語句運(yùn)行的執(zhí)行環(huán)境受到實(shí)例級別以及會話級別之外，最后的，優(yōu)先級最高的級別即是語句級別。因此語句級別的設(shè)置直接影響最終 --SQL語句執(zhí)行計(jì)劃的產(chǎn)生。SQL語句級別的參數(shù)設(shè)置是通過提示來完成的。 --在下面的例子中，我們通過為同一SQL 語句使用提示來使其運(yùn)行在不同的環(huán)境之中。 scott@ORCL> select /*+ no_hint */ ename,job,sal from emp where empno=7369; -->此SQL語句會根據(jù)實(shí)例與會話級別的優(yōu)化器環(huán)境 -->的設(shè)置來生成SQL執(zhí)行計(jì)劃并執(zhí)行 ENAME |JOB | SAL -->此時的no_hint不是一個有效的提示，被當(dāng)成注釋 ----------|---------|---------- Henry |CLERK | 1400 scott@ORCL> select /*+ all_rows */ ename,job,sal from emp where empno=7369; -->此時該SQL語句使用了all_rows提示 -->注，此語句與上一語句盡管得到的相同的結(jié)果 ENAME |JOB | SAL -->但運(yùn)行在不同的優(yōu)化器環(huán)境模式之中 ----------|---------|---------- -->兩者會有不同的sql_id Henry |CLERK | 1400 scott@ORCL> show parameter optimizer_index_cost_adj -->查看參數(shù)設(shè)置 NAME |TYPE |VALUE ------------------------------------|-----------|------------------------------ optimizer_index_cost_adj |integer |100 scott@ORCL> show parameter cursor_sharing -->查看參數(shù)設(shè)置 NAME |TYPE |VALUE ------------------------------------|-----------|------------------------------ cursor_sharing |string |EXACT scott@ORCL> select avg(sal) from emp; -->聚合運(yùn)算 AVG(SAL) ---------- 2335 scott@ORCL> alter session set cursor_sharing=force; -->基于session級別修改參數(shù) Session altered. scott@ORCL> alter session set optimizer_index_cost_adj=80; -->基于session級別修改參數(shù) Session altered. scott@ORCL> select avg(sal) from emp; -->再次聚合運(yùn)算 AVG(SAL) ---------- 2335 -->下面的查詢得到同一SQL語句由于不同運(yùn)行環(huán)境的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語句父游標(biāo)與子游標(biāo)執(zhí)行環(huán)境不同的地方 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語句運(yùn)行時優(yōu)化器的詳細(xì)信息 -->我們看到父游標(biāo)與子游標(biāo)的信息全部被列出來 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 五、總結(jié)
1、通過三個重要的優(yōu)化器性能視圖v$sys_optimizer_env,v$ses_optimizer_env,v$sql_optimizer_env可以獲得SQL語句的優(yōu)化器相關(guān)的信息
2、三個性能視圖獲得的數(shù)據(jù)實(shí)際上Oracle 10053 事件的一部分，即也可以通過Oracle 10053 事件進(jìn)行剖析來得到相同的結(jié)果
3、為調(diào)試SQL語句設(shè)置不同級別的優(yōu)化器相關(guān)參數(shù)提供了便利

六、更多參考

NULL 值與索引(一)

NULL 值與索引(二)

SQL tuning 步驟

高效SQL語句必殺技

父游標(biāo)、子游標(biāo)及共享游標(biāo)

綁定變量及其優(yōu)缺點(diǎn)

dbms_xplan之display_cursor函數(shù)的使用

dbms_xplan之display函數(shù)的使用

執(zhí)行計(jì)劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執(zhí)行計(jì)劃

啟用 AUTOTRACE 功能

函數(shù)使得索引列失效

Oracle 綁定變量窺探

Oracle 自適應(yīng)共享游標(biāo)

使用優(yōu)化器性能視圖獲取SQL語句執(zhí)行環(huán)境