Couple of ideas...
- all the stats gathering facilities do a *lot* of sorting. See if you can drop your 20hours by
tinkering with the various pga parameters and/or parallel.
- but mostly when it comes to bigger tables, I'm a fan of "when in doubt, cheat"...If *you* have
got a good idea of the stats, just use set_..._stats. If you don't have a decent starting point,
sacrifice (say) 60 hours on a weekend to get the best stats you can, and then use them as base for
subsequent "cheating". Every few months/years/etc, take some stats - see how they compare to your
cheats and adjust accordingly.
hth
connor
- "Teehan, Mark" <mark.teehan_at_csfb.com> wrote:
>
> I have a 7bn row, weekly partitioned IOT that adds hundreds of millions of rows each week. A
> five percent stats job estimate takes 20 hrs every weekend.
> Given that I only insert to the latest partition, all others are RO, and have no global indexes,
> how should I gather stats? As the app doesnt specify the partition in selects, I need to
> maintain global stats, which means I need to rescan all 7bn rows each time. Is there a better
> way to do this? How to you gather stats on data that is 90% read-only, but maintain accurate
> global stats?
>
> Thanks!
> Mark Teehan
>
> ==============================================================================
> This message is for the sole use of the intended recipient. If you received this message in
> error please delete it and notify us. If this message was misdirected, CSFB does not waive any
> confidentiality or privilege. CSFB retains and monitors electronic communications sent through
> its network. Instructions transmitted over this system are not binding on CSFB until they are
> confirmed by us. Message transmission is not guaranteed to be secure.
> ==============================================================================
>
> --
> http://www.freelists.org/webpage/oracle-l
>
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 12 2005 - 08:44:16 CDT
