Comparison of execution time between Timesten and Oracle SQL
Just took a simple test.
Timesten 11g and Oracle 11.2.0.4 are installed on two physical machines with the same hardware environment, respectively.
Create a table in both databases
Create table test (a number,b varchar2 (20))
Commit
Then insert the data
Declare
Z number
X varchar2 (20): = 'abc'
Begin
For z in 1..1000000 loop
Insert into test values (ZBI x)
End loop
End
/
Then open the execution plan in 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 | 581K (7) | 00:00:07 |
Then update it and check the time.
SQL > update test set axi123 where breadth 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 |
Do the same on Timesten 11g.
Create table test1 (a number,b varchar2 (20))
Commit
Then insert the data
Declare
Z number
X varchar2 (20): = 'abc'
Begin
For z in 1..1000000 loop
Insert into test1 values (ZBI x)
End loop
End
/
Execute select and query execution time
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 1314 22 purl 53.263000
MAXEXECUTETIME:. 1
LASTEXECUTETIME:. 1
MINEXECUTETIME: 0
Only 0.1s is much faster than oracle's 7s.
Execute update and query time
SQLCMDID: 33556105440
PRIVATE_COMMAND_CONNECTION_ID: 2048
EXECUTIONS: 2
PREPARES: 2
REPREPARES: 0
FREEABLE: 1
SIZE: 2576
OWNER: Shawn
QUERYTEXT: update test1 set adept 123 where baggage abc'
FETCHCOUNT: 0
STARTTIME: 2014-11-18 13 purl 51 purl 18.14 1000
MAXEXECUTETIME: 14.044
LASTEXECUTETIME: 13.944
MINEXECUTETIME: 0
Here comes the problem!
The fastest Timesten update took 13.944 seconds, which is twice as slow as oracle?
I hope there is a great god who can help to answer ~ Thank you. Just got in touch with Timesten.
