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

ONLINE TABLE REDEFINITION (online redefinition tables) (see "performing online redefinition tables with DBMS_REDEFINITION packages (ONLINE TABLE REDEFINITION): http://blog.itpub.net/23135684/viewspace-1765128/) can also complete data space recycling, but ONLINE SEGMENT SHRINK is more suitable for simple table space recycling. This article will discuss the recycling of wasted space through online segment contraction based on the content of the official document.

one。 The principle of recycling unused space

Over time, UPDATE and DELETE operations on objects under the tablespace create individual empty spaces that are not large enough to be used by newly inserted data. This type of empty space is considered to be a fragment of free space.

Objects with free space debris waste space and affect the performance of the database. The preferred method of defragmentation and space recovery is to perform segment contraction (online segment shrink), which integrates the fragments of the free space below the high water mark, compacts the segment, after which the high water mark can be moved, and the end result is that the new free space is above the high water level, and the space above the high water level will be recovered later (deallocated). DML operations can still be queried and performed in the middle of the entire segment collection operation, and no additional disk space needs to be allocated.

Using Segment Advisor to mark segments is the advantage of online segment recovery, and only segments belong to local surface space management and ASSM is feasible.

If the segment does not meet the requirements of online segment recycling, or if you want to change the logical or physical properties of the table in the process of reclaiming space, you can use online table redefinition to achieve segment space recycling. Online redefinition is considered to reorganize tables, and unlike segment recycling, it requires the allocation of additional disk space.

Note: there is a difference between SHRINK and DEALLOCATE. SHRINK is defragmentation and DEALLOCATE is lowering the high water mark. The difference between the two will be further explained below.

II. Segment Advisor

Segment Advisor is used to identify whether Segment space recycling is feasible. It analyzes by checking usage, growth statistics in AWR, and sample data in segments. Segment Advisor is configured to run maintenance tasks automatically during the maintenance window, but it can also be run manually. Segment Advisor automatic maintenance is considered to be called Automatic Segment Advisor.

Segment Advisor generates the following recommendations:

1). Segment Advisor determines whether an object has significant free space, and it is recommended to execute online segment SHRINK. If the table corresponding to the object does not meet the recycling criteria, Segment Advisor recommends that you redefine the table online.

2). Segment Advisor determines that tables can gain an advantage from compression or OLTP compression, and this method is recommended.

3). Segment Advisor encounters that the table row chain exceeds the threshold, which records the situation of a large number of chain row in the table.

If you receive a space management alarm, or if you want to recycle space, you should start with Segment Advisor.

Instead of analyzing every object in the database, Automatic Segment Advisor examines database statistics, samples segment data, and then selects the following objects for analysis:

> the table space exceeds the critical point, or the space threshold alarm.

> the most active segment.

The segment with the highest growth rate.

Segment Advisor is also called manually, which can be called through the OEM and DBMS_ADVISOR packages. Here we discuss the methods that are called through DBMS_ADVISOR.

Use the stored procedures of the DBMS_ADVISOR package to create the Segment Advisor work, set the properties of the work, and then perform the work. You must have ADVISOR permission to use this package.

Here is an example to discuss the method of manually calling Segment Advisor through DBMS_ADVISOR. The stored procedure of the DBMS_ADVISOR package must be executed with the EXECUTE object permission of the package, or the ADVISOR system permission.

Note: if the table is manipulated through DBMS_ADVISOR.CREATE_OBJECT, if the table is a partitioned table, Segment Advisor analyzes all partitions of the table and generates separate findings and recommendations for each partition.

Variable id number

Begin

Declare

Name varchar2 (100)

Descr varchar2 (500)

Obj_id number

Begin

Name:='Manual_Employees'

Descr:='Segment Advisor Example'

Dbms_advisor.create_task (

Advisor_name = > 'Segment Advisor'

Task_id = >: id

Task_name = > name

Task_desc = > descr)

Dbms_advisor.create_object (

Task_name = > name

Object_type = > 'TABLE'

Attr1 = > 'HR'

Attr2 = > 'EMPLOYEES'

Attr3 = > NULL

Attr4 = > NULL

Attr5 = > NULL

Object_id = > obj_id)

Dbms_advisor.set_task_parameter (

Task_name = > name

Parameter = > 'recommend_all'

Value = > 'TRUE')

Dbms_advisor.execute_task (name)

End

End

/

View the results of Segment Advisor

Segment Advisor creates several types of results, recommendations,findings,actions and objects.

Method 1: view the results through DBMS_SPACE.ASA_RECOMMENDATIONS.

This is the easiest way.

Select tablespace_name, segment_name, segment_type, partition_name

Recommendations, c1 from

Table (dbms_space.asa_recommendations ('FALSE',' FALSE', 'FALSE'))

TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE

-

PARTITION_NAME

-

RECOMMENDATIONS

C1

TVMDS_ASSM ORDERS1 TABLE PARTITION

ORDERS1_P2

Perform shrink, estimated savings is 57666422 bytes.

Alter table "STEVE". "ORDERS1" modify partition "ORDERS1_P2" shrink space

TVMDS_ASSM ORDERS1 TABLE PARTITION

ORDERS1_P1

Perform shrink, estimated savings is 45083514 bytes.

