Good day,
The application seem to hang when trying to export user objects. But it is rather due to a bad query against the dictionary views.
Here is the query made by Pl/sql developer when I bring the export user objects view.
select *
from sys.all_objects o
where owner = 'THIS_OBJECT_OWNER'
and object_name not like 'BIN$%'
and object_type in ('PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'JAVA SOURCE', 'LIBRARY', 'DIRECTORY','TABLE', 'SEQUENCE', 'VIEW', MATERIALIZED VIEW', 'SYNONYM')
and (object_type 'TYPE' or exists (select null from all_types t where t.owner = o.owner and t.type_name = o.object_name))
I have bolded the condition that cause this problem. all_types is a very slow view in environments with large number of objects like ours. it can take more than 1 full minute to retrieve all types by owner and around 2-3 seconds by owner and object_name. If we multiple this 2-3 seconds by 600 objects, we get around 20-30 minutes to retrieve this list. Now, if we remove this check, the list gets populated very quickly. Any reason why this check on all_types view is occuring?
The application seem to hang when trying to export user objects. But it is rather due to a bad query against the dictionary views.
Here is the query made by Pl/sql developer when I bring the export user objects view.
select *
from sys.all_objects o
where owner = 'THIS_OBJECT_OWNER'
and object_name not like 'BIN$%'
and object_type in ('PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'JAVA SOURCE', 'LIBRARY', 'DIRECTORY','TABLE', 'SEQUENCE', 'VIEW', MATERIALIZED VIEW', 'SYNONYM')
and (object_type 'TYPE' or exists (select null from all_types t where t.owner = o.owner and t.type_name = o.object_name))
I have bolded the condition that cause this problem. all_types is a very slow view in environments with large number of objects like ours. it can take more than 1 full minute to retrieve all types by owner and around 2-3 seconds by owner and object_name. If we multiple this 2-3 seconds by 600 objects, we get around 20-30 minutes to retrieve this list. Now, if we remove this check, the list gets populated very quickly. Any reason why this check on all_types view is occuring?
