Re: explain plan / big table help plz...

"Allen" <allenh_at_starbase.neosoft.com> wrote in message news:2F7647C0841B1E05.032F18EA42A89880.524B49B6BD32EA27_at_lp.airnews.net...
>
> Oracle 9.2, CBO=choose. Sorry if these are silly questions.
>
> Ok we got a big table, gona have 100 million+ rows in it soon. We're
> trying various queries against this table to see what is most efficient.
>
> Using explain plan, is the COST the main thing we should be looking at?
> Is it pretty much guaranteed that if two queries return the same results,
> but the COST is lower for one, then should we always go with the lower
> COST query?

Generally speaking yes. However, I personally like to look at the actual statistics and inspect them as they tell the real cost of each plan (when viewed intelligently). I also test with a simple set timing on (running each a couple of times to ensure caching doesn't overly effect the results) and see which wins. There's nothing like a faster response to tell which is preferable.

>
> Another wrinkle, regarding bind variables. The applications were written
> using Web Objects, which can turn bind variables off or on. Since the
> apps are not using cursors, its my contention that bind variables aren't
> doing us any good. But when we look at the explain plan results it
> sometimes shows lower COST values with binds.. same query plug in real
> values and COST goes higher. Any suggestions on if binds are doing us any
> good or not?

OK. The advantage of bind variables is that they reduce reparse overheads as Oracle generates an execution plan based on 'whatever' the value. So the same effective code is shared in memory.

The disadvantage of bind variables is that Oracle can generate only the one execution plan. no matter what the values. Now if the values are evenly distributed, it makes no difference which value is actually used. However if the values are not evenly distributed, then it make a big difference. For very rare values, it might make more sense to use an index. For very common values, a full table scan might be more appropriate. However with bind variables, Oracle can only assume even distribution and the plan generated will be incorrect for one of the scenarios. By default Oracle only stores the min, max and distinct number of values for each column and so can only assume even distribution. However, if you have histogram statistics, the distribution of data is better recognised and appropriate execution plans can be generated depending on the value. But bind variables have no choice but to ignore histograms.

>
> Finally, this table will have some range scans, which is probably going to
> be a performance problem. Would performance increase if we partition this
> table (using oracle's table/index partition stuff)? In other words is
> Oracle's CBO smart enuf to range scan only the partition with the
> appropriate data (assuming that is what the partition was on)?
>

Definitely. There are many advantages to partitioning but by far the most important is the performance advantages they provide. By partitioning on a column frequently referenced in where conditions, Oracle can perform "partition pruning", that is ignore all partitions that it determines can not contain the required data. Note partitions can be fully scanned or accessed by local indexes providing a greater or more flexible level of granularity when determining execution plans. Then by "equal partitioning" frequently joined tables, the subsequent joins are so much more efficient as Oracle can clearly determine the join sets that are required. Add to that the power of parallelism (when it works) and you're on to a winner.

Definitely worth investigation.

Cheers

Richard
> thx... allen
Received on Tue Sep 10 2002 - 21:02:08 CDT