本文介绍了在Oracle EBS R12环境中，针对同义词XLA_TRANSACTION_ENTITIES在APPS和APPSREAD两个用户下进行相同查询时得到不同结果的问题。通过SQL执行计划分析，发现是VPD（虚拟专用数据库）策略导致了额外的过滤条件。在APPSREAD用户下添加与APPS相同的VPD策略后，查询结果变得一致。
Oracle EBS R12 - 查询同一个表在不同用户下的同义词(XLA_TRANSACTION_ENTITIES)时返回的结果不一样
在EBS R12系统中有张表"XLA"."XLA_TRANSACTION_ENTITIES",并且同时存在APPS和APPSREAD针对该表的同义词,
定义如下:
CREATE OR REPLACE SYNONYM "APPS"."XLA_TRANSACTION_ENTITIES" FOR "XLA"."XLA_TRANSACTION_ENTITIES";
CREATE OR REPLACE SYNONYM "APPSREAD"."XLA_TRANSACTION_ENTITIES" FOR "XLA"."XLA_TRANSACTION_ENTITIES";
但是针对同义词相同条件的查询返回的结果却不一样:
SQL> select count(*) from apps.XLA_TRANSACTION_ENTITIES where application_id=222;
COUNT(*)
----------
1
SQL> select count(*) from appsread.XLA_TRANSACTION_ENTITIES where application_id=222;
COUNT(*)
----------
112884
SQL>
看到这个问题首先想到就是用trace跟踪一下:
SQL> set autotrace traceonly explain
SQL> select count(*) from apps.XLA_TRANSACTION_ENTITIES where application_id=222;
Execution Plan
----------------------------------------------------------
Plan hash value: 2139858008
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 481 (2)| 00:00:06 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 8 | 481 (2)| 00:00:06 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 1 | 8 | 481 (2)| 00:00:06 | 2 | 2 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SECURITY_ID_INT_1" IS NULL)
SQL> select count(*) from appsread.XLA_TRANSACTION_ENTITIES where application_id=222;
Execution Plan
----------------------------------------------------------
Plan hash value: 1648015674
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 331 (1)| 00:00:04 | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | |
| 2 | PARTITION LIST SINGLE| | 112K| 438K| 331 (1)| 00:00:04 | KEY | KEY |
|* 3 | INDEX FULL SCAN | XLA_TRANSACTION_ENTITIES_U1 | 112K| 438K| 331 (1)| 00:00:04 | 2 | 2 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("APPLICATION_ID"=222)
SQL>
trace告诉了我们真相,实际查询时二者的条件完全不一样:
apps使用的是: filter("SECURITY_ID_INT_1" IS NULL)和("APPLICATION_ID"=222)，不过后者用来定位表的分区了
appsread使用的是：access("APPLICATION_ID"=222)
当我们把filter("SECURITY_ID_INT_1" IS NULL)加到appsread查询上时，得到了和apps相同的结果：
SQL> set autotrace on
SQL> select count(*) from appsread.XLA_TRANSACTION_ENTITIES where application_id=222 and ("SECURITY_ID_INT_1" IS NULL);
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2139858008
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 481 (2)| 00:00:06 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 8 | 481 (2)| 00:00:06 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 1 | 8 | 481 (2)| 00:00:06 | 2 | 2 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SECURITY_ID_INT_1" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1661 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
为什么apps会多出一个我们没有指定的条件呢？这其实是Oracle VPD自动加上去的，
类似你要查询oe_order_headers的数据时要先调用fnd_global初始化ORG_ID，
有关VPD可以参考另一篇blog：
利用Oracle VPD（虚拟专用数据库）实现类似EBS R12里的多OU屏蔽
http://blog.youkuaiyun.com/t0nsha/article/details/7423638
知道了原因，我们试着给APPSREAD用户也增加APPS相似的policy，看看结果怎样：
SQL> BEGIN
2 DBMS_RLS.add_policy (object_schema => 'APPSREAD'
3 ,object_name => 'XLA_TRANSACTION_ENTITIES'
4 ,policy_name => 'APPSREAD_policy'
5 ,function_schema => 'APPS'
6 ,policy_function => 'XLA_SECURITY_POLICY_PKG.MO_POLICY'
7 ,statement_types => 'SELECT');
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
设置了和APPS相同的policy后，APPSREAD获得了和APPS用户相同的查询结果：
SQL> set autotrace on;
SQL> select count(*) from appsread.XLA_TRANSACTION_ENTITIES where application_id=222;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2139858008
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 481 (2)| 00:00:06 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 8 | 481 (2)| 00:00:06 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 1 | 8 | 481 (2)| 00:00:06 | 2 | 2 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SECURITY_ID_INT_1" IS NULL)
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
1661 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
综上可以看出,如果DBMS_RLS.add_policy作用于同义词时,则只对在该过程的object_schema参数中指定的用户下的同义词有效。
最后删除policy，恢复环境:
SQL> exec DBMS_RLS.DROP_POLICY(object_schema=>'APPSREAD',object_name=>'XLA_TRANSACTION_ENTITIES',policy_name=>'APPSREAD_policy');
PL/SQL procedure successfully completed.
SQL>
REF:
1. Using Oracle Virtual Private Database to Control Data Access
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm
在EBS R12系统中有张表"XLA"."XLA_TRANSACTION_ENTITIES",并且同时存在APPS和APPSREAD针对该表的同义词,
定义如下:
CREATE OR REPLACE SYNONYM "APPS"."XLA_TRANSACTION_ENTITIES" FOR "XLA"."XLA_TRANSACTION_ENTITIES";
CREATE OR REPLACE SYNONYM "APPSREAD"."XLA_TRANSACTION_ENTITIES" FOR "XLA"."XLA_TRANSACTION_ENTITIES";
但是针对同义词相同条件的查询返回的结果却不一样:
SQL> select count(*) from apps.XLA_TRANSACTION_ENTITIES where application_id=222;
COUNT(*)
----------
1
SQL> select count(*) from appsread.XLA_TRANSACTION_ENTITIES where application_id=222;
COUNT(*)
----------
112884
SQL>
看到这个问题首先想到就是用trace跟踪一下:
SQL> set autotrace traceonly explain
SQL> select count(*) from apps.XLA_TRANSACTION_ENTITIES where application_id=222;
Execution Plan
----------------------------------------------------------
Plan hash value: 2139858008
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 481 (2)| 00:00:06 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 8 | 481 (2)| 00:00:06 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 1 | 8 | 481 (2)| 00:00:06 | 2 | 2 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SECURITY_ID_INT_1" IS NULL)
SQL> select count(*) from appsread.XLA_TRANSACTION_ENTITIES where application_id=222;
Execution Plan
----------------------------------------------------------
Plan hash value: 1648015674
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 331 (1)| 00:00:04 | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | |
| 2 | PARTITION LIST SINGLE| | 112K| 438K| 331 (1)| 00:00:04 | KEY | KEY |
|* 3 | INDEX FULL SCAN | XLA_TRANSACTION_ENTITIES_U1 | 112K| 438K| 331 (1)| 00:00:04 | 2 | 2 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("APPLICATION_ID"=222)
SQL>
trace告诉了我们真相,实际查询时二者的条件完全不一样:
apps使用的是: filter("SECURITY_ID_INT_1" IS NULL)和("APPLICATION_ID"=222)，不过后者用来定位表的分区了
appsread使用的是：access("APPLICATION_ID"=222)
当我们把filter("SECURITY_ID_INT_1" IS NULL)加到appsread查询上时，得到了和apps相同的结果：
SQL> set autotrace on
SQL> select count(*) from appsread.XLA_TRANSACTION_ENTITIES where application_id=222 and ("SECURITY_ID_INT_1" IS NULL);
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2139858008
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 481 (2)| 00:00:06 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 8 | 481 (2)| 00:00:06 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 1 | 8 | 481 (2)| 00:00:06 | 2 | 2 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SECURITY_ID_INT_1" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1661 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
为什么apps会多出一个我们没有指定的条件呢？这其实是Oracle VPD自动加上去的，
类似你要查询oe_order_headers的数据时要先调用fnd_global初始化ORG_ID，
有关VPD可以参考另一篇blog：
利用Oracle VPD（虚拟专用数据库）实现类似EBS R12里的多OU屏蔽
http://blog.youkuaiyun.com/t0nsha/article/details/7423638
SQL> SELECT object_owner,object_name,pf_owner,package,function,sel FROM dba_policies where object_name = 'XLA_TRANSACTION_ENTITIES';
OBJECT_OWNER OBJECT_NAME PF_OWNER PACKAGE FUNCTION SEL
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---
APPS XLA_TRANSACTION_ENTITIES APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES APPS XLA_SECURITY_POLICY_PKG XLA_STANDARD_POLICY YES
APPS XLA_TRANSACTION_ENTITIES APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
7 rows selected.
SQL>知道了原因，我们试着给APPSREAD用户也增加APPS相似的policy，看看结果怎样：
SQL> BEGIN
2 DBMS_RLS.add_policy (object_schema => 'APPSREAD'
3 ,object_name => 'XLA_TRANSACTION_ENTITIES'
4 ,policy_name => 'APPSREAD_policy'
5 ,function_schema => 'APPS'
6 ,policy_function => 'XLA_SECURITY_POLICY_PKG.MO_POLICY'
7 ,statement_types => 'SELECT');
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT object_owner,object_name,policy_name,policy_group,pf_owner,package,function,sel FROM dba_policies where object_name = 'XLA_TRANSACTION_ENTITIES';
OBJECT_OWN OBJECT_NAME POLICY_NAME POLICY_GROUP PF_OW PACKAGE FUNCTION SEL
---------- ------------------------------ ------------------------------ --------------- ----- ------------------------------ ------------------------------ ---
APPS XLA_TRANSACTION_ENTITIES PA_SECURITY_POLICY PA APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES DPP_SECURITY_POLICY DPP APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES OKL_SECURITY_POLICY OKL APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES XLA_SECURITY_POLICY XLA APPS XLA_SECURITY_POLICY_PKG XLA_STANDARD_POLICY YES
APPS XLA_TRANSACTION_ENTITIES IGC_SECURITY_POLICY IGC APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES SQLAP_SECURITY_POLICY SQLAP APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPS XLA_TRANSACTION_ENTITIES AR_SECURITY_POLICY AR APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
APPSREAD XLA_TRANSACTION_ENTITIES APPSREAD_POLICY SYS_DEFAULT APPS XLA_SECURITY_POLICY_PKG MO_POLICY YES
8 rows selected.
SQL>设置了和APPS相同的policy后，APPSREAD获得了和APPS用户相同的查询结果：
SQL> set autotrace on;
SQL> select count(*) from appsread.XLA_TRANSACTION_ENTITIES where application_id=222;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2139858008
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 481 (2)| 00:00:06 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 8 | 481 (2)| 00:00:06 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 1 | 8 | 481 (2)| 00:00:06 | 2 | 2 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SECURITY_ID_INT_1" IS NULL)
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
1661 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
综上可以看出,如果DBMS_RLS.add_policy作用于同义词时,则只对在该过程的object_schema参数中指定的用户下的同义词有效。
最后删除policy，恢复环境:
SQL> exec DBMS_RLS.DROP_POLICY(object_schema=>'APPSREAD',object_name=>'XLA_TRANSACTION_ENTITIES',policy_name=>'APPSREAD_policy');
PL/SQL procedure successfully completed.
SQL>
REF:
1. Using Oracle Virtual Private Database to Control Data Access
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm
扫一扫
235
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
