Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Make Table/Index Analyse Run Faster
On Fri, 24 Mar 2000 09:11:14 GMT, nsouto_at_nsw.bigpond.net.au.nospam (Nuno Souto) wrote:
>On Fri, 24 Mar 2000 10:02:50 -0800, "Rob Risetto"
><robr_at_powerup.com.au> wrote:
>
>>I'm migrating a PeopleSoft db from v7... to v8.0.6 and would like to cut
>>down the time for the table/index analyse. One option would be to break up
>>the individual analyse statements into separate files and run them
>>simultaneously. Are there any gotchas with this approach - system table
>>locks etc. Any other tips or techniques.
>There is an undocumented PL/SQL procedure that will do the fastest
>analyze I've ever seen. Does it using parallel techniques, at least in
>UNIX. Havne't used it in a while. Last time I looked it was called
>"analyze_schema" and it lived inside dbms_util. Look in your
>rdbms/admin directory and do a search (grep?) for "analyze" in all the
>.sql files there. Doco on how to use it is in the source package.
>
>It is very,very fast. But I suppose a custom, separate-files process
>could eventually beat it.
The usage is easy.
exec dbms_utility.analyze_schema('SCHEMA_NAME', 'ANALYSIS_TYPE');
where SCHEMA_NAME is the name of your schema, and ANALYSIS_TYPE is the type of analyze you want - ESTIMATE, COMPUTE, etc.
There may be other options as well - those are the ones I use.
Chris