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