We are moving to 10g in a couple of weeks, but testing shows a problem with QueryReporter. When trying to display 'all objects' in the object browser, it takes an inordinate amount of time (> 20 minutes). Our DBA, in monitoring it, says that it is doing a select against SYS.All_Objects. Is there a way to make it go against DBA_Objects instead? Or, is there a configuration option to make it work against a 10g database?

It is interesting in that I, personally, use PL/SQL Developer, and in doing the same thing as our users, it is going against DBA_Objects.

Also, this is not a problem going against a 9i database. So something about 10g is causing the problem?

Thanks for any help you can give,

Steve

PL/SQL Developer has an option to use DBA_OBJECTS. Query Reporter does not have such an option though. All you can do is select "My objects" instead of "All objects", or investigate why the ALL_OBJECTS view is so slow.

Is that the 'use DBA views if available' option in PL/SQL Developer that you are talking about?

Turning it off or on seems to make no difference for me in 'object browsing' the tables, etc. while in PL/SQL Developer.

There were some indications in threads in the PL/SQL developer forum that there -were- performance problems in going against the dictionary object in 10g. Do you think this is the problem? But I couldn't see any 'fixes' to the issue.

The underlying query that QueryReporter is doing to 'browse' the objects has a 'mean' subquery in it that seems to give 9i no problems at all, but chokes 10g to a grinding halt.

select object_name, owner object_owner, status, object_type, created, last_ddl_time
from sys.all_objects o1
where object_type = 'TABLE'
and (object_type != 'SYNONYM' or
(
(owner = user or owner = 'PUBLIC') and
exists (select * from sys.all_objects o2,
sys.all_synonyms s2
where s2.owner = o1.owner
and s2.synonym_name = o1.object_name
and o2.owner = s2.table_owner
and o2.object_name = s2.table_name
and o2.object_type in ('TABLE', 'VIEW')))
)
order by decode(owner, user, 0, 1),
owner,
object_name

One more finding: Inserting a hint of 'RULE' in the above query seems to fix everything. The query return times goes (in my case and for the 1800 or so objects I have access to) from 20 minutes to 1.2 seconds!

To say "Just use MyObjects' in the drop-down list won't work because most users don't own the objects they have access to. For instance, in my case those 1800 objects are owned by 10 or 20 schema owners (some pieces of software, some individuals). Using 'MyObjects' only gives me access to those objects that I personally have created.

Question: If there is no 'good' solution, could we try a version of QueryReporter that has the 'RULE' hint inserted in the query?

Thanks,

Steve

You can download a Query Reporter with the rule hint applied here . Let me know if this helps.

Yes, it helps very much. The object browser is not 'lightning' fast -- sub-second.

I know you are working on it, but any chance we could have a version of this 'RULE' QueryReporter with a fix to topic 134 web page (can't copy from result set) in place?

Thanks for all your good work and this wonderful tool,

Steve

{for some reason I couldn't edit my mistake on the post)

I mean

Yes, it helps very much. The object browser is NOW 'lightning' fast -- sub-second.

I know you are working on it, but any chance we could have a version of this 'RULE' QueryReporter with a fix to topic 134 web page (can't copy from result set) in place?

Thanks for all your good work and this wonderful tool,

Steve

Sure, I will see what I can do.

You can download a fix for the copy function here .

I get 'Evaluation version needs Delphi' error message when I try to run it.