Re: Opinions for this storage parameters.

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 3 Jul 2002 20:05:58 +1000
Message-ID: <FVzU8.26569$Hj3.82346_at_newsfeeds.bigpond.com>


Hi Christopher,

Regarding PCTINCREASE = 1

I've been described by various DBAs in sunny Canberra as a realistic twit (I've been called worse but we won't go there) over discussions on this subject.

With regard to Dictionary Managed Tablespaces I've followed this basic philosophy. Don't use the following storage clauses when defining an object (INITIAL, NEXT, PCTINCREASE), leave it to the default storage clause of the tablespace you place the object in. Set the default storage clause of the tablespace such that INITIAL and NEXT are the same (lets say 10M) and PCTINCREASE = 0. Now, EVERY object that lives in the tablespace has EXACTLY the same sized extents. If I were to drop an object, I leave ONLY 10M "holes" in the tablespace. And you're absolutely correct, SMON doesn't automatically coalesce such a tablespace (because it's default PCTINCREASE = 0). But, so what ? If as you describe an object needs to extend, it will either reuse EXACTLY one of the holes previously left behind or use a new piece of the tablespace. However, fragmentation is NOT going to be an issue to me. So SMON, put your feet up and relax for a while.

But the non believers cry, what if I only have a small table. 10M is way too big and would waste space. Then it gets assigned to another tablespace (with default storage of INITIAL and NEXT set to say 64K) where they are also sized exactly the same without wasting space.

INITIAL and NEXT are still vital considerations when defining a table. The only difference is that I pick an appropriate tablespace rather than picking an appropriate storage clause. I actually believe that setting PCTINCREASE to 1 is possibly the worst value to set it to as it kinda guarantees objects being created of all manner of sizes.

In my view, Locally Managed Tablespaces kinda supports this approach as it automatically implements such a philosophy (when used with the uniform clause). When using LMT, this discussion is no longer relevant.

I'm sure this has been discussed many times here but the non believers are still out there and I'm still an idealistic twit !!

Cheers

Richard

"Christopher Merry" <merryct_at_constructingbits.com> wrote in message news:ui5g5c3kijfude_at_corp.supernews.com...
> Just wanted to add a couple of comments to those already mentioned. I
 don't
> think you really need to over-analyze the settings though!
>
> 1) Table Extent Sizes (in general)
>
> I would take a look at your table extents (not so much for this table but
> for future reference). If Oracle decides to perform a full-table scan, a
> single fetch returns the value of DB_MULTI_BLOCK_READ_COUNT *
 DB_BLOCK_SIZE.
> The defaults for these values results in 64k [DMBRC (8) * DB_BLOCK_SIZE
 (8k)
> = 64k]. That is the reason it is very common to set extent sizes to the
> value of 64k. In general, you are better off setting your extent sizes in
> multiples of this value (64k, 128k, 256k, etc).
>
> 2) PCTINCREASE value of 1
>
> The PCTINCREASE value of 1 is not necessarily a bad idea. It just depends
> on the circumstances. The only reason I use this setting is when I am
> dealing with a dictionary managed tablespace. This value will force SMON
 to
> coalesce the tablespace freespace periodically (any value greater than 0
> causes this).
>
> Here is the scenario when I like this value:
> Typically, allocation of a new extents takes place during an INSERT
> operation. Many times, the INSERT takes place during more active times of
> the day. (Of course not always but it stands to reason that more INSERT
> will take place during busier times of the day) During this extent
> allocation, if Oracle cannot find sufficient freespace (dictionary
 managed)
> SMON performs freespace coalescence. This is not an optimal time for this
> operation. If SMON has already coalesced the freespace at a more
 convenient
> time, it MAY save a bit of processing at the moment. Of course this is
 not
> a huge deal, but it is one philosophy.
>
> By the way, a PCTINCREASE of 1 is not going to cause tremendous growth of
> the future extents, but it is a bit of an annoyance if you are a very
> structured DBA that expects to see certain NEXT value patterns.
>
> 3) PCTFREE for an index
>
> This setting is really only used to setup the index. Once the index has
> been created and Oracle begins its "balancing act", Oracle over-rides the
> original PCTFREE value.
>
> It is nice to see someone taking the time to setup their environment with
> careful forethought. So many times I have seen nothing more than Oracle's
> default storage parameters used.
>
> cm
>
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:LdtU8.26320$Hj3.81112_at_newsfeeds.bigpond.com...
> > Hi Fernando,
> >
> > I think it's great you've taken the trouble to perform analysis on your
> > table and tried to select appropriate storage parameters. Unfortunately
 many
> > don't bother and take easy or general (albeit inappropriate) options.
> >
> > I guess since the tables are so tiny, you're not going to go too wrong
 here
> > no matter what you do (except perhaps waste heaps of unnecessary space).
 A
> > few points I would make.
> >
> > I agree with Howard and suggest you store these little darlings in a
 locally
> > managed tablespace and use a small uniform extent size (64K is not
 uncommon
> > for small objects). Yes you will waste a little space (but not much in
 the
> > scheme of things) but all other such little babies can also be housed in
> > this tablespace keeping your extent sizes consistent.
> >
> > The appropriate setting of PCTREE (for the table) is dependent of the
> > potential growth of your rows . The appropriate PCTFREE (for the index)
 is
