After looking at the SQL tracked, there is a few things to consider.
1) Yes, ALL_SYNONYMS is very slow, but that is an Oracle issue when selecting against it - there is nothing that can be done about that queries slowness. However...
2) Using DBA_SYNONYMS is faster and Toad tries to use that if the user has that privilege to select against it. In this case, it does not appear the user has priv.
Do a simple check by doing a select * from dba_synonyms.
One option may be to have that privilege granted to the user, if they are allowed.
3) The query is related to Team Coding (TC).
The support bundle shows they do not have it installed or are not using it on that DB, but maybe they did at one time.
Toad looks to see if the PUBLIC synonym 'DELL_COM_TEAM_CODING' exists.
If it does, then it will run those checks to see if the user has access to dba_synonyms - if not, then it will use all_synonyms (it needs the information for TC, so it has to query against all_synonyms or dba_synonyms).
If they were not using Team Coding at all on that database, then the easiest answer would be to uninstall TC and remove the synonym.
Just be aware there may be other people on the DB using it, so they would want to confirm that first. Get more information about how you all are using TC.
In addition, below is the code that checks to see if the user has the privileges/roles mentioned previously. It looks like if one has the 'SELECT_ANY_DICTIONARY' or 'SELECT CATALOG_ROLES', it also means they have access to DBA_SYNONYMS; thus, one could be granted either of those roles as well to force Toad to use DBA_SYNONYMS instead of ALL_SYNONYMS.
-- check for other ways dba_objects and dba_synonyms could be granted
if (select_any_dictionary = 1) or (select_catalog_role = 1) then
has_dba_synonyms := 1;
has_dba_objects := 1;
-- just try to select from dba_synonyms and dba_objects as a privilege check
qry_str := 'select 1 from DBA_SYNONYMS where rownum = 1';
execute immediate qry_str into has_dba_synonyms;
when others then
has_dba_synonyms := 0;
© ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center