对一个1200万的表进行analyze操作，大约要多久

对一个1200万的表进行analyze操作，大约要多久 Posted: 2012-10-29 上午1:04		Reply
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi create table TRFF_APP.DRV_FLOW ( lsh VARCHAR2(13) not null, sfzmhm VARCHAR2(18) not null, dabh CHAR(12), xm VARCHAR2(30) not null, ywlx CHAR(1) not null, ywyy VARCHAR2(8), kssj DATE, jssj DATE, ywgw VARCHAR2(8) not null, kskm CHAR(6), xygw CHAR(1) not null, glbm VARCHAR2(12) not null, ffbz CHAR(1), rkbz CHAR(1), hdbz CHAR(1), xgzl VARCHAR2(15), zjcx VARCHAR2(15), ywzt CHAR(1), ywblbm VARCHAR2(12), fzjg VARCHAR2(10) ) tablespace DT_DRV_DAT pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 next 1 minextents 1 maxextents unlimited ); -- Create/Recreate indexes create index TRFF_APP.IDX_DRV_FLOW_DABH on TRFF_APP.DRV_FLOW (DABH) tablespace DT_DRV_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_FFBZ on TRFF_APP.DRV_FLOW (FFBZ) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 136M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_FZJG on TRFF_APP.DRV_FLOW (FZJG) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 160M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_GLBM_XYGW on TRFF_APP.DRV_FLOW (GLBM, XYGW) tablespace DT_DRV_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_HDBZ on TRFF_APP.DRV_FLOW (HDBZ) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 112M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_JSSJ on TRFF_APP.DRV_FLOW (JSSJ) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 200M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_KSKM on TRFF_APP.DRV_FLOW (KSKM) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 208M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_KSSJ on TRFF_APP.DRV_FLOW (KSSJ) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 224M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_RKBZ on TRFF_APP.DRV_FLOW (RKBZ) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 120M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_SFZMHM on TRFF_APP.DRV_FLOW (SFZMHM) tablespace DT_DRV_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_XGZL on TRFF_APP.DRV_FLOW (XGZL) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 144M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_XM on TRFF_APP.DRV_FLOW (XM) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 192M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_XYGW on TRFF_APP.DRV_FLOW (XYGW) tablespace DT_DRV_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_YWBLBM on TRFF_APP.DRV_FLOW (YWBLBM) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 264M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_YWGW on TRFF_APP.DRV_FLOW (YWGW) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 160M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_YWLX on TRFF_APP.DRV_FLOW (YWLX) tablespace DT_DRV_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_YWYY on TRFF_APP.DRV_FLOW (YWYY) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 88M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_YWZT on TRFF_APP.DRV_FLOW (YWZT) tablespace DRV_INDEX1 pctfree 10 initrans 2 maxtrans 255 storage ( initial 136M next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_DRV_FLOW_ZJCX on TRFF_APP.DRV_FLOW (ZJCX) tablespace DT_DRV_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index TRFF_APP.IDX_FLOW_XYGW_YWBLBM on TRFF_APP.DRV_FLOW (XYGW, YWBLBM) tablespace DT_DRV_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Create/Recreate primary, unique and foreign key constraints alter table TRFF_APP.DRV_FLOW add constraint PK_DRV_FLOW primary key (LSH) using index tablespace DT_DRV_IDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );

Liu Maclean(刘相兵) Posts: 684 Registered: 08/21/12

Re: 对一个1200万的表进行analyze操作，大约要多久 Posted: 2012-10-29 上午6:49   in response to: 955079 ask_maclean , oracle数据库官方中文技术论坛 , 甲骨文中国 Correct   Reply 首先要说明一下 一张表这么多索引， 对DML的性能消耗是很大的， OLTP中一般一张表不超过6个索引 对于这类问题为什么不自己测试一下呢？ SQL> select count(*) from TRFF_APP.DRV_FLOW;   COUNT(*) ---------- 12000001 SQL> select count(*) from dba_indexes where table_name='DRV_FLOW';   COUNT(*) ---------- 20   SQL> select AVG_ROW_LEN,NUM_ROWS,EMPTY_BLOCKS from dba_tables where table_name='DRV_FLOW';   AVG_ROW_LEN NUM_ROWS EMPTY_BLOCKS ----------- ---------- ------------ 147 12000001 5517   以上是一张 1200万记录的表，平均行长147 bytes   SQL> set timing on;   SQL> exec dbms_stats.gather_table_stats('TRFF_APP','DRV_FLOW');   PL/SQL procedure successfully completed.   Elapsed: 00:00:44.36     使用默认的自动采样 DBMS_STATS.AUTO_SAMPLE_SIZE ，耗费44s     SQL> exec dbms_stats.gather_table_stats('TRFF_APP','DRV_FLOW',estimate_percent=>100);   PL/SQL procedure successfully completed.   Elapsed: 00:04:59.41     指定100% percent 全表收集信息，耗费 5分钟   环境为 x86-64 bit 10.2.0.5.0 ， IO吞吐量约为100MB/s     SQL> select * from v$version;   BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production       [oracle@vrh8 admin]$ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 42 model name : Intel(R) Core(TM) i7-2600 CPU @ 3.40GHz stepping : 7 cpu MHz : 3253.646 cache size : 6144 KB physical id : 0 siblings : 2 core id : 0 cpu cores : 2 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 5 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx lm constant_tsc rep_good pni ssse3 lahf_lm bogomips : 6507.29 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management:   processor : 1 vendor_id : GenuineIntel cpu family : 6 model : 42 model name : Intel(R) Core(TM) i7-2600 CPU @ 3.40GHz stepping : 7 cpu MHz : 3253.646 cache size : 6144 KB physical id : 0 siblings : 2 core id : 1 cpu cores : 2 apicid : 1 initial apicid : 1 fpu : yes fpu_exception : yes cpuid level : 5 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx lm constant_tsc rep_good pni ssse3 lahf_lm bogomips : 6537.81 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual     [root@vrh8 ~]# hdparm -tT /dev/sdh   /dev/sdh: Timing cached reads: 40908 MB in 1.96 seconds = 20841.11 MB/sec Timing buffered disk reads: 314 MB in 3.02 seconds = 104.02 MB/sec 转载：https://forums.oracle.com/forums/thread.jspa?threadID=2457616&tstart=0