最后，作为本系列的最后实验，我们一起来看看优化器模式和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语句的共享。

ITPub博客

SQL语句为什么不会共享（下）

最新文章