WEB开发网
开发学院数据库Oracle 为1.7亿张记录表创建快速索引 阅读

为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

上一页  1 2 3 4  下一页

Tags:记录表 创建 快速

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接