微信号:OraNews

介绍:分享数据库技术、新闻与信息,尤其是和Oracle数据库相关的内容,文章内容来自原创、专栏作者投稿或读者投稿.

View Merge 在安全控制上的变化,是 BUG 还是增强 ?

2018-03-13 23:59 黄玮


作者简介

黄玮(Fuyuncat)

资深 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)回复关键字获取

‘2017DTC’,2017 DTC 大会 PPT

‘DBALIFE’,“DBA 的一天”海报

‘DBA04’,DBA 手记4 经典篇章电子书

‘RACV1’, RAC 系列课程视频及 PPT

‘122ARCH’,Oracle 12.2 体系结构图

‘2017OOW’,Oracle OpenWorld 资料

‘PRELECTION’,大讲堂讲师课程资料

 
数据和云 更多文章 腾讯游戏海量业务场景下的个性化安全运营之道 12C 新特性 | 标量子查询自动转换 深入内核丨12C 新特性之 TOP - N 频率柱状图原理和算法 美团外卖自动化业务运维系统 - Alfred 关于 Oracle 存储双活配置和实战
猜您喜欢 UPYUN开发者大赛即将启动,看代码如何改变世界! 美国监狱开编程课 囚犯出狱后可当计算机工程师 金融壹账通移动技术周报(第五十四期) 【第342期】优秀程序员是如何处理糟糕代码的 金三银四跳槽期,你应该这样写简历