Problem selecting 'All Objects' when connected against 10g

[psneeley]

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

 

[Marco Kalter]

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.

 

[psneeley]

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

 

[psneeley]

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

 

[Marco Kalter]

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

 

[psneeley]

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

 

[psneeley]

{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

 

[Marco Kalter]

Sure, I will see what I can do.

 

[Marco Kalter]

You can download a fix for the copy function here .

 

[psneeley]

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

 

[Marco Kalter]

My apologies, please try again from here .

 

[psneeley]

I downloaded and installed it. Unfortunately, it still doesn't let me copy from the result set and paste into, say, MS Word, or notepad, etc.

(Note: The executable is named 31.272 but when it installs, clicking on the 'i' shows it as 3.0.1.271 -- I doubt this is a problem though)

 

[Marco Kalter]

Works fine for me. What exactly goes wrong?

 

[psneeley]

It works for you? How?

I query a table to get the result set displayed in the lower pane, then select just one column/row (any column/row)and right click, I can use copy to copy it (or alternately, Cntrl-C). But if I open up MS Word, or notepad, etc., and try to paste, the paste option on the menu isn't even enabled to do the paste.

I can easily copy and paste between two notepad sessions, or between MS Word and notepad, etc., so it isn't that some 'glitch' has disabled the clipboard.

The clipboard viewer (run, 'clipbrd') in Windows confirms that nothing is placed on the clipboard by copying from QueryReporter. It -does- confirm that copying between notepad or MS Word sessions does place content on the clipboard.

Do you suspect a set-up or security problem on my end?

 

[Marco Kalter]

It works for you? How?

I copy an arbitrary selection of fields from the result set, and paste it elsewhere.

Do you suspect a set-up or security problem on my end?

Maybe. Do you have some clipboard management tool installed?

 

[psneeley]

No, No clipboard manager installed at all.

But it is embarrassing, because I just asked a co-worker to try it and it works for him. Therefore, it -is- a problem with my machine or software configuration. I'm not sure why it works for me on the old queryreporter, but not the new; or why it works on pl/sql developer and not the new queryreporter.

Do you have any ideas of what might have changed?

Best Wishes,

Steve

 

[Marco Kalter]

I have no immediate clues.