英文:
Oracle select * from all_sequences too slow
问题
我的项目使用了Hibernate，我希望能够减少启动时间（目前为1.5分钟），因此我检查了启动过程中发生了什么。在SessionFactory初始化过程中，其中一个发出的查询是：
select * from all_sequences;
这个查询需要近一分钟的时间！在Oracle SQL Developer中运行相同的查询也需要类似的时间。总共返回了102条记录。
还有其他查询运行正常（响应时间为几毫秒）。
为什么会这么慢？
英文:
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?
答案1
得分: 3
以下是您要翻译的内容：
收集数据字典和固定对象的优化器统计信息：
begin
dbms_stats.gather_dictionary_stats;
dbms_stats.gather_fixed_objects_stats;
end;
/
Oracle需要良好的对象统计信息才能构建良好的执行计划。有许多机制可以为我们的自定义对象收集统计信息，但偶尔我们也需要为系统对象收集统计信息。（虽然我对于开箱即用时需要这样做感到惊讶。通常只有在出现极端更改，比如创建了一百万个新序列之后，才会出现这些问题。）
如果收集优化器统计信息没有帮助，请尝试按照以下步骤生成执行计划，并将结果发布在问题中：
--运行查询：
select /*+ gather_plan_statistics */ * from all_sequences;
--查找SQL_ID：
select * from gv$sql where sql_text like '%gather_plan_statistics%';
--生成带有估计和实际结果的执行计划。
select *
from table(dbms_xplan.display_cursor(sql_id => '9wgbmhhrf0bwr', format=>'ALLSTATS LAST'));
英文:
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'));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论