最後，作為本系列的最後實驗，我們一起來看看最佳化器模式和nls系列引數對SQL共享的影響。

7、Optimizer_Mode最佳化器模式影響

Optimizer_Mode是一個決定Oracle生成執行計劃目標的重要引數。常用的有all_rows和first_rows，分別體現著生成計劃目標。All_Rows要求的是整體查詢綜合成本最低，而First_Rows要求的是最最佳化相應，儘快將值返回。不同的最佳化器模式，會影響到Oracle SQL語句的共享的。

SQL> show parameter optimizer_mode

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode string ALL_ROWS

SQL> select name, value from v$parameter where name='optimizer_mode';

NAME VALUE

-------------------- --------------------

optimizer_mode ALL_ROWS

SQL> select /*+ demo_5 */ count(*) from t;

COUNT(*)

----------

18015

我們先觀察一下父遊標情況。

SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_5 */%';

SQL_TEXT SQL_ID OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS EXECUTIONS VERSION_COUNT

------------------------------------- ------------- -------------- ---------- --------------- -------- ---------- -------------

select /*+ demo_5 */ count(*) from t g9zbcf1aqvtq6 ALL_ROWS 1433265862 2966233522 697545A4 1 1

此時，已經存在最佳化器模式ALL_ROWS的執行計劃了。修改引數，之後重新執行計劃。

SQL> alter session set optimizer_mode='FIRST_ROWS';

Session altered

SQL> select name, value from v$parameter where name='optimizer_mode';

NAME VALUE

-------------------- --------------------

optimizer_mode FIRST_ROWS

SQL> select /*+ demo_5 */ count(*) from t;

COUNT(*)

----------

18015

我們觀察父子游標情況。

SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_5 */%';

SQL_TEXT SQL_ID OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS EXECUTIONS VERSION_COUNT

-------------------------------------------------------------------------------- ------------- -------------- ---------- --------------- -------- ---------- -------------

select /*+ demo_5 */ count(*) from t g9zbcf1aqvtq6 FIRST_ROWS 1433265862 2966233522 697545A4 2 2

SQL> select sql_id, PARSING_SCHEMA_NAME,OPTIMIZER_MODE, child_number,executions from v$sql where sql_id='g9zbcf1aqvtq6';

SQL_ID PARSING_SCHEMA_NAME OPTIMIZER_MODE CHILD_NUMBER EXECUTIONS

------------- ------------------------------ -------------- ------------ ----------

g9zbcf1aqvtq6 SYS ALL_ROWS 0 1

g9zbcf1aqvtq6 SYS FIRST_ROWS 1 1

結果顯而易見，Oracle共享重用了父遊標，將原有的ALL_ROWS模式轉為了FIRST_ROWS模式。同時，子游標中加入了FIRST_ROWS模式的執行計劃。

8、NLS系列引數

NLS系列參數列示的是一些時區、地域等內容。一些引數的變化，也是會影響到SQL執行計劃的。下面列出一種情形：

SQL> select * from v$nls_parameters where parameter='NLS_SORT';

PARAMETER VALUE

-------------------- --------------------

NLS_SORT BINARY

nls_sort引數影響到進行列排序order by操作的時候，遵循什麼樣的標準進行。預設情況下，是使用binary方式進行。根據不同的場景，可以進行動態的配置。

SQL> select /*+ demo_6 */ * from t order by name;

NAME

--------------------

ad拓寬思路

時間

未來

一篇

此時，我們修改nls_sort引數，再次執行相同引數。

SQL> alter session set nls_sort='SCHINESE_RADICAL_M';

Session altered

SQL> select * from v$nls_parameters where parameter='NLS_SORT';

PARAMETER VALUE

-------------------- --------------------

NLS_SORT SCHINESE_RADICAL_M //採用中文筆畫排序

SQL> select /*+ demo_6 */ * from t order by name;

NAME

--------------------

ad拓寬思路

一篇

時間

未來

檢視遊標使用情況。

SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_6 */ *%';

SQL_TEXT SQL_ID OPTIMIZER_MODE EXECUTIONS VERSION_COUNT

-------------------------------------------- ------------- -------------- ---------- -------------

select /*+ demo_6 */ * from t order by name ccyd9w6dsn3pa ALL_ROWS 2 2

SQL> select sql_id, PARSING_SCHEMA_NAME,OPTIMIZER_MODE, child_number,executions from v$sql where sql_id='ccyd9w6dsn3pa';

SQL_ID PARSING_SCHEMA_NAME OPTIMIZER_MODE CHILD_NUMBER EXECUTIONS

------------- ------------------------------ -------------- ------------ ----------

ccyd9w6dsn3pa SYS ALL_ROWS 0 1

ccyd9w6dsn3pa SYS ALL_ROWS 1 1

出現了父遊標共享，存在兩個子游標的情形。子游標對應的執行計劃存在差異。

//兩個執行計劃有刪節，篇幅原因。

SQL> select * from table ( dbms_xplan. display_cursor ( 'ccyd9w6dsn3pa' ,0,' advanced' ));

PLAN_TABLE_OUTPUT

---------------------------------------

SQL_ID ccyd9w6dsn3pa, child number 0

-------------------------------------

select /*+ demo_6 */ * from t order by name

Plan hash value: 961378228

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | SORT ORDER BY | | 4 | 48 | 3 (34)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T | 4 | 48 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - (#keys=1) "T"."NAME"[VARCHAR2,20]

2 - "T"."NAME"[VARCHAR2,20]

43 rows selected

SQL> select * from table( dbms_xplan. display_cursor ('ccyd9w6dsn3pa' , 1, 'advanced' ));

PLAN_TABLE_OUTPUT

-------------------------------------------

SQL_ID ccyd9w6dsn3pa, child number 1

-------------------------------------

select /*+ demo_6 */ * from t order by name

Plan hash value: 961378228

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | SORT ORDER BY | | 4 | 48 | 3 (34)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T | 4 | 48 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - (#keys=1) NLSSORT("T"."NAME",'nls_sort=''SCHINESE_RADICAL_M''')[1

70], "T"."NAME"[VARCHAR2,20]

2 - "T"."NAME"[VARCHAR2,20]

Note

44 rows selected

結論：一些nls相關引數的變化，也會影響到SQL語句的共享。

SQL語句為什麼不會共享（下）

相關文章