Re: 11G Statistics Collection
From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Sat, 14 Apr 2012 05:32:10 -0700 (PDT)
Message-ID: <658c8118-94ff-4490-9f4d-f2ac99fcdec9_at_js1g2000pbc.googlegroups.com>
Yes, I agree, the whole thing is rather silly. Some application vendors provide customized script for Oracle statistics collection, but most don't and default seems to be to collect it daily in pre-10g versions. In 10g it is done automatically once total number of INSERT/UPDATE/DELETE exceeds 10% of rowcount. This makes it somewhat unpredictable. Besides in 10g statistics collection includes histograms and this leads to bind variable peeking.
Date: Sat, 14 Apr 2012 05:32:10 -0700 (PDT)
Message-ID: <658c8118-94ff-4490-9f4d-f2ac99fcdec9_at_js1g2000pbc.googlegroups.com>
Yes, I agree, the whole thing is rather silly. Some application vendors provide customized script for Oracle statistics collection, but most don't and default seems to be to collect it daily in pre-10g versions. In 10g it is done automatically once total number of INSERT/UPDATE/DELETE exceeds 10% of rowcount. This makes it somewhat unpredictable. Besides in 10g statistics collection includes histograms and this leads to bind variable peeking.
There is also some mystery with sample size: quite often plan could be fixed simply by collecting stats with 100% sample size. Why 5% isn't enough for table with 100,000,000 rows
I think for properly written application frequent statistics collection isn't required. However the issue is with queries that depen on min/max value for a particular column. Something like
SELECT * FROM TABLE WHERE CREATED_DATE > TRUNC(SYSDATE). Received on Sat Apr 14 2012 - 07:32:10 CDT