Oracle tune v $ UNDOSTAT

Oracle tune v $ UNDOSTAT a 11g database running on an 18-touch AIX database. During the check, it is found that the data in view V $ UNDOSTAT is very strange, 1st records have never been cut. the start time is 09:52:08 and the end time is 15:20:34. Of course, no records are recorded in the archive table. Under normal circumstances, the view should be filled with data for less than 10 minutes, lasting for four days, that is, 6*24*4 = 576 records, and then the table will be reused; related history records are archived to DBA_HIST_UNDOSTAT. Records are inserted in descending order of time, from the nearest to the far. Among them, 1st records are the last collection end point to the current time interval. You can see that END_TIME is constantly refreshing. Let's see what is normal. [SQL] [oracle @ LINUXDB ~] $ Export NLS_DATE_FORMAT = 'yyyy-mm-dd hh24: mi: ss' [oracle @ LINUXDB ~] $ Sqlplus "/as sysdba" SQL * Plus: Release 11.2.0.3.0 Production on Tue May 14 15:19:58 2013 Copyright (c) 1982,201 1, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select begin_time, end_time from v $ undostat; BEGIN_TIME END_TIME ----------------- -- 15:16:04 15:20:00 15:06:04 15:16:04 14:56:04 15:06:04 14:46:04 14:56:04: 46: 04 13:56:04 13:36:04 13:46:04 13:26:04 13:36:04 13:16:04: 36: 04 12:16:04 12:26:04 12:06:04 12:16:04 11:56:04 12:06:04 11:46:04 22 rows selected. SQL> select begin_time, end_time from dba_hist_undostat; BEGIN_TIME END_TIME --------------------- ------------------- 11:46:04 11:56:04 12:56:04 13:06:04 3-05-14 13:16:04 13:16:04 13:26:04 13:26:04 13:36:04 ------------------- 201 3-05-14 12:36:04 12:46:04 12:46:04 12:56:04 13:56:04 14:06:04 14:06:04 14:16:04 14:16:04 14:26:04 14:26:04 14:36:04 14:36:04 14:46:04 14:46:04 14:56:04 rows selected. now let's see if there is no cutting: AIX Version 6 Copyright IBM Corporation, 1982,201 1. Login: oracle's Password: **************************************** **************************************** * *** Welcome to AIX Version 6.1! * ***** Please see the README file in/usr/lpp/bos for information pertinent to ** this release of the AIX Operating System. **************************************** **************************************** * *** Last unsuccessful login: fri May 3 21:52:33 GMT + 08:00 2013 on/dev/pts/6 from 10.46.154.32 Last login: Tue May 14 15:11:11 GMT + 08:00 2013 on/dev/pts/6 from 10.46.160.207 AIXDB: /or Acle $ export NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: SS 'aixdb:/oracle $ sqlplus "/as sysdba" SQL * Plus: release 11.2.0.2.0 Production on Tue May 14 15:20:12 2013 Copyright (c) 1982,201 0, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select begin_t Ime, end_time from v $ undostat where rownum <= 5; BEGIN_TIME END_TIME limit ------------------- 2011-12-26 09:52:08 2013-05-14 15:20:34 2011-12-26 09:42:08 2011-12-26 09:52:08 2011-12-26 09:32:08 2011-12-26 09:42:08 2011-12-26 09:22:08 2011-12-26 09:32:08 SQL> select begin_time, end_time from limit; no rows selected is really not normal, so a line that spans all time periods The statistical data is the same as that without statistical data, and is not measurable and tested. However, because it is a production environment, the information statistics may be canceled for any reason, let's take a look at how the setting will lead to this effect. Then we found a different phenomenon. In Linux 11203 and AIX 11202, the parameter _ undo_autotune is set to a different value by default: SQL> show parameter undo name type value =----------- export undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> show parameter undo NAME TYPE VALUE =----------- export _ undo_a Utotune boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 is a bit confusing. I guess it is possible that the platform is different because of different versions, in this way, we can explain why the data differences between V $ UNDOSTAT and DBA_HIST_UNDOSTAT are observed: When _ undo_autotune = FALSE, statistics are not automatically stored and archived, to enable this feature, SET _ undo_autotune = TRUE. When _ undo_autotune = FALSE is set: SQL> alter system set "_ undo_autotune" = FALSE scope = both; System altered. SQL> startup force; ORACLE instance started. total System Global Area 2.0176E + 10 bytes Fixed Size 2237048 bytes Variable Size 2483031432 bytes Database Buffers 1.7650E + 10 bytes Redo Buffers 41488384 bytes Database mounted. database opened. SQL> select sysdate from dual; SYSDATE ------------------- 16:23:59 SQL> select begin_time, end_time from v $ undostat; BEGIN_TIME END_TIME ------------------- 16:22:35 2013-05-14 16:24:00 SQL> select begin_time, end_time from v $ undostat; BEGIN_TIME END_TIME ------------------- ----------------- 2013-05-14 16:22:35 2013-05-14 16:54:27 you can see that it has been half an hour and no new records have been inserted. When _ undo_autotune = TRUE is set: SQL> alter system set "_ undo_autotune" = TRUE scope = both; System altered. SQL> select begin_time, end_time from v $ undostat; BEGIN_TIME END_TIME ------------------- ----------------- 2013-05-14 17:02:35 2013-05-14 17:05:37 2013-05-14 16:52:35 2013-05-14 17:02:35 2013-05-14 16:32:35 2013-05-14 16:52:35 so that the automatic collection status is restored. -EOF-