ALL_SYNONYMS versus USER_SYNONYMS

I was tuning an application wide performance issue via an AWR report when I found a SQL consuming a huge number of logical I/O and executed several times. This SQL looks like:

SELECT COUNT(1) FROM DUAL WHERE EXISTS
(SELECT 1 FROM ALL_SYNONYMS WHERE SYNONYM_NAME = :B1 AND OWNER = USER
);

After a couple of minutes of discussion with developers, they refused to get rid of this part of the code which seems to me useless. Then, in a second tentative, I suggested them to replace the above code with the following one:

SELECT COUNT(1) FROM DUAL WHERE EXISTS
(SELECT 1 FROM USER_SYNONYMS WHERE SYNONYM_NAME = :B1
);

They refused again saying that it will not give the same results.

Can someone let me know a situation where this result difference is possible?

SQL> SELECT count(1) FROM ALL_SYNONYMS where OWNER = USER;

  COUNT(1)

       405

SQL> SELECT count(1) FROM USER_SYNONYMS;

  COUNT(1)

       405

Thanks in advance

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

--
http://www.freelists.org/webpage/oracle-l