Re: Should we stop analyzing?

Hi Mogens,

Ok, fun topic! Here is my take:

1 - Frequency of re-analyze

• It astonishes me how many shops prohibit any un-approved production changes and get re-analyze schema stats weekly, acting surprised when things change!
• I agree, most shops do not have to do this, and I agree with Dave; One very-deep sample (with histograms) is usually sufficient.
• The only exception that I have seen are highly-dynamic (e.g. lab research) systems where a table is huge one-day and small the next. The 10g "dynamic sampling" feature may address this issue!
• For my clients, I use the "monitoring" option and also method_opt=repeat, after I;'m confident that all histograms are in-place.

2 - Saving and re-using stats

• I like the 9ir2 features for export and import of statistics, especially the ability to collect the external cup_cost and io_cost figures.
• I have a client that got huge benefits from using two sets of stats, one for OLTP (daytime), and another for batch (evening jobs).
• I also export production stats into the development instances so that execution plan more closely resemble production.

3 - Getting top-quality stats

• I think that the CBO has gotten a bad reputation solely because the DBA does not give the CBO good statistics.
• In 9ir2, the CBO almost always makes a good decision when given good schema information.
• Because 9i stats work best with external system load, I like to schedule a valid sample (method_opt=auto_sample_size) during regular working hours.

4 - My pet peeves

• I see a lot of shops that do not use method_opt=skewonly and suffer from poor execution plans on skewed column access.
• Many DBAs forget that the CBO must have foreign-key histograms in order to determine the optimal table join order (i.e. the ORDERED hint).
• Whenever I see a sub-optimal order for table joins, I resist the temptation to add the ORDERED hint, and instead create histograms on the foreign keys of the join.
• I'm playing with the 10g automatic histogram collection mechanism that interrogates v$sql_plan to see where the foreign keys are and generate histograms when appropriate. Very cool!

BTW, what's the deal with Dave Ensor?
He told be that he was retiring from BMC to become a Barrister! Regards,

Donald K. Burleson
www.dba-oracle.com
www.remote-dba.net

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 30, 2003 5:34 AM

>
> Friends,
>
> I'd like to start a debate, which perhaps has already taken place, but
> if so I don't recall it: Should we stop analyzing tables and indexes?
>
> Let me clarify:
>
> I've always told people that using the 'monitoring' option (alter table
> X monitoring in 8i, plus alter index I monitoring in 9i) was a good
> thing, because they would make sure that after a certain amound of data
> changes you got fresh stats (after, of course, using
> dbms_stats.gather_stale_statistics, etc. on the collected objects). We
> can always discuss whether the 10% threshold that
> gather_stale_statistics is based on is sound or not, but it can be as
> good as any other number. Except 42 :).
>
> But then I listened to Dave Ensor at the UKOUG conference, and he said
> roughly this:
>
> * Stop analyzing after the first analyze. It's the new stats that cause
> the optimizer to change execution plans.
> * "I know that big tables tend to stay big. Small tables stay small.
> Unique indexes stay unique and non-unique indexes stay non-unique..."
> * If the data changes A LOT you should of course re-analyze.
>
> It made terrific sense in one respect to let the stats stay the same,
> thus letting the optimizer have access to the same information, thus
> choosing the same execution plan instead of changing it constantly. On
> the other hand it was irritating, because I had always beleived (and
> said) the opposite. Even more frustrating was Anjo's grin afterwards and
> his "Yeah, of course you shouldn't analyze all the time" remark. Hrmf.
> So everybody else knew but me. Typical.
>
> Looking back, I can recall several places where they analyzed every
> weekend, and on Monday the system could very well behave differently.
> Makes sense if the optimizer has some new/different information to
consider.
>
> On the other hand, it feels so intuitively right to constantly have
> up-to-date stats, doesn't it?
>
> I'd like to know what practical and philosofical ideas you guys have on
> this topic.
>
> Best regards - and Happy New Year,
>
> Mogens
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
> INET: mln_at_miracleas.dk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Don Burleson
  INET: don_at_burleson.cc

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).