最近研究设置数据,稍微总结一下,以后继续补充:
在一个18摸AIX上跑的一台11g的库,检查的时候发明视图V$UNDOSTAT的数据很诡异,第1条的记载时光段竟然一直没有被切割过,开始时光是2011-12-26 09:52:08结束时光是2013-05-14 15:20:34,当然归档表也没记载。
正常情况下该视图应该被没10分钟的数据给填充满,一直延续4天,也就是 6*24*4=576 条记载,之后会重用这个表;相关的历史记载会归档到DBA_HIST_UNDOSTAT中去。
记载按照时光从近到远的倒序插入,其中第1条记载是最后一次搜集的结束点到以后的时光间隔,你可以看到END_TIME是一直在刷新的。
来看下正常情况的是怎么样的。
[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, 2011, 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
------------------- -------------------
2013-05-14 15:16:04 2013-05-14 15:20:00
2013-05-14 15:06:04 2013-05-14 15:16:04
2013-05-14 14:56:04 2013-05-14 15:06:04
2013-05-14 14:46:04 2013-05-14 14:56:04
2013-05-14 14:36:04 2013-05-14 14:46:04
2013-05-14 14:26:04 2013-05-14 14:36:04
2013-05-14 14:16:04 2013-05-14 14:26:04
2013-05-14 14:06:04 2013-05-14 14:16:04
2013-05-14 13:56:04 2013-05-14 14:06:04
2013-05-14 13:46:04 2013-05-14 13:56:04
2013-05-14 13:36:04 2013-05-14 13:46:04
BEGIN_TIME END_TIME
------------------- -------------------
2013-05-14 13:26:04 2013-05-14 13:36:04
2013-05-14 13:16:04 2013-05-14 13:26:04
2013-05-14 13:06:04 2013-05-14 13:16:04
2013-05-14 12:56:04 2013-05-14 13:06:04
2013-05-14 12:46:04 2013-05-14 12:56:04
2013-05-14 12:36:04 2013-05-14 12:46:04
2013-05-14 12:26:04 2013-05-14 12:36:04
2013-05-14 12:16:04 2013-05-14 12:26:04
2013-05-14 12:06:04 2013-05-14 12:16:04
2013-05-14 11:56:04 2013-05-14 12:06:04
2013-05-14 11:46:04 2013-05-14 11:56:04
22 rows selected.
SQL> select begin_time,end_time from dba_hist_undostat;
BEGIN_TIME END_TIME
------------------- -------------------
2013-05-14 11:46:04 2013-05-14 11:56:04
2013-05-14 12:56:04 2013-05-14 13:06:04
2013-05-14 13:06:04 2013-05-14 13:16:04
2013-05-14 13:16:04 2013-05-14 13:26:04
2013-05-14 13:26:04 2013-05-14 13:36:04
2013-05-14 13:36:04 2013-05-14 13:46:04
2013-05-14 13:46:04 2013-05-14 13:56:04
2013-05-14 11:56:04 2013-05-14 12:06:04
2013-05-14 12:06:04 2013-05-14 12:16:04
2013-05-14 12:16:04 2013-05-14 12:26:04
2013-05-14 12:26:04 2013-05-14 12:36:04
BEGIN_TIME END_TIME
------------------- -------------------
2013-05-14 12:36:04 2013-05-14 12:46:04
2013-05-14 12:46:04 2013-05-14 12:56:04
2013-05-14 13:56:04 2013-05-14 14:06:04
2013-05-14 14:06:04 2013-05-14 14:16:04
2013-05-14 14:16:04 2013-05-14 14:26:04
2013-05-14 14:26:04 2013-05-14 14:36:04
2013-05-14 14:36:04 2013-05-14 14:46:04
2013-05-14 14:46:04 2013-05-14 14:56:04
19 rows selected.
现在来看看没有切割的情况:
AIX Version 6
Copyright IBM Corporation, 1982, 2011.
login: oracle
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:/oracle$ 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, 2010, 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_time,end_time from v$undostat where rownum<=5;
BEGIN_TIME END_TIME
------------------- -------------------
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
2011-12-26 09:12:08 2011-12-26 09:22:08
SQL> select begin_time,end_time from dba_hist_undostat;
no rows selected
这个确实不太正常,这样一条高出所有时光段的统计数据和没有统计数据一样,不具备可衡量和检测性;但是由于是出产环境,可能出于什么原因取消了信息的统计临时不管,我们来看看究竟是怎么样的设置才会致使这样的效果。
接着发明个不一样的现象,在Linux的11203和AIX的11202当中有个参数_undo_autotune默认不一样的取值:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
这点着实有点困惑,个人猜想由于版本不一样,况且平台还不一样,所以不一致是有可能的,这样就可以解释为什么我观察到的V$UNDOSTAT和DBA_HIST_UNDOSTAT的数据差异的情况了:
当_undo_autotune =FALSE的时候,就不会自动将统计信息分割存放和归档,如果要启用这个特性需要设置_undo_autotune =TRUE。
设置_undo_autotune =FALSE 时候:
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
-------------------
2013-05-14 16:23:59
SQL> select begin_time,end_time from v$undostat;
BEGIN_TIME END_TIME
------------------- -------------------
2013-05-14 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
可以看到都过了半个小时了还没有插入新的记录。
设置_undo_autotune =TRUE 时候:
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
2013-05-14 16:22:35 2013-05-14 16:32:35
这样就由恢复自动搜集的状态了。
-EOF-
文章结束给大家分享下程序员的一些笑话语录: N多年前,JohnHein博士的一项研究表明:Mac用户平均IQ要比PC用户低15%。超过6000多的参加者接受了测试,结果清晰的显示IQ比较低的人会倾向于使用Mac。Mac用户只答对了基础问题的75%,而PC用户却高达83%。