Re: VLDBA's: gather stats on a large table
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Apr 2005 13:40:17 +0100 (BST)
Couple of ideas...
a) 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.
b) 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@xxxxxxxx> 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.
> ==============================================================================
>
> --
> //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@xxxxxxxxx
"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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
