ORA-01555：snapshot too old Error (一)

ORA-01555：snapshot too old Error
产生ORA-01555错误主要有三种可能性：
1、UNDO段对于系统所运行的工作负荷太小；
2、在循环过程中有事务提交是造成ORA-01555错误的最常见的原因；
3、block cleanout；

解决ORA-01555错误的解决方案有下面几种：
1、适当的设置UNDO_RETENTION参数值（大于事务执行可能需要的最长时间）；如果UNDO_RETENTION参数值变大，那么同时也需要根据果UNDO_RETENTION参数值大小修改UNDO表
   空间的大小也需要做相应的修改；（适合于UNDO管理是自动管理）
   网上查询UNDO表空间的大小可以根据公式：
   Undo Size = Undo_retention * UPS
   UPS是undo block per second, 我们可以通过V$UNDOSTAT. UNDOBLKS获得       .
   SQL> select avg(undoblks)/(10*60) UPS from v$undostat;
   UPS
   -------------
   0.03
   则undo_retention=10800，至少需要  10800*0.03=324个数据块。
2、如果是手动管理UNDO，增长UNDO段的大小或者增加UNDO段的的个数，这样做可以减少在查询延续的时间比较长的情况下UNDO数据被覆盖的可能性。这个解决方案可以解决上面
   的三种造成错误原因的情况；
3、减少查询时间，优化查询语句。如果可能的话，这是最好的方法，所以这是首要考虑的方法。因为减少查询时间可以减少对UNDO空间的需求。这个解决方案可以解决上面
   的三种造成错误原因的情况；
4、对相关的对象做下分析统计。因为这个方法会避免上面造成错误的第三种可能性：block cleanout的情况发生。因为block cleanout的情况经常是大量的更新或者插入操作
   的结果。所以在进行完大量的更新或者插入操作之后做下相关对象的分析统计是有必要的。

实验1：针对第二种最常见的情况和第一种可能情况：
--创建undo表空间时固定表空间的大小
create undo tablespace undo_small
 datafile 'undo_small .dbf'
 size 2m
 autoextend off ；
--切换表空间为定义的小的undo表空间  
alter system set undo_tablespace=undo_small;

drop table t

--创建测试表，并且要求数据是随机产生的
create table t
as
select *
from all_objects
order by dbms_random.random;

alter table t add constraint t_pk primary  key(object_id);

--统计表
begin
dbms_stats.gather_table_stats('DAIMIN','T',cascade=>true);
end;

--大量更新操作
begin
 for x in ( select rowid rid from t )
 loop
 update t set object_name = lower(object_name) where rowid = x.rid;
 commit;
 end loop;
 end; 
(注：采用循环更新提交比一次性更新提交要慢很多，即提交的次数多反而性能慢。即上面的更新语句比update t set object_name = lower(object_name)要慢很多。
 有兴趣的可以测试下，这个不是这里主要讲的。)
--在上一个程序执行的同时，下面的语句在另一个会话中执行，则会报ORA-01555错误
declare
 cursor c is  
 select /*+ first_rows */ object_name    --c游标中的数据集是按照object_id排好序的。
 from t
 order by object_id;
l_object_name t.object_name%type;
 l_rowcnt number := 0;
 begin
 open c;
 loop
fetch c into l_object_name;
exit when c%notfound;
dbms_lock.sleep( 0.01 );
l_rowcnt := l_rowcnt+1;
end loop;
 close c;
 exception
 when others then
 dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
  raise;
 end;

报错分析：
1、undo表空间太小；
2、因为在更新是一行一行按照原来插入数据的顺序地更新的，减少了对每个指定行更新前的查找记录的时间；相反，对于上面的查询来说，
因为不是按照原来插入数据的顺序查找数据的，而是按照自己object_id排好序的顺序查找数据的。所以下面的查询语句必定比上面的更
新语句执行的慢，所需要的时间也比较的长。这样就有可能发生这样的情况：当查询语句需要查一行数据时，但是该行数据已经被更新语
句在很久以前就已经被更新修改过并且已经提交掉，因为UNDO表空间很小，又由于更新采用的是循环更新操作，使得UNDO表空间被循环利用，
在循环中后面的更新语句所产生的UNDO数据会覆盖掉之前产生的UNDO数据，造成此时查询语句查找不到在查找开始的时间点上该行记录的
数据，则会报ORA-01555。
-----------------------------------------------------------------------------------------------------
对上面出现的问题进行解决需要做两件事情：
1、对UNDO_RETENTION参数需要设置的大点，设置为允许读数据进程全部完成所需要的最长时间；
2、在调大UNDO_RETENTION参数值同时，需要将undo_tablespace的大小也需要设置的大点；
   如果是自动管理UNDO表空间，则设置为自动增长；
   如果是手动管理UNDO表空间则分配更多的磁盘空间给UNDO表空间

对于上面的例子：
1）对于上面的查询需要的时间小于900s,执行时间大概为780s，通过
  SQL> show parameter UNDO_RETENTION
  NAME                                 TYPE        VALUE
  ----------------------------------- ----------- ------------------------------
  undo_retention                       integer     900
查询UNDO_RETENTION原来的值为900s，UNDO_RETENTION参数值是足够的，所以先不需要修改；
2）由于原来的UNDO表空间为2M，是不可以自动增长的，所以设置UNDO表空间为自动增长，以1M的速度增长，并且最大设置为2G；
SQL> select file_name
  2   from dba_data_files
  3   where tablespace_name = 'UNDO_SMALL';
FILE_NAME
--------------------------------------------------------------------------------
C:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/UNDO_SMALL .DBF

alter database
 datafile 'C:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/UNDO_SMALL .DBF'
 autoextend on
 next 1m
 maxsize 2048m; 
 做了上面的修改操作之后，下面来重新同时执行上面的更新语句和查询语句，就不会有ORA-01555错误产生；