Jobs tree ridiculous slow open in 12c.

[jareeq]

Hi,
Currently using developer 10.0.5 as well as demo of 12.1 with database 12c
Problem is with slow opening of scheduler jobs from objects tree.
We have over 500 jobs defined, problems started from about 250.
After debugging is on it looks like PL/SQL Developer spends 10-15 seconds (after a while it speeds up to about 5-7 seconds) on reading each of job so tree opens after ... 1 hour.
Similar problem appears with "Edit table" it takes about 5-10 seconds to see properties.
Same amount of jobs (or tables) in 11g or 10g opens uder second.
There is clearly something wrong with query that generates output for tree - it work just fine in 10 and 11g but in 12c it is terrible slow.
Example from SQLDebug for job tree open (it appears for every job so x500 -duration is killer):
00:00:53.139 TOracleQuery $0A36F540 End (1 record processed)
Duration = 10,343
00:00:53.140 TOracleQuery $0A36F540 Start Query.Execute
SQL = select o.*, s.*
from sys.all_objects o,
sys.all_scheduler_jobs s
where s.owner = bject_owner
and s.job_name = bject_name
and o.owner = bject_owner
and o.object_name = bject_name
and :sub_object is null
and bject_type is not null
:OBJECT_TYPE = JOB
:OBJECT_OWNER = AAAAA
:OBJECT_NAME = CCCCCCCCCCCCCCCCCCCCC
:SUB_OBJECT =
:OBJECT_PLURAL = jobs
:OWNER_FIELD = s.owner
:NAME_FIELD = s.job_name
00:01:05.157 TOracleQuery $0A36F540 End (1 record processed)
Duration = 12,012
00:01:05.158 TOracleQuery $0A36F540 Start Query.Execute
SQL = select o.*, s.*
from sys.all_objects o,
sys.all_scheduler_jobs s
where s.owner = bject_owner
and s.job_name = bject_name
and o.owner = bject_owner
and o.object_name = bject_name
and :sub_object is null
and bject_type is not null
:OBJECT_TYPE = JOB
:OBJECT_OWNER = AAAAA
:OBJECT_NAME = XXXXXXXXXXXXXXXXX
:SUB_OBJECT =
:OBJECT_PLURAL = jobs
:OWNER_FIELD = s.owner
:NAME_FIELD = s.job_name
00:01:15.516 TOracleQuery $0A36F540 End (1 record processed)
Duration = 10,358
00:01:15.517 TOracleQuery $0A36F540 Start Query.Execute
SQL = select o.*, s.*
from sys.all_objects o,
sys.all_scheduler_jobs s
where s.owner = bject_owner
and s.job_name = bject_name
and o.owner = bject_owner
and o.object_name = bject_name
and :sub_object is null
and bject_type is not null
:OBJECT_TYPE = JOB
:OBJECT_OWNER = AAAAA
:OBJECT_NAME = ZZZZZZZZZZZZZZZZZZ
:SUB_OBJECT =
:OBJECT_PLURAL = jobs
:OWNER_FIELD = s.owner
:NAME_FIELD = s.job_name

Can you help ?

 

[Marco Kalter]

It seems like a simple query where all key information is provided and that returns just 1 row. It is instantaneous on our 12c database. Can you ask your DBA to analyze why this simple query takes 12 seconds on your database?

 

[IvanZ]

Run dbms_stats.gather_dictionary_stats

 

[jareeq]

Sorry for for delay,
Indeed it looks like problem in 12c however after DBA created stats it is still slow.
Problem is less severe after disabling dynamic sampling in entire database.