RE: ALL_SYNONYMS versus USER_SYNONYMS
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <dmarc-noreply@xxxxxxxxxxxxx>, <dedba@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 15 Sep 2014 13:04:29 -0400
… and regardless of the merits of the use, I’m a bit surprised that predicate
didn’t push down to generate an identical plan.
IF they won’t change, perhaps some form of “outline” or an index could fix this
if repairing the poor performance is the goal of the folks writing the checks.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of David Fitzjarrell (Redacted sender "oratune@xxxxxxxxx" for DMARC)
Sent: Monday, September 15, 2014 11:03 AM
To: dedba@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Cc: Hemant-K.Chitale@xxxxxx
Subject: Re: ALL_SYNONYMS versus USER_SYNONYMS
It will but not with the query posted as the WHERE clause restricts the output
to only those synonyms owned by user.
David Fitzjarrell
Principal author, "Oracle Exadata Survival Guide"
On Monday, September 15, 2014 2:56 AM, De DBA <dedba@xxxxxxxxxx> wrote:
I think that user_synonyms won't list public synonyms, whereas all_synonyms
should...
Cheers,
Tony
On 15/09/14 18:39, Chitale, Hemant K wrote:
I have developers who prefer to use the ALL_% views (e.g. ALL_TABLES) even when
I tell them to use the USER_% views (USER_TABLES).
Must be something in their prior experiences that “taught” them to use the
ALL_% views !
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of David Fitzjarrell
Sent: Friday, September 12, 2014 11:07 PM
To: lyallbarbour@xxxxxxxxxxxxxxx; mohamed.houri@xxxxxxxxx; ORACLE-L
Subject: Re: ALL_SYNONYMS versus USER_SYNONYMS
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@xxxxxxxxxxxxxxx> 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@xxxxxxxxx
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 <https://twitter.com/MohamedHouri> Twitter - MohamedHouri
<https://twitter.com/MohamedHouri>
-- //www.freelists.org/webpage/oracle-l
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html.
Other related posts:
