7.1.4.1389 Browser -- refresh 'Tables'

[pwo1433]

We have 7.1.4.1389 and use Oracle 10g DB.
Clicking the + on Tables in the Browser takes very long (several minutes) to return the tables. Other staff here still have versions 6.0.5 or 7.0.3 and the Tables refresh in seconds.
On the 7.1.4.1389 we have the 'noqueryhints' parameter installed but it doesn't help on this.
We've looked through the online help (User Guide) and found nothing about this.
Any ideas how to speed-up the Browser's Table refresh.
Thanks.

 

[Marco Kalter]

Can you upgrade to 7.1.5 and try again? If this is still slow, we can investigate which query is slow in 7.1.5 and fast in 7.0.3, and what the exact difference is.

 

[pwo1433]

Can we have 2 versions on a pc at the same time ?
We wondered if that would cause problems.
Thanks.
Paul and Ron

 

[Marco Kalter]

Yes, this is possible. You can install them in separate directories.

 

[Tom Laughery]

I am running version 7.1.5.1396 and we recently upgraded to Oracle 10.2.0.4 and have begun experiencing this problem. Are there any resolutions?

 

[Marc T]

We are currently running version 7.1.5.1396 on a 10.2.0.4 oracle database and we are experiencing the same problem. We did quite a bit of research on this.

If you use filter "All object" it takes several minutes (between 8 and 15 minutes) to get an answer. If you use filter "My objects" is takes a few seconds (around 10 seconds).

Thanks

We traced the query that is being executed by plsql developer to perform the query on the dictionary tables when using all_objects as a filter.

Query performed:
select object_name, owner object_owner, status, object_type, created, last_ddl_time
from sys.all_objects o
where object_type = 'TABLE'
and object_name not like 'BIN$%'
order by decode(owner, user, 0, 1),
owner,
object_name

Oracle note 364822.1 mentions that "The definition of ALL_OBJECTS and ALL_ARGUMENTS have changed in version 10.2 as a result of a fix to an outstanding defect. A side affect of this new, more complex, definition is that queries that select against the new view definitions have much more complicated execution plans and maybe more expensive when compared to execution plans in earlier versions (such as 9.2)."

As a workaround they suggest to that it is possible to revert to the old (9.2) ALL_OBJECTS view definition by creating a new view with the old definition and then allowing the users to select from that new view.

Since the query in plsql developer hardcodes "from sys.all_objects " in its query, it is not possible to apply the workaround. In an earlier forum (see: Sql statements executed in 10gR2 when pl/sql developer starts, Jan. 2007)) you suggested an ER to enable modification of the query in the filter (not just the where and order by clauses).

Would it be possible to have this ER or for you to change the query to simply use all_objects instead of sys.all_objects?

 

[Marco Kalter]

Could you go to Tools > Browser Filters and remove the order by clause of the "All objects" filter to see if that affects performance?

 

[Marc T]

As requested I removed the order by clause and it did not improve response time. It still takes aroud 10 minutes to get an answer.

 

[Marco Kalter]

That would make the sys.all_objects view unusable, wouldn't it? The select list, view list, and where clause are trivial, and yet it takes 10 minutes. This does not seem right, regardless of Oracle note 364822.1.

 

[Marc T]

The all_objects view is much more complex in 10g (120 more lines).

You can execute the query and see for yourself.

Even though the view is much slower in 10G (all_synonyms has the same problem), it is not the end user that is affected (they don't query database objects), it's the dba's, developpers etc.

I am not sure if Oracle is planning to do anything to solve the problem.

In the mean time, we have to find a workaround to be productive.

 

[Marco Kalter]

You can execute the query and see for yourself.

It just takes 6 seconds for me, including the order by clause. That's why I think something else must be wrong if it takes 10 minutes for you.

 

[Worker]

Is it just the Tables folder that is affected, or do other folders (e.g: Packages) also take 10 minutes?

How many Tables are returned by the query?

 

[Marc T]

Marco,

this is the kind of response time we used to get against a 9i database.

I have tried the same query against other tools (SQLPlus, SQl Developer) and I get the same response time.

It is not the tool that is a problem, it is the fact that the query is built against the sys.all_objects view. The all_object view is slow.

 

[Marc T]

From the test we have done, here are some results:

Filter: All objects
Folder selected: Tables
Objects returned: 1336
Response time (mm:ss): 10:15

Filter: All objects
Folder selected: Views
Objects returned: 2827
Response time (mm:ss): 11:45

Filter: All objects
Folder selected: Packages
Objects returned: 1818
Response time (mm:ss): 10:32

Filter: My objects
Folder selected: Tables
Objects returned: 731
Response time (mm:ss): 00:06

Filter: My objects
Folder selected: Views
Objects returned: 466
Response time (mm:ss): 00:05

Filter: My objects
Folder selected: Packages
Objects returned: 271
Response time (mm:ss): 00:03

 

[Alan Jacobson]

We have both Oracle 10.2.0.3 and 10.2.0.4 installed. The query against 10.2.0.3 is fast but against 10.2.0.4 is extremely slow. Oracle did make extensive changes in the view between these two releases of the database.

If pl/sql developer used a synonym instead of reverencing the view directly, we could find a workaround to this problem.

As it is, we are not going to change any of the sys views.

 

[Marc T]

Any news on this? thanks

 

[pwo1433]

We remain very interested in a solution too. We are on Oracle 10.2.0.3 with PL.SQL Dev. 7.1.4.1389 and experience 2 very, very slow responses (several minutes each):
clicking on the + on Tables in the Browser window
and
on entering the period (.) after the schema in the SQL or Command Window.
(It used to take long for the fields to come back after entering the period after the table name or alisis, but once we used the parameter 'NoQueryHints', that was solved.)

We have 100s of developers and power users working with Dev. and these very slow responses are slowing their productivity.

Thanks for everyones help and ideas.
Paul

 

[pwo1433]

Hello Marco or anyone,

Any ideas yet ?
" We remain very interested in a solution too. We are on Oracle 10.2.0.3 with PL.SQL Dev. 7.1.4.1389 and experience 2 very, very slow responses (several minutes each):
clicking on the + on Tables in the Browser window
and
on entering the period (.) after the schema in the SQL or Command Window. "
Thanks.
Paul

 

[da bull]

I have also noticed this slowness the first time I attempt to open the Views for the first time each day. After I have waited up to 5+ minutes for them to open, I can go and close and reopen the Views many times without slows, but it would be nice to open them the first time without such a delay.