Code assistant performance

[mike]

Our code assistant performance is poor for column names and we have been unable to make it better.. If I type a table alias followed by a period, it takes 20 to 60 seconds for the column names to appear. So, I turned the code assistant off and try not to use it. I also made a custom table to hold our table/column names which is much faster.

I notice that there is a CANames.sql file for some of the code assistant queries. Could we get the query for column names added to that? We could use our custom table instead that way.

 

[mike]

Never mind. I found out that if I change the preference to "Starts With" instead of "Contains", it works well.

 

[mike]

After using this setup for a few days, performance is still poor sometimes and I turned it back off, so my original request is back. I don't know if CANames.sql would work because it has limited column names, but maybe something like that?

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 16\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 16 directory (e.g. C:\Users\<User>\AppData\Roaming\PLSQL Developer 16).

 

[mike]

Thanks Marco. I emailed the file.

 

[Marco Kalter]

Thanks. As a test, can you go to Preferences > User Interface > Code Assistant, disable the "Describe Context" option, restart PL/SQL Developer, and try again?

If it is still slow, please send me a new debug.txt file.

 

[D.Wenzel]

@mike we have Patched the canames.sql with other sql that works better in the data dictionary inside in our largest dev-system

No Support - 4 Inspriration only

select /*+ optimizer_features_enable('9.2.0') no_parallel*/ object_name, object_type
from sys.dba_objects o
where o.owner = :schema
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
;
select /*+ optimizer_features_enable('9.2.0') no_parallel*/ object_name, object_type
from (select connect_by_root(synonym_name) object_name, a.object_type
from sys.dba_synonyms s
CROSS JOIN LATERAL (select o.object_type
from sys.dba_objects o
where 1 = 1
and s.table_owner = o.owner
and s.table_name = o.object_name
and o.object_type in ('TABLE',
'VIEW',
'PACKAGE',
'TYPE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE') ) a
where 1 = 1
and connect_by_isleaf = 1
and connect_by_iscycle = 0
start with s.owner in ('PUBLIC', USER)
connect by nocycle s.owner = prior s.table_owner
and s.synonym_name = prior s.table_name)
where 1 = 1
and object_type is not null
;
select /*+ optimizer_features_enable('9.2.0') no_parallel*/ db_link as object_name, 'DATABASE LINK' as object_type
from sys.dba_db_links o
where o.owner = :schema
or o.owner = 'PUBLIC'
;