Alter table "STEVE". "ORDERS1" modify partition "ORDERS1_P1" shrink space

TVMDS_ASSM_NEW ORDERS_NEW TABLE

Perform shrink, estimated savings is 155398992 bytes.

Alter table "STEVE". "ORDERS_NEW" shrink space

TVMDS_ASSM_NEW ORDERS_NEW_INDEX INDEX

Perform shrink, estimated savings is 102759445 bytes.

Alter index "STEVE". "ORDERS_NEW_INDEX" shrink space

Method 2: you can get the results by querying the DBA_ADVISOR_* view.

Before querying the DBA_ADVISOR_* view, you can ensure that the Segment Advisor work is finished by querying the DBA_ADVISOR_TASKS.STATUS field.

Select task_name, status from dba_advisor_tasks

Where owner = 'STEVE' and advisor_name =' Segment Advisor'

TASK_NAME STATUS

Manual Employees COMPLETED

The following example shows how to query the DBA_ADVISOR_* view to retrieve work submitted by a STEVE user from all running Segment Advisor:

Select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message

From dba_advisor_findings af, dba_advisor_objects ao

Where ao.task_id = af.task_id

And ao.object_id = af.object_id

And ao.owner = 'STEVE'

TASK_NAME SEGNAME PARTITION TYPE MESSAGE

Manual_Employees EMPLOYEES TABLE The free space in the obje

Ct is less than 10MB.

Manual_Salestable4 SALESTABLE4 SALESTABLE4_P1 TABLE PARTITION Perform shrink, estimated

Savings is 74444154 bytes.

Manual_Salestable4 SALESTABLE4 SALESTABLE4_P2 TABLE PARTITION The free space in the obje

Ct is less than 10MB.

three。 SHRINK in Segment Space

Segment SHRINK is online and operates internally. The movement of data during the segment SHRINK phase does not affect DML operations and query operations. Parallel DML operations will be blocked for a short time when the SHRINK operation ends and the spatial deallocated is completed. The index is maintained during the SHRINK operation and is still available after the operation is completed. Segment SHRINK does not require additional space allocation.

Segment SHRINK unused space above and below the high water mark, by contrast, space deallocation only recycles space above the high water mark. In a recycling operation, by default, the database compacts the segment, adjusts the high water mark, and frees up wasted space.

Segment Shrink requires rows to be moved to a new location, so you must first enable row migration on the exclusive you want shrink, and disable rowid-based trigger definitions on objects. Through ALTER TABLE... The ENABLE ROW MOVEMENT statement enables row migration.

Shrink only supports localizing tables under tablespace management and ASMM management.

You can shrink space on tables, IOT tables, indexes, partitions, subpartitions, materialized views, and materialized view logs.

If you have a function-based index on the table, you will not be able to shrink the table (neither CASCADE nor NOCASCADE).

Two shrink sentences are used to control how to shrink the operation:

> COMPACT divides the SHRINK segment into two stages. When you specify the COMPACT,Oracle database to defragment the segment and compress the table row, the reset of the high watermark and deallocation space will be delayed. This is useful for long-running queries that may read recycled blocks. The result of this defragmentation and compaction is retained to disk, after which the SHRINK SPACE statement can be executed again with a command without the COMPACT sentence.

> the CASCADE extension shrink operates on all objects that depend on segments. For example, when you specify CASCADE when you SHRINK table segments, all indexes related to the table will be SHRINK (you do not need to specify CASCADE when partitioning the SHRINK table). You can run the DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS stored procedure to view all objects that depend on segments.

If the COMPAT statement is not specified, all SQL statements after the execution of the segment SHRINK will be reparsed because of the invalid cursor, because this is a DDL operation.

Example:

> Shrink table and all segments that depend on it: (including BASICFILE LOB segment):

ALTER TABLE employees SHRINK SPACE CASCADE

> only Shrink BASICFILE LOB segments:

ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE)

> single partition of Shrink partition table:

ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE

> Shrink IOT index segment and overflow segment:

ALTER TABLE cities SHRINK SPACE CASCADE

> only SHRINK IOT overflow segments:

ALTER TABLE cities OVERFLOW SHRINK SPACE

IV. Unused space in Deallocate

When deallocate unused space is executed, the database makes unused space available after the end of the database segment (lower the high water mark). Note that the high water mark is lowered by default after SHRINK the table, and DEALLOCATE is used when the SHRINK is not executed or when the non-default SHRINK is used.

Before executing deallocation, you can run the DBMS_SPACE.UNUSED_SPACE procedure to return the high watermark location information and the total number of unused space in the segment space. For tablespaces that are localized and managed automatically by segments, SPACE_USAGE can get more accurate data.

The unused space (table, index, or cluster) of the deallocate segment of the following statement:

ALTER TABLE table DEALLOCATE UNUSED KEEP

ALTER INDEX index DEALLOCATE UNUSED KEEP

ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP

KEEP is an optional word that specifies the total amount of space reserved by the segment, and you can verify that the DEALLOCATED is free by checking the DBA_FREE_SPACE view.

Related articles:

"Moving table to a new segment or tablespace": http://blog.itpub.net/23135684/viewspace-1766480/

-- end--

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.