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

 

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

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-703857/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-703857/