Karthik,
Since I don't see where you've received a reply to your question, I'll offer
a few suggestions:
- DW requirements are difficult to predict because it is by design an ad-hoc
environment. If you can predict your users' behavior, then in my opinion,
you don't truly have a DW.
- DW applications use memory very differently than OLTP applications. With
OLTP, the game is to get all the commonly used stuff into memory. In DW,
often there isn't any commonly used stuff. Queries are sporatic and may only
occur once. In OLTP, the game is to eliminate full-table scans. In DW, FTS
is often a fact of life and if you haven't licensed the Oracle Partitioning
option, you should start investigating it immediately. But DW queries may
perform massive sorts, so that is often where you'll end up allocating
significant memory to SORT_AREA_SIZE.
- Log buffer settings depend greatly on the refresh cycle. Some DW are
continually updated, so the log buffer can be modest. Some DW are refreshed
nightly so it is more of a batch process which needs to be carefully tuned.
- Learn what each of those components involve and figure out how you are
going to monitor them.
- Hopefully study your test environment carefully. That will give you clues.
- Determine what your "heavy hitters" are. What are the significant loads?
- Work with your system administrator to coordinate memory settings. Warn
your sys admin that this beast may consume more memory that any other system
you've experienced.
- Visit http://www.evdbt.com/papers.htm and read Tim Gorman's paper "Scaling
to Infinity", and browse his other papers and scripts. Anything by Tim is
great.
- If you are new to DW, and your questions imply you may be, realize that DW
administration requires a different mindset from OLTP. Visit Ralph Kimball's
site and read some of his books. Better to go in with an open mind than
insist the DW behave the way your other databases do and have a long period
of frustration.
- Bottom line, investigate, study, test, then take your best shot at it, try
to overshoot rather than undershoot, and plan to change the settings shortly
after startup, and frequently thereafter.
- If your organization has stringent change control requirements like you
can only change settings on Saturday midnight after a Wednesday change board
meeting, lobby hard to exempt the DW from these rules at least until it gets
going.
- Enjoy the adventure.
Dennis Williams
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Aug 25 2007 - 07:37:18 CDT
