help! 查询DBA_SEGMENTS突然变得很慢

richto · 发表于 2002-9-17 17:00

So, you can temporary use this SQL 'select count(*) from sys.seg$' TO replace 'select count(*) from segments'. We have a customer who have the same problem, that is Oracle815 problem. May be some index or table extents in System cannot be cached for nested loop operation.

feng_xin · 发表于 2002-9-17 17:14

最初由 richto 发布
[B]So, you can temporary use this SQL 'select count(*) from sys.seg$' TO replace 'select count(*) from segments'. We have a customer who have the same problem, that is Oracle815 problem. May be some index or table extents in System cannot be cached for nested loop operation. [/B]

May be some index or table extents in System cannot be cached for nested loop operation？对这句不是很理解，能详细讲讲吗？在什么情况下会出现这种情况，是由于什么原因引起的，出现这种情况为什么会严重影响查询性能？

真对不起，浪费您的时间了，您也可以给我推荐一些资料来看。非常感谢！

biti_rainy · 发表于 2002-9-17 17:29

也许一些系统的表或者索引的数据不能被缓存在内存中用于nested loop

这样nested loop 每次都需要直接从物理文件读取

feng_xin · 发表于 2002-9-17 17:39

最初由 biti_rainy 发布
[B]也许一些系统的表或者索引的数据不能被缓存在内存中用于nested loop

这样nested loop 每次都需要直接从物理文件读取 [/B]

我想知道为什么不能缓存在内存中呢？在什么情况下会出现这种情况，是由于什么原因引起的？

biti_rainy · 发表于 2002-9-17 17:43

我只是翻译这句话

别问我为什么

如果真如楼上的这个预测，那就是bug

richto · 发表于 2002-9-17 17:44

Thanks, biti_rainy !
That is what I guessed, since the problem happened in Israel, I could not check their database myself. According to your plan, which is the same as what we have in my database. The symptom is that both of you have many tables and indexes in your database. Due to some table growth, new extents are created in System, if Oracle has problem to cache(or being flushed) new extents. For Nested Loop operation is a disaster. Oracle have to re-read a table and index everytime. I think that is the problem on Oracle815 only. I have not seem any in other versions.
I don't recommend Import and Expert system tables.