oracle事务隔离级别_笔记1：解释热表上超出期望的I/O

解释热表上超出期望的I/O

在另外一种情况下很有必要了解读一致性和多版本，这就是生产环境中在一个大负载条件下，一个查询使用的I/O比在测试或开发系统时观察到的I/O要多得多，而你无法解释这一现象。查看查询执行的I/O时，注意到它比开发系统中看到的I/O次数要多得多，多得简直不可想像。然后，你再在测试环境中恢复这个生产实例，却发现I/O又降下来了。但是到了生产环境中，它又变得非常高。可以看到，造成这种现象的原因是：在你的测试系统中，由于它是独立的，所以不必撤销事务修改。不过，在生产系统中，读一个给定的块时，可能必须撤销(回滚)多个事务所做的修改，而且每个回滚都可能涉及I/O来获取undo信息并应用于系统。

可能只是要查询一个表，但是这个表上发生了多个并发修改，因此你看到Oracle正在读undo段，从而将块恢复到查询开始时的样子。通过一个会话就能很容易地看到由此带来的结果，从而了解到底发生了什么。

sys@ORCL>conn scott/tiger

Connected.

scott@ORCL>create table t(x int);

Table created.

scott@ORCL>insert into t values(1);

1 row created.

scott@ORCL>exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

scott@ORCL>select * from t;

X

----------

1

 

下面，将会话设置为使用SERIALIZABLE隔离级别，这样无论在会话中运行多少次查询，都将得到事务开始时刻的查询结果：

scott@ORCL>alter session set isolation_level=serializable;

Session altered.

下面，查询这个小表，并观察执行的I/O次数：

scott@ORCL>set autotrace on statistics

scott@ORCL>select * from t;

X

----------

1

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

523 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

 

由此可见，完成这个查询用了6个I/O(一致获取，consistent get)。在另一个会话中，我们将反复修改这个表：

scott@ORCL>begin

2 for i in 1 .. 10000

3 loop

4 update t set x = x+1;

5 commit;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

再返回到前面的SERIALIZABLE会话，重新运行同样的查询：

scott@ORCL>select * from t;

X

----------

1

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

10001 consistent gets

0 physical reads

0 redo size

523 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

scott@ORCL>

 

这一次执行了10001次I/O，简直有天壤之别。那么，所有这些I/O是从哪里来的呢?这是因为Oracle回滚了对该数据库块的修改。在运行第二个查询时，Oracle知道查询获取和处理的所有块都必须针对事务开始的那个时刻。到达缓冲区缓存时，我们发现，缓存中的块“太新了”，另一个会话已经把这个块修改了10000次。查询无法看到这些修改，所以它开始查找undo信息，并撤销上一次所做的修改。它发现这个回滚块还是太新了，然后再对它做一次回滚。这个工作会反复进行，直至最后发现事务开始时的那个版本(即事务开始时数据库中的已提交块)。这才是我们可以使用的块，而且我们用的就是这个块。

通常就是这些小的“热表”会因为读一致性遭遇I/O膨胀问题。另外，如果查询需要针对易失表长时间运行，也经常受到这个问题的影响。运行的时间越长，“它们也就会运行得越久”，因为过一段时间，它们可能必须完成更多的工作才能从缓冲区缓存中获取一个块。

附一：以SYS用户登录后执行alter session set isolation_level=serializable;报ORA-08178错误解决办法：

OERR: ORA 8178 illegal SERIALIZABLE clause specified for user INTERNAL (文档 ID 48928.1)转到底部

修改时间:2012-7-26类型:REFERENCE

Error:ORA 8178

Text:illegal SERIALIZABLE clause specified for user INTERNAL

---------------------------------------------------------------------------

Cause:Serializable mode is not supported for user INTERNAL.

Action:Reconnect as another user and retry the SET TRANSACTION command.

附二：SCOTT用户set autotrace on statistics时报sp2-0618解决办法：

sys@ORCL>@/home/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin/utlxplan

sys@ORCL>CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;

CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

sys@ORCL>GRANT ALL ON PLAN_TABLE TO PUBLIC;

Grant succeeded.

sys@ORCL>GRANT ALL ON PLAN_TABLE TO SCOTT;

Grant succeeded.

[oracle@vcenteroracle ~]$ cd $ORACLE_HOME

[oracle@vcenteroracle db_1]$ cd sqlplus

[oracle@vcenteroracle sqlplus]$ cd admin

[oracle@vcenteroracle admin]$ ls -al

total 28

drwxr-xr-x 3 oracle oinstall 4096 Dec 14 2012 .

drwxr-xr-x 7 oracle oinstall 4096 Dec 14 2012 ..

-rw-r--r-- 1 oracle oinstall 368 Apr 10 2011 glogin.sql

drwxr-xr-x 2 oracle oinstall 4096 Dec 14 2012 help

-rw-r--r-- 1 oracle oinstall 226 Aug 5 2011 libsqlplus.def

-rw-r--r-- 1 oracle oinstall 813 Mar 7 2006 plustrce.sql

-rw-r--r-- 1 oracle oinstall 2118 Feb 16 2003 pupbld.sql

[oracle@vcenteroracle admin]$ exit

exit

sys@ORCL>@/home/oracle/app/oracle/product/11.2.0/db_1/sqlplus/admin/plustrce;

sys@ORCL>

sys@ORCL>drop role plustrace;

drop role plustrace

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

sys@ORCL>create role plustrace;

Role created.

sys@ORCL>

sys@ORCL>grant select on v_$sesstat to plustrace;

Grant succeeded.

sys@ORCL>grant select on v_$statname to plustrace;

Grant succeeded.

sys@ORCL>grant select on v_$mystat to plustrace;

Grant succeeded.

sys@ORCL>grant plustrace to dba with admin option;

Grant succeeded.

sys@ORCL>

sys@ORCL>set echo off

sys@ORCL>grant plustrace to public;

Grant succeeded.

sys@ORCL>grant plustrace to scott;

Grant succeeded.

sys@ORCL>

本文来源 我爱IT技术网 http://www.52ij.com/jishu/5224.html 转载请保留链接。