New Features of Oracle9i: Undo Space Management

zhaozj2021-02-16 72

Author: Lunar

Prior to Oracle 9i, Oracle uses the rollback segment to manage undo, that is, the transaction's read consistency is guaranteed by rollback segments. In Oracle 9i, there can be two solutions to maintain transaction's read consistency, even if you have been using the rollback segments used in Oracle 6, or using undo tablespace (System-management) Undo (SMU) or Automatically Managed Undo (AMU), but both methods cannot be used at the same time.

When a transaction begins to assign a rollover segment to store the data before the data is modified (before the start of the transaction). Usually, Oracle DBA has to spend a lot of time and effort to consider the design and use of undo space:

• It is very important to set enough rollback segments in the database. Too little rollback segments will cause new things to wait for the wait for a drop. Too many returns may waste a lot of space for increasing concurrency processing.

· The size of the rollback segment should be appropriate. If the rollback segment is too small, there is more space that has a long run or a big transaction. The rollback segment will expand into the remaining free space in the table space. If the (expanded) space required by the segment is much more space than the available space in the table, this transaction will fail.

· The EXTENTS of the rollback segment should be set to the appropriate size. Ideally, a transaction should just be in an extents of a roll band. If a transaction is larger than this extents, this transaction extends from an extent ring to the next extents in the same rollove segment. Excessive surroundings will result in reducing the use of internal spaces.

Before Oracle 9i, the rollback segment had to manually manage DBA. Regarding the size and quantity of returning segments, the size of the size will have a significant performance impact on the system and concurrent transactions. In Oracle 9i Allows the use of two ways to manage rollback segments:

· Handmade. This way is what we usually say Rollback-Managed undo (RMU). It is set to Manual by putting the undo_management parameter. Handmade is in nature and the management of the previous returns of Oracle 9i.

· Automatic management. This way is what we usually say system-management undo (SMU) or Automatically Managed undo (AMU). It is set to Auto by putting the undo_management parameter. Using this way, Oracle can manage the use of undo space yourself, and it considers the use of undo space, and the Undo Blocks competition and maintenance read consistency. Using this method, Oracle allows DBA to assign a separate undo table space to undo space, and undo segments will automatically create in the undo tablespace, and all of these undo segments creation, deletion, etc. are maintained by Oracle.

Oracle 9i allows DBA to configure the Oracle 9i database to RMU OR SMU mode, but even use SMU mode, DBA still considers the size of the undo table space. In addition, all Undo Segments have the same size in the Undo table space, which is difficult for the application of hybrid transactions. For example, in some applications, there are some long-running transactions and transactions to a large amount of data, and there are other small transactions (transactions in life cycle) do not need to change a lot of data.

What is the initialization parameter of the UNDO space?

· Undo_management: This parameter determines the management method of UNDO space. This parameter is a static parameter (cannot be dynamically modified). If set to Manual, use the Rollback-Managed undo (RMU) management mode; if set to Auto, use System-Managed Undo (SMU) or Automatically Managed Undo (AMU) management mode. In the Oracle 9i database, Auto is the default. · Undo_tablespace: This parameter is only required when using System-Managed undo (SMU) or Automatical Management and AMASTical or Automatical Management mode. It specifies the UNDO table space used by the SMU or AMU (the table space must be the table space already created). This setting can be dynamically changed through the alter system command. If you ignore the settings of this parameter, the first UNDO table space in the database will be used. If there is no available undo table space, the System returns will be used.

Create Undo TableSpace:

SQL> CREATE undo TableSpace undo_test

2 DataFile 'E: /TEST_TEST.ORA' SIZE 10M

3 AutoExtend on Next 2M maxsize 700M

4 /

The table space has been created.

SQL>

· Undo_retention: This parameter is only required when using System-Managed undo (SMU) or Automatically Managed undo (AMU) management mode. It specifies the time that keeps heavy done in the redo table space, this setting can be dynamically changed through the alter system command, and the default is 3 hours. There are two main purposes, one is the data that is being used for lengthy queries that is not active (inactive), and the other is to support the query of the current state of the current data, that is, "Flashback Query" in Oracle 9i. New feature. For example, if you set the undo_retrion = 10800, Oracle will enter the most mighty retention for 3 hours, that is, 3 hours of queries will not report ORA-01555 errors.

· Undo_suppress_error: When using the SMU management mode, you cannot run the management command of the RMU management mode, such as putting a roll-off or offline or the like. This parameter determines whether the management command of the RMU management mode generates an error message when using the SMU management mode. This setting can be dynamically changed through the alter system command, and the default value is false, ie, the error message is not allowed. E.g:

C: /> SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production on Sunday June 29 10:10:04 2003

The idle routine is connected.

SQL> Startup

Oracle routines have been started.

Total System Global Area 93395628 Bytes

Fixed Size 453292 bytes

Variable size 67108864 bytes

Database buffers 25165824 bytesredo buffers 667648 BYTES

The database is loaded.

The database has been opened.

SQL> Show parameter undo_suppress_errors

