SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 5月 18 10:05:36 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
/*1.自己用户*/
SQL> conn scott/123456;
已连接。
SQL> create table t(f int);
表已创建。
SQL> insert into t(f)values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter session set isolation_level=serializable;
会话已更改。
---另一session执行多次更新--------------
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 UPDATE scott.t SET f=f+1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。
----------------------------------------
SQL> set autot on statistics;
SQL> select * from t;
F
----------
1
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1002 consistent gets
0 physical reads
0 redo size
296 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/*
2.用其他非dba用户看看
*/
SQL> conn byfei/123456
已连接。
SQL> alter session set isolation_level=serializable;
会话已更改。
SQL> set autot on statistics;
---另一session执行多次更新--------------
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 UPDATE scott.t SET f=f+1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。
----------------------------------------
SQL> select * from scott.t;
F
----------
1001
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1002 consistent gets
0 physical reads
0 redo size
297 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/*
3.用dba用户执行,发现有异样,consistent gets保持一样?!
*/
SQL> conn / as sysdba
已连接。
SQL> alter session set isolation_level=serializable;
会话已更改。
SQL> set autot on statistics;
---另一session执行多次更新--------------
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 UPDATE scott.t SET f=f+1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。
----------------------------------------
SQL> select * from scott.t;
F
----------
3001
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
297 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/*4.用sql_trace=true来跟踪,结果类似
*/
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 7 0 1
按道理dba用户应该和其他用户一样,consistent gets也是1002,是bug,还是“特权阶级”就给面子,少点?
我在9.2.0.8里测试得到的结果雷同!
[ 本帖最后由 microsoft_fly 于 2009-5-18 10:31 编辑 ]
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
/*1.自己用户*/
SQL> conn scott/123456;
已连接。
SQL> create table t(f int);
表已创建。
SQL> insert into t(f)values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter session set isolation_level=serializable;
会话已更改。
---另一session执行多次更新--------------
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 UPDATE scott.t SET f=f+1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。
----------------------------------------
SQL> set autot on statistics;
SQL> select * from t;
F
----------
1
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1002 consistent gets
0 physical reads
0 redo size
296 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/*
2.用其他非dba用户看看
*/
SQL> conn byfei/123456
已连接。
SQL> alter session set isolation_level=serializable;
会话已更改。
SQL> set autot on statistics;
---另一session执行多次更新--------------
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 UPDATE scott.t SET f=f+1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。
----------------------------------------
SQL> select * from scott.t;
F
----------
1001
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1002 consistent gets
0 physical reads
0 redo size
297 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/*
3.用dba用户执行,发现有异样,consistent gets保持一样?!
*/
SQL> conn / as sysdba
已连接。
SQL> alter session set isolation_level=serializable;
会话已更改。
SQL> set autot on statistics;
---另一session执行多次更新--------------
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 UPDATE scott.t SET f=f+1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。
----------------------------------------
SQL> select * from scott.t;
F
----------
3001
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
297 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
/*4.用sql_trace=true来跟踪,结果类似
*/
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 7 0 1
按道理dba用户应该和其他用户一样,consistent gets也是1002,是bug,还是“特权阶级”就给面子,少点?
我在9.2.0.8里测试得到的结果雷同!
[ 本帖最后由 microsoft_fly 于 2009-5-18 10:31 编辑 ]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/751051/viewspace-731741/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/751051/viewspace-731741/