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@xxxxxxx)

(585) 475-7886

"Life is like a sewer, what you get out of it depends on what you put
into it." - Tom Lehrer.

 

Other related posts: