‘SELECT COUNT (*)’作了什么？ - Oracle专题深入讨论 - ITPUB论坛

solearn
中级会员

精华贴数 0
个人空间 0
技术积分 915 (2047)
社区积分 16 (8518)
注册日期 2006-4-28
论坛徽章:1

#1

使用道具

发表于 2008-5-19 11:27

‘SELECT COUNT (*)’作了什么？

For better performance while accessing data in a read-only tablespace, you can issue
a query that accesses all of the blocks of the tables in the tablespace just before
making it read-only. A simple query, such as SELECT COUNT (*), executed
against each table ensures that the data blocks in the tablespace can be subsequently
accessed most efficiently. This eliminates the need for Oracle to check the status of
the transactions that most recently modified the blocks.

以上是administrator's guide上的一段话。我不理解SELECT COUNT (*)执行后，对数据块作了什么，而使得read only操作性能得到了改善。是关于cleanout吗？

__________________

某年，空中旅行。观舷窗外，景色灿烂。乃感而留言。

扶摇上青天，凌云近日边。
千光投下界，万色绣山川。
居高无阴雨，御风任左前。
层天绝尘境，云何不羡仙。

=====================
===抬起头，看看彩色的世界===
=====================

只看该作者

Yong Huang
版主

精华贴数 2
个人空间 0
技术积分 4178 (340)
社区积分 129 (3006)
注册日期 2001-10-9
论坛徽章:6

#2

使用道具

发表于 2008-5-19 21:12

Block cleanout, yes. The document failed to mention that if the table has a suitable index as is often the case, select count(*) will do a fast full index scan instead of full table scan. So select /*+full(tablename) */ count(*) is needed.

Yong Huang

只看该作者

solearn
中级会员

精华贴数 0
个人空间 0
技术积分 915 (2047)
社区积分 16 (8518)
注册日期 2006-4-28
论坛徽章:1

#3

使用道具

发表于 2008-5-20 09:35

QUOTE:

原帖由 Yong Huang 于 2008-5-19 21:12 发表

Block cleanout, yes. The document failed to mention that if the table has a suitable index as is often the case, select count(*) will do a fast full index scan instead of full table scan. So select /*+full(tablename) */ count(*) is needed.

Yong Huang

Thanks for reply.

This seems to implies that a full table scan is conducted to check if blocks are cleanouted while the datafiles switch to read-only. So the cleanout should be surely done. Now I wonder why doing cleanout job beforehand is better than doing the same job while switching to read-only.

And would 'analyze table XXX compute statistics' also work in this case?

__________________

某年，空中旅行。观舷窗外，景色灿烂。乃感而留言。

扶摇上青天，凌云近日边。
千光投下界，万色绣山川。
居高无阴雨，御风任左前。
层天绝尘境，云何不羡仙。

=====================
===抬起头，看看彩色的世界===
=====================

只看该作者

kl911
中级会员

精华贴数 0
个人空间 0
技术积分 634 (2997)
社区积分 8 (12213)
注册日期 2005-11-3
论坛徽章:1

#4

使用道具

发表于 2008-5-20 15:10

同问！

“executed
against each table ensures that the data blocks in the tablespace can be subsequently
accessed most efficiently. This eliminates the need for Oracle to check the status of
the transactions that most recently modified the blocks. ”
如果是full table scan的话，tablespace中的data blocks可以以最高效的方式被部分访问。
oracle因此不需要检查最近改变这些blocks的事务状态。

也不是很理解，先了解一下，如果非read-only的table具体过程是怎样的?
1. 是否get segment name, and header_filename, header_block ？
2. 是读入部分的块(包含5行的块)，进行排序，还是一次读取所有的块?
3. 如果是读取所有的块到buffer cache中，如果超过了大小，应该怎么处理？
4. 如果是部分读取，它是一什么样的单元来读呢？一次N个块，还是一次N行？

只看该作者

Yong Huang
版主

精华贴数 2
个人空间 0
技术积分 4178 (340)
社区积分 129 (3006)
注册日期 2001-10-9
论坛徽章:6

#5

使用道具

发表于 2008-5-20 21:18

QUOTE:

原帖由 solearn 于 2008-5-19 19:35 发表

This seems to implies that a full table scan is conducted to check if blocks are cleanouted while the datafiles switch to read-only. So the cleanout should be surely done. Now I wonder why doing cleanout job beforehand is better than doing the same job while switching to read-only.

And would 'analyze table XXX compute statistics' also work in this case?

They're saying "before", not "while" the tablespace is changed to read only. Can the clean out be done on blocks inside a read only tablespace? I doubt it. We can test to find out.

I think "analyze...compute" can do the same. I just did a test in 10.2.0.1. I do see db file scattered read on all the blocks my table segment covers.

Yong Huang

只看该作者

Yong Huang
版主

