This looks like one of those things where the speaker knows exactly what they mean, but the hearer interprets it differently. DSS/DW systems tend to do lots of large, brute-force queries, so lots of memory for PGA can be very helpful. DSS/DW systems tend to scan large tables (or hit large volumes of data very randomly) so you can't often set a big enough cache to get good caching of the fact tables. BUT - DSS/DW systems also have things like dimension or lookup table, and sometimes relatively small indexes, or possibly 'popular partitions'; so setting a cache large enough for the popular data can be very helpful. Even so, the cache may be still be (much) smaller than the size you set for the PGA - but the view point should be "make the cache as big as it needs to be and no bigger", not "the cache can be small because it's a DSS". Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html----- Original Message ----- From: "Hemant K Chitale" <hkchital@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx> Sent: Sunday, July 20, 2008 5:34 AM Subject: DSS System -- db_cache_size and pga_aggregate_target
I have been informed that advice to increase the db_cache_size makes sense for an OLTP system but not a DSS system. That for a DSS system, the db_cache_size should not be large but the pga_aggregate_target should be large.Are there any "best practices" , "test cases", "white papers", "benchmarks" which indicate such ?Hemant K Chitale http://hemantoracledba.blogspot.com -- //www.freelists.org/webpage/oracle-l No virus found in this incoming message.Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.2/1562 - Release Date: 19/07/2008 14:01
-- //www.freelists.org/webpage/oracle-l