Shulou(Shulou.com)05/31 Report--

What this article shares with you is the collation and analysis of oracle table fragments. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Database in the daily use of the process, continuous insert,delete,update operations, resulting in table and index fragments is inevitable, more fragments, sql implementation efficiency is naturally poor, the reason is very simple, high water level (HWL) under many data blocks are no data, but the full table scan to the high water mark data blocks!

The fragmentation of a table is different from the fragmentation of a file system. As more and more operations are performed on the DML on a table, there may be a lot of free space before HWM, and blocks below HWM will be read in when reading the table, which will generate more IO and affect performance. It is only in DDL operation that the meter shrinks.

To defragment the table, defragment methods are: 1, use the alter MOVE table, and then index rebuild;2. Use alter table enable row movement; and then alter table shrink space cascade (use restrictions on shrink, note); 3, use export and import tables through create table XXX as select * from abb; 4

The experiments are as follows:

SQL > create table T1 as select * from dba_objects

Table created.

SQL > select count (*) from T1

COUNT (*)

-

86956

SQL > insert into T1 select * from T1

86956 rows created.

SQL > commit

Commit complete.

SQL >

SQL > create index idx_t1_id on T1 (object_id)

Index created.

-- query table size and statistical information first:

SQL > set lines 200

SQL > COL TABLE_NAME FOR A15

SQL > COL TABLESPACE_NAME FOR A15

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1'

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

-

SYS T1 SYSTEM

SQL > COL SEGMENT_NAME FOR A15

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1s are not used to ID XT1IDs')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS

-

SYS T1 SYSTEM 20971520 2560 35

SYS IDX_T1_ID SYSTEM 4194304 512 19

You can see that the table size can already be recorded in dba_segment, but not in dba_tables.

-- use dbms_stats mobile phone statistics

SQL > exec dbms_stats.gather_table_stats ('SYS','T1',CASCADE= > TRUE)

PL/SQL procedure successfully completed.

SQL >

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1'

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

-

SYS T1 SYSTEM 173912 2476 0 98 2017-10-26 05:35:37

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1s are not used to ID XT1IDs')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS

SYS T1 SYSTEM 20971520 2560 35

SYS IDX_T1_ID SYSTEM 4194304 512 19

It is found that there is a record BLOCKS block size record in dba_table, but there is no empty_blocks empty block record and AVG_ space value.

-- you need to use the analyze clause to collect empty block information for table T1.

The DBMS_STATS package cannot get EMPTY_BLOCKS statistics, so you need to collect statistics again with the analyze command

SQL > analyze table T1 compute statistics

Table analyzed.

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1'

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

-

SYS T1 SYSTEM 173912 2476 83 863 101 2017-10-26 05:38:18

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1s are not used to ID XT1IDs')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS

SYS T1 SYSTEM 20971520 2560 35

SYS IDX_T1_ID SYSTEM 4194304 512 19

It is found that the EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN field in dba_tables has a value, and the value of AVG_ROW_LEN has changed.

-- calculate how much space is available under the high water mark. This value should be as low as possible. The closer the meter utilization is to the high water mark, the less useless work will be done by the full table scan, as follows:

