HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

12c中View Merge安全控制变化——Bug还是安全增强

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2017-08-09 01:11:01

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

 数据库升级至12c后,一些语句出现了极大的性能变化。经仔细研究测试,发现是由于View Merge的安全控制导致的。

View Merge是一种查询转换的优化手段。当查询中引用了View或inline view时,优化器可以将主查询中的查询条件并入视图当中去进行优化选择以获得代价最小的执行计划。而如果视图不属于当前执行语句的用户,View Merge就可能存在潜在OPTIMIZER_SECURE_VIEW_MERGING(默认是TRUE)控制当执行语句的用户缺乏对视图的MERGE VIEW权限、也没有MERGE ANY VIEW权限时,是否允许优化器进行View Merge优化。

我们遇到的问题就是这一安全控制导致语句未能正确选择索引。以下是案例重现代码(在12.1.0.2和12.2.0.1中均可重现):

SQL代码
  1. HelloDBA.COM>  
  2. conn / as sysdba  
  3. drop user demo cascade;  
  4. create user demo identified by demo;  
  5. grant CREATE SYNONYM,UNLIMITED TABLESPACE to demo;  
  6. grant CONNECT,PLUSTRACE,RESOURCE to demo;  
  7. Connected.  
  8. HelloDBA.COM>  
  9. User dropped.  
  10.   
  11. HelloDBA.COM>  
  12. User created.  
  13.   
  14. HelloDBA.COM>  
  15. Grant succeeded.  
  16.   
  17. HelloDBA.COM>  
  18. Grant succeeded.  
  19.   
  20. conn demo/demo  
  21. create or replace FUNCTION fnCheckNumber(in_num IN VARCHAR2) RETURN NUMBER IS  
  22. BEGIN  
  23.   return 1;  
  24. end;  
  25. /  
  26. Connected.  
  27. HelloDBA.COM>   2    3    4    5  
  28. Function created.  
  29.   
  30. conn / as sysdba  
  31. drop user demo2 cascade;  
  32. create user demo2 identified by demo2;  
  33. grant create session, resource to demo2;  
  34. alter user demo2 default tablespace lmt_data;  
  35. Connected.  
  36. alter user demo2 quota unlimited on lmt_data;  
  37. grant create view to demo2;  
  38. drop user demo2 cascade  
  39.           *  
  40. ERROR at line 1:  
  41. ORA-01918: user 'DEMO2' does not exist  
  42.   
  43.   
  44. HelloDBA.COM>  
  45. User created.  
  46.   
  47. HelloDBA.COM>  
  48. Grant succeeded.  
  49.   
  50. HelloDBA.COM>  
  51. User altered.  
  52.   
  53. HelloDBA.COM>  
  54. User altered.  
  55.   
  56. HelloDBA.COM>  
  57. Grant succeeded.  
  58.   
  59. conn demo2/demo2  
  60. create table t1 as select * from all_tables;  
  61. create table t2 as select * from all_objects;  
  62. create unique index t2_idx1 on t2(object_id) compute statistics;  
  63. create view v1 as select * from t1;  
  64. Connected.  
  65. create view v2 as select * from t2;  
  66. grant select on t1 to demo;  
  67. grant select on t2 to demo;  
  68. grant select on v1 to demo;  
  69. grant select on v2 to demo;  
  70.   
  71. Table created.  
  72.   
  73. HelloDBA.COM>  
  74. Table created.  
  75.   
  76. HelloDBA.COM>  
  77. Index created.  
  78.   
  79. HelloDBA.COM>  
  80. View created.  
  81.   
  82. HelloDBA.COM>  
  83. View created.  
  84.   
  85. HelloDBA.COM>  
  86. Grant succeeded.  
  87.   
  88. HelloDBA.COM>  
  89. Grant succeeded.  
  90.   
  91. HelloDBA.COM>  
  92. Grant succeeded.  
  93.   
  94. HelloDBA.COM>  
  95. Grant succeeded.  
  96.   
  97. conn / as sysdba  
  98. alter system flush shared_pool;  
  99. conn demo/demo  
  100. Connected.  
  101. set autot trace  
  102. select fnCheckNumber(tablespace_name) from demo2.v1  
  103. union all  
  104. select 1 from demo2.v2 where object_id = fnCheckNumber('567785951');  
  105.   
  106. System altered.  
  107.   
  108. HelloDBA.COM> Connected.  
  109. HelloDBA.COM> HelloDBA.COM>   2    3  
  110. 106 rows selected.  
  111.   
  112.   
  113. Execution Plan  
  114. ----------------------------------------------------------  
  115. Plan hash value: 3515064724  
  116.   
  117. ----------------------------------------------------------------------------------  
  118. | Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  119. ----------------------------------------------------------------------------------  
  120. |   0 | SELECT STATEMENT       |         | 75158 |   953K|    40   (3)| 00:00:01 |  
  121. |   1 |  UNION-ALL             |         |       |       |            |          |  
  122. |   2 |   TABLE ACCESS FULL    | T1      |   106 |   530 |     3   (0)| 00:00:01 |  
  123. |*  3 |   VIEW                 | V2      | 75052 |   952K|    37   (3)| 00:00:01 |  
  124. |   4 |    INDEX FAST FULL SCAN| T2_IDX1 | 75052 |   439K|    37   (3)| 00:00:01 |  
  125. ----------------------------------------------------------------------------------  
  126.   
  127. Predicate Information (identified by operation id):  
  128. ---------------------------------------------------  
  129.   
  130.    3 - filter("OBJECT_ID"="FNCHECKNUMBER"('567785951'))  
  131.   
  132.   
  133. Statistics  
  134. ----------------------------------------------------------  
  135.         661  recursive calls  
  136.           2  db block gets  
  137.         501  consistent gets  
  138.         163  physical reads  
  139.           0  redo size  
  140.        2428  bytes sent via SQL*Net to client  
  141.         629  bytes received via SQL*Net from client  
  142.           9  SQL*Net roundtrips to/from client  
  143.          33  sorts (memory)  
  144.           0  sorts (disk)  
  145.         106  rows processed  

