Re: ALL_SYNONYMS versus USER_SYNONYMS

This is why I told them that it is useless. They came up with suggestions that are not persuasive at all. But, since they want to keep that code, I am trying to make it (a) consuming less ressource and (b) executed less frequently

Best regards
Mohamed Houri

2014-09-12 12:01 GMT+02:00 Lyall personal <lyallbarbour_at_sanfranmail.com>:

> 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 <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>
>
>
>

-- 

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