Toad World® Forums

getting table, view, etc.. list is very slow on users not using dbaviews


Since 11g there is a known issue when accessing the all_synonyms view. The response is very slow with no apparent reason. When the user has the proper rights quest’s tools allows for dba_views that perform ok. All though this is not an issue with quest there is a workarround that requires tool’s help. One can create another view on sys (I think the workaround is recommended by oracle and suggests the 9i all_synonyms view but better checkit out) using another name (e.g fast_all_synonyms). Then on each user you want to accelerate you create a synonym named all_synonyms pointing to fast_all_synonyms. That would work on sqlnavigator too if only navigator would not select on sys.all_synonyms instead of all_synonyms as i expected. I believe a parameter letting user choose what is needed would be very helpful.



Thanks for reminding this Oracle issue and suggesting the solution. I’m afraid in latest SQL Navigator the workaround is no long working. We recently qualified all the Oracle dictionary views with SYS schema in every SQL that Navigator runs. I created a tracker story PT43832211 to implement an option allowing user to choose which view to use. I will get back and note you when we can include this option to the release.



Thanks for your respond. Please keep in mind that in some cases the very slow response makes the tool unusable and it is not easy (sometimes is impossible) to grant enhanced privileges to users using the tool (in order to use dbaviews)