HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Security View Merge in 12c --- Bug or Improvment

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2017-08-09 01:11:01

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

 After upgraded to 12c, we noted performance degradation in some SQLs. With further investigation, we found it's related to View Merging Security control.

View Merge is one of Query Transformation methods used by Oracle SQL Optimizer, it allows the optimizer to merge the predications in the main query into the views/inline views involved in the query to choose an optimized execution plan. However, if the view(s) do not belong to the user running the SQL, and the user does not have the Merge View privilege on these views or Merge Any View privilege, the parameter OPTIMIZER_SECURE_VIEW_MERGING will control if allow the optimizer to do VIew Merging to avoid security issue.

The problem we encountered is caused by this control. Here is the reproducable sample code (it can be reproduced in 12.1.0.2 and 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  

You can the execution plan does not have a correct Index Access method. After we granted Merge Any View to the user (or changed OPTIMIZER_SECURE_VIEW_MERGING to FALSE), the execution plan had the correct index scan method.

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 All reseverd.

Declaration
by fuyuncat