Re: DSS System -- db_cache_size and pga_aggregate_target
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- 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".
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
Other related posts: