Querys using sys.all_synonyms and sys.all_objects ??

[rtsaito]

Hi,

Sorry if this a newbee question, but I did a search on the forum and could not find anything about it.

Does anyone know if this query is used by pl/sql and when? My problem is that it takes a lot of the oracle cpu cycles and goes to "internal lock wait".

Thanks.

--
select s.synonym_name object_name, o.object_type
from sys.all_synonyms s,
sys.all_objects o
where s.owner in ('PUBLIC', user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')

 

[Marco Kalter]

This query is executed when you open the Synonyms folder in the Object Browser.

------------------
Marco Kalter
Allround Automations

 

[rtsaito]

hum... is there any way to disable it or minimize the impact on the database?

We just downgraded some of the machines to olders versions of pl/sql and the problem disappeared on those machines.

thanks!

 

[Marco Kalter]

This query responds instantaneous when I try it. Maybe the Oracle Dictionary tables need some tuning in this database instance? Maybe some dictionary index was dropped?

------------------
Marco Kalter
Allround Automations

 

[l.pohlmann]

found this topic by forum-search, and just wanted to bring it up again.

i have a similar problem. this query is always running (and using LOTS of cpu und logical-reads) as long as i have an instance on pl-sql-developer open, and is not restricted to its own session, but seems to pop-up randomly on sessions of other users.

when i snipe such a session, it continues to run on another available session, not matter who the owner of the session is. (at least the enterprise-manager shows it that way).

as soon as i close all instances of the developer, this query vanishes.

this happens since the last update. any ideas?

 

[l.pohlmann]

update: this does not happen on every server, just on one. so it seems that the db is damaged.

 

[l.pohlmann]

another update:

the problem occurs here:

where s.owner in ('PUBLIC', user)

i tried some variations:

where s.owner in ('PUBLIC') -- fast
where s.owner in (user) -- fast
where s.owner in ( 'USERNAME' ) -- fast
where s.owner = user -- fast
where s.owner = 'PUBLIC' --fast
where s.owner = 'USERNAME' --fast

without owner-clause at all -- fast

where s.owner in ('PUBLIC', 'USERNAME') -- not ending
where ( s.owner = 'PUBLIC' or s.owner = user ) -- not ending

etc...

i'm confused.

 

[Marco Kalter]

We could change the query to a union to fix the problem in your case, but there is definitely something wrong with your dictionary tables or views.

 

[lgbs]

We are experiencing a similar problem. When I first login to PLSQL Developer I can see on the database an ACTIVE session executing the following SQL:

select s.synonym_name object_name, o.object_type
from sys.all_synonyms s,
sys.all_objects o
where s.owner in "SYS_B_0", user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in "SYS_B_1", :"SYS_B_2", :"SYS_B_3",:"SYS_B_4", :"SYS_B_5", :"SYS_B_6", :"SYS_B_7")

This is also on the OS server side as a session using at least 16% cpu time. I know you mention a data dictionary issue, but is there any other possibility it's the application causing this performance hit?

 

[Marco Kalter]

Does it help if you execute this query with "where s.owner = user" instead?

This query (which runs in a background thread) can be disabled if you disable the "Describe context" preference (Tools > Preferences > Code Assistant).

 

[HK]

Hello,
We have the same issue - have disabled the feature, but it is still being called out somewhere in the program..

Is there any way we can change this query with in our installation of plsqldev ? I saw that adding a rule hint made it come back much much faster.

Thanks.

 

[Marco Kalter]

The query cannot be modified externally. We'll consider an option for this in the next release.