ࡱ> UWTt#` 0Pbjbj\.\. 5>D>DH888LZZZ8,[@[L~H\^\^\^\^\]]]vvvv, wz`~$h&~8^]]^^~^\^\h~ggg^:8^\8^\vg^vggNp 8r^\<\ adZ3bxJr0r$~0~zr0f`r8r$]>^,g4^$X^]]]~~fj]]]~^^^^LLL%6$$LLL6LLL Statistics how often and how precise? (Sept 2004) Jonathan Lewis, JL Computer Consultancy Preamble The Cost Based Optimizer needs information about your data - but why, and how much? Some sites recompute statistics for all columns of every schema every night; some sites take a small estimate of the odd object from time to time; most sites will probably be somewhere in the middle of the range. Some people will just switch to AUTO with 10g. Is there any approach that will be just right for everybody? And if not, why not? The presentation explains some of the basics of optimizer statistics, and will help you decide how to minimize the resources spent gathering statistics. Optimal Thinking. The first (and only) rule of optimization is: "Avoid unnecessary effort". But you have to operate this rule at many levels. In the case of statistics, for example, you have three considerations: The optimizer should be able to use your statistics to find the most efficient execution plan for a statement. You should not use unnecessary machine resources generating statistics. You should not spend excessive amounts of time trying to work out a minimal strategy for generating statistics. You can cause problems (other than the simple waste of resources) by collecting too many statistics but as a general rule: if you don't see any performance problems that could be caused by inappropriate statistics, and if you don't have any problems hitting your SLAs because of the time spent gathering statistics, then point 3 applies: you can probably find better things to do with your time than refine a strategy that is already working. This paper is particularly for people who think their performance problems may be related to optimizer issues, or for people who are under pressure to find some more time in their batch windows. However, if you are interested in how the optimizer works, you might want to read this paper simply to enhance your understanding. What does the CBO use statistics for? In order to work out the optimum execution path, the optimizer needs to decide the best join order, the best join mechanism (e.g. hash, merge, nested loop), and the best data acquisition method (e.g. index, tablescan) for each table. Unless Oracle can work out a reasonably accurate estimate of the number of rows that will be acquired at each stage of the join it is likely to make some bad decisions. Consider, for example, the single-table query: select {list of columns} from t1 where n1 between 1000 and 2000 ; If Oracle's estimate of the number of rows to be acquired is very small then it is possible that an index on (n1) will be used. If Oracle's estimate is that a few hundred rows will be acquired then the index may be not used, even if the target rows are very tightly clustered. The estimated number of rows returned can affect the data acquisition mechanism. Consider a more complex case: select t1.*, t2.* from t1, t2 where t1.n1 between 10 and 20 and t2.id = t1.id and t2.n2 between 100 and 200 ; Oracle may decide that there are just a few relevant rows in t1, and that these rows can be acquired using an index on (n1), and that there are just a few rows in t2 which can be acquired using an index on (id) for each row in t1. In this case the optimizer could decide that the join order should be (t1 to t2), and the join mechanism should be a nested loop. But if the optimizer's estimate of the number of relevant rows in t1 is large, then it may decide that a nested loop would do too many indexed accesses into t2 to be efficient, and it might switch to a hash join. (Even then it might be a good idea to use an indexed access path into t2 using index on (n2) just once). So an error in the estimated number of rows to be acquired from t1 can result in a change in join mechanism. Similarly, if the optimizer decided that each value of t1.id would find a lot of rows in t2, then it might decide to start by selecting rows from t2 using an index on (n2) before joining to t1. So an error in the estimated number of rows can result in a change of join order. Technically, Oracle bases it's arithmetic on estimates of selectivity (fraction of available rows) and then derives the cardinality (number of rows to be acquired); however, the switch between cardinality and selectivity doesn't really make a lot of difference to the discussion, and it is sometimes easier to comprehend what is going on by looking at the cardinality. How do you get accuracy For the simpler cases (ignoring the requirement for histograms, the problems of column dependencies, and some 'sanity checks' that have appeared in recent versions of Oracle), there are basically eight critical numbers that Oracle wants: Number of used blocks in each table ** Number of rows in each table Number of distinct values for each column Number of nulls in each column Average length of each column Number of branch levels of index ** Number of leaf-blocks in index Clustering factor of index The figures marked ** can be found very cheaply, but Oracle may have to do a lot of work to 'learn' the other numbers. For the table-related figures, Oracle could read a random selection of rows (and check the figure for rows per block (nrow in the block dump)for each block it had to visit) and assume that the rows are a true representation of the table. The arithmetic would not be very difficult. For the index-related figures, Oracle has to check a selection of blocks from the index to be able to generate a clustering factor. It is interesting to note that one of the most extreme differences between using the newer dbms_stats call and the old analyze call for collecting statistics on indexes is that they count index leaf blocks differently dbms_stats considers only leaf blocks which are currently in use, analyze includes the leaf blocks that are on the index's free list. Given that the mechanisms are relatively straightforward, the important questions are: How much error can you allow in any particular statistic before the optimizer does the wrong thing ? How large a sample is needed to keep the error within that limit ? How long can you leave the statistics before the statistics come to be outside the limit ? The general answer is: you can get away with being very lazy in most cases. However, you do need to be careful some of the time. Moreover, there are cases where Oracle is simply unable to produce a meaningful statistic however much work you make it do. To demonstrate the principles, consider the following worked example. Test Case I am going to generate four sets of numeric data in a table of 1,000,000 rows. Three of the data sets will each have 50,000 distinct values and 20 rows for each value, but the distribution of the values across the rows will be different in each set. One data set will cluster the values very tightly, one data set will scattered them very evenly through the table, and the third data set will use the dbms_random package to scatter the values randomly in fact, this last data set will probably not have exactly 20 rows for each value. The final data set will be generated from the "normal distribution" function that is available in the package dbms_random. As a result, you will not see the same number of rows for each value generated. The SQL uses subquery factoring (an Oracle 9i innovation) to produce a large result set easily create table t1 as with milli_row as ( select /*+ materialize */ rownum from all_objects where rownum <= 1000 ) select mod(rownum-1, 50000) scattered, trunc((rownum-1)/20) clustered, trunc(dbms_random.value(0,50000)) uniform, trunc(7000 * dbms_random.normal) normal from milli_row m1, milli_row m2 where rownum <= 1000000 ; We can now use dbms_stats.gather_table_stats() against this table at different sample sizes, to see how good Oracle is at estimating the number of distinct values there are for each column. The results from one test run on a 9.2.0.4 system were as follows (because of Oracle's randomized sampling, these results will not be exactly reproducible): Sample PercentScatteredClusteredUniformNormal10050,00050,00050,000421868050,00150,00150,00141,1066050,00150,00150,00139,7024049,99949,99949,99437,5412050,00349,95748,94229.7261049,92949,92448,94229,726849,90949,89848,78428,818649,92949,39548,48627,737449,49249,67348,15326,574250,54049,59446,62225,643150,93948,79449,67625.062This highlights two important points. First that there are columns where even a 1% sample size does not introduce much error; second that there are columns where the sample size has a huge impact on the statistical results. Let's deal with the 'nice' data first columns where the distribution of values is even, boring and dull. In the worst possible case, Oracle has produced the value 46,662 as the estimate of the number of distinct values. This means that if you supplied the predicate "column = constant", the optimizer would estimate the number of relevant rows to be 1,000,000 / 46,622; which is 21.449: which Oracle would round to 21. It's probably close enough in most cases to leave the optimizer doing the right thing with a large table. But what about the last column of results, the 'normal' column depending on the sample size, the optimizer will predict anything between 24 and 40 rows on a simple predicate like 'column = constant'. It's quite possible that a variation of nearly 100% would cause the optimizer to pick a sub-optimal execution path. Why has this happened. Let's look at the data more closely: Select count(*) from t1 where normal = -15000 -- 6 rows Select count(*) from t1 where normal = -5000 -- 47 rows Select count(*) from t1 where normal = -200 -- 59 rows Select count(*) from t1 where normal = 0 -- 114 rows Select count(*) from t1 where normal = 200 -- 62 rows Select count(*) from t1 where normal = 5000 -- 41 rows Select count(*) from t1 where normal = 15000 -- 8 rows The normal distribution is the famous 'bell curve' of the statistician lots of values in the middle, tapering away to either side. How is Oracle supposed to provide one 'magic number' that describes the data. If our business is always asking about values in the range 10,000 20,000 then we want Oracle to believe that we will only get half a dozen rows from our queries. If our business is always asking about values in the range 10 to + 10, we want Oracle to believe that we will always get about 100 rows from our queries. There is no solution to this problem except, perhaps, to invent some statistics that manage to describe the data to Oracle in the way that we see it. Histograms Of course, for cases like this, Oracle does give us some help with histograms. So let's take a look at what a histogram is. To start, let's draw a graph of our 'normal' data. Obviously, we could count how many rows there are for each value, and then plot a point (value, number of rows) on a simple line graph. However, there are a million rows in the table, so this would be quite a lot of effort. So let's approximate by sorting the rows and batching them. We have two obvious options Option 1 we could draw a bar chart by saying: "How many rows have a value between 35,000 and 30,000, draw a bar that height. How many rows have a value between 30,000 and -25,000, draw a bar that height". Option 2 we could say: "break the sorted list into equal size chunks (say 100,000 rows each), what are the lowest and highest value in the first chunk, draw a bar across that range. The average number of rows for any value in that range is the number of rows in the chunk divided by the number of different possible values in the range, i.e. 100,000 / (highest lowest). The nice thing about option 2 is that we don't have to know in advance the highest and lowest values in the table before we decide on the widths of the bars we need to draw so lets use that method, and write a little SQL to work the results out for us. Analytic functions are excellent for this type of work: select tenth, min(normal), max(normal), round(100000 / (max(normal) - min(normal)),2) height from ( select normal, ntile(10) over (order by normal) tenth from t1 ) group by tenth order by tenth ; The inline view sorts the data into order, and breaks it into tenths, giving each row an extra column that identifies which 'tenth' of the data it belongs to. The outer query then gets the low/high for each 'tenth' and works out a height for the bar by dividing this range into the number of rows in this tenth (in this case hard-coded at 100,000). The resulting graph is shown below.  EMBED PowerPoint.Slide.7  BUT an interesting thing effect appears when you use the dbms_stats package to create a histogram for this data - you find that Oracle runs SQL like the following. select min(minbkt), maxbkt, substrb(dump(min(val),16,0,32),1,120) minval, substrb(dump(max(val),16,0,32),1,120) maxval, sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from ( select val, min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from ( select /*+ cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ "NORMAL" val, ntile(10) over (order by "NORMAL") bkt from "TEST_USER"."T1" t where "NORMAL" is not null ) group by val ) group by maxbkt order by maxbkt ; Look at the innermost of the inline views apart from the hints, it is the same call to an analytic fuction that I used to generate the numbers for my graphical representation of the data. Oracle is simply drawing an approximate picture of the data by calculating the same bar widths and heights that I did with option 2. (Oracle refers to this as a 'height-balanced' histogram; don't be put off by the expression "height-balanced" it is inappropriate and misleading this is just a ordinary histogram, the area of all the bars is the same, the widths vary, and the height of each bar represents the average number of occurrences of a value in the range.). So Oracle can draw pictures of your data, and can use these pictures with some degree of sophistication in calculating the number of rows that would be returned by a predicate (in particular a range-based predicate). Oracle also uses some of the information it gathers in the SQL above to produce a 'representative' value for the data density (which is otherwise 1/(number of distinct values in the column). The full power of the histogram is available only when you use literal values in your code (or when Oracle peeks at the bind variables to find the values), but the modified density will be used in various cases, even when your code employs bind variables. So how accurate do your histograms have to be: it depends on the oddity of your data, and what you want to do with it. As an example, here are the results from computing the histogram on my 'normal' column, and then sampling at five percent. End-pointComputeSample 5%0-32,003-28,9821-8,.954-8,9032-5,872-58423-3,647-3,6314-1,748-1,7235224361,7861,79373,6813,73785,9055,96198,9939,0041034,66028,338 Read carefully down this table, and you will notice an important detail. The only place where the two sets of data (hence two pictures) differ is on the two extreme values. Think about the bell curve the 5% sample has only gone wrong at the extreme edges where the raw data is most thinly scattered in my test case, there were literally a couple of dozen rows that fell between the values 28,338 and 34,660, you could even argue that ignoring (or losing) these rows actually gives Oracle a better picture of the data. So, a histogram is just a graphical representation of your data distribution and again a small sample size may be totally sufficient to give the optimizer a realistic and useful image of what your data looks like. Wrapping it all Up The precision you need in your statistics is dictated by the way your business uses the data. The columns you use in join predicates and filter predicates need reasonably accurate statistics so that Oracle can estimate the amount of work needed to acquire the data. For small tables (up to a few thousand rows), it does not cost much to have Oracle compute basic statistics, and if the real statistics for critical columns in the table change regularly then it is worth recomputing them regularly. For large tables, a fairly small sample (1 percent or lower) will usually provide perfectly reasonable statistics for most columns, and the rate of data change as a percentage of the data may well be so small that the statistics do not need to be updated with extreme frequency. Even with small rates of change, though, there may be some columns (typically sequence based) where the nature of the change may require you to take special action on a regular basis. There will be columns where the nature of the data requires you to generate histograms to allow Oracle to estimate row counts effectively. In special cases, remember that it is perfectly reasonable to make controlled use of literal values in SQL statements to allow Oracle to use the histograms to their best effect. Note, however, there are always cases where the business use of the data is something that Oracle cannot infer simply by looking at the numbers. In these cases, you may be able to set explicit values (typically for the column "density" or "num_distinct") that make Oracle produce the right responses to the important business queries. Finally, remember that there are special case columns where you understand the data, but Oracle can only get the right answers if you do an expensive statistics collection on a regular basis. It is possible to use your 'insider' information to construct appropriate statistics and write them into the data dictionary (using procedures like dbms_stats.set_column_stats) much more cheaply than Oracle can 'learn' them by wading through the data. About the Author Jonathan Lewis has been an independent IT specialist for the last 20 years, of which the last 17 years have been spent using and investigating the Oracle database engine. He is the author of one book on Oracle, has contributed to a couple of others, and is busy writing a book that explains the working of Oracle's Cost Based Optimizer. Sept 2004     DBA Paper # '3\7BuallpRY pz.#9###''&/:/o1v1999999UCrC1J7JJJJKNNPPPPPPPPPPPƾhkOJQJh>OJQJj hkU,jPEF hkCJOJQJUVmHnHujhkU hk5 hk6hkhmH sH hihkA4\e  ~ 5 `_>\drPPP>nu Dc~MP !!!#$$$$%$%/%B%Y%[%c%%%%&&&$&*&>&@&''''''$If'''$Ifkd$$Iflr xP#I  t%204 lap2'''''''''(PkdQ$$Iflr xP#I t%04 la$If ( (((((&(-(Pkd+$$Iflr xP#I t%04 la$If-(.(1(8(?(F(M(VPPPPP$Ifkd$$Iflr xP#I t%04 laM(N(Q(X(_(f(m(VPPPPP$Ifkd$$Iflr xP#I t%04 lam(n(q(x((((VPPPPP$Ifkd$$Iflr xP#I t%04 la(((((((VPPPPP$Ifkd$$Iflr xP#I t%04 la(((((((VPPPPP$Ifkdm$$Iflr xP#I t%04 la(((((((VPPPPP$IfkdG$$Iflr xP#I t%04 la((((() )VPPPPP$Ifkd!$$Iflr xP#I t%04 la ) ) )))!)()VPPPPP$Ifkd$$Iflr xP#I t%04 la())) *,n---.@.x.VTTTTTTTTkd $$Iflr xP#I t%04 la x..."/11S2346>7F7O7_7n77777777 88899b:j:y:y:::::;#;7;h;m;q;};;;;;;;; <%<><Y<n<w<<<<<====&=6=8=?cBUC_CgCqC$If qCrCtC|CCWQQQ$Ifkd$$IflF 7x  t%0    4 lapCCCCC|vvv$Ifkd$$IflF 7x t%0    4 laCCCCC|vvv$Ifkd^$$IflF 7x t%0    4 laCCCCC|vvv$Ifkd $$IflF 7x t%0    4 laCCCCC|vvv$Ifkd$$IflF 7x t%0    4 laCCCCC|vvv$Ifkdh$$IflF 7x t%0    4 laCCCCC|vvv$Ifkd$$IflF 7x t%0    4 laCCCCC|vvv$Ifkd$$IflF 7x t%0    4 laCCCCC|vvv$Ifkdr$$IflF 7x t%0    4 laCCDD D|vvv$Ifkd $$IflF 7x t%0    4 la DDDDD|vvv$Ifkd$$IflF 7x t%0    4 laD D!D+FGGHIJeM!O2OPP|zzzxzzzzzxzsgd>kd|$$IflF 7x t%0    4 la PPPPPPPPPPPPPPPPPgd>$a$$a$PPPPhkOJQJhkhkmH sH 3 0&PP/ =!"#$% O$$If!vh5#5555I#v##v#v#v#vI:V l  t%205#5555I4p2$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4$$If!vh5#5555I#v##v#v#v#vI:V l t%05#5555I4 Dd  B  S A? 2r fV#0NN  T`!F fV#0N1`2ОE-4 x͛ahsvG=ҳ\2;L mb%]q/Bl /D|qdqy!RXE_%c2L{DVmm)s|>MMXOքHx&$߇ O5G+7S-!!xTqHffL:dBo#O{?YnX!nJ{by6fu{澵jx:|IQ%,RgSPW^>/Uwo_{z׺v4uIG3wf{ڙ=Ÿ/DW-ۙ|jܕqoڙ;#l~vA'Q4YkX_܇7?Ak١,;Kvه}xË[W.<)¿r=5ωע_{-rqL8P_Fg¤f3bUh,y:oL2zo;F,|nk;_Dwov7Ӎ{5O;/jO¦<[н_w;\.>s4O,J*q^[շ]+Ľ=T{Pغ]չv~ΖhwoȍfD['uWVlN^ۦwއg7nmZ<G}zVƅƌ8X>QVIgaksMԱu8$ 6C&D%n"͑慛v7QH7QI(aKCM cIGŎ 71'n-N 7QVHg  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKMNOPQRSVY[\]^_`abcdefghijklmnopqrsvwxyz{|}~Root Entry F$BadXData L*WordDocument5ObjectPoolad$Bad_1178947836q{;QadadOle Header9CompObju Microsoft (R) PowerPoint (R) Windows _ q{;QMicrosoft PowerPoint SlideMSPresentationPowerPoint.Slide.79q23,000 25,600 18,000 -32000 34600 -9000 9000 3.9 56.3 53.0 45.1 32.5 Column Value Number of rows PowerPoint DocumentObjInfo Text_ContentoSummaryInformation( Z2PowerPoint Document(uXOh+'02 `h  No Slide Title  2Microsoft PowerPoint@B@Y3qd@dG11% &`2 &&TNPPb=P & TNPP &&TNPP  ` j: --  :`Times New Roman؟ww wfU-. Times New Roman؟ww wfU-.  & & --"SystemUfU !d-@Ze-cT--'-  $njnP  $JNJhTimes New Roman؟ww wfU-.  T2 X23,000((((( & ---@BM-K<--'-  $VRV8  $262PTimes New Roman؟ww wfU-.  <2 @25,600((((( ---@)~-|#--'-  $h  $8Times New Roman؟ww wfU-.  #2 p18,000((((( & &-̙- ̙iQP8 ̙ih ̙i9 ̙iA0 ̙i0` ̙i@ ̙ia ̙i ̙i ̙iiPP ---0@-*--'Times New Roman؟ww wfU-.  *2  -32000(((((Times New Roman؟ww wfU-.  (2 34600(((((Times New Roman؟ww wfU-.  (2 -9000((((Times New Roman؟ww wfU-.  ( 2 9000((((---@0x-hvh*--'Times New Roman؟ww wfU-.  h* 2 83.90((Times New Roman؟ww wfU-.  ( 2 56.3(((---@-  $  $  $  $  $  $""  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~  $  $  $  $  $  $  $  $  $  $""  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~  $  $  $  $  $  $  $  $  $  $""  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~ --'--@0p-  $nNnRrRrN  $~N~RRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NR"R"N  $.N.R2R2N  $>N>RBRBN  $NNNRRRRN  $^N^RbRbN  $nNnRrRrN  $~N~RRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NR"R"N  $.N.R2R2N  $>N>RBRBN  $NNNRRRRN  $^N^RbRbN  $nNnRrRrN  $~N~RRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NRRN  $NR"R"N --'Times New Roman؟ww wfU-.  ( 2 H53.0(((---@ 0-  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~  $  $  $  $  $  $  $  $  $  $""  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~  $  $  $  $  $  $  $  $  $  $""  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~  $  $  $  $  $  $  $  $  $ --'Times New Roman؟ww wfU-.  ( 2 045.1(((---@x-  $v.v2z2z.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.2 2 .  $.22.  $&.&2*2*.  $6.62:2:.  $F.F2J2J.  $V.V2Z2Z.  $f.f2j2j.  $v.v2z2z.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.2 2 .  $.22.  $&.&2*2*.  $6.62:2:.  $F.F2J2J.  $V.V2Z2Z.  $f.f2j2j.  $v.v2z2z.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.22.  $.2 2 .  $.22.  $&.&2*2*.  $6.62:2:.  $F.F2J2J.  $V.V2Z2Z.  $f.f2j2j.  $v.v2z2z.  $.22.  $.22.  $.22. --'Times New Roman؟ww wfU-.  ( 2 832.5(((---@ -  $""  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~  $  $  $  $  $  $  $  $  $  $""  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~  $  $  $  $  $  $  $  $  $  $""  $..22  $>>BB  $NNRR  $^^bb  $nnrr  $~~  $  $  $  $  $  $  $  $ --' &Times New Roman؟ww wfU-.  (2 X Column Value6((>(:#(#Times New Roman؟ww wfU-.  T(2 ]Number of rows:(>($((9 &TNPP &---:XX,L3Pbf4(xd3P T 4 @`T 4  @@``T 4hh88T 4@`0T 4  @@``@L <Times New RomanL <"Arial$)B* x,,Rw,Rw,P,Pdddd7dd5ddddx RwRwPPdddd7dd5ddddx  Rw Rw P Pdddd7dd5ddddxRwRwPPdddd7dd5ddddxRwRwPPdddd7dd5ddddxRwRwPP 75xRwRwPP75  xRwRwPP 75xRwRwPP75   (] 800t:nPD$42dd35//0011- c-@ p H./04x$̙33  (@ p^ 8^00 +  [;P $*  [;P z   8P^` * (v6P 8^P`:nPj$ph523,000</,/w_X0H04w_dd(11w_ z p  8Pm^` * (m^Fr6P 8^`:nPj$p8525,600</,/|_X0H04|_dd(11|_ z p  8m^` * (m^6P 8^`:nPj$p518,000</,/D_X0H04D_dd(11D_ ) [;P  (p_ 8^P`  (P6P 8^P`  (Pp6P 8^P` :nP  (`6P 8^P`  (`6P 8^P`  (6P 8^P`  (06P 8^P`  (p06P 8^P`  (p6P 8^P`  (6P 8^P` z P  8P` * (C?=6P 8^P`:nPj$Q@` 5-32000</,/l_X0H04l_dd(11l_ ) (vCz =6P 8^P`:nPi$Q`@ 534600</,/_X0H04_dd(11_ ) (C=6P 8^P`:nPi$Q`U 5-9000</,/ė_X0H04ė_dd(11ė_ ( (vC*=6P 8^P`:nPh$Q` 59000</,/p_X0H04p_dd(11p_ z P   8P` ? (2 6P 8^P`:nP$Qp0  0 @53.9</,/_X0H04_dd(11_ @ (66P 8^P`:nP$Qppp@556.3</,/8_X0H048_dd(118_ z pp  8P` z P   8P` @ (VSM6P 8^P`:nP$Qp0553.0</,/Ч_X0H04Ч_dd(11Ч_ z `0  8P` ( (v 6P 8^P`:nPh$`545.1</,/_X0H04_dd(11_ z `P`  8P` @ (6 6P 8^P`:nP$Pp p 532.5</,/_X0H04_dd(11_ z @PP  8P` 0 (m^_ 83P6P`:nPp$  5Column Value</,/ 0_X0H0 40_dd(11 0_ 2 (m^2R,_ 83P6P:nPr$O5Number of rows</,/_X0H04_dd(11_ @ p$7̙33$73$73333f$7999MMM$7f$7f3$73 H./04x$̙33  (@ p] 8^00  \  (3Pp ^ 8^00:nP$-V , 5Click to edit Master title style</,/ $],X0H0 4$]dd(11 $] 2  (3Ppp ^ 8^00:nPZ$V sR5Click to edit Master text styles Second level Third level Fourth level Fifth level//!]  ] ]]x0h0!4]dd 4]dd 4]dd 4]dd 4]dd(11R] =  (3Pp ^ 8^00:nPe$  5*</,/]X0H04]dd(11] =  (3Pp^ 8^00:nPe$ V 5*</,/@]X0H04@]dd(11@] =  (3P ^ 8^00:nPe$ V  5*</,/]X0H04]dd(11] D _VBA_PROJECT047d7d8d8d` H \]$de,DocumentSummaryInformation8 PersistentStorage Directory8>1TableqSummaryInformation(l՜.+,0d `h  On-screen ShowX ArialTimes New RomanDefault DesignNo Slide Title  Fonts UsedDesign Template Slide TitlesJ:+l?59a'݈l?6a͒vl4/l?ݤ=[ ~a- ltH~`IGvtL~`I'L:%l?a ltN~`I7lM~`# l7iHt@~`["K'uޙ/?߈&BT=(DnD:$0vtDQ(pq &2pltF*J:+D [#nƩ7fHۄbM9Ҽpn& ip~&"頰HvtD~`GIDŽvtB~`ˤS[!*鬰Ht#`͐ lC~`sya&iltP~`KC;L:"l?c;N:!l?e)altV~`ks[Oђ`͐ lC,x޹wG!"wGr_Q=Mv=rs1u;87QH7QI(aKCM cIGŎ 71'n-N 7QVHgbM԰59&:iґ8rlM,6K!D# l7iHt@~`["aa%q a-N ltF~`[#zҍ8!m,iHM#l?>a'"頰HvtD~`GIDŽvtB~`ˤS[!*鬰Ht#`͐9}is=G9}G8Ip9_IO7q{pI 7q.;B M\^"@ˤ 7q {S&cn5A>nb5v5Z&6`7nn EM؝؃CW8=BzL~`ؓ4Ya=GzA~`/~ l?I?k u#1դkvFa"&l?;Iw ҽ!=&l?IOIS{# l?H?ˤ 5O: a)!l?I ҍEM~`w={v?Aa=BzL~`ؓ4Yta=τ-yM=W~Olt){o͑慛v7QH7QI(aKCM cIGŎ 71'n-N 7QVHgbM԰59&:i' 6C&D%i^~`I{@'l?[$鐰(阰NltJ~`+3[%霰֓nl?6a͒vl4/l?ݤ=[ ~a- ltH~`IGvtL~`I'L:%l?a ltN~`I7 K'u.d}:$'Fz~~[8]WCNma}/9qo;jYю~Fm|G l?}7-ٰVBXǯϵm K־sX|g秖B?Y$$If!vh5755x#v7#v#vx:V l  t%05755x4p$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4$$If!vh5755x#v7#v#vx:V l t%05755x4Oh+'0<4    $,4(Collecting Oracle Statistics (JLComp)Jonathan Lewis@Oracle dbms_stats statistics CBO performance tuning histogramdExamples of Oracle's statistics collections and the requirements of the CBO (Cost Based Optimizer) Normal.dotJonathan Lewis12Microsoft Office Word@ա@q@Wd =՜.+,D՜.+,P  hp|   %jH &Collecting Oracle Statistics (JLComp) TitletH,DocumentSummaryInformation8CompObjqH`h_AdHocReviewCycleID_EmailSubject _AuthorEmail_AuthorEmailDisplayName_PreviousAdHocReviewCycleID_ReviewingToolsShownOncelAnother Template AdjustmentJulie_Ferry@sba.comFerry, Julie2)  FMicrosoft Office Word Document MSWordDocWord.Document.89q7P`P Normal (( CJOJQJ_HaJmH sH tH DA@D Default Paragraph FontVi@V  Table Normal :V 44 la (k@(No List H`H Header  !CJOJQJ^JaJNoN Computer Code CJOJQJaJ.O. Body Copy>o1> Subhead 2@& >*CJaJ^o^ Subhead 1$$H$@&5:>*B*CJ\aJph>o!> Subhead 3@& 56\]@o@ Author $Xa$6CJ]aJ^oR^ Paper Title$$@a$59:B*CJ$\aJ$ph4Or4 Bullet  & F$LoL Figure tag$a$6CJ]aJH `H Footer  !CJOJQJ^JaJ@Oq@ Numbered Lists  & F.)@. Page Number<Oq< Bullet2 h^<Oq< Bullet3 h88^8ROR Numbered Lists2 & F ^TOT Numbered Lists3 & F h8^8Lo!L Subhead 4 H$@&6:B*]ph>O> Style1!$a$CJOJQJ^J2B@"2 Body Text"x>O2> Style2#$a$CJOJQJ^JLU`AL Hyperlink"5>*B*CJOJQJ\aJphFo!RF Subhead4 %H$6:B*]phFV@aF FollowedHyperlink >*B* phvorv _Bullets<' & F 8p @  <^CJOJQJaJLoL _Byline (6CJOJQJ]^JaJvov _Code8) # h8p @   ^ 5CJOJQJ\^JaJdod _Text0*x # h8p @  CJOJQJaJRoR _Figure Caption +$<a$5CJ\aJdod _List0,x # h8p @  CJOJQJaJJOJ _NumberedList- & F <^^o^ _Paper Title .$5:CJ0OJQJ\^JaJ0|o| _Subhead16/$@& # h8p @  5CJOJQJ\^JaJ|o| _Subhead260$x@& # h8p @  5CJOJQJ\^JaJ|o| _Subhead361$x@& # h8p @  5CJOJQJ\^JaJRoR _Subhead42$<@&5:OJQJ\^Jo2 aw sidebar73 7n J 77<<]7^76CJOJQJ]^JaJxoBx aw heading 2'4xx 7n J 6>*CJOJQJ]^JaJnoRn aw base text'5<< 7n J CJOJQJ^JaJXoQbX aw code body67^7CJOJQJ^JaJHH4\e~5`_ > \ d r > nu Dc~MP$/BY[c$*>@     & - . 1 8 ? F M N Q X _ f m n q x  ! ! ! !!!!!(!)! "$n%%%&@&x&&&"'))S*+,.>/F/O/_/n//////// 00011b2j2y222223#373h3m3q3}33333333 4%4>4Y4n4w44444555&565857c:U;_;g;q;r;t;|;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;<< <<<<< <!<+>??@ABeE!G2GHHHHHHHHHHHHHHHHH000000000 0 0 0000000000000000000h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0,F  h0h0h0h0h0h0h0h0h0h0,0*h0h0h0h0h0`OW\h0h0h0h0h0h0 h0 h0 h0 h0 h0 h0 h0 h0 h0 h0h0h0h0h0zHh0h0h0h0h0,0*h0h0h0h0h0h0zH<h0h0h0h0h0h0h0h0h0h0HLh0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 000000000000h0h0h0h0h0h0h0h0@0h0@0h0h0h0h04\e~5`_ > \ d r > nu Dc~MP$/BY[c$*>@     & - . 1 8 ? F M N Q X _ f m n q x  ! ! ! !!!!!(!)! "$n%%%&@&x&&&"'))S*+,.>/F/O/_/n//////// 00011b2j2y222223#373h3m3q3}33333333 4%4>4Y4n4w44444555&565857c:U;_;g;q;r;t;|;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;<< <<<<< <!<+>??@ABeE!G2GHHHHHHj0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h00000000000 0 0 0 0 0 0 0 00000 0 0 000000000000000000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 000000000000000000000000000000000000000000000000000000000000000000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 000000000000@0h0L [@0 0 PP)J!''(-(M(m((((( )()x.y:=qCCCCCCCCCC DDPP*,-./0123456789:;<=>?@ABCDEFGHIP+111H:t  ,2$|O_Z=Iw@H 0( X4 0(  B S  ?H 0 \ 0 # 0  0 ,\44H44H8*urn:schemas-microsoft-com:office:smarttagsCity9*urn:schemas-microsoft-com:office:smarttagsplace ?.3lppz.9$/'-6AJP ,2Ol//12p2v2{2222222222222222233 33 3.343`3f33333333333333333334#4+4=4D4T4_4l4444444 555$5/55555@@EEFFHHHHHHHHHHHHHHH'(4 \ b f p r v HO8< '-15DI[aei$),2gmd%m%%%%%&&H&N&&&&&&&''?,,>/D/H/M/Q/U/a/e/p/v///////////// 00b2h2l2p2{22222233%3+393=3h3l3t3z333333333333333L4U4444444445555&5+5s5y5777999HHHHHHHHHHHHHHH333333333333333333333333333333333333333333333333333333333333333333333333333333333333333.3HHHHHHHHHHHHHHH|2}LF~v5, d R&XFu8#'p$ y0( $*\0.OA&?( Y-Z1xki@     & - . 1 8 ? F M N Q X _ f m n q x  ! ! ! !!!!!(!)!c:U;_;g;q;r;t;|;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;<< <<<<< <HHHHHHHH33@PDF995Ne00:winspoolPDF995 Printer DriverPDF995S odXXLetterPRIV0''''\KhC?,[PDF995S odXXLetterPRIV0''''\KhC?,[33\;33HP@UnknownGz Times New Roman5Symbol3& z Arial?5 z Courier New9Garamond71 Courier;Wingdings"hhzqf  =% =%#4djHjH-2HP ?>2%Collecting Oracle Statistics (JLComp)=Oracle dbms_stats statistics CBO performance tuning histogramJonathan LewisJonathan Lewis`