latch - undo global data

latch - undo global data

根据metalink文档解释，是由于undo表空间不足引起的。

This implies that sessions are struggling to find new undo extents and are having to steal.
"ktusm_stealext_2" is used to steal undo extents. As _undo_autotune is FALSE then the undo retention should be static.
In conclusion, this problem could occur if available undo space is not enough.

从会话等待事件中，latch free的等待是undo global data。

       SID EVENT                                  P1 P1RAW                    P2 P2RAW                    P3 P3RAW
---------- ------------------------------ ---------- ---------------- ---------- ---------------- ---------- ----------------
        40 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
        43 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       134 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       151 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       201 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       290 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       341 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       368 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       473 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       546 latch free                     1503248727 0000000380018D68        139 000000000000008B          1 0000000000000001
       636 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       698 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       735 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       834 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00
       873 latch free                     1503248727 0000000380018D68        139 000000000000008B          0 00

Top 5等待事件：latch free占用等待事件时47%
 
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free                                      8,169,518      22,424    47.61
enqueue                                           405,207      13,784    29.27
CPU time                                                        9,980    21.19

latch活动报告：undo global data等待时间是20923s。
                                           Pct    Avg   Wait                 Pct
                              Get          Get   Slps   Time       NoWait NoWait
Latch                       Requests      Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
simulator lru latch               3,092    0.0             0      134,676    0.2
sort extent pool                    560    0.0             0            0
spilled msgs queues list             20    0.0             0            0
temp lob duration state               7    0.0             0            0
temporary table state ob              6    0.0             0            0
transaction allocation      163,843,848    0.0    0.0      0            0
transaction branch alloc             15    0.0             0            0
undo global data          1,851,751,408    3.0    0.1  20923            0
user lock                         2,045    3.5    0.0      0            0

latch breakdown报告：undo global data睡眠的次数，并有231800是进入第4次休眠的。

                                      Get                            Spin &
Latch Name                       Requests      Misses      Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
undo global data            1,851,751,408  55,786,095   6,852,440 50414818/417
                                                                  7753/961724/
                                                                  231800/0
row cache enqueue latch       214,001,136  53,622,544       6,620 53615931/660
                                                                  6/7/0/0
enqueues                      155,739,542  43,524,289      34,176 43491102/322
                                                                  51/885/51/0
row cache objects             214,009,831  21,511,576   1,270,644 20381340/101
                                                                  0482/101868/

latch source报告：具体出现undo global data的地方是ktusm_stealext: KSLBEGIN。

                                                     NoWait              Waiter
Latch Name               Where                       Misses     Sleeps   Sleeps
------------------------ -------------------------- ------- ---------- --------
session allocation       ksucri                           0          1        2
shared pool              kghalo                           0        148       37
shared pool              kghfrunp: clatch: nowait         0         88        0
shared pool              kghfrunp: alloc: wait            0         69        2
shared pool              kghalp                           0         26        0
shared pool              kghfrunp: clatch: wait           0         12        0
shared pool              kghupr1                          0          4      207
undo global data         ktusm_stealext: KSLBEGIN         0  6,776,284 ########
undo global data         ktusmupst: KSLBEGIN              0     27,878   36,154
undo global data         ktucmt: ktugd_cuux               0     21,752   17,954
undo global data         ktusmasp: ktugd_suux             0     12,158   18,322
undo global data         ktusmasp: ktugd_tuux             0     11,934   21,100
undo global data         ktugnb: ktugd_tuux               0      1,633    1,706
undo global data         ktudba: KSLBEGIN                 0        268    5,789

undo报告
-> Undo segment block stats:
-> uS - unexpired Stolen,   uR - unexpired Released,   uU - unexpired reUsed
-> eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed

Undo           Undo        Num  Max Qry     Max Tx Snapshot Out of uS/uR/uU/
 TS#         Blocks      Trans  Len (s)   Concurcy  Too Old  Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
  28         96,574 ##########    2,198         27        0      0 107954/576/67
                                                                   361/106935/23
                                                                   744/0

Column Datatype Description
BEGIN_TIME DATE Identifies the beginning of the time interval
开始采样时间
END_TIME DATE Identifies the end of the time interval
结束采样时间
UNDOTSN NUMBER Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported.
上次激活的undo表空间
UNDOBLKS NUMBER Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.
上次激活的undo块数
TXNCOUNT NUMBER Identifies the total number of transactions executed within the period
事务数
MAXQUERYLEN NUMBER Identifies the length of the longest query (in seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view.
最长事务数
MAXQUERYID VARCHAR2(13) SQL identifier of the longest running SQL statement in the period
最长事务数的sql语句
MAXCONCURRENCY NUMBER Identifies the highest number of transactions executed concurrently within the period
并发事务数
UNXPSTEALCNT NUMBER Number of attempts to obtain undo space by stealing unexpired extents from other transactions
尝试获取undo空间，通过从其他事务steal没有过期的extents。
UNXPBLKRELCNT NUMBER Number of unexpired blocks removed from certain undo segments so they can be used by other transactions
从undo段中去除的没有过期的块数，这样其他事务开始使用。
UNXPBLKREUCNT NUMBER Number of unexpired undo blocks reused by transactions
被事务重用的没有过期的块数。
EXPSTEALCNT NUMBER Number of attempts to steal expired undo blocks from other undo segments
尝试steal过期extents的次数。
EXPBLKRELCNT NUMBER Number of expired undo blocks stolen from other undo segments
从其他undo段steal过期的块数。
EXPBLKREUCNT NUMBER Number of expired undo blocks reused within the same undo segments
被事务重用的过期的块数。
SSOLDERRCNT NUMBER Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.
ORA-01555出现的次数。
NOSPACEERRCNT NUMBER Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.
请求undo空间时发现空间不够的次数。
ACTIVEBLKS NUMBER Total number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period
所有活动的extents的块数
UNEXPIREDBLKS NUMBER Total number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period
所有没有过期的extents的块数
EXPIREDBLKS NUMBER Total number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the period
TUNED_UNDORETENTION NUMBER Amount of time (in seconds) for which undo will not be recycled from the time it was committed. At any point in time, the latest value of TUNED_UNDORETENTION is used to determine whether data committed at a particular time in the past can be recycled.
所有过期的extents的块数

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-753491/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-753491/