ORA-01555的模拟以及绑定变量

本文章为网络笔记，看了warehouse老师的视频受益匪浅，更是感觉自己技术太过初级，特写了本笔记，方便以后反复学习！
如有任何不妥，请发邮件至[e-mail]删除文章！
关于warehouse：
http://blog.itpub.net/19602/viewspace-1059211/
11gR2视频第四版 4_02_ora-01555的模拟以及绑定变量
Oracle提供了一个视图用来监控online的undo segment

USN：undo segment number对应的就是dba_rollback_segs中的segment_id

开启一个事物

SQL> show user
USER is "A"
SQL> update t1 set name='abc' where id=1;

1 row updated.

确定这个session开启了事务

SQL> select distinct sid from v$mystat;

       SID
----------
	28

TADDR就是事务ID

用的是14号undo segment

XACTS：transaction active这个undo segment上transaction的数量

INACTIVE这个session中的sql执行完了，TADDR不为空是还没有提交，为空的时候说明这个session是空闲的

v$session中的prev_sql_id是执行过的sql id

最后执行的就是他

undo_management管理undo表空间的方式，auto指定一个undo_tablespace就不用管了，Oracle自动创建undo segment自动管理undo 表空间，参数还可以设置成手动，就要手动创建undo segment就是回到9.2版本以前
语法：create rollback segment rsg1;11g执行这个不报错但是也没有创建上

SQL> show parameter undo;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
undo_management 		     string	 AUTO
undo_retention			     integer	 900
undo_tablespace 		     string	 UNDOTBS2

undo_retention单位是秒，表示事务没有结束之前，undo中的数据永远不能被覆盖，提交之后还要尽可能的保留undo_retention默认是900秒，例子：一般报表查询都比较慢，几个小时一个报表任务执行然后另一个session修改了其中一个数据并提交了过段时间之后报表任务访问到了修改的数据，这个时候需要查询修改前的数据，这时候要是修改前的数据不在undo中了就报错了报ORA-01555（快照太旧），undo_retention不是强制的，undo不够用的时候覆盖commit过的undo
模拟ORA-01555

SQL> show parameter undo;
undo_tablespace 		     string	 UNDOTBS2

现在10M，不是自动扩展

要写一个死循环不停的修改数据，把undo中的数据覆盖掉
定义一个绑定变量

SQL> 
SQL> show user;
USER is "A"
SQL> variable i number;

这三条sql对于Oracle来说是三条不同的sql，需要解析三次这样就大大浪费了CPU时间绑定变量就是替换掉1、2、3，每次查询都传入不同的参数，这样第一次解析的结果就放在shared pool里的Library cache里，只解析一次提高性能
在plsql中给变量赋值要有:=变量前加:

SQL> exec :i :=1;

PL/SQL procedure successfully completed.

SQL> print i;

	 I
----------
	 1

SQL> select * from t1 where id=:i;

	ID NAME
---------- --------------------------------------------------
	 1 a

SQL> exec :i :=2;

PL/SQL procedure successfully completed.

SQL> select * from t1 where id=:i;

	ID NAME
---------- --------------------------------------------------
	 2 b

其他sql执行了一次而且每次都解析了，第二条执行了两次只解析了一次

定义一个游标类型的变量，游标变量能把查询到的结果集放到变量里

SQL> variable j refcursor

这样就模拟了一个长时间执行的报表任务

SQL> exec open :j for select * from t1;

PL/SQL procedure successfully completed.

开始执行了，结果

SQL> print j;

	ID NAME
---------- --------------------------------------------------
	 1 a
	 2 b
	 3 c

开始查询

SQL> exec open :j for select * from t1;

PL/SQL procedure successfully completed.

新开一个session修改数据

SQL> show user;
USER is "A"
SQL> declare      			定义一个变量
  2  m number :=0;  		数值类型，初始值0
  3  begin
  4  loop					循环
  5  update t1 set id=m;		修改数据，循环一次把m的值给他
  6  m:=m+1;				循环一次m+1
  7  if mod(m,100)=0 then	mod取余，能被100整除就往下走
  8  commit;				提交
  9  end if;
 10  end loop;
 11  end;
 12	 /						执行让他执行一会

回到之前的session

SQL> print j;
ERROR:
ORA-01555: snapshot too old: rollback segment number 11 with name
"_SYSSMU11_152612148$" too small

no rows selected

找到update的session kill掉，占用CPU资源最高就是这个操作系统进程10968

找到这个Oracle process

找到这个session

找到sql，就是这个session

kill掉update的session

SQL> alter system kill session '25,53';

System altered.

回到update的session，提示session已经被kill了

SQL> declare
  2  m number :=0;
  3  begin
  4  loop
  5  update t1 set id=m;
  6  m:=m+1;
  7  if mod(m,100)=0 then
  8  commit;
  9  end if;
 10  end loop;
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-00028: your session has been killed
ORA-06512: at line 5

以上就是undo_retention的意义