Name Type Value

----------------------------------- --- ---------------------------

undo_suppress_errors Boolean False

SQL> Show parameter undo_management

Name Type Value

----------------------------------- --- ---------------------------

undo_management string auto

SQL> Show parameter undo_tablespace

Name Type Value

----------------------------------- --- ---------------------------

Undo_tablespace string undotbs1

SQL> ALTER ROLLBACK segment system offline;

Alter rollback segment system offline

Error is located on the first line:

ORA-30019: Retreat in automatic revocation mode illegal

SQL> ALTER SESTEM SET undo_suppress_errors = true;

The system has changed.

SQL> Show parameter undo_suppress_errors

Name Type Value

----------------------------------- --- -------

undo_suppress_errors boolean True

SQL> ALTER ROLLBACK segment system offline;

The return will be changed. ------------- In fact, there is no change, but it is no longer reported.

SQL>

Can you use multiple undo table spaces?

The undo table space is only useful in the SMU or AMU mode, namely undo_management = auto. In Oracle 9i, you can create multiple undo tablespace using the create undo tablespace command, but only one undo table space is currently used in the data is running normally, this parameter determines which UNDO table space is current Events undo tablespace:

C: /> SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production on Sunday June 29 10:10:04 2003

The idle routine is connected.

SQL> StartUporacle routines have been started.

Total System Global Area 93395628 Bytes

Fixed Size 453292 bytes

Variable size 67108864 bytes

Database buffers 25165824 BYTES

Redo buffers 667648 bytes

The database is loaded.

The database has been opened.

SQL> Show parameter undo_management

Name Type Value

----------------------------------- --- ---------------------------

undo_management string auto

SQL> Show parameter undo_tablespace

Name Type Value

----------------------------------- --- ---------------------------

Undo_tablespace string undotbs1

SQL> SELECT TABLESPACE_NAME, STATUS

2 from DBA_TABLESPACESPACES

3 where TableSpace_name Like 'undo%';

TableSpace_name status

---------------------------------------

Undotbs1 Online

Undotbs2 online

SQL> ALTER system set undo_tablespace = undotbs2;

The system has changed.

SQL> Show parameter undo_tablespace

Name Type Value

----------------------------------- --- -----------

Undo_tablespace string undotbs2

SQL>

How to monitor the use of undo space in SMU or AMU mode?

• In Oracle 9i, the V $ undostat view can be used to determine the usage of undo space. The following query is used to determine the time executed in the undo space:

E.g:

Select to_char (min (begin_time), 'DD-MON-YYYY HH24: MI: SS')

"Begin Time",

TO_CHAR (MAX (End_Time), 'DD-MON-YYYY HH24: MI: SS')

"End Time",

SUM (Undoblks) "Total Undo Blocks USED",

SUM (TXNCOUNT) "TOTAL NUM TRANS EXECUTED",

MaxQuerylen "LONGEST Query (In Secs)", Max (MaxConcurrency) "Highest Concurrent Transaction Count",

SUM (SSOLDERRCNT),

SUM (NospaceerRcnt)

From v $ undostat;

SQL> SELECT TO_CHAR (MIN (Begin_Time), 'DD-MON-YYYY HH24: MI: SS')

2 "Begin Time",

3 TO_CHAR (MAX (End_Time), 'DD-MON-YYYY HH24: MI: SS')

4 "End Time",

5 SUM (Undoblks) "Total Undo Blocks Used",

6 SUM (TXNCOUNT) "Total Num Trans Ex Ex Ex Export",

7 MAX (MAXQUERYLEN) "LONGEST Query (in Secs),

8 Max (MaxConcurrency) "Highest Concurrent Transaction",

9 SUM (SSOLDERRCNT),

10 sum (NospaceerRcnt)

11 from v $ undostat;

Begin Time End Time Total undo Blocks Used Total Num Trans Ex Ex Ex Ex Ex Ex Ex Ex Ex Ex Ex Ex Ex Ex Executed LONGEST Query (in Secs) Highest Concurrent Transaction Sum (NospCNT) SUM (NospaceerRcnt)

------------------------------------------------ ---------------------------------------------------------------- ---------- ---------------------------------------- ------------------------

29-June -2003 10:18:05 29- June -2003 12:47:26 13 160 4 1 0 0

SQL>

· V $ WaitStat view can be used to determine the waiting for each returning segment data block:

SELECT CLASS, Count

From v $ waitstat

Where class like '% Undo%'

And count> 0;

SQL> SELECT CLASS, COUNT

2 from v $ waitstat

3 WHERE CLASS LIKE '% UNDO%'

4 and count> 0;

Class Count

--------------------------

SQL>

· V $ SYSSTAT view can determine the total data request:

Select Sum (Value) "Data Requests"

From v $ sysstat

WHERE Name In ('CONSISTENT GETS'); SQL> SELECT SUM (Value) "Data Requests"

2 from V $ sysstat

3 WHERE NAME IN ('DB Block Gets');

Data Requests

-------------

110019

SQL>

