Oracle EBS R12 - 查询同一个表在不同用户下的同义词(XLA_TRANSACTION_ENTITIES)时返回的结果不一样

最新推荐文章于 2024-02-23 16:53:16 发布

t0nsha

最新推荐文章于 2024-02-23 16:53:16 发布

阅读量3.4k

点赞数

分类专栏： Oracle - EBS/APP

本文链接：https://blog.csdn.net/t0nsha/article/details/11661229

版权

本文介绍了在Oracle EBS R12环境中，针对同义词XLA_TRANSACTION_ENTITIES在APPS和APPSREAD两个用户下进行相同查询时得到不同结果的问题。通过SQL执行计划分析，发现是VPD（虚拟专用数据库）策略导致了额外的过滤条件。在APPSREAD用户下添加与APPS相同的VPD策略后，查询结果变得一致。

摘要由CSDN通过智能技术生成

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

----------------------------------------------------------------------------------------------------------------------