DBA Help needed!! 1

[MikeJones]

Hi I have acouple of questions I need a DBA to provide an answer for, I'm not sure if this is bread and butter stuff or a little off the line....

The Environment here is 8.1.6.

If you amend the PCTFREE and / or PCTUSED for a table with lots of data in it what happens?

If you have a Table that is Occuping 2 extents and you amend the NEXT and PCTINCREASE for the table what happens? are you even allowed to do this?

Thanks in advance,

Mike.

 

[karluk]

You can alter all of these parameters. I'm fairly sure, however, that Oracle doesn't go through the entire table and adjust the freelists based on the new values of pctfree and pctused. The new values are effective only for database activity from that time on. You may need to export and import in order to get the data rearranged the way you want.

Next and pctincrease take effect immediately, but of course only affect actual storage at the time the next extent is allocated.

 

[karluk]

By the way, the syntax of the command is

ALTER TABLE your_tabe PCTFREE number PCTUSED number STORAGE(NEXT bytes PCTINCREASE number)

 

[carp]

Just to confirm Karluk's information. He has it exactly right.

 

[raygg]

For indexes - set pctfree very low 1-5%
For heavily updated tables - set pctfree at 40-60%
For readonly tables - set pctfree 5-20%


Re: NEXT - for all the tables in the same schema - make the NEXT a multiple of itself - start with 32k, then 128k, 1mb, 8mb etc. Settle on 3-6 incremental values for NEXT and stick with it. Will get allocated at least 5 times your block size allocated by Oracle automatically no matter how small you set this. Space will be allocated rounded to the next even block size by Oracle. These reasons give argument to making the NEXT equal to 5 times your block size for its smallest increment. That's probably why the default initial and next in Oracle 8 for NT is 10k because the default block size is 2k.

ReCTINCREASE - for most tables set it at 1% - be very careful about not specifying this value - it will default to 50% and that multiplier can cause a lot of problems over an extended period. Even with a table you frequently load up and then truncate - such as daily collected payments - that NEXT stays at its latest incremental value and can lead to a database error if it gets too large looking for a space allocation you don't have. Always set it at least at 1% to promte coalescing.

RE:inital value - no matter how small you make this - you can get up to 5 times your db_block_size automatically allocated by Oracle.

 

[MikeJones]

Thanks guys,

I asked this as we are making our tables storage / transaction stuff configureable by the clients, through the product, kinda moving some of the frequent DBA stuff into the application. While the product gets delivered with standard settings and they would normally start with these settings they may realise 6 months down the line that our initial "guesstimates" are not accurate for there data loads. These are pretty much the only changeable things for a table (once it has been populated) as I've been reliably informed that you can not alter the initial extent size of the table once it's been created (only drop and recreate with new size). My concern is that if the client changes the pctfree/used while there is data in it it will only effect changed data from that point on and so any DBA worth his salt would want to import export the data so that it all obeys the new rules. Is this the case??

If so, rather than do an actual import export of the data I would rather do a "create table as select" and then an "insert into select" instead as this can all be done within PL/SQL and give me greater error control. I was also thinking of doing the initial "create as select" with the no logging option as some of these table contain 5 - 10 Million rows and with the logging on the transaction could take forever. How dangerous is this though? would it be best to ensure a resent backup has been done incase there is some system crash while the operation is completed? Would this normally be done by a DBA anyway?

Thanks again for the valuable, not to mention free advice you all give.

Mike.

 

[Kenrae]

Yes, it would normally be done by a DBA. What you're telling us is a good idea, though.
For performance and possible crashes I'd recomend doing the job with a batch process at night, after doing the backup. Users changes could program a job instead of changing the tables right away. Then the application should tell them that the next day (or whenever you do the backups) the changes will take effect. Since it's a delicate operation, executing it immediately could be a handicap.

Hope this helps.