аЯрЁБс>ўџ /1ўџџџ.џџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџьЅС#` №П0вbjbj\.\. .$>D>DвџџџџџџЄ*******>ЂЂЂЂ$Ц >еЖоооооЙЙЙTVVVVVV$‹hѓDz*ЙЙЙЙЙz**оол­­­Й^*о*оT­ЙT­­8 Ь**8 ов РtYu0oЩЂ TЅ0е 7-v748 7*8 ЙЙ­ЙЙЙЙЙzzЃ ЙЙЙеЙЙЙЙ>>>dЂ>>>Ђ>>>******џџџџ Statistics – When and How? (June 2007) Imagine you own a supermarket. As time passes the number of distinct suppliers you have doesn’t change much; the number of distinct products changes slowly and steadily; the volume of sales changes constantly. What does this tell you about the statistics that Oracle needs to describe your data? There are some statistics about your data that can be left unchanged for a long time, possibly forever; there are some statistics that need to be changed periodically; and there are some statistics that need to be changed constantly. You may even need to massage some statistics so that they describe part of the data rather than all of the data - and there is no way that any supplied Oracle package can create such statistics. For example, many businesses hold data for seven years but the end-users are often only interested in what’s been happening in the last few weeks. The “average statistics” for seven years may give the optimizer a misleading image of the data the user wants to query. (Think of the odd sales patterns a supermarket has around Christmas and Thanksgiving.) For most people, the critical problem is to work out how to do the minimum amount of work to generate the best possible statistics; and it can take a lot of up-front effort to work out which statistics fall into which of the classes identified above. Fortunately it is possible to start simply and enhance your collection strategy over time. My guidelines are simple: you may as well compute statistics for small tables, but large tables usually need just a small sample size (can you check this on a backup); some indexes will need their clustering_factor adjusted; a few columns will need histograms; a few columns will need specially constructed programs to manufacture “business aware” statistics; and partitioned tables will, in general, need programs to construct their statistics (possibly only at the table level after a “normal” call to the dbms_stats package has created some new partition-level statistics). The biggest problem is that you need to understand the data. You may be able to take advantage of table monitoring to determine which tables are subject to significant change and v$col_usage to get an idea of how columns are used in predicates, but neither of these helps you understand which columns have data distributions that require special treatment, and which indexes need correction. Ultimately I believe you need a table-driven mechanism – and initially you could probably set up a simple system to emulate your current stats collection. For each non-partitioned table you need to record the frequency of collection, sample size, collection method, and whether or not to include indexes – in other words most of the parameters to the gather_table_stats() procedure. For tables where you don’t automatically gather index statistics, you need to include records for the relevant indexes with a similar list of parameter values. Finallyyou need entries showing how to handle any special columns – which may simply mean calls to create histograms. In all three cases, you may choose to reference a (home-grown) procedure that specifies a mechanism for creating “artificial” statistics. For partitioned tables, my approach is to design a custom stats collection package for each table as soon as I define the table. Your stats collection routine can start as a loop to scan the table and obey the instructions it finds there – and you can start simply with a driving table that emulates your current dbms_stats calls, enhancing the system as your knowledge grows. There’s a lot more to say – but with only 600 words, no room to say it – but this is the core of the optimum strategy. (Written for the NoCOUG magazine – Summer 2007 edition.) &'037DGTclv’˜ЂЋГМТЯеэјљџ  N O P ъ і 8 9 : ; = > E O Z j w  я є џ  6 S X Œ ‘ — Ћ ќјєј№ьшьфрьмиьрьридиьаьЬьШмШмФьмьрьРМИДАЌАмЌмЈЌЈЌЄ ЄфЄ ЄрЄ–hп…hО6]hXihОhЧS}hKРhƒ`hy={hš(–hк0XhЧ$”hмah­&hu•hНvџhy<ht#XhT44hР^hxgеh“d™hФIњhЋU=hЮ}&hеayh‘kv;'O P < = b c Й К ћќ…†()!"™вњѕ№№№№№№ыы№ѕ№ц№№№№№gd [tgdA}GgdЧS}gdxgеgdеayв§Ћ Ж е ћ  # , > O _ ` a b c  ‰ Э х ц ь = \ _ h ˆ  И Й К Ы в д ћ  ',?`b›ГЭ "ќјє№ь№шфр№ьм№ьидаиаЬШФЬмЬРМИДИАЬдЌмЌРЌРЌЈЄЌРЌЄ—ЄЌьЌьЌьhP~йhР^6]hР^hЁY56\]hЁYhC)Пh\Jh‘ hЧ$”hQуhH7hy={hќDžhxgеhqCПhCwh/sShт'"h˜yЄh•~сhx,XhЯdЎhР^h‘AйhпzXhЉUthО7"wxЖРйјљњћќ$/ЄЏКОЭяѕ  Asƒ„…†‘›щ№!"+;S‘•цњ @EQhlu­дшћќјќыќчјчќупчлчлзЪзЦзлзлзлзТчуОКЖКВКЎКВКЊВЊЊ™Њ•Њ™Њ™Њ‘ЦhчC hORТhœ?Lhн&…hœ?Lh\U’56\]h\U’h3Kыhє2љhZVГhйюhHnBhPЇhxgеhPЇhР^56\]hР^hЃ~hQъh/sShC)ПhК5h\J56\]hP~йh\J7ћZs„Єљ&'()GQ[uœЊЖисцыёќ !*0™бвќјє№ьєшфрмирдадЬдШдШФЗЊФІиФЂдžд”ŠhЭ<4hр\6]hЭ<4hЭ<46]hJfНh\U’h-!>h-!>hђƒ56\]h-!>h-!>56\]hђƒhsŠhXihJF…hр\hAHhDP[h’sNhќ?яhО5h [thxgеh7AhORТhKЈ!,1hАа/ Ар=!А"А# $ %ААФАФ Ф†œ@@ёџ@ NormalCJ_HaJmH sH tH R@R еay Heading 3$Є№Є<@&5CJOJQJ\aJDAђџЁD Default Paragraph FontRiѓџГR  Table Normalі4ж l4жaі (kєџС(No List2B@ђ2 ЧS} Body TextЄxв$џџџџ'OP<=bcЙКћќ… † ( ) !"™д(0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€˜0€€Ћ "ћв в в№8№@ёџџџ€€€ї№’№№0№( № №№B №S №ПЫџ ?№ЖРЏКц ј І А с ы ЊАдМТд3&DG66хцОЭ­ ­ д ш s Є ™бдд |џџџИƒьџџџџџџџџџ}џџџ4ьЪбџџџџџџџџџ~џџџ Э2cџџџџџџџџџџџџ28 џџџџџџџџџ€џџџЎт:уџџџџџџџџџџџџve`џџџџџџџџџ‚џџџtиYџџџџџџџџџƒџџџ‰жџџџџџџџџџˆџџџ> ˆЇџџџџџџџџџ‰џџџЊЈР;џџџџџџџџџ„д„˜ўЦд^„д`„˜ў.„Й„˜ўЦЙ^„Й`„˜ў.„ž„˜ўЦž^„ž`„˜ў.„ƒ„˜ўЦƒ^„ƒ`„˜ў. „д„˜ўЦд^„д`„˜ўOJQJo(З№ „Й„˜ўЦЙ^„Й`„˜ўOJQJo(З№ „ž„˜ўЦž^„ž`„˜ўOJQJo(З№ „ƒ„˜ўЦƒ^„ƒ`„˜ўOJQJo(З№„h„˜ўЦh^„h`„˜ў. „h„˜ўЦh^„h`„˜ўOJQJo(З№ ‰џџџƒџџџ‚џџџџџџ€џџџˆџџџџџџ~џџџ}џџџ|џџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџ џџ х€њVР^О5Ѓ~Cw­&р\H7zl|@!}чC т'"Ю}&D,l~/™33T44Э<4К5 9y<ЋU=-!>7AHnBA}GAH\Jœ?L’sNѕ.O/sSCTЁVECWt#Xx,Xк0XпzXЁYDP[мacbid­hБhXi„\iИ/jХNm %sЉUt [t‘kvOXyеayy={ЧS}ƒ`ђƒп…н&…JF…с9†ђ‡цˆsŠЬGŽО\U’N“Ч$”u•š(–?—*!˜v˜'*™“d™ќDž`hŸ) ‘ UnЂО Є˜yЄPЇKЈ;#ЎЯdЎ\АZVГкQЛё@НJfН†SОC)ПqCПKР;РЯ СORТ™mЧэ:ЬаЯЖеxgеТ!ж’4и‘AйP~й‚мч?нњ?п•~сQуQъ3Kы% юйюќ?яНPєrїє2љ•њФIњˆ1ўНvџџ@€OOА™™OOв@@џџUnknownџџџџџџџџџџџџG‡z €џTimes New Roman5€Symbol3& ‡z €џArial"qˆ№аhQЋДЦ+б&~f6œ 6œ !№ ДД24ЫЫ2ƒ№HX(№џ?фџџџџџџџџџџџџџџџџџџџџџеay2џџ)Collecting Statistics in Oracle (JL Comp)HOracle statistics dbms_stats performance tuning cost based optimizer cboJonathan LewisJonathan Lewis4         ўџр…ŸђљOhЋ‘+'Гй00˜Ьи№D”Ј РЬ ь ј  (ф,Collecting Statistics in Oracle (JL Comp)Jonathan LewisLOracle statistics dbms_stats performance tuning cost based optimizer cboHNotes on collecting statistics for Oracle's cost based optimizer (cbo). Normal.dotJonathan Lewis126Microsoft Office Word@ф2@nX„Ч@jY0oЩ6œ ўџеЭеœ.“—+,љЎ0$ hp˜ Ј АИРШ а фJL Computer ConsultancyЫЋ *Collecting Statistics in Oracle (JL Comp) Title ўџџџўџџџ !"#$%ўџџџ'()*+,-ўџџџ§џџџ0ўџџџўџџџўџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџRoot Entryџџџџџџџџ РF [eu0oЩ2€1TableџџџџџџџџkWordDocumentџџџџџџџџ.$SummaryInformation(џџџџDocumentSummaryInformation8џџџџџџџџџџџџ&CompObjџџџџџџџџџџџџqџџџџџџџџџџџџџџџџџџџџџџџџўџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџџўџ џџџџ РFMicrosoft Office Word Document MSWordDocWord.Document.8є9Вq