Execution Plan Changes When Executing The Query As Non-owner

Execution Plan Changes When Executing The Query As Non-owner

SYMPTOMS

Query runs fine when it is executed as owner. But when the same query is executed from non-owner, the execution plan is changed and the query performance is poor.

CAUSE

There is a new feature in 10.2 called secure view merging which is causing the change of execution plan when the query is executed as different user.

When a user is not the owner of a view, indexes are not used or the execution plan is not optimal and therefore query performance is impacted.

To solve the problem subordinate users should have MERGE ANY VIEW privilege or the Database parameter OPTIMIZER_SECURE_VIEW_MERGING should be set to false (the default value is TRUE).

Setting OPTIMIZER_SECURE_VIEW_MERGING to FALSE will give the same behaviour
as Database versions before 10.2.

If the parameter optimizer_secure_view_merging is set to TRUE (default) then it is possible that some users might get bad execution plans when querying views owned by other users as they would be unable to MERGE the views unless they had one of the following 3 conditions met.

1. MERGE ANY VIEW granted to the user
2. GRANT MERGE ON VIEW x.y TO z granted to them for all views owned by other users
3. optimizer_secure_view_merging set to FALSE (pre 10g R2 behaviour)

CBO will not allow a view to be merged in an execution plan where the view is owned by somebody else unless one of the 3 conditions above is met. 

SOLUTION

connect user_a
create table tab_a (a1 number);
create unique index a_a1 on tab_a(a1);
create table tab_b (b1 number);
create or replace view v1 as
select a.a1 from tab_a a, tab_b b where a.a1 = b.b1;

grant select on tab_a to user_b;
grant select on tab_b to user_b;
grant select on v1 to user_b;

show parameters optimizer_secure_view_merging

NAME TYPE VALUE
----------------------------------- -----------
------------------------------
optimizer_secure_view_merging boolean TRUE

/* Query executed as owner */ 

set autotrace on
select * from user_a.v1;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TAB_B| 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| A_A1 | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------- 

/* Query executed as non-owner */

connect user_b
set autotrace on
select * from user_a.v1;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | VIEW | V1 | 1 | 13 | 3 (0)| 00:00:01 |           <=========View not merged
| 2 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TAB_B| 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCA| A_A1 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

The above is an example scenario where the change of execution plan is seen because of the feature.

Fix

===

connect sys as sysdba

alter system set optimizer_secure_view_merging=false;

======================================================================

Note---> This informationmation taken from oracle metalink. all copy rights oracle only.