Re: number of extents question

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:alnhup$1rrr6j$1_at_ID-152732.news.dfncis.de...
> Thank You, Richard, for Your input.
>
> Now, there is one more detail I would like to ask:
>
> "Richard Foote" <richard.foote_at_bigpond.com> schrieb im Newsbeitrag
> news:x9wf9.29168$g9.83917_at_newsfeeds.bigpond.com...
> > Hi Jan,
> >
> > Option 2.
> ...
>
>
> > One last point. Having one large extent may very well be the *worst*
 value
> > to set a table to with respect to performance.
>
> I do agree at once with this if it comes to very large tables of several
 GB.
>
> Now, I do not have many big tables -
> most of them are around 10-30MB, only one is 200MB and one is 1GB, in a
> special TS; all TS are dictionary managed.
> I inherited the db from former specialists, and the management has a quite
> conservative attitude what schema objects we use.

Conservative is good, provided that it is willing to accept that progress is and can be made (albeit slowly), see below though.

> So we do not have any partitioning (due to exp limitations),
> no LOBs or Object Types, no Java, just PL/SQL and Forms/Reports in a C/S
> environment.

Object types are only really required for specialist apps, just use them when appropriate. LOB's on the other hand are a big advance over longs. If you don't use longs then fair play. if you do use longs then LOB's perform better are easier to code for and can be accessed easily through sql and PL/SQL. Longs and especially LONG RAW have had their day.

No JAVA I can understand that, but it does bring real advantages. Do you wish to send email from the db (say if a job fails) use utl_smtp which requires Java then you can do it all in the exception handling routine of the job instead of all that shell scripting nonsense. Do you have some business logic that should sit in a stored procedure but can't because it is coded in C? Move it to Java and put it where it belongs.

>
> The 1GB table has 40 extents, the one with 200MB has 25.
> Would You do something about it at all ?

Don't bother (unless the 1gb table had 1 extent a week ago). Fast growth is much more damaging than numbers of extents per se.

>
> Some smaller indices with very small INITIALs have some 1000 extents. I
> would reorganize them into one bigger extent.
> Would You ?

I'd reorganise them with a larger extent size certainly. I'd be using locally managed tablespaces as well (as richard says)

>
> I would be glad to hear from You. Jan

Hope my contribution was welcome not obstructive

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************