Shulou(Shulou.com)06/01 Report--

The source of table fragments

When there are many deletions against a table, the table produces a large number of fragments. The space freed by the delete operation is not immediately reused by the insert operation, or even will never be reused.

How to determine if there are watch fragments

-- collect table statistics

SQL > exec dbms_stats.gather_table_stats (ownname= > 'SCHEMA_NAME',tabname= >' TABLE_NAME')

[@ more@]

-- determine the degree of fragmentation

SQL > or use the script in gist below to find tables with more than 25% table fragments in a Schema. Before using this script, make sure that the table statistics in Schema are collected.

SELECT table_name

ROUND ((blocks * 8), 2) "high water space k"

ROUND ((num_rows * avg_row_len / 1024), 2) "Real use Space k"

ROUND ((blocks * 10 / 100) * 8,2) "reserved space (pctfree) k"

ROUND ((blocks * 8-(num_rows * avg_row_len / 1024)-

Blocks * 8 * 10 / 100)

2) "waste space k"

FROM dba_tables

WHERE table_name = 'BP_RESERVE_ORDERLIST'

-- View the table when statistics were last collected

Select table_name,last_analyzed from dba_tables where owner = 'SCHEMA_NAME'

-- collect statistics for objects throughout the Schema

SQL > exec dbms_stats.gather_schema_stats (ownname= > 'SCHEMA_NAME')

Why do you want to defragment the table?

Oracle has a concept of HWM (High Water Mark) for the management of data segments. The high water level is the dividing line between used and unused blocks in a segment. Blocks below the high water level have been used before, and the above blocks have never been used or initialized.

When Oracle performs a full table scan (FTS, Full table scan), it reads all data blocks at high water levels. If there is a lot of free space (debris) at high water level, reading these free data blocks will degrade the performance of the operation.

Row links and row migration

Row link Row Chaining: when inserting a row with a large amount of data, if a Block cannot hold a record, part of the record will be stored in other Block in the same Extent, and these block form a data block chain. Row migration Row Migration: when Update results in an increase in record length and the stored Block is full, a row migration occurs. Oracle migrates the whole row of data to a Block that can store the entire row of data, and the original pointer of the migration points to the Block,ROWID of the new stored row data.

When data rows are linked (chain) or migrated (migrate), accessing them will degrade the performance of Oracle O, because Oracle must access more data blocks (data block) in order to obtain the data of these data rows.

Problems caused by table fragmentation

Slow query response time (especially full table scans) results in a large number of row migration wasted space

Defragmenting tables does not improve the performance of index-based queries much.

How to defragment a table

Before 10g

There are two ways:

Export the table, delete it, and then import the table alter table move

The second option is generally chosen, and the index needs to be rebuilt.

After 10g

Starting with 10g, provide a shrink command that requires that the tablespace is based on automatic segment management.

It can be divided into two steps:

-- organize the table without affecting DML operation

SQL > alter table TABLE_NAME shrink space compact

-- reset the high water level. There can be no DML operation at this time.

SQL > alter table TABLE_NAME shrink space

It can also be achieved in one step:

-- organize the table and reset the high water level

SQL > alter table TABLE_NAME shrink space

Advantages of shrink:

There is no need to rebuild the index. It can be operated online. No free space is needed, and alter move needs free space the same size as the current table.

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.