Re: ALL_SYNONYMS versus USER_SYNONYMS

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Fri, 12 Sep 2014 08:06:45 -0700
Message-ID: <1410534405.54604.YahooMailNeo_at_web124704.mail.ne1.yahoo.com>

In the absence of the qualifier "where owner=user" ALL_SYNONYMS can contain more synonyms than USER_SYNONYMS however the queries you post are equivalent. I second Lyall's question of why does the app 'need' to know about synonyms? This should be a configuration step prior to releasing the application to production; there should be no need for such a query to be run.

It sounds like either the developers are misinformed or, well, arrogant. I would hope it is due to misinformation.  

David Fitzjarrell

Principal author, "Oracle Exadata Survival Guide"

On Friday, September 12, 2014 4:02 AM, Lyall personal <lyallbarbour_at_sanfranmail.com> wrote:  

You could give your developers the code for those two views. user_synonyms text does what they are doing in the where clause. At least looks like that to me. Strange query for an "application" to run. Why does the app need to know if synonyms exist?

Sent from my BlackBerry 10 smartphone.
From: Mohamed Houri
Sent: Friday, September 12, 2014 5:47 AM To: ORACLE-L
Reply To: mohamed.houri_at_gmail.com
Subject: ALL_SYNONYMS versus USER_SYNONYMS

Dear list,

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
Visit My      
  - Blog
Let's
Connect - Linkedin
Profile
My Twitter      - MohamedHouri

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

Received on Fri Sep 12 2014 - 17:06:45 CEST