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

什么是 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)

原文发表时间:2018-03-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle AWR 阙值影响历史执行计划

      最近有网友提到为什么在dba_hist_sql_plan中无法查看到sql语句的历史执行计划,对于这个问题是由于缺省情况下,Oracle 设定的阙值...

762
来自专栏坚毅的PHP

mysql DUPLICATE KEY UPDATE 问题

DUPLICATE KEY UPDATE batch执行时出死锁错误 背景知识 一、 mysql  insert 与 duplicate key: 典型的插入语...

4635
来自专栏极客慕白的成长之路

MySQL的实战系列:大字段如何优化

除特别注明外,本站所有文章均为慕白博客原创,转载请注明出处来自https://geekmubai.com/programming/747.html

4933
来自专栏与神兽党一起成长

修改MySQL varchar类型字段的排序规则

记录一个在工作中遇到的问题,也不算是问题,为的是找一种简便的方法批量修改数据表字段的排序规则,在MySQL中叫collation,常常和编码CHARACTER一...

2823
来自专栏漫漫全栈路

Oracle 学习笔记

前言 本贴内容纪录Oracle课程中的学习笔记,和Oracle的课后作业,以及数据库相关课程的学习笔记,笔记部分使用实例代码记录,不记详细语法。 用户管理...

3525
来自专栏杨建荣的学习笔记

关于primary key和unique index的奇怪问题 (58天)

今天一个dba交给我一个问题,让我帮忙查一下。说有个脚本运行的时候有错,让我看看是什么原因。 脚本的思路如下: 先drop PK,FK之类的constraint...

29612
来自专栏杨建荣的学习笔记

MySQL和Oracle的添加字段的处理差别 (r10笔记第73天)

昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道Oracle中这个操作就比较纠结了,因为是按照追加的方式来处...

3606
来自专栏MYSQL轻松学

MYSQL RR隔离级别下MVCC及锁解读

MVCC(Multi-Version Concurrent Control):多版本并发控制,只作用于RC和RR隔离级别,主要是为了避免脏读、非重复读,而非幻读...

5218
来自专栏吴生的专栏

30多条mysql数据库优化方法,千万级数据库记录查询轻松解决

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

44910
来自专栏日常学python

情人节福利,手把手教你学会mysql语句操作

这是我的第八篇原创文章 上篇文章简单说了下数据库,并教了怎样安装mysql工具和可视化工具,不知道你现在安装好了没?那今天我们继续说mysql,今天说下mysq...

38314

扫码关注云+社区

领取腾讯云代金券