IMU technology and redoprivatestrand Technology in Oracle

Source: Internet
Author: User
Tags flushes

IMU technology and redoprivatestrand Technology in Oracle

Oracle030
IMU technology and redo private strand Technology in oracle030Oracle



3. Explain the Oracle IMU Mechanism
Select * from v $ sysstat where name like "% IMU % ';
STATISTIC #, NAME, CLASS, VALUE, STAT_ID
312 IMU commits 128 393 1914489094
313 IMU Flushes 128 88 2099506212
314 IMU contention 128 1 2909373607
315 IMU recursive-transaction flush 128 2 2591100633
316 IMU undo retention flush 128 0 2087226422
317 IMU ktichg flush 128 0 1206609541
318 IMU bind flushes 128 0 2756376339
319 IMU mbu flush 128 0 3723686946
320 IMU pool not allocated 128 0 659017805
321 imu cr rollbacks 128 114 2225124543
322 IMU undo allocation size 128 1867712
323 IMU Redo allocation size 128 161408
324 IMU-failed to get a private strand 128 0 2412863545

4. Oracle private redo strands Mechanism
Check the usage of rollback segments. Which user is using the resources of the rollback segments?
Select s. username, u. name from v $ transaction t, v $ rollstat r,
V $ rollname u, v $ session s where s. taddr = t. addr and
T. xidusn = r. usn and r. usn = u. usn order by s. username;

Check the UNDO Segment status
Select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks
From v $ rollstat order by rssize;



Determine the UNDO tablespace used by the current routine
Show parameter undo_tablespace

Show all UNDO tablespaces of the database
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'undo ';

Show UNDO tablespace statistics
SELECT TO_CHAR (BEGIN_TIME, 'hh24: MI: ss') BEGIN_TIME,
TO_CHAR (END_TIME, 'hh24: MI: ss') END_TIME, UNDOBLKS
From v $ UNDOSTAT;

Show UNDO segment statistics
SELECT a. name, B. xacts, B. writes, B. extents
FROM v $ rollname a, v $ rollstat B
WHERE a. usn = B. usn;

Show active transaction information
Col username format a10
Col name format a10
SELECT a. username, B. name, c. used_ublk
FROM v $ session a, v $ rollname B, v $ transaction c
WHERE a. saddr = c. ses_addr AND B. usn = c. xidusn
AND a. username = 'hr ';







Common columns in V $ ROLLSTAT

USN Rollback segment number
EXTENTS Number of extents in the rollback segment
RSSIZE Size (in bytes) of the rollback segment.
WRITES Number of bytes written to the rollback segment
XACTS Number of active transactions
GETS Number of header gets
WAITS Number of header waits
OPTSIZE Optimal size of the rollback segment
HWMSIZE High-watermark of rollback segment size
SHRINKS Number of times the size of a rollback segment decreases
WRAPS Number of times rollback segment is wrapped
EXTENDS Number of times rollback segment size is extended
AVESHRINK Average shrink size
AVEACTIVE Current size of active extents, averaged over time.
STATUS Rollback segment status:
ONLINE
PENDING OFFLINE
OFFLINE
FULL
CUREXT Current extent
CURBLK Current block


5. read consistency
ORA-01555 error.

6. undo advisor
EM

Redo's medium read consistency (ORA-01555 error mechanism problem) problem undo tablespace size settings



Solve this problem: 1. Ensure that the data retention time of the undo tablespace is at least greater than the time of the longest SQL statement. 2. Increase the size of the undo tablespace by looking for the time.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.