My project uses Hibernate and I wanted to increase startup time(currently 1.5 minutes), so I inspected what's happening on startup. During SessionFactory initialization, one of the queries issued is:
select * from all_sequences;
It takes close to a minute! The same query from Oracle SQL Developer takes similar amount of time. Total records returned are 102.
There are other queries which run normally(one digit ms response time)
Why is it so slow?
Gather optimizer statistics for the data dictionary and fixed objects:
begin
dbms_stats.gather_dictionary_stats;
dbms_stats.gather_fixed_objects_stats;
end;
/
Oracle needs good statistics for objects in order to build good execution plans. There are many mechanisms for gathering statistics for our custom objects, but occasionally we also need to gather statistics for system objects. (Although I'm surprised this is necessary right out of the box. Normally these problems only happen after extreme changes, like creating a million new sequences.)
If gathering optimizer statistics doesn't help, try generating an execution plan with the below steps, and posting the result in the question.
--Run the query:
select /*+ gather_plan_statistics */ * from all_sequences;
--Find the SQL_ID:
select * from gv$sql where sql_text like '%gather_plan_statistics%';
--Generate the execution plan, with estimated and actual results.
select *
from table(dbms_xplan.display_cursor(sql_id => '9wgbmhhrf0bwr', format=>'ALLSTATS LAST'));