Re: DBMS_STATS compared to the analyze command: weird!

From: Mark Bole <makbo_at_pacbell.net>
Date: Sat, 28 Feb 2004 01:47:18 GMT
Message-ID: <GqS%b.18081$Lh6.6499@newssvr29.news.prodigy.com>

Rick Denoire wrote:

> Mark Bole <makbo_at_pacbell.net> wrote:

[...]
>

>>exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => upper('schema_owner'), -
>>         degree => DBMS_STATS.DEFAULT_DEGREE, -
>>         cascade => true, -
>>         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE  );

>
>
> Will this command be translated to an analyze command by Oracle?
>
> Bye
> Rick Denoire
>

I have examined "EXPLAIN PLAN" output (via OEM stand-alone client) while this command is running (ver 9.2 under Solaris) and it is clear that internal tables are being updated. I decided I didn't really want to know about them at the column and row level... ;-)

It is my impression that Oracle cost-based optmizer statistics have changed quite a bit, with each major version, over the last [n] number of years, since the decision to retire the RBO was made in the previous century. I heard that in 10g, there are two separate solutions for two separate problems:

failure to routinely gather fresh statistics - solution: default regularly scheduled jobs
what to do when statistics are missing or suspected inaccurate -- depending on the estimated cost of the query, go out and sample some statistics on the fly if the cost-benefit trade-off is worth it.

--Mark Bole Received on Fri Feb 27 2004 - 19:47:18 CST