一:准备测试数据,使用HR用户,创建T1表,插入约30W的数据,并根据object_id创建普通索引,表占存储空间34M左右,索引占6M左右的存储空间 SQL> conn /as sysdba 已连接。 SQL> select default_tablespace from dba_users where username='HR'; DEFAULT_TABLESPACE ------------------------------------------------------------ USERS SQL> conn hr/hr 已连接。 SQL> insert into t1 select * from t1; 已创建 74812 行。 SQL> insert into t1 select * from t1; 已创建 149624 行。 SQL> commit; 提交完成。 SQL> create index idx_t1_id on t1(object_id); 索引已创建。 SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); PL/SQL 过程已成功完成。 SQL> select count(1) from t1; COUNT(1) ---------- 299248 SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'; SUM(BYTES)/1024/1024 -------------------- 34.0625 SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID'; SUM(BYTES)/1024/1024 -------------------- 6 二:估算表在高水位线下还有多少空间可用,这个值应当越低越好,表使用率越接近高水位线,全表扫描所做的无用功也就越少! DBMS_STATS包无法获取EMPTY_BLOCKS统计信息,所以需要用analyze命令再收集一次统计信息 SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 4302 0 299248 SQL> analyze table t1 compute statistics; 表已分析。 SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 4302 50 299248 SQL> col table_name for a20 SQL> SELECT TABLE_NAME, 2 (BLOCKS * 8192 / 1024 / 1024) - 3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 4 FROM USER_TABLES 5 WHERE table_name = 'T1'; TABLE_NAME Data lower than HWM in MB -------------------- ------------------------- T1 5.07086182 三: 查看执行计划,全表扫描大概需要消耗CPU 1175 SQL> explain plan for select * from t1; 已解释。 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 299K| 28M| 1175 (1)| 00:00:15 | | 1 | TABLE ACCESS FULL| T1 | 299K| 28M| 1175 (1)| 00:00:15 | -------------------------------------------------------------------------- 四:删除大部分数据,收集统计信息,全表扫描依然需要消耗CPU 1168 SQL> delete from t1 where object_id>100; 已删除298852行。 SQL> commit; 提交完成。 SQL> select count(*) from t1; COUNT(*) ---------- 396 SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); PL/SQL 过程已成功完成。 SQL> analyze table t1 compute statistics; 表已分析。 SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 4302 50 396 SQL> explain plan for select * from t1; 已解释。 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------ Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 396 | 29700 | 1168 (1)| 00:00:15 | | 1 | TABLE ACCESS FULL| T1 | 396 | 29700 | 1168 (1)| 00:00:15 | -------------------------------------------------------------------------- 五:估算表在高水位线下还有多少空间是无数据的,但在全表扫描时又需要做无用功的数据 SQL> SELECT TABLE_NAME, 2 (BLOCKS * 8192 / 1024 / 1024) - 3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 4 FROM USER_TABLES 5 WHERE table_name = 'T1'; TABLE_NAME Data lower than HWM in MB -------------------- ------------------------- T1 33.5791626 六:对表进行碎片整理,重新收集统计信息 SQL> alter table t1 enable row movement; 表已更改。 SQL> alter table t1 shrink space cascade; 表已更改。 SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'; SUM(BYTES)/1024/1024 -------------------- .125 SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID '; SUM(BYTES)/1024/1024 -------------------- .0625 SQL> SELECT TABLE_NAME, 2 (BLOCKS * 8192 / 1024 / 1024) - 3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 4 FROM USER_TABLES 5 WHERE table_name = 'T1'; TABLE_NAME Data lower than HWM in MB -------------------- ------------------------- T1 33.5791626 SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); PL/SQL 过程已成功完成。 这个时候,只剩下0.1M的无用功了,执行计划中,全表扫描也只需要消耗CPU 3 SQL> SELECT TABLE_NAME, 2 (BLOCKS * 8192 / 1024 / 1024) - 3 (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 4 FROM USER_TABLES 5 WHERE table_name = 'T1'; TABLE_NAME Data lower than HWM in MB -------------------- ------------------------- T1 .010738373 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 396 | 29700 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T1 | 396 | 29700 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- 总共只有5个块,空块却有50个,明显empty_blocks信息过期 SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 5 50 396 SQL> analyze table t1 compute statistics; 表已分析。 SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1'; BLOCKS EMPTY_BLOCKS NUM_ROWS ---------- ------------ ---------- 5 3 396 关于如何确定哪些表需要进行碎片整理,可以使用附件中的脚本去查询,具体请参考:http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTNN18/Default.aspx 转载请保留固定链接: https://linuxeye.com/database/1400.html |