index 集群因子高

sqysl

楼主这么分析有些牵强，应该还是应用的问题。

opsc

原帖由 Yong Huang 于 2009-10-28 04:33 发表


To reduce the clustering factor of an index, you have to rebuild the table in the order of the index column(s). It sounds weird because a heap table is said to have no internal order. But more or less there *is* some order in it. To do this data re-ordering, you need to create a new table as select * from the old table order by the column(s) of the index. And make other changes as needed (constraints, etc).

The wait event "gc cr block 2-way" is not an idle wait. The parameters are undocumented. But I believe p1 and p2 are simply the file# and block#. How do you know this wait is related to the high index clustering factor? Did you check p1 and p2 in v$session or v$session_wait?

Always tell us Oracle version.

Yong Huang

Agree that it's not easy to control the cluster factor.

This wait event looks good if you do need to scan this amount of blocks unless you can do some tuning to your application requirement or sql performance.

viadeazhu

同样没找到具体的官方资料，但我还是转载一个牛人的观点吧：
这些等待可以被忽略。


The following wait events indicate that the remotely cached blocks were shipped to the local instance without having been busy, pinned or requiring a log flush and can safely be ignored:
* gc current block 2-way
* gc current block 3-way
* gc cr block 2-way
* gc cr block 3-way
http://www.dba-oracle.com/real_application_clusters_rac_grid/performance_analysis.htm

killkill_shadow

按照被索引列的进行排序重建表，可以降低该索引的clusering factor，但是就怕影响到同在这张表上面的索引的clusering factor。

Yong Huang

原帖由 viadeazhu 于 2009-10-28 20:25 发表
同样没找到具体的官方资料，但我还是转载一个牛人的观点吧：
这些等待可以被忽略。

The following wait events indicate that the remotely cached blocks were shipped to the local instance without having been busy, pinned or requiring a log flush and can safely be ignored:
* gc current block 2-way
* gc current block 3-way
* gc cr block 2-way
* gc cr block 3-way
http://www.dba-oracle.com/real_a ... rmance_analysis.htm

I disagree. These events should NOT be ignored. Their high numbers generally indicate high current or consistent gets.

Not all 牛人 are created equal. Ignore this one. Always take his words with a grain of salt.

Yong Huang

Yong Huang

原帖由 killkill_shadow 于 2009-10-28 22:36 发表
按照被索引列的进行排序重建表，可以降低该索引的clusering factor，但是就怕影响到同在这张表上面的索引的clusering factor。

Exactly. There's only one real (approximate) order in the table. Before you rebuild the table to make one index happy, keep other indexes in mind. Only do this for the most important index.

Yong Huang

killkill_shadow

原帖由 Yong Huang 于 2009-10-30 01:41 发表


Exactly. There's only one real (approximate) order in the table. Before you rebuild the table to make one index happy, keep other indexes in mind. Only do this for the most important index.

Yong Huang

Great!  That is the KEY POINT.
BTW : How to find out the most important (Hot,Most Use)  index any Good Idea or SQL ? ^_^

Yong Huang

原帖由 killkill_shadow 于 2009-11-11 02:16 发表
Great!  That is the KEY POINT.
BTW : How to find out the most important (Hot,Most Use)  index any Good Idea or SQL ? ^_^

There's no magic here. You just keep monitoring your database. Find the most expensive SQLs, in terms of buffer gets and buffer gets per execution. Check their plans. If some indexes are frequently used, focus on them. See if their clustering factors are high. Use your own judgement as to whether to reorganize the tables.

Yong Huang