Remember Oracle's undo table space Full analysis process

This article is a record of an Oracle database Undo table Space Full analysis process, the main collation, carding the colleagues analysis of the idea. The process is as follows:

In the morning, I received an alarm message from the database server's undo tablespace, the first one was 7:55 (the monitoring job was 15 minutes), from the alarm email analysis, as if the undo table space was suddenly exhausted.

Db	Tablespace	Allocated	Free	Used	% FREE	% used
192.168.xxx.xxx:1521	UNDOTBS1	16384	190.25	16193.75	1.16	99

Using some SQL to analyze the undo tablespace usage, and the undo segment state, and so on, very much want to locate which or those SQL exhausted the undo tablespace, but no SQL can achieve my idea, or I do not understand.

Select UPPER (f.tablespace_name) as "Tablespace_name",

ROUND (d.max_bytes,2) as "Tbs_total_size",

ROUND (D.availb_bytes, 2) as "Tablespace_size",

ROUND (d.max_bytes-d.availb_bytes +used_bytes,2) as "Tbs_avable_size",

ROUND ((d.availb_bytes-f.used_bytes), 2) as "Tbs_used_size",

To_char (ROUND (d.availb_bytes-f.used_bytes)/d.availb_bytes * 100,

2),

' 999.99 ') as "used_rate (%)",

ROUND (F.used_bytes, 6) as "Free_size (G)"

From (SELECT Tablespace_name,

ROUND (SUM (BYTES)/(1024x768 * 1024x768), 6) Used_bytes,

ROUND (MAX (BYTES)/(1024x768 * 1024x768), 6) max_bytes

From SYS. Dba_free_space

GROUP by Tablespace_name) F,

(SELECT DD.) Tablespace_name,

ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 6) Availb_bytes,

ROUND (SUM (DECODE (DD). MaxBytes, 0, DD. BYTES, DD. MaxBytes)/(1024*1024*1024), 6) max_bytes

From SYS. Dba_data_files DD

GROUP by DD. Tablespace_name) D

Here D.tablespace_name = F.tablespace_name

and D.tablespace_name=&undo_tablespace_name

Rder by 5 DESC;

Select Usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

From V$rollstat order by Rssize;

Since the direct start, unable to locate, then the curve analysis, first check, analysis of redo log, found at 7 points this time, the log switch 83 times, horizontal, vertical contrast, obvious anomalies, as follows:

SELECT

To_char (first_time, ' yyyy-mm-dd ') Day,

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 00 ', 1,0)), ' 99 ') "00",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 01 ', 1,0)), ' 99 ') "01",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 02 ', 1,0)), ' 99 ') "02",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 03 ', 1,0)), ' 99 ') "03",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 04 ', 1,0)), ' 99 ') "04",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 05 ', 1,0)), ' 99 ') "05",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 06 ', 1,0)), ' 99 ') "06",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 07 ', 1,0)), ' 99 ') "07",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 08 ', 1,0)), ' 99 ') "0",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 09 ', 1,0)), ' 99 ') "09",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 10 ', 1,0)), ' 99 ') "10",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 11 ', 1,0)), ' 99 ') "11",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 12 ', 1,0)), ' 99 ') "12",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 13 ', 1,0)), ' 99 ') "13",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 14 ', 1,0)), ' 99 ') "14",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 15 ', 1,0)), ' 99 ') "15",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 16 ', 1,0)), ' 99 ') "16",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 17 ', 1,0)), ' 99 ') "17",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 18 ', 1,0)), ' 99 ') "18",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 19 ', 1,0)), ' 99 ') "19",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 20 ', 1,0)), ' 99 ') "20",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 21 ', 1,0)), ' 99 ') "21",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 22 ', 1,0)), ' 99 ') "22",

To_char (SUM (DECODE (To_char (first_time, ' HH24 '), ' 23 ', 1,0)), ' 99 ') "23"

From

V$log_history

GROUP by

ORDER by 1 DESC;

The AWR report of the instance in the 7:00~8:00 time period is generated, and we can see from the following indicator that the DB instance is in fact very idle during this time, because it is 9.74 (mins)

In addition, from the Time Model statistics section, the main timing is spent in background elapsed times instead of db time, and we can tell that the times are mainly spent in the background process, not the foreground process. In addition, SQL execute elapsed time consumes 70.36 of the times of DB.

Then we look at the SQL order by gets section information, the first SQL is deleted Wrh$_sql_plan records, of course, there are deleted Wrh$_sqltext, wrh$_seg_stat_obj table Records of SQL, as follows

DELETE

From Wrh$_sql_plan tab

WHERE (: Beg_snap <= tab.snap_id

and tab.snap_id <=: End_snap

and dbid =:d Bid)

And not EXISTS

(SELECT 1

From Wrm$_baseline b

WHERE (tab.dbid = b.dbid)

and (tab.snap_id >= b.start_snap_id)

and (tab.snap_id <= b.end_snap_id)

)

DELETE

From Wrh$_sqltext tab

WHERE (tab.dbid =:d bid

And:beg_snap <= tab.snap_id

and tab.snap_id <=: End_snap

and Tab.ref_count = 0)

And not EXISTS

(SELECT 1

From Wrm$_baseline b

WHERE (b.dbid =:d Bid2

and tab.snap_id >= b.start_snap_id

and tab.snap_id <= b.end_snap_id)

);

DELETE

From Wrh$_seg_stat_obj tab

WHERE (: Beg_snap <= tab.snap_id

and tab.snap_id <=: End_snap

and dbid =:d Bid)

And not EXISTS

(SELECT 1

From Wrm$_baseline b

WHERE (tab.dbid = b.dbid)

and (tab.snap_id >= b.start_snap_id)

and (tab.snap_id <= b.end_snap_id)

);

View the SQL ordered by reads section of the information, found that the main is to delete the system table Wrh$_sql_plan records (this table is very large)

DELETE

From Wrh$_sql_plan tab

WHERE (: Beg_snap <= tab.snap_id

and tab.snap_id <=: End_snap

and dbid =:d Bid)

And not EXISTS

(SELECT 1

From Wrm$_baseline b

WHERE (tab.dbid = b.dbid)

and (tab.snap_id >= b.start_snap_id)

and (tab.snap_id <= b.end_snap_id)

)

Then we look at the tablespace IO stats part of the AWR report, the IO is mainly focused on the SYSAUX,UNDOTBS1 table space, and then you will find that the table Wrh$_sql_plan is under Sysaux

So, the evidence above shows that we can almost conclude that the main result of this SQL is the explosion of undo table space usage. Of course, in the analysis process, there are some audit evidence. There is no need to list this in this sense.

DELETE

From Wrh$_sql_plan tab

WHERE (: Beg_snap <= tab.snap_id

and tab.snap_id <=: End_snap

and dbid =:d Bid)

And not EXISTS

(SELECT 1

From Wrm$_baseline b

WHERE (tab.dbid = b.dbid)

and (tab.snap_id >= b.start_snap_id)

and (tab.snap_id <= b.end_snap_id)

)

Xiaoxiang Hidden Source: http://www.cnblogs.com/kerrycode/

Remember Oracle's undo table space Full analysis process