昨天有用户反馈使用业务用户查询表中数据时，显示为0条，事实上表中是有数据的。
登陆上数据库使用SYS用户去查询可以查出数据，使用业务用户查询，显示0行数据。
一开始在想是否是同义词视图之类等同名对象的影响，经排查也排除了这些可能。 对于SQL的执行情况，使用10046及dbms_xplan.display_cursor分析后，可以清楚的发现使用业务用户执行查询语句时，被自动加了FILTER过滤动作filter("TASSETACL"."RESPONSEAPPUSER"()=1 AND "TASSETACL"."RESPONSE"()=1)； 看起来像是使用了数据防护类产品，让用户去了解是否近期新上了类似产品； 后面了解到是用户新上线了防统方系统(医院HIS系统)，关闭后验证即可正常查询此表数据。后续重新设置此系统的规则，问题解决。
整个问题排查思路的主要过程：
SYS用户查询：
SQL> conn / as sysdba
Connected.
SQL> select count(*) from AAHIS.AA_ABCD;
COUNT(*)
----------
2365
SQL> set pagesize 100 linesize 150
col plan_table_output for a120
set long 9000
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));SQL> SQL> SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID ft2a43ra3rp2g, child number 0
-------------------------------------
select count(*) from AAHIS.AA_ABCD
Plan hash value: 474665402
------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| PK_AA_ABCD | 2327 | 2 |
------------------------------------------------------------
业务用户查询：
SQL> conn AAHIS/AAHIS
Connected.
SQL>
SQL> select count(*) from AAHIS.AA_ABCD;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 3465689780
-------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | FILTER | | | |
| 3 | INDEX FAST FULL SCAN| PK_AA_ABCD | 2327 | 2 |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TASSETACL"."RESPONSEAPPUSER"()=1 AND
"TASSETACL"."RESPONSE"()=1)
10046TRACE的跟踪：
SQL ID: ft2a43ra3rp2g Plan Hash: 474665402
select count(*)
from
AAHIS.AA_ABCD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 17 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 17 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=17 pr=0 pw=0 time=365 us)
2365 2365 2365 INDEX FAST FULL SCAN PK_AA_ABCD (cr=17 pr=0 pw=0 time=193 us cost=2 size=0 card=2327)(object id 23207)
====
SQL ID: ft2a43ra3rp2g Plan Hash: 3465689780
select count(*)
from
AAHIS.AA_ABCD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.08 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.09 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 41
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=45 pr=2 pw=0 time=87006 us)
0 0 0 FILTER (cr=45 pr=2 pw=0 time=87000 us)
0 0 0 INDEX FAST FULL SCAN PK_AA_ABCD (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=2327)(object id 23207)