Re: Wolfgang Breitling's papers at centrexcc.com

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Thu, 29 May 2003 15:06:42 -0700
Message-ID: <tCvBa.12$No4.1044@news.oracle.com>

"W.Breitling" <member28455_at_dbforums.com> wrote in message news:2933923.1054170175_at_dbforums.com...

> I changed the settings and re-uploaded the papers and presentations.
> Hopefully they will now be viewable with any Acrobat Reader version 3
> and up. Let me know if there are still problems and thanks for bringing
> it to my attention.

Thank you, Wolfgang. Very professinally written papers.

Couple of comments.
1. A minor convenience: v$sql_plan and v$sql_plan_statistics are joined into v$sql_plan_statistics_all already.
2. I don't agree that manually changing statistics is the best way correcting selectivity of single table predicates like this

L1.tree_node_num between ... and ...
or
L1.tree_node_num between ... and ...

Changing stats would affect other simpler queries too.

Dynamic sampling is supposed to amend selectivity in this case. There are couple of pitfalls for dynamic sampling as well: i) if recursive sampling query is returning 0 rows, sampling is considered "statistically insignificant" and rejected in favour of estimation based upon static statistics.
ii) if there is no more predicates on L1 table, sampling wouldn't be triggered as well. One have to give an explicit hint, or add dummy tautology predicate on a different L1 column, or wrap column variable into a [built-in] function.
iii) if plan is concatenated, then I don't see correct cardinalities calculated by sampling in the plan too. The difficulty might be pushing the numbers inside of each branch without being have to fire more sampling queries... Received on Thu May 29 2003 - 17:06:42 CDT