Re: DSS System -- db_cache_size and pga_aggregate_target
Date: Sun, 20 Jul 2008 08:49:26 +0100
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".
Author: Cost Based Oracle: Fundamentals
The Co-operative Oracle Users' FAQ
- Original Message ----- From: "Hemant K Chitale" <hkchital_at_singnet.com.sg> To: <oracle-l_at_freelists.org> 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
> 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