View Merge 在安全控制上的变化，是 BUG 还是增强 ？
作者简单介绍
资深 Oracle DBA，致力于数据库底层技术的研究。其作品获得广大同行的高度评价。
个人站点 www.HelloDBA.com
什么是 View Merge
View Merge 是 12C 引入的新特性，也是一种优化手段。当查询中引用了 View 或 inline view 时，优化器能够将主查询中的查询条件并入视图其中去进行优化选择以获得代价最小的运行计划。而假设视图不属于当前运行语句的用户。View Merge 就可能存在潜在 OPTIMIZER_SECURE_VIEW_MERGING（默认是 TRUE）控制。
当运行语句的用户缺乏对视图的 MERGE VIEW 权限。也没有 MERGE ANY VIEW 权限时。是否同意优化器进行 View Merge 优化。
View Merge 问题重现
以下是一个安全控制导致语句未能正确选择索引的演示。首先创建对应的測试用户（demo）并授予对应的权限。
SQL 代码例如以下：
SQL> conn / as sysdba
Connected.
SQL> drop user demo cascade;
User dropped.
SQL> create user demo identified by demo;
User created.
SQL> grant CREATE SYNONYM,UNLIMITED TABLESPACE to demo;
Grant succeeded.
SQL> grant CONNECT,PLUSTRACE,RESOURCE to demo;
Grant succeeded.
创建对应的測试函数。
SQL 代码例如以下：
SQL> conn demo/demo
Connected.
SQL> create or replace FUNCTION fnCheckNumber(in_num IN VARCHAR2) RETURN NUMBER IS
2 BEGIN
3 return 1;
4 end;
5 /
Function created.
创建測试用户 demo 2。授予连接与 resource 的权限。
SQL 代码例如以下：
SQL> conn / as sysdba
Connected.
SQL> drop user demo2 cascade;
User dropped.
SQL> create user demo2 identified by demo2;
User created.
SQL> grant create session, resource to demo2;
Grant succeeded.
SQL> alter user demo2 default tablespace lmt_data;
User altered.
SQL> alter user demo2 quota unlimited on lmt_data;
User altered.
SQL> grant create view to demo2;
Grant succeeded.
SQL> drop user demo2 cascade;
User dropped.
创建測试表 t1。t2 以及測试视图 v1，v2。并往表中增加測试数据，同一时候授予 demo 訪问的权限。
SQL 代码例如以下：
SQL>conn demo2/demo2
Connected.
SQL> create table t1 as select * from all_tables;
Table created.
SQL> create table t2 as select * from all_objects;
Table created.
SQL> create unique index t2_idx1 on t2(object_id) compute statistics;
Index created.
SQL> create view v1 as select * from t1;
View created.
SQL> create view v2 as select * from t2;
View created.
SQL> grant select on t1 to demo;
Grant succeeded.
SQL> grant select on t2 to demo;
Grant succeeded.
SQL> grant select on v1 to demo;
Grant succeeded.
SQL> grant select on v2 to demo;
Grant succeeded.
将 share pool 曾经保存的 SQL 运行计划所有清空。释放少数的共享池资源，保证 SQL 运行计划的又一次解析。
SQL 代码例如以下：
SQL> conn / as sysdba
Connected.
SQL> alter system flush shared_pool;
System altered.
查看运行计划。
SQL 代码例如以下：
SQL> conn demo/demo
Connected.
SQL> set autot trace
SQL> select fnCheckNumber(tablespace_name) from demo2.v1
union all
select 1 from demo2.v2 where object_id = fnCheckNumber('567785951');
106 rows selected.
Execution Plan
----------------------------------------------
Plan hash value: 3515064724
-------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 75158 | 953K| 40 (3)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 106 | 530 | 3 (0)| 00:00:01 |
|* 3 | VIEW | V2 | 75052 | 952K| 37 (3)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| T2_IDX1 | 75052 | 439K| 37 (3)| 00:00:01 |
-----------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------
3 - filter("OBJECT_ID"="FNCHECKNUMBER"('567785951'))
Statistics
----------------------------------------
661 recursive calls
2 db block gets
501 consistent gets
163 physical reads
0 redo size
2428 bytes sent via SQL*Net to client
629 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
33 sorts (memory)
0 sorts (disk)
106 rows processed
获取正确的索引訪问方式
在上面的查询计划中，未能正确选择索引訪问方式。当我们赋予用户 MERGE VIEW 权限（或改动 OPTIMIZER_SECURE_VIEW_MERGING 为 FALSE）后，运行计划获取到了正确的索引訪问方式。
授权的 SQL 代码例如以下：
SQL> conn / as sysdba
Connected.
SQL> grant MERGE ANY VIEW to demo;
Grant succeeded.
再对 share pool 中原有的运行计划进行清空一次，便于又一次解析。
详细的 SQL 代码例如以下：
SQL> alter system flush shared_pool;
System altered.
对上面语句再解析一遍，获取该语句的运行计划。
详细 SQL 代码与运行计划例如以下：
SQL> set autot trace
SQL> select fnCheckNumber(tablespace_name) from demo2.v1 union all
select 1 from demo2.v2 where object_id = fnCheckNumber('567785951');
106 rows selected.
Execution Plan
-----------------------------------------------
Plan hash value: 809018835
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 536 | 4 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 106 | 530 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| T2_IDX1 | 1 | 6 | 1 (0)| 00:00:01 |
-----------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------
3 - access("OBJECT_ID"="FNCHECKNUMBER"('567785951'))
Statistics
------------------------------------------
680 recursive calls
2 db block gets
340 consistent gets
0 physical reads
0 redo size
2428 bytes sent via SQL*Net to client
629 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
33 sorts (memory)
0 sorts (disk)
106 rows processed
能够看到上面的运行计划已经正确选择索引訪问方式了。
改动 optimizer_secure_view_merging 參数。
SQL 代码例如以下：
SQL> show parameter optimizer_secure_view_merging
NAME TYPE VALUE
-------------------------- ----------- -----------------------
optimizer_secure_view_merging boolean TRUE
SQL> alter system set optimizer_secure_view_merging =false;
System altered.
研究收获
从上面的案例能够分析出 View Merge 是 12C 的一个新特性。由于这个安全控制导致在查询计划中未能正确选择索引訪问方式，仅仅须要取消掉这个新特性或者将 MERGE ANY VIEW 授予用户后就能够得到对应正确的索引訪问方式。
资源下载
关注公众号：数据和云（OraNews）回复keyword获取
‘2017DTC’，2017 DTC 大会 PPT
‘DBALIFE’，“DBA 的一天”海报
‘DBA04’，DBA 手记4 经典篇章电子书
‘RACV1’, RAC 系列课程视频及 PPT
‘122ARCH’，Oracle 12.2 体系结构图
‘2017OOW’，Oracle OpenWorld 资料
‘PRELECTION’。大讲堂讲师课程资料
