Re: Automatic Segment Space Management
Date: Thu, 4 Mar 2010 17:30:38 -0800 (PST)
Message-ID: <9c430db6-f17f-4efd-b8ae-94625ec3e355_at_y11g2000yqh.googlegroups.com>
On Mar 4, 9:49 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Thu, 04 Mar 2010 06:31:29 -0800, Mark D Powell wrote:
> > The pctfree parameter is still valid when ASSM is in use. Determining
> > the proper value to use for this parameter is an important as ever for
> > limiting migrated rows and for making efficient use of block space.
>
> You are right. I discussed that with Tanel Poder few days ago:
>
> http://www.orafaq.com/maillist/oracle-l/2003/11/11/0848.htm
>
> Needless to say, Tanel was right. Amazingly, after all this time, the
> link to Tanel's paper still works.
>
> --http://mgogala.byethost5.com
I am going to have to give a differing opinion on the subject. PCTFREE and PCTUSED are interesting parameters to deal with. In my experience, I do not like to mess with these. Why? In many cases this is needless micro-management. I don't have time to fix problems that don't have an impact. It is fun if I have free time and want to expiriment or learn, but in reality there is usually little payoff. Leave the scripts alone. They don't need changing to use ASSM and they don't need setting really (in my experience) unless the following occurs:
You have row migration issues and it is causing a real performance impact. Full table scans don't really matter because even if it takes two blocks to read your row, you are reading all the blocks anyway. If you are doing a single row index read and the index read takes 4 IO's, and then the data block takes 1 IO, then another due to row migration, is your app so sensitive that 6 IO's is too much and 5 IO's is better? I hope not. If you are doing large range scans and there is a LOT of row migration that causes an increase in IO that is creating a visible impact to the query OR a visible impact to the total concurrent IO of the system, then it should be looked into.
Another problem I see is many DBA's or developers want to micromanage the database at this level. That is fine, but really this requires a very good knowledge of the data and usage of the system. These settings are great for space utilization and to prevent row migration where you know exactly how it will be used at all times. If it is a data warehouse and the table receives 0 row updates ever, then filling a block completely is good to reduce IO (denser blocks) and to reduce space consumption as data warehouses can me space hogs. if you know that the table will receive plenty of updates and you can predict how much space should be left to allow it to still reside in 1 block, then great. Use it.
Sorry to say that many times I see these being set by pulling a number out of a hat or by defaults set in the script generation tool or whatever. In my opinion, setting things for no reason shouldn't be done arbitrarily. If the script has them already, leave them alone. If it doesn't or you don't like the settings, I wouldn't mess with them without a good reason. Received on Thu Mar 04 2010 - 19:30:38 CST