DBAORACLE

欢迎来到DBAORACLE>>　　 | 首页 资源中心 | Oracle数据库 | 操作系统 | 怀旧经典 | 经济学 | 其他 | 小飞飞 | ITPUB论坛

估计创建索引需要的排序空间数量

发表人:dbaoracle | 发表时间: 2005年五月12日, 16:58

表 T 3765888 rows  准备在2个字段上建立索引(object_type,object_name) ，分析过后从user_tab_columns中获得平均列长度的统计信息

avg_row_len object_name 23 avg_row_len object_type 8

9i得utlxpls虽然能够估计查询使用的临时表空间，但是仍然不能估计创建索引所需要的临时表空间数量，如下

 SQL> explain plan for create index tidx on t(object_type,object_name) ;

Explained.

SQL> @C:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id  | Operation              |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |             |  3765K|   111M|  4975 |
|   1 |  INDEX BUILD NON UNIQUE| TIDX        |       |       |       |
|   2 |   SORT CREATE INDEX    |             |  3765K|   111M|       |
|   3 |    TABLE ACCESS FULL   | T           |  3765K|   111M|  4975 |
----------------------------------------------------------------------

 111M就是oracle根据avg_row_len 和表的num_rows计算而得；如果使用select排序操作，测试得到的结果显然过大。

SQL> explain plan for select object_type,object_name from t order by object_type
,object_name;

Explained.

SQL> @c:oracleora92rdbmsadminutlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes |TempSpc| Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  3765K|   111M|       | 26877 |
|   1 |  SORT ORDER BY       |             |  3765K|   111M|   288M| 26877 |
|   2 |   TABLE ACCESS FULL  | T           |  3765K|   111M|       |  4975 |
----------------------------------------------------------------------------

Note: cpu costing is off

SQL> select 288*1024/8 from dual;

288*1024/8
----------
     36864 个block,不清楚cbo是使用多少sort_area_size估计的288M,或许是multiple merge sort pass

实际测试 create index on t(object_type,object_name)使用了24192个临时表空间blocks （v$sort_usage）
索引创建后，index segment使用了 23424 blocks（受pctfree影响）.且创建索引是在 one merge pass sort 情况下  (oracle9i可以从v$sesstat的workarea%统计数据察看).

  • 按照每个索引项长度44计算 1+6+1+8+1+23+3= 44  20226 block。
    按照每个索引项长度48计算 2+6+2+8+2+23+4= 48  22065 block。

感觉还比较接近。保证20%或者30%的自由临时空间就可以了



  • 理论支持

创建大索引的时候，保证sort_area_size足够大，使排序操作只需要one merge pass sort。我一般都设置几百M到几G.不过设置太大，可能会遇见pga sort-heap allocate的错误，遇见过，还不知道为何，可能是32bit的限制。

one merge pass sort情况下，需要的临时表空间的数量就是参与排序的数据源大小。因此创建索引的时候，要参与排序的数据源包括所有要索引的列和rowid,还有每个列的长度，还有index entry header. 但我还不肯定column length是占1bytes还是2bytes；index entry header是2，3 or 4 bytes？

先计算每个index entry的总长度

   每个indexed column的平均长度+2*索引烈的数目+rowid长度（6 for non-partition 10 for partition）+rowid column length(maybe 1 or 2)+index header length(  3 or 4 bytes which I'm not sure)

折合为近似4的整数。  将计算的平均索引项长度* num_rows (*_tables)，就可以估计出需要使用的临时表空间（它并不受索引pctfree参数影响）。

内存排序的时候，每个block肯能会有60字节的浪费(here are  some bytes waste in block during  sort,maybe 60 bytes per block).但不会对估计的结果有大的影响。 最好保证实际可用的临时表空间大小比我们估计的大20%或30%，不然出现unable to extend sort segment in TEMP的错误可就浪费时间了。

参考 http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/2b44c7c9ee47e528/8cd7604f6a6c3943?tvc=1&hl=en&

分类: ( Oracle数据库 ) :: 评论 (2) :: 静态链接网址 :: 引用 (0)

有几个问题请教 [回复]

1、实际测试 create index on t(object_type,object_name)使用了24192个临时表空间blocks （v$sort_usage）

问：这里的”24192个临时表空间blocks“是如何获取此数据的？是从v$sort_usage
视图中得到的吗？ 请说明操作步骤。

2、index segment使用了 23424 blocks（受pctfree影响）.且创建索引是在 one merge pass sort 情况下 (oracle9i可以从v$sesstat的workarea%统计数据察看).

问：”oracle9i可以从v$sesstat的workarea%统计数据察看“ 是这样计算得来的
吗？： select sum(value) from v$sesstat a,v$statname b where a.statstic#=b.statstic# and a.sid="??" and name like workarea%；

3、这段话：创建大索引的时候，保证sort_area_size足够大，使排序操作只需要one merge pass sort。我一般都设置几百M到几G.不过设置太大，可能会遇见pga sort-heap allocate的错误，

9I里不是使用pga_aggregate_size 来给排序计算分配内存空间了吗？怎么还
使用sort_area_size参数？

ZALBB | 18/05/2005, 19:59

[回复]

1 跟踪v$sort_usage;建立索引需要3个步骤:源数据扫描，排序，写索引段；因此在写索引段的时候，v$sort_usage中的blocks纪录还不会消失

2这个是9i才有的参数v$statname；8i下我不清楚怎么察看
workarea executions - optimal 表示完全是内存排序
workarea executions - onepass 表示排序在one pass merge sort内结束
workarea executions - multipass 表示排序在多个merge sort内结束
optimal和onepass排序是最好/较好的；要避免multipass 排序

3我们这里使用的是8i; 不过9i默认 pga_aggregate_size 为每个session指定pga的有个限制 max（100M,5%*pga_aggregate_size )； 默认值有时候应该还不够，需要手工加大

玉面飞龙 | 20/05/2005, 01:01

发表评论

标题

在此添加评论

称呼

邮箱地址（可选）

个人主页（可选）


authimage



Valid XHTML 1.0 Strict and CSS. Powered by pLog
Design by Blog.lvwo.com