Timesten与Oracle SQL执行时间对比
刚做了个简单的测试。
在两台硬件环境相同的实体机上分别装了Timesten 11g和Oracle 11.2.0.4
在两边数据库中都建立一个表
create table test(a number,b varchar2(20));
commit;
然后插入数据
declare
z number;
x varchar2(20):='abc';
begin
for z in 1..1000000 loop
insert into test values(z,x);
end loop;
end;
/
然后在oracle打开执行计划
set autot on;
SQL> select count(*) from test;
COUNT(*)
----------
1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 581 (7)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL | TEST| 8725K| 581 (7)| 00:00:07 |
然后update一下，查看时间。
SQL> update test set a=123 where b='abc';
1000000 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 839355234
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1128K| 26M| 552 (2)| 00:00:07 |
| 1 | UPDATE | TEST | | | | |
|* 2 | TABLE ACCESS FULL| TEST | 1128K| 26M| 552 (2)| 00:00:07 |
---------------------------------------------------------------------------
在Timesten 11g上执行同样操作。
create table test1(a number,b varchar2(20));
commit;
然后插入数据
declare
z number;
x varchar2(20):='abc';
begin
for z in 1..1000000 loop
insert into test1 values(z,x);
end loop;
end;
/
执行select并查询执行时间
SQLCMDID: 33556034320
PRIVATE_COMMAND_CONNECTION_ID: 2048
EXECUTIONS: 5
PREPARES: 5
REPREPARES: 0
FREEABLE: 1
SIZE: 3912
OWNER: Shawn
QUERYTEXT: select count(*) from test1
FETCHCOUNT: 5
STARTTIME: 2014-11-18 13:22:53.263000
MAXEXECUTETIME: .1
LASTEXECUTETIME: .1
MINEXECUTETIME: 0
只有0.1s比oracle的7s快很多。
执行update并查询时间
SQLCMDID: 33556105440
PRIVATE_COMMAND_CONNECTION_ID: 2048
EXECUTIONS: 2
PREPARES: 2
REPREPARES: 0
FREEABLE: 1
SIZE: 2576
OWNER: Shawn
QUERYTEXT: update test1 set a=123 where b='abc'
FETCHCOUNT: 0
STARTTIME: 2014-11-18 13:51:18.141000
MAXEXECUTETIME: 14.044
LASTEXECUTETIME: 13.944
MINEXECUTETIME: 0
这时候问题来了！
Timesten update最快居然用了13.944秒，比oracle要慢了快一倍？
希望有大神能帮忙解答下~谢谢。刚接触Timesten。
