Re: DBMS_STATS compared to the analyze command: weird!

Mark Bole <makbo_at_pacbell.net> wrote:

>How many CPU's do you have on this server (how did you pick
>"degree=>4"?) The dbms_stats package can be quite a resource hog while
>it's running on large tables in parallel. Was the original performance
>slow-down seem while the gathering was going on, or afterward? ( the
>former is a hardware bottleneck, the latter is an optimizer issue).

4 CPUs. Of course the issue is about performance at execution time of regular queries and transactions.

>Try something like the following, if you have a one or more schemas of
>primary interest, under Oracle 9.2. My guess is it will use more
>resources per minute, for a shorter overall duration, than a
>single-threaded "analyze" statement, but nevertheless dbms_stats is
>Oracle's stated future direction (under version 10g with dynamic
>sampling, all is forgiven...)

dbms_stats is "real SQL", it even has an execution plan ;-) while analyze is a kind of special utility.

>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 Received on Thu Feb 26 2004 - 17:24:24 CST