| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Performance Difference Based On Tablespace Used
We've been doing some benchmarking on different servers and accidentally came across something that is puzzling. When the same SQL is run by the same user, but with the tables in the SYSTEM tablespace, it takes up to 66% more time to run than it does when the tables are created in another tablespace. Can anyone shed any light on why this would be the case? Here's a summary of what was done:
Summation:
-running the same SQL in the SYSTEM tablespace takes about
66% longer than an identically defined user tablespace.
-2 delete statements made up 2/3 of the added run time.
-the trace stats on these two deletes and the summary were
near identical except CPU & elapsed.
-other columns like counts, disk, query were
very close ... weird????
-Oracle 10.2.0.3 with the April, 2007 CPU
-Solaris 10
-db_cache is set to 128 MB, table sizes total about 85 MB
... operation should occur pretty much in cache.
-user tablespace was empty. If tables in the system
tablespace caused fragmentation a slow down, wouldn't the stats show
more disk I/O?
Runtime results over the past day:
(note 10:00 batch had trace turned on, appears to have added a minute or two of overhead. Answers that question, trace incurs overhead but looks to be <10%)
TEST user/SYSTEM tablespace
2007/10/09 12:00:03 2007/10/09 12:25:10 25:07
2007/10/09 14:00:02 2007/10/09 14:24:48 24:46
2007/10/09 18:00:02 2007/10/09 18:24:19 24:17
2007/10/10 06:00:02 2007/10/10 06:26:21 26:19
2007/10/10 10:00:01 2007/10/10 10:26:01 26:00
TEST user/USER_XAUTO_SMAN tablespace
2007/10/09 12:25:10 2007/10/09 12:40:36 15:26
2007/10/09 14:24:48 2007/10/09 14:40:37 15:49
2007/10/09 18:24:19 2007/10/09 18:40:09 15:50
2007/10/10 06:26:21 2007/10/10 06:42:13 15:52
2007/10/10 10:26:02 2007/10/10 10:43:17 17:15
SYS user/SYSTEM tablespace
2007/10/09 12:40:36 2007/10/09 13:04:45 24:09
2007/10/09 14:40:37 2007/10/09 15:05:27 24:50
2007/10/09 18:40:09 2007/10/09 19:04:23 24:14
2007/10/10 06:42:14 2007/10/10 07:06:57 24:43
2007/10/10 10:43:17 2007/10/10 11:09:57 26:40
SYS user/USER_XAUTO_SMAN tablespace
2007/10/09 13:04:45 2007/10/09 13:19:54 15:09
2007/10/09 15:05:27 2007/10/09 15:20:54 15:27
2007/10/09 19:04:23 2007/10/09 19:20:10 15:47
2007/10/10 07:06:57 2007/10/10 07:22:30 15:33
2007/10/10 11:09:58 2007/10/10 11:26:42 16:44
... with the exception of the datafile name & file size, the tablespace creates are identical ....
CREATE SMALLFILE TABLESPACE "SYSTEM" LOGGING DATAFILE '/u01/oradata/ITSATEST/sys01.dbf' SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL CREATE SMALLFILE TABLESPACE "USER_XAUTO_SMAN" LOGGING DATAFILE '/u01/oradata/ITSATEST/user_xauto_sman01.dbf' SIZE 100M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL Scott Canaan '88 (Scott.Canaan_at_rit.edu)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 10 2007 - 14:13:17 CDT
 |
 |