RE: VLDBA's: gather stats on a large table

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Tue, 12 Apr 2005 08:43:53 -0400
Message-ID: <E518A02B320C8D4E97D99A8C546480210C3EAD77@dohsmailhq01.doh.ad.state.fl.us>

Connor - so you are saying come up with a baseline then actually set your stats to that for those tables and indexes then periodically check those stats? =20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Connor McDonald Sent: Tuesday, April 12, 2005 8:40 AM
To: 'oracle-l_at_freelists.org'
Subject: Re: VLDBA's: gather stats on a large table

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. =20

hth
connor

"Teehan, Mark" <mark.teehan_at_csfb.com> wrote: >=20
> I have a 7bn row, weekly partitioned IOT that adds hundreds of=20
> 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,=20
> and have no global indexes, how should I gather stats? As the app=20
> doesnt specify the partition in selects, I need to maintain global=20
> 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?

>=20

> Thanks!
> Mark Teehan

>=20
> =

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

> =3D=3D=3D=3D=3D=3D=3D=3D This message is for the sole use of the =
intended recipient.=20
> If you received this message in error please delete it and notify us.=20
> If this message was misdirected, CSFB does not waive any=20
> confidentiality or privilege. CSFB retains and monitors electronic=20
> 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.
> =

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

> =3D=3D=3D=3D=3D=3D=3D=3D

>=20

> --
> http://www.freelists.org/webpage/oracle-l
>=20

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"

=09

Do you Yahoo!?=20
Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/

--
http://www.freelists.org/webpage/oracle-l


BEGIN-ANTISPAM-VOTING-LINKS
------------------------------------------------------
Teach CanIt if this mail (ID 29381537) is spam:
Spam:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Ds&i=3D29381537&m=3D5587=
6defa
ab3
Not spam:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Dn&i=3D29381537&m=3D5587=
6defa
ab3
Forget vote:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Df&i=3D29381537&m=3D5587=
6defa
ab3
------------------------------------------------------
END-ANTISPAM-VOTING-LINKS

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 12 2005 - 08:47:55 CDT