SQL > select table_name, (blocks * 8192 / 1024 / 1024)-(num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name ='T1'

TABLE_NAME data lower than hwm in mb

--

T1 2.59235382

To view the execution plan, a full table scan will probably cost CPU 675.

SQL > explain plan for select * from T1

Explained.

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 | | 173k | 16m | 675 (1) | 00:00:09 |

| | 1 | TABLE ACCESS FULL | T1 | 173k | 16m | 675 (1) | 00:00:09 |

8 rows selected.

-- delete most of the data and collect statistics. Cpu 673is still consumed for full table scanning, as shown below:

SQL > select count (*) from T1

COUNT (*)

-

173912

SQL > delete T1 where rownum select count (*) from T1

COUNT (*)

-

3913

-- use dbms_stats to analyze tables

SQL > exec dbms_stats.gather_table_stats ('SYS','T1',CASCADE= > TRUE)

PL/SQL procedure successfully completed.

-- query dba_segments and dba_tables views again

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1'

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

-

SYS T1 SYSTEM 3913 2476 83 863 101 2017-10-26 05:50:29

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1s are not used to ID XT1IDs')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS

SYS T1 SYSTEM 20971520 2560 35

SYS IDX_T1_ID SYSTEM 4194304 512 19

It is found that the num_rows field in the dba_tables has been updated, the other fields have not been updated, and the related fields in the dba_segments view have not changed. This indicates that in the delete row operation of the DML operation, even though the statistics are updated, the table size does not change because of the fragmentation in the table.

-- use analyze to analyze tables:

SQL > analyze table T1 compute statistics

Table analyzed.

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1'

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

-

SYS T1 SYSTEM 3913 2476 83 7761 104 2017-10-26 05:52:00

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1s are not used to ID XT1IDs')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS

SYS T1 SYSTEM 20971520 2560 35

SYS IDX_T1_ID SYSTEM 4194304 512 19

There is still no change, and the conclusion is as above.

-- looking at the implementation plan, cpu cost 673 has hardly changed

SQL > explain plan for select * from T1

Explained.

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 | | 3913 | 397k | 673 (1) | 00:00:09 |

| | 1 | TABLE ACCESS FULL | T1 | 3913 | 397k | 673 (1) | 00:00:09 |

8 rows selected.

-- estimate again how much space there is no data in the table below the high water mark, but the data blocks that need to do useless work during the full table scan are as follows:

SQL > select table_name, (blocks * 8192 / 1024 / 1024)-(num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name ='T1'

TABLE_NAME data lower than hwm in mb

--

T1 18.9556503

It was found that the fragments in the table increased a lot.

-defragment the table and re-collect statistical information, as follows:

Note: defragmentation methods are: 1, use the alter MOVE table, and then index rebuild;2. Use alter table enable row movement; and then alter table shrink space cascade (use restrictions on shrink, note); 3, use export and import tables through create table XXX as select * from abb; 4

SQL > alter table T1 disable row movement

Table altered.

SQL > alter table T1 move

Table altered.

SQL > select INDEX_NAME,STATUS from dba_indexes where index_name = 'IDX_T1_ID'

INDEX_NAME STATUS

IDX_T1_ID UNUSABLE

SQL > alter index IDX_T1_ID rebuild online

Index altered.

-- query dba_tables/dba_segments first:

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1'

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

-

SYS T1 SYSTEM 3913 2476 83 7761 104 2017-10-26 05:52:00

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1s are not used to ID XT1IDs')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS

SYS T1 SYSTEM 524288 64 8

SYS IDX_T1_ID SYSTEM 131072 16 2

It is found that after defragmentation and without collecting statistics, the block size of dba_segments has been automatically updated, while the fields of dba_tables have not been updated.

-- query the fragment again:

SQL > select table_name, (blocks * 8192 / 1024 / 1024)-(num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name ='T1'

TABLE_NAME data lower than hwm in mb

--

T1 18.9556503

No change.

-- collect statistics and use the dbms_stat package:

SQL > exec dbms_stats.gather_table_stats ('SYS','T1',CASCADE= > TRUE)

PL/SQL procedure successfully completed.

SQL >

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1'

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

-

SYS T1 SYSTEM 3913 58 83 7761 101 2017-10-26 06:07:17

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1s are not used to ID XT1IDs')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS

SYS T1 SYSTEM 524288 64 8

SYS IDX_T1_ID SYSTEM 131072 16 2

SQL > select table_name, (blocks * 8192 / 1024 / 1024)-(num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name ='T1'

TABLE_NAME data lower than hwm in mb

--

T1. 076220512

It is found that the blocks and AVG_ROW_LEN fields of dba_tables have been updated after the statistical information collected by dbms_stat package, and the fragments under high water level have been recovered, but the EMPTY_BLOCKS and AVG_SPACE fields have not been updated.

-- collect EMPTY_BLOCKS field statistics using the analyze clause, as follows

SQL > analyze table T1 compute statistics

Table analyzed.

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where TABLE_NAME='T1'

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN LAST_ANALYZED

-

SYS T1 SYSTEM 3913 58 5887 104 2017-10-26 06:10:06

SQL > select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name in ('T1s are not used to ID XT1IDs')

OWNER SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS

SYS T1 SYSTEM 524288 64 8

SYS IDX_T1_ID SYSTEM 131072 16 2

SQL > select table_name, (blocks * 8192 / 1024 / 1024)-(num_rows * avg_row_len / 1024 / 1024) "data lower than hwm in mb" from user_tables where table_name ='T1'

TABLE_NAME data lower than hwm in mb

--

T1. 06502533

It is found that the EMPTY_BLOCKS and AVG_SPACE fields of dba_tables are updated after the statistics are collected by analyze clause.

-- execute sql again and find that the CPU cost is only 17, as follows:

SQL > explain plan for select * from T1

Explained.

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 | | 3913 | 397k | 17 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 3913 | 397k | 17 (0) | 00:00:01 |

8 rows selected.

The above is the collation and analysis of oracle table fragments, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.