Re: Analyze /LMT Question .....

On Wed, 19 Feb 2003 21:27:48 +0000, Mahesh Hardikar wrote:

> Hello all ,
>
> I am a bit new to Oracle. We are having Oracle EE 8.1.7.1 on HP-UX 11
> We do index rebuild & analyze table compute statistics weekly.

There is *usuallu* very little reason ever to rebuild an index in Oracle, so you're just exerting unnecessary effort. Why are you computing statistics weekly, too? Doing so causes a lot of table scans, a lot of I/O, and is probably unnecessary, unless the fundamental nature of the data has changed in the week such that all your execution plans are inappropriate.

And you should get out of the habit of doing 'alter table BLAH compute statistics', because it's deprecated in 9i, and will disappear in the future... and Oracle has thoughtfully provided the exceptionally nice dbms_stats package to make your life more interesting and easier in regard to collecting statistics (for example, you can collect statistics for an entire schema, or specify a degree of parallelism in the collection process).

> Do I need to recompile all procedures after analyze in order touse
> fresh statistics ? Or they will get automatically recompiled upon next
> execution ?

You do not *need* to recompile everything, but procedures etc. referencing tables for which you've re-collected statistics or rebuilt indexes will be invalidated, automatically. The first time they are called after the invalidation, they will be automatically re-compiled. However, this means that the person making that first call will experience considerable slowness in response. Another reason not to collect statistics or rebuild indexes unnecessarily.

>
> Is there anything else I need to do ?
>
> One more question :
>
> We have all tablespaces as DICTIONARY managed. I wish to turn them
> (except SYSTEM) LMT's with Uniform Extent Size. I read about
> dmbs_space_admin.tablespace_migrate_to_local. But will it reorganise
> existing data as per new storage settings or affect only future
> allocation ?
> On Test , I saw it converted tablespace to LMT but made it
> AUTOALLOCATE . How can I make it UNIFORM & specify size ?

Create a new uniform LMT, and use 'alter table BLAH move tablespace NEW' syntax. Don't touch the dbms_space_admin package: it leaves your tablespaces looking like my bedroom: A mess.

Regards
HJR
>
> Will it be advisable to export schema , drop user , convert tablespace
> to LMT (or drop existing TS & create new LMT with specified size , run
> DDL's for Tables ) & then do Import ?
>
> Any help is kindly appreciated ...
>
> Thanks for reading such a long post ...
>
> Regards ,
> Mahesh Hardikar
Received on Thu Feb 20 2003 - 12:50:16 CST