Re: AUTO_SAMPLE_SIZE is every row

From: Andy Sayer <andysayer_at_gmail.com>
Date: Wed, 17 Jan 2018 16:38:05 +0000
Message-ID: <CACj1VR4COvyk9XpgfGpQO2DWCmrwpTN8Asnma10PKJgGgTFZqA_at_mail.gmail.com>



Hi Matt,

I’m not sure about the method changing from patch to patch but it certainly changed in 11g. Have a read of
https://blogs.oracle.com/optimizer/how-does-autosamplesize-work-in-oracle-database-11g There’s a link too for the 12c version.

In summary: yes, it will be sampling every row for some things. No, that should not actually give you a problem - memory usage is still quite low and the full scan of the table itself shouldn’t really take that long.

Of course, if you are manually gathering statistics every day during business hours then you may be more effected than if you are just letting the automatic task take care of one or two stale tables each night.

The cascade option you are using will mean that indexes will have their statistics regathered each time too, this could easily add up.

If you are having trouble gathering statistics due to time constraints, the flavour of the month problem is incremental statistics.

Hope that helps,
Andrew

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 17 2018 - 17:38:05 CET

Original text of this message