Plsqldeveloper internal select takes too much CPU

[PMrb]

Hi Marco

We analyzed database pefromance after stars using plsqldeveloper 11 and found that most usage of cpu is done by plsqldeveloper select (see select down on this post). Problem is too that this select runs even Ive loged out from database, select is still there as active session. This needs to be tune up, main problem is that select is slow and takes up too much cpu. Anyway why this select needs to be run? Is there any option how to disable it? And what happens when it will be disabled?

select s.synonym_name as object_name, o.object_type
from all_synonyms s, sys.all_objects o
where s.owner in ('PUBLIC', :schema)
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')

Pavel

 

[IvanZ]

It's probably used by code assistant or object browser.

Your database must be idle for that SQL to appear in TOP.

 

[PMrb]

No, our database isnt on idle and this statement takes almost 1 cpu...
Marco can you look onto this? We will need to resolve this soon, because now only two of us using Plsqldeveloper 11, and my connection takes 1 cpu, what will happen when all other users starts to use plsqldeveloper? If everyone takes 1 cpu our machine will go down, which will be on production databases big problem...

EDIT - this is command which was on 10 too (thanks Marco for clarified this), after some tests I believe that this problem appears only on one database, so we try to check why. On my other databases where Ive tried to connect this command takes too some cpu, but its realized much quicker, so its not so big problem.

Pavel

 

[Maxmix]

I vaguely remember, that queries to dictionary tables could be very slow, if we (by mistake) gather statistics for SYS schema. Last time I saw it was 10 years ago I think.

If you only have problems with the Code Assistant, then I found your SELECT in the CANames.sql , so it could be tuned or removed from there.

 

[IvanZ]

Times have changed.
Gathering stats on SYS is recommended since 11.2.

 

[Hilarion]

That is true, but those should be generally gathered using methods dedicated for dictionary data, etc. like DBMS_STATS.GATHER_DICTIONARY_STATS, DBMS_STATS.GATHER_SYSTEM_STATS.
Gathering stats for SYS schema (or any other, but this one is quite critical) using improper parameters may lead to performance degradation.

 

[Marco Kalter]

You can remove or change this query the CANames.sql file in the PL/SQL Developer installation directory. As a result the Code Assistant will no longer display synonyms when typing partial synonym names. For example, when a user types

dbms_ou

the Code Assistant will no longer suggest "dbms_output" (which is a public synonym for sys.dbms_output).