Re: Performance Difference Based On Tablespace Used

(resending)

The db_block_checking and db_block_checksum are always enabled for SYSTEM tablespace blocks, thus the higher CPU requirements.  

The db_block_checksum is calculated and verified on physical reads and calculated & stored to block headers by DBWR (or direct load interface) on writes.  

The db_block_checking can be more expensive as it will do extra checking on logical block structure and not on physical reads/writes, but on changes to the block in buffer cache.  

So, you could repeat your test by either setting db_block_checking=true that the non-system tablespace would have block checking enabled (or set _db_always_check_system_ts=false if this is an experimental database you're working on - that way you can disable block checking for SYSTEM tablespace).

--
Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/> 


 



  _____  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Scott Canaan
Sent: Thursday, October 11, 2007 03:13
To: oracle-l_at_freelists.org
Subject: 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?


--
http://www.freelists.org/webpage/oracle-l