> > dependent on the amount of free space you want allocated for subsequent
> > inserts. Considering the table is static, low (possibly 0) is
 appropriate.
> >
> > I'm not sure about the formula you use for calculating the PCTUSED
 values.
> > It kind of depends on the average length of a row (which is small in
 your
> > case so you're OK) and the amount of DML (particularly deletes) you have
> > (which is none so you're OK). The key thing you want to avoid is having
> > 100 - (PCTFREE + PCTUSED) to be less than the space occupied by an
 average
> > row. Being taken off the freelist below the PCTUSED marker is not a
 pretty
> > thing. Note this no longer an issue with segment space management in 9i.
> >
> > The PCTINCREASE of 1 is not a good idea. With dictionary managed
 tablespaces
> > it simply creates extents of differing sizes and possible fragmentation
> > issues. If you use a locally managed tablespace then it's not an issue.
> >
> > Again well done for the analysis. With larger tables, it all good stuff
 in
> > deciding appropriate tablespaces and such to use.
> >
> > Good Luck
> >
> > Richard
> >
> >
> > "Fernando Ruza" <fruza_at_yahoo.com> wrote in message
 news:3d8dfca3.0207020210.
> > 6962a4be_at_posting.google.com...
> > > Hi,
> > >
> > > I'm creating all the objects for one application in an Oracle
> > > database. After reading a lot about sizing tables and indexes and at
> > > the end following the rules commented in the book "Oracle8i DBA
> > > Handbook" I have decided the following storage parameters for an
> > > static table.
> > >
> > > I just would like to know your opinions about the analysis to obtain
> > > the datas and if these parameters are ok for this kind of table.
> > >
> > >
> > > -- Table CLIENTES
> > > -----------------
> > > -- Database Block Size: 8192 bytes (8kb)
> > > -- PCTFREE: 5 % => 8102 * 0.05 = 405 bytes
> > > -- PCTUSED: PCTUSED + PCTFREE = 85 => PCTUSED = 80
> > > -- Average Row Length: 100 bytes (Get it with command ANALYZE)
> > > -- Number of expected rows: 50 - 100
> > > -- Block space available: 8192 - 90 (Fixed block header) = 8102 - 405
> > > = 7697 bytes
> > > -- Rows per Block: 7697 / 100 (bytes per row) = 76,97 ~ 77 rows.
> > > -- Number of Blocks: 100 (rows expected) / 77 (rows per Block) = 1,29
> > > ~ 2 Blocks
> > >
> > > -- Table CLIENTES
> > > CREATE TABLE CLIENTES
> > > (CODCLIENTE NUMBER NOT NULL,
> > > NOMBRECLIENTE VARCHAR2(30),
> > > RAZONSOCIAL VARCHAR2(50),
> > > CONTACTO VARCHAR2(50),
> > > DIRECCION VARCHAR2(50),
> > > CODIGOPOSTAL VARCHAR2(5),
> > > LOCALIDAD VARCHAR2(50),
> > > PROVINCIA VARCHAR2(50),
> > > PAIS VARCHAR2(50),
> > > CIF VARCHAR2(15),
> > > TFNO1 VARCHAR2(15),
> > > TFNO2 VARCHAR2(15),
> > > FAX VARCHAR2(15),
> > > FECHAALTA DATE,
> > > FECHABAJA DATE,
> > > OBSERVACIONES VARCHAR2(200)
> > > )
> > > TABLESPACE DATA
> > > STORAGE ( INITIAL 16384
> > > NEXT 16384
> > > MINEXTENTS 1
> > > MAXEXTENTS 2
> > > PCTINCREASE 1
> > > )
> > > PCTFREE 5
> > > PCTUSED 80;
> > >
> > > -- Primary Key of CLIENTES
> > > --------------------------
> > > -- Database Block Size: 8192 bytes (8kb)
> > > -- PCTFREE: 5 % => 8192 * 0.05 = 405 bytes
> > > -- Average index lenght: 26 bytes (Get it with SELECT
> > > AVG(NVL(VSIZE(Col1),0)) Avg_Row_Length FROM table_name;)
> > > -- Space used per row = Avg_Row_Length + Number of columns + Number
> > > of long columns + 8 header bytes.
> > > -- Number of expected rows: 50 - 100
> > > -- Block space available: 8192 - 161 (Fixed index block header) = 8031
> > > - 405 = 7626 bytes
> > > -- Rows per Block: 7626 / 26 (bytes per row) = 293,30 ~ 293 rows.
> > > -- Number of Block: 100 (rows expected) / 293 (rows per Block) = 1
> > >
> > > -- Creamos Primary Key
> > > ALTER TABLE CLIENTES ADD CONSTRAINT PK_CLIENTES
> > > PRIMARY KEY (CODCLIENTE)
> > > USING INDEX
> > > TABLESPACE INDEXES
> > > STORAGE ( INITIAL 8192
> > > NEXT 8192
> > > MINEXTENTS 1
> > > MAXEXTENTS 1
> > > PCTINCREASE 1
> > > )
> > > PCTFREE 5;
> > >
> > >
> > > Thanks in advance for any reply,
> > >
> > > Fernando.
> >
> >
>
>
Received on Wed Jul 03 2002 - 12:05:58 CEST

Original text of this message