RE: VLDBA's: gather stats on a large table
- From: <Paula_Stankus@xxxxxxxxxxxxxxx>
- To: <hamcdc@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Apr 2005 08:43:53 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Connor McDonald
Sent: Tuesday, April 12, 2005 8:40 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: Re: VLDBA's: gather stats on a large table
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. =20
hth
connor
--- "Teehan, Mark" <mark.teehan@xxxxxxxx> 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
> --
> //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@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"
------------------------------------------------------------
=09
__________________________________
Do you Yahoo!?=20
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