· DBA_ROLLBACK_SEGS view can determine the storage information and status of the rollback segment, etc. Information:

Select segment_name, owner, tablespace_name, status,

Initial_extent, next_extent,

MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE

From DBA_ROLLBACK_SEGS;

SQL> SELECT Segment_Name, Owner, TableSpace_name, STATUS,

2 INITIAL_EXTENT, NEXT_EXTENT,

3 min_extents, max_extents, PCT_INCREASE

4 from DBA_ROLLBACK_SEGS;

Segment_name oowner tablespace_name status initial_extent next_extent min_extents max_extents PCT_INCREASE

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------

System sys system online 114688 1 32765

_SYSSMU1 $ public undotbs1 offline 131072 2 32765

_SYSSMU2 $ public undotbs1 offline 131072 2 32765

_SYSSMU3 $ public undotbs1 offline 131072 2 32765

_SYSSMU4 $ public undotbs1 offline 131072 2 32765

_SYSSMU5 $ public undotbs1 offline 131072 2 32765_syssmu6 $ public undotbs1 offline 131072 2 32765

_SYSSMU7 $ public undotbs1 offline 131072 2 32765

_SYSSMU8 $ public undotbs1 offline 131072 2 32765

_SYSSMU9 $ public undotbs1 offline 131072 2 32765

_SYSSMU10 $ public undotbs1 offline 131072 2 32765

_SYSSMU11 $ public undotbs2 online 131072 2 32765

_SYSSMU12 $ public undotbs2 Online 131072 2 32765

_SYSSMU13 $ public undotbs2 online 131072 2 32765

_SYSSMU14 $ public undotbs2 Online 131072 2 32765

_SYSSMU15 $ public undotbs2 Online 131072 2 32765

_SYSSMU16 $ public undotbs2 online 131072 2 32765

_SYSSMU17 $ public undotbs2 online 131072 2 32765_syssmu18 $ public undotbs2 online 131072 2 32765

_SYSSMU19 $ public undotbs2 Online 131072 2 32765

Segment_name oowner tablespace_name status initial_extent next_extent min_extents max_extents PCT_INCREASE

_SYSSMU20 $ public undotbs2 Online 131072 2 32765

21 rows selected

SQL>

· V $ Transaction view can determine the quantity of each transaction using undo space:

SELECT MAX (USED_UBLK)

From V $ Transaction;

How to reduce the competition of the rollback segment?

The database usually runs in RMU mode will bring more questions about the competition of returning segments. In order to reduce the competition of the rollback segment, the following strategies are usually considered:

· Increase the number of returns

· Set the storage parameters next and initial to the same value

· Set the storage parameter min_extents to at least 20 value

· Set the storage parameters Optimal to Initial X Min_extents

· Make sure there are many free space in the split segment table space

When the database is running in the SMU or AMU mode, the Undo Segment's competition is managed by Oracle. However, this does not mean that the DBA can be used completely without the use of undo space, because:

· At the same time, there is only one activity UNDO table space

· All undo segments in a undo tablespace has the same size

· This size of this undo segment may not be suitable for every transaction

• When undo tablespace online / offline, all undo segments in this undo space is Online / Offline

· Oracle manages undo segments, and they always occupy space (not released)

• Although Oracle management Undo Segments competition, there are still some problems, such as shrinkage and loop problems when using Extends. If the size of the undo segments is inappropriate, these problems will still lead to performance issues. · The setting of the sessions parameter affects the number and size of the Undo Segments

When using SMU or AMU, in order to improve performance and avoid (decrease) Undo segments related issues, Oracle DBA can consider:

· Determine the use of undo space, the degree of transaction, the number of times the database instance is loaded, and give the sessions parameter to a suitable value

· Create multiple different sizes of undo tablespace to cater to different types of transactions:

UndoSpace =

* UPS OVERHEAD

among them:

• undoSpace is the number of blocks of undo blocks;

• UR is the value of undo_retrion, namely

• UPS is the undo blocks (transaction rate, transaction rate) per second, and you can get the value of the transaction rate by querying the V $ undostat view when transaction stabilization.

• Overhead is some small media data such as Transaction Tables, Bitmaps, and more. The value of Overhead can also be obtained by querying the V $ undostat view.

For example, it is assumed that the undo_retention is set to 2 hours (7200 seconds), the transaction rate (UPS) is 200 undo blocks per second, the data block size is 4KB, then the required space is: (7200 * 200 * 4K) = 5.8 GB.

· Dynamically change the undo_tablespace parameters as needed to use the most suitable Undo space to handle transactions

· Monitor the V $ undostat view so that Oracle is how to manage Undo space; when performance problems occur, consider whether to adjust the appropriate undo tableSpace, or recreate a more suitable size undo tablespace.

to sum up

Run Oracle 9i database in RMU or SMU mode, Oracle DBA ultimately throws the performance impact of the database; these two modes may be due to unsolicient design results in performance issues.

转载请注明原文地址:https://www.9cbs.com/read-17674.html

9cbs

New Post(0)