在上面的查询计划中,未能正确选择索引访问方式。当我们赋予用户MERGE VIEW权限(或修改OPTIMIZER_SECURE_VIEW_MERGING为FALSE)后,执行计划获取到了正确的索引访问方式:

SQL代码
  1. conn / as sysdba  
  2. grant MERGE ANY VIEW to demo;  
  3. Connected.  
  4. HelloDBA.COM>  
  5. Grant succeeded.  
  6.   
  7. conn / as sysdba  
  8. alter system flush shared_pool;  
  9. conn demo/demo  
  10. set autot trace  
  11. Connected.  
  12. select fnCheckNumber(tablespace_name) from demo2.v1  
  13. union all  
  14. select 1 from demo2.v2 where object_id = fnCheckNumber('567785951');  
  15.   
  16. System altered.  
  17.   
  18. HelloDBA.COM> Connected.  
  19. HelloDBA.COM> HelloDBA.COM>   2    3  
  20. 106 rows selected.  
  21.   
  22.   
  23. Execution Plan  
  24. ----------------------------------------------------------  
  25. Plan hash value: 809018835  
  26.   
  27. ------------------------------------------------------------------------------  
  28. | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  29. ------------------------------------------------------------------------------  
  30. |   0 | SELECT STATEMENT   |         |   107 |   536 |     4   (0)| 00:00:01 |  
  31. |   1 |  UNION-ALL         |         |       |       |            |          |  
  32. |   2 |   TABLE ACCESS FULL| T1      |   106 |   530 |     3   (0)| 00:00:01 |  
  33. |*  3 |   INDEX UNIQUE SCAN| T2_IDX1 |     1 |     6 |     1   (0)| 00:00:01 |  
  34. ------------------------------------------------------------------------------  
  35.   
  36. Predicate Information (identified by operation id):  
  37. ---------------------------------------------------  
  38.   
  39.    3 - access("OBJECT_ID"="FNCHECKNUMBER"('567785951'))  
  40.   
  41.   
  42. Statistics  
  43. ----------------------------------------------------------  
  44.         680  recursive calls  
  45.           2  db block gets  
  46.         340  consistent gets  
  47.           0  physical reads  
  48.           0  redo size  
  49.        2428  bytes sent via SQL*Net to client  
  50.         629  bytes received via SQL*Net from client  
  51.           9  SQL*Net roundtrips to/from client  
  52.          33  sorts (memory)  
  53.           0  sorts (disk)  
  54.         106  rows processed  

--- Fuyuncat ---

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat