Re: Index Maintenance for DW environment
- From: Jeff Thomas <thomasjd@xxxxxxxxxxxxx>
- Date: Fri, 11 Aug 2006 16:44:38 -0400
Thanks for all the feedback.
1) Yes we are using nologging and parallelism
2) No to partitioning -- but I'm pushing for it. I might add this is
a Siebel OLAP/Analytics database
that I inherited after the fact. I've perused
OTN/Metalink/Siebel and found nearly zilch on
best practices/tuning for Siebel on Oracle.
3) The big issue is the bitmap indexes in reality -- we have over 750 of
them being dropped and
rebuilt each night.
4) As for stats, I got a baseline saved to a stats table, and I only
allow them to restore stats via
a stored procedure I wrote. I don't gather stats until they are
stale to the point of degrading
performance.
Thanks,
Jeff
Niall Litchfield wrote:
On 8/11/06, *thomasjd@xxxxxxxxxxxxx <mailto:thomasjd@xxxxxxxxxxxxx>*
<thomasjd@xxxxxxxxxxxxx <mailto:thomasjd@xxxxxxxxxxxxx>> wrote:
Our shop is new to large-scale datawarehouse projects. We are
finding our current index maintenance strategy for nightly jobs to
be unworkable, as it consists of dropping all indexes before the
nightly run, and building them anew after the run is completed,
via large SQL scripts.
I am not a DW DBA. However it sounds at least possible to me that
appropriate use of partitioning might be an option for you.
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
