为1.7亿张记录表创建快速索引
2007-05-10 12:16:25 来源:WEB开发网核心提示: nohup time createind.sh &vi createind.sh#!/bin/shsqlplus user/password <<EOFcreate index IDX_SUBMIT_RECORDTIME on STAT_SUBMIT_CENTER(RECORD
nohup time createind.sh &vi createind.sh#!/bin/shsqlplus user/password <<EOFcreate index IDX_SUBMIT_RECORDTIME on STAT_SUBMIT_CENTER(RECORDTIME) local;exitEOF
4)创建过程中可以观察v$sort_segment,v$sort_usage看排序情况:
select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;select * from v$sort_usage;
5)创建完成后,把tempfile和pga_aggregate_target改回原值:
alter database tempfile '/bgdata/oracle/temp01.dbf' resize 4096m;alter system set pga_aggregate_target=500m;
4. 实际创建过程中观察到的情况
1)开始之前:
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS------------------------------- ------------- ------------ ----------- -----------TEMP 0 431360 0 431360SQL> select * from v$sort_usage;no rows selected
2)创建之初,抓到这么一条sql:
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
3)然后v$sort_segment.USED_BLOCKS变大,v$sort_usage.BLOCKS变大,一直增长到:
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS------------------------------- ------------- ------------ ----------- -----------TEMP 1 431360 46720 384640SQL> select * from v$sort_usage;USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH------------------------------ ------------------------------ ---------------- ----------- ---------------- ----------TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------DPC DPC 00000003974CFFB0 6134 0000000399CAB288 1254950678TEMP TEMPORARY SORT 201 431113 365 46720 1这个过程中抓到的sql:select file# from file$ where ts#=:1
4)v$sort_segment.USED_BLOCKS变为0,v$sort_usage.BLOCKS变为0
更多精彩
赞助商链接