精华贴数 2
个人空间 0
技术积分 4178 (340)
社区积分 129 (3006)
注册日期 2001-10-9
论坛徽章:6

#6

使用道具

发表于 2008-5-21 01:03

QUOTE:

原帖由 Yong Huang 于 2008-5-20 07:18 发表

Can the clean out be done on blocks inside a read only tablespace? I doubt it. We can test to find out.

Yong Huang

I happened to read Bug 3801750. It says "This is a source of problems in the case that the index has been placed into a READ ONLY tablespace. The problem is that block cleanout may be required during the read of the index, but it is not possible to perform block cleanout of the current block image as the tablespace is READ ONLY. Hence an ORA-372 occurs."

Also interesting is "an index range or full scan prior to placing the tablespace in read only mode should clean out most blocks. However such a scan will not clean out all the branch blocks. Also there is no customer way to confirm all blocks are cleaned out prior making the tablespace read only"

Yong Huang

只看该作者

solearn
中级会员

精华贴数 0
个人空间 0
技术积分 915 (2047)
社区积分 16 (8518)
注册日期 2006-4-28
论坛徽章:1

#7

使用道具

发表于 2008-5-21 08:32

QUOTE:

原帖由 Yong Huang 于 2008-5-20 21:18 发表

They're saying "before", not "while" the tablespace is changed to read only. Can the clean out be done on blocks inside a read only tablespace? I doubt it. We can test to find out.

I think "analyze...compute" can do the same. I just did a test in 10.2.0.1. I do see db file scattered read on all the blocks my table segment covers.

Yong Huang

Oh, my 'while' is 'while the "alter tablespace read only" statement bounded out'. If I execute this "alter tablespace read only"sentence, the datafiles involved cannot switch to the 'read-only' status until cleanout operations are implicitly conducted. While I execute "select count(*)", cleanouts are also done. Why the latter action can get better performance? Why cleanout operations triggered by "alter tablespace read only" statement are more expensive than those by "select count(*)'?

__________________

某年，空中旅行。观舷窗外，景色灿烂。乃感而留言。

扶摇上青天，凌云近日边。
千光投下界，万色绣山川。
居高无阴雨，御风任左前。
层天绝尘境，云何不羡仙。

=====================
===抬起头，看看彩色的世界===
=====================

只看该作者

solearn
中级会员

精华贴数 0
个人空间 0
技术积分 915 (2047)
社区积分 16 (8518)
注册日期 2006-4-28
论坛徽章:1

#8

使用道具

发表于 2008-5-21 09:07

QUOTE:

原帖由 Yong Huang 于 2008-5-21 01:03 发表

I happened to read Bug 3801750. It says "This is a source of problems in the case that the index has been placed into a READ ONLY tablespace. The problem is that block cleanout may be required during the read of the index, but it is not possible to perform block cleanout of the current block image as the tablespace is READ ONLY. Hence an ORA-372 occurs."

Also interesting is "an index range or full scan prior to placing the tablespace in read only mode should clean out most blocks. However such a scan will not clean out all the branch blocks. Also there is no customer way to confirm all blocks are cleaned out prior making the tablespace read only"

Yong Huang

So index behaves differently than table here. You can always see bugs where you do not expect.

__________________

某年，空中旅行。观舷窗外，景色灿烂。乃感而留言。

扶摇上青天，凌云近日边。
千光投下界，万色绣山川。
居高无阴雨，御风任左前。
层天绝尘境，云何不羡仙。

=====================
===抬起头，看看彩色的世界===
=====================

只看该作者

wisdomone1
西北苍狼

希望会员

精华贴数 0
个人空间 0
技术积分 2478 (632)
社区积分 60 (4464)
注册日期 2007-3-15
论坛徽章:5

#9

使用道具

发表于 2008-5-22 00:02

学习.

只看该作者

sqysl
孤独剑客

来自山东
精华贴数 0
个人空间 0
技术积分 1268 (1376)
社区积分 31 (6200)
注册日期 2006-12-20
论坛徽章:0

#10

使用道具

发表于 2008-5-22 15:57

回复 #7 solearn 的帖子

因为你执行ALTER TABLESPACE XXX READ ONLY时，系统并不执行CLEAN OUT，这样表空间改为READ ONLY后，以后进行查询时，都要检查每个块上事务的状态，而这些事务实际上已经结束了，因此，这种检查是多余的，是因为块头中事务信息没及时CLEAN OUT的结果，因此，在将表空间READ ONLY前，先执行一个SELECT COUNT(*) ，对表空间里段的块头信息CLEAN OUT,这样将表空间READ ONLY后，随后的查询就不会再麻烦检查块头的事务状态了，个人想法，一起讨论，谢谢。

__________________

曾经沧海难为水，除却巫山不是云。
天若有情天亦老，人间正道是沧桑。

只看该作者