WILL>>Using the following parameters seem to work best for me:
NOTE:The sql in front and end gathers the current time to let you know
how long it takes to run.


select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Start Time" from dual
/
execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SCHEMA_OWNER', -
tabname => 'TABLE_NAME', -
partname => NULL, -
estimate_percent => 5, -
block_sample => TRUE, -
method_opt => 'for all columns size 1', -
degree => 8, -
granularity => 'ALL', -
cascade => TRUE);
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "End Time" from dual
/


Claudio Henrique Penha Duarte wrote:

>Hi all!
>
>I'm new in database administration, and i'm having some problems regarding
>the gathering statistics!
>
>My applications are running in a very good performance. Here we have
>9.2.0.5.0 Oracle 9i Database! And there's no statistics! So i think we are
>running our queries in a rule CBO mode! (Is that ritgh?!?)
>
>Now we want to migrate to 10g but first we want to test our applications
>through a statistics collected environment! For that we have run (over our
>dev db) the dbms_stats.gather_schema_stats(OWNER => 'OWNER') statement for
>just one owner!
>
>The performance got as down as you can imagine!
>
>Could anyone please advise me about that!
>
>Thanks in advance!
>
>Best regards,
>
>Claudio Duarte
>-------------------------------------------------
>OCP - Desenvolvedor Oracle
>Analista de Sistemas Sr.
>[Email address protected]
>Tel....: + 55.19.3565.5304
>Mob..: + 55.16.9168.2368
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
>


Oracle LazyDBA home page