Re: help with Analyze!!

In article <4J0z6.5339$5_2.463141_at_news1.telusplanet.net>, Glen A Stromquist <stromqgl_at_alpac.ca> writes:
>I wrote a script to delete statistics, then do an analyze compute statistics
>on all of the tables in a schema. The anaylze failed on one table because
>the temp tablepace filled up.
>
>Performance is now so dismal the application is almost unusable, should I
>delete all of the stats from the analyze? I have to see if there is room to
>increase the temp tablepace so the analyze will run on the one table it
>failed on.
>
>
>thanks in advance
>
>

Try using the estimate option to get around the problem.

Analyze table owner.table_name estimate statistics sample 75000 rows;

Use the largest sample size you can that time/resources support.

If your system was not written to run cost based then the possibility exists that you shouldn't have analyzed it. We have used the cost based optimizer, CBO, since version 7.0 with good results. But some queries will have to be tuned by hand. If you have access to the code you can tune queries that are not handled correctly by the CBO, but without access to the code there isn't much you can do unless you have version 8.1 where the outlines feature may be able to solve this. I haven't had a chance to play with them yet, but soon.

Also beware of work tables when analyzing. If they have only a few rows one day and 3 times as many the next and if you analyze them on the low count days queries against them are likely to go out to lunch.

Good luck.

• Mark D. Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net