DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Basic Question

  1. #1
    Join Date
    Apr 2002
    Posts
    55
    Hi,

    I have a question which to many of you would be quite simple and may be repeated also.

    Its has to do with sizing....

    I have establish that the size of my table would be 700KB.Now I want to know how should we specify the initial extent,next extent,pctincrease,etc at segment level and at tablespace level.Pls give example if it doesn't take a lot of your time.

    Sorry for my English..

    Thanks All.

  2. #2
    Join Date
    Aug 2001
    Posts
    36
    Hi,

    create table Table_name (col1 varchar2(4)) storage (initial 300k next 100k maxextents 5) tablespace abc;

    Regards,

  3. #3
    Join Date
    Apr 2002
    Posts
    55
    Thanks for the answer...


    I wanted to know on what basis do you decide the size of initial , next and pct increase for segment and tablespace.

    Thanks.

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    For such small table I would try to put everything in one extent. And thus initial 800K, next 100k
    If you are using 8i or above consider using LMT with uniform extent size.

    Sanjay

  5. #5
    Join Date
    Apr 2002
    Posts
    55
    Originally posted by SANJAY_G
    For such small table I would try to put everything in one extent. And thus initial 800K, next 100k
    Thanks Sanjay...

    This table was just an example.I think i am not able to communicate my words in a proper manner.I dont intend to know for this table but I want a general idea so as to what factors are considered while determining the extents in the storage parameter.Like as u said that you will keep in 1 extent.What prompted you to decide that you will keep it in one extent and not distribute it.

    Hope I made it clear.

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    In LMT's (UNIFORM) you cannot specify, INITIAL and NEXT, MINSIZE and PCTINCREASE for obvious reasons. What isn't so obvious is the MAXEXTENTS which STILL have to be set at the segment level. I queried Oracle to why you can't set a LMT default level for MAX_EXTENTS as a safeguard (developers do create some objects in the incorrect tablespace, and whilst fragmentation isn't an issue in LMTs, unnessary expansion of a datafile may be unwarranted if space is tight) some people loooove AUTOEXTEND.. not me personally.

    Regarding PCTINCREASE in a DD environment.. and I can't stress this enough... keep it PCTINCREASE=0 ALWAYS... no exceptions.. always PCTINCREASE =0.

    Now back to your original question.

    The way in which I calculate my data estimate is to create the table in a test area, input some rows of 'expected' data. Analyze the table and then get thr AVG_ROW_LEN. Use this to determine how many rows are expexcted each day and calculate the expected total for a day, than a week and then a month etc. This gives you an idea of the expected growth rate of the sable. And it'll also allow for you to place the segment into the appropraitely sized tablespace.

    Hope this helps...

    Cheers,

    [Edited by grjohnson on 06-04-2002 at 01:51 AM]
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Apr 2002
    Posts
    55
    Thanks...

    It was a good explanation.From this what i have understood is that I should make LMT and then there is nothing to worry about the extent parameter at segment level.It will take default parameters of the tablespace.I should only calculate the size of my tables so as to determine the data file size.What propotion do you keep the size of LMT in contrast with the total file size at the time of creating the tablespace.

    Do correct me wherever I went wrong.

    Thanks.

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    tanaka,

    "From this what i have understood is that I should make LMT and then there is nothing to worry about the extent parameter at segment level".

    You should size your UNIFORM extent size for you LMT's according to the size of your objects.

    EXAMPLE ONLY...

    DATA_LGE (128MB UNIFORM EXTENTS)
    DATA_MED (4MB UNIFORM EXTENTS)
    DATA_SML (40K UNIFORM EXTENTS)

    Now if you estimate a table has RAPID growth at 500 MB per month, you may want to partition the table by Months and place it in the DATA_LGE tablespace . Alternatively, you may have a very small lookup table of 500K in which doesn't grow.. this should be placed in the DATA_SML tablespace. It's all about using space effectively whilst maintaining a positive administration strategy. i.e. it doesn't make sense putting the LARGE (500MB per month table) into the DATA_MED tablespace simply because it'd become unlogical to manage.

    "It will take default parameters of the tablespace."

    Once you set the UNIFORM extents for a LMT any object created in the tablespace with be sized according to the UNIFORM extent sixe. (In 9i you can choose to manage the segment automatically , PCTUSED, FREELIST, FREELIST GROUPS) You may still want to set MAXEXTENTS and you should also set PCTFREE appropriatly.

    "I should only calculate the size of my tables so as to determine the data file size."

    Datafile size is proportional to the database/segment size. I like to keep my segments (if possible) under 400 extents, just for adminisration ease. In the case of DATA_MED, if you had 10 objects with 50 extents at 4M you'd need at least one datafile of 2GB to accomidate this. If you created 1 file @ 3GB, you'd have enough room to accomidate a slow level of growth.

    "What propotion do you keep the size of LMT in contrast with the total file size at the time of creating the tablespace."

    Once again, it depends on the number of segments and their perceived extents that need to be allocated. Remember you CAN'T remove a single datafile from you TABLESPACE once create. That's why if disk space is tight, you'll want to size you datafiles correctly.

    hope this helps...

    Cheers

    [Edited by grjohnson on 06-04-2002 at 02:27 AM]
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #9
    Join Date
    Apr 2002
    Posts
    55
    Hi,

    Thanks a lot for the detailed explanation.

    Thanks..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width