| How often to ANALYZE tables in DW [message #50517] |
Tue, 19 March 2002 12:01  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Need some expert opinion please:
For Data Warehouse system
- Oracle 8.1.6.2.0 EE/AIX 4.3.3
- 35 GB prod database
- biggest table is 44 million rows with 5 partitions, biggest partition is 12 million rows and growing ..
- major data load is whole day Sunday, ready for Monday morning
- smaller scale incremental load through out the week (previous days work).
- Database in cost-based optimization (all_rows)
How often should the tables be analyzed ?
compute or estimate statistics ?
compute all or limited size (x % or y rows) ?
I am thinking once a week ?
(Analyzing 2 schemas take about 3-4 hours)
Thank you.
|
|
|
|
| Re: How often to ANALYZE tables in DW [message #50518 is a reply to message #50517] |
Tue, 19 March 2002 14:28  |
gwo
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
|
I don't think you need to do a full analyze. Estimate should be sufficient. Estimating takes much less time. Use the DBMS_STATS package to collect them instead. I find that it works better than the analyze table statement on partitioned tables.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
