Re: DSS System -- db_cache_size and pga_aggregate_target

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_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
> http://hemantoracledba.blogspot.com
>
>
> --
> http://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
>
>
>

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