| 如何为新索引初始大小做适当评估 |
| ［ 作者：佚名 转贴自：网络转载 阅读次数：31 更新时间：2007-5-10 15:02:00 录入：刘光勇 ］ |
|
|
|
本系列有两部分，我们将在第一部分中讨论，如何为一个新的索引的初始大小做一个适当的评估。已存在的索引如何? 它们的大小是否合适?在现有系统中，正如我们最常见的：索引被依据某些评估制作出来，现在，我们需要来计算它的大小是否合适。
Validate Structure
首先，我们用“analyze index … validate structure”这个命令去校验索引的大小。此命令会将结果放入 index_stats 表中，并生成有关索引的详细视图。使用这个方法，我遇到的问题是：一，它运行缓慢；二，“analyze”这个命令会丢失客户；三，结果会因分区索引或子分区索引而不同。因此我打算找寻一个新的方法。
dba_indexes
通常情况下，我们至少一周分析一次表，一般都在晚上。因此，我们所需要的统计都已经可用。
从8.0开始，ORACLE把 num_rows 加入 dba_indexes 表. 之前，我们需要将dba_indexes 和 dba_tables 做一个联合查询才能得到一个索引的num_rows 。在执行这个操作时，我们没有考虑到索引值可能为空而没有被包括在内的情况。索引的行数有可能会小于或等于表的行数。因此，我们所添加的区域所占用的空间大小就最终决定了索引的空间大小。
运行下边的语句，可以查看当前表TASK的索引用到的空间：
select a.owner,a.index_name,a.leaf_blocks,a.num_rows
from dba_indexes a
where a.owner not in ('SYS','SYSTEM')
and a.index_type!='BITMAP'
and a.last_analyzed is not null
and a.table_name = 'TASK';
OWNER INDEX_NAME LEAF_BLOCKS NUM_ROWS
ORADBA TASK_APPT_FK 65 23771
ORADBA TASK_CENTER_FK 99 45975
ORADBA TASK_CLIENT_FK 101 45975
ORADBA TASK_ENC_FK 117 45975
ORADBA TASK_TYPE_FK 74 45975
ORADBA TASK_DT_REQ 84 45975
看这里，我们查出当前索引 task_appt_ft 的23771行 都在65 个块中，可能有不用的空间在这些 65个块中，但是我们稍后将会处理。这意味着，我们在每个块中大约拥有366行 (23,771/65) 。使用在上一章--B-Tree 大小中我们提到的逻辑，如果我们有超过 366个块，那么我们会有一个branch blocks。 但是这个索引只有这个root block。而索引的真正需求是65 leaf_blocks+1 root block =66个块，正像它目前被定义的一样。
那么索引到底需要多大空间呢？我们可以从dba_segments 找到答案，让我们把它加入到我们的查询中；
select a.owner,a.index_name,a.leaf_blocks,a.num_rows,b.blocks
from dba_indexes a
,dba_segments b
where a.owner not in ('SYS','SYSTEM')
and a.index_type!='BITMAP'
and a.last_analyzed is not null
and a.table_name = 'TASK'
and a.owner = b.owner
and a.index_name = b.segment_name;
OWNER INDEX_NAME LEAF_BLOCKS NUM_ROWS BLOCKS
ORADBA TASK_APPT_FK 65 23771 175
ORADBA TASK_CENTER_FK 99 45975 145
ORADBA TASK_CLIENT_FK 101 45975 205
ORADBA TASK_ENC_FK 117 45975 215
ORADBA TASK_TYPE_FK 74 45975 195
ORADBA TASK_DT_REQ 84 45975 86
我们会发现 task_appt_fk 索引需要 66个块，但是现在却占有了175个块。因此，如果我们按照正确的大小重新建立索引，我们可以释放109个块。而所有的这些则是建立于先前运行的没有丢失任何用户的统计的基础之上的。我可以选择一天中的任何时间。
重新指定大小和碎片整理的逻辑步骤：
那么，我们使用这些新的发现以做得更好？这里有一个最好的案例：我们制作一个完整的表空间时，可以使重新设定大小和整理表空间碎片同时进行。具体步骤如下：
• 根据dba_indexes索引的大小，在不同得表空间中重新创建索引。这会去掉索引中任何被删除的空间。
• Coalesce表空间
• 通过分析这些索引得到基于分钟的统计
• 把索引重新创建回index tablespace
我们需要一个简单的方法来详细说明刚刚进行过的每一步。我通常只是把名字放在这个仅仅为了练习而存在的表中。当你完成时，确认这个临时表已删除。稍后，我们也pull out 块的大小，以使语句能够快一些：
create table t_names
storage (initial 64K next 64K pctincrease 0)
pctfree 0 pctused 80
as select owner,index_name
from dba_indexes
where tablespace_name = 'HRX';
variable block_size number
begin
select to_number(value) into :block_size
from v$parameter where name = 'db_block_size';
end;
/
现在，如果觉得数据有些陈旧，我们可以将索引的分析结果保存成文本并且运行该文本。这样做不会丢失任何用户：
spool c:\analyze_index.sql
select 'analyze index '||owner||'.'||index_name||' compute statistics;'
from t_names;
spool off
@c:\analyze_index
运行结果如下：
analyze index ORADBA.TASK_DT_REQ compute statistics;
analyze index ORADBA.TASK_TYPE_FK compute statistics;
analyze index ORADBA.TASK_ENC_FK compute statistics;
analyze index ORADBA.TASK_CLIENT_FK compute statistics;
analyze index ORADBA.TASK_APPT_FK compute statistics;
analyze index ORADBA.TASK_CENTER_FK compute statistics;
下一个步骤要求我们授予所移动的索引的用户在新的目标表空间中的权限，你可以通过查看 dba_ts_quotas 和 dba_sys_privs，得知他们是否已经得到权限：
select distinct 'alter user '||owner||' quota unlimited on hrd;'
from t_names a
where not exists (select 'x' from dba_sys_privs
where a.owner = grantee and rownum =1)
and not exists (select 'x' from dba_ts_quotas
where tablespace_name = 'HRD'
and a.owner = username and rownum =1);
接着我们得出结论：
alter user ORADBA quota unlimited on hrd;
完成后，确认已经将我们所改变的用户的权限恢复为0。现在基础工作已经完成，我们可以开始执行真正的移动：
spool c:\resize_new_index.sql
select 'alter index '||a.owner||'.'||a.index_name||
' rebuild tablespace hrd'||chr(10)||
' storage(initial '||
((decode(blevel
,0,0
,ceil(a.leaf_blocks/trunc(a.num_rows/a.leaf_blocks)))
+a.leaf_blocks)
*:block_size)||
' next '||a.next_extent||
' maxextents '||a.max_extents||' pctincrease 0)'||
' nologging;'
from dba_indexes a
,dba_segments b
,t_names c
where a.leaf_blocks > 0
and a.num_rows > 0
and a.owner not in ('SYS','SYSTEM')
and a.index_type != 'BITMAP'
and a.last_analyzed is not null
and a.owner = b.owner
and a.index_name = b.segment_name
and a.owner = c.owner
and a.index_name = c.index_name
order by (decode(blevel,0,0
,ceil(a.leaf_blocks/trunc(a.num_rows/a.leaf_blocks)))+a.leaf_blocks)
;
spool off
运行结果如下：
alter index ORADBA.TASK_APPT_FK rebuild tablespace hrd
storage(initial 1081344 next 2097152 maxextents 1017 pctincrease 0)
nologging;
alter index ORADBA.TASK_TYPE_FK rebuild tablespace hrd
storage(initial 1228800 next 2097152 maxextents 1017 pctincrease 0)
nologging;
alter index ORADBA.TASK_DT_REQ rebuild tablespace hrd
storage(initial 1392640 next 65536 maxextents 1017 pctincrease 0)
nologging;
alter index ORADBA.TASK_CENTER_FK rebuild tablespace hrd
storage(initial 1638400 next 1048576 maxextents 1017 pctincrease 0)
nologging;
alter index ORADBA.TASK_CLIENT_FK rebuild tablespace hrd
storage(initial 1671168 next 2097152 maxextents 1017 pctincrease 0)
nologging;
alter index ORADBA.TASK_ENC_FK rebuild tablespace hrd
storage(initial 1933312 next 2097152 maxextents 1017 pctincrease 0)
nologging;
结论：
通过移动、合并、分析然后移回，我们按照正确的大小重新建立了索引，与此同时，我们整理了表空间。索引无时不在，删除索引的风险也随之时刻存在。提醒一句：在你移动索引的时候，用户将受到影响，因此，请选择非繁忙时段进行上述操作。我们不能为了试验数据而丢失客户。
初始化的extent计算方法如下：
(number of leaf blocks + number of computed branch blocks) * block size。
注意：我以索引的大小来排序，这是基于假设较大的索引通常有更多的活动空间，这么做也是为了将来减少碎片所占据的空间。
你会注意到这个操作忽略那些行为0或者块为0的空索引。为了初始化extent，这些空索引也应该被重新建立成1个块，或者删除，除非你知道在不久的将来，它们会被数据占用。对于分区索引，可以用同样的查询实现。但是sql语句中要把dba_indexes替换为dba_ind_partitions，分区名必须包含在rebuild 语句中。
|
|
|
下载中心
先飞电脑技术网 →
技术文章