Re: Automatic Segment Space Management
From: joel garry <joel-garry_at_home.com>
Date: Fri, 5 Mar 2010 11:11:11 -0800 (PST)
Message-ID: <a21eea43-e52d-4569-9434-31fd35c3c794_at_q2g2000pre.googlegroups.com>
On Mar 4, 5:30 pm, "mfuller..._at_gmail.com" <mfuller..._at_gmail.com> wrote:
> 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.
Date: Fri, 5 Mar 2010 11:11:11 -0800 (PST)
Message-ID: <a21eea43-e52d-4569-9434-31fd35c3c794_at_q2g2000pre.googlegroups.com>
On Mar 4, 5:30 pm, "mfuller..._at_gmail.com" <mfuller..._at_gmail.com> wrote:
> 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.
Well, if you are wasting 20% of the space in every big table, and you are doing lots of stuff to the tables, you are lowering the headroom under which everything goes to hell (like the "knee" Cary M. is always going on about). It is a physical DBA's task to know the app and know the data. It isn't too much to expect an app vendor to have figured this out for 1000's of tables, though sadly, I've seen them be wrong about it.
We can be grateful that the defaults are reasonable in most cases these days, but if you are waiting for complaints about an app that you don't know about before you do anything, that might not be considered working smart. Disk space may be cheap, but performance analysis under fire isn't.
jg
-- _at_home.com is bogus. http://www.breitbart.com/article.php?id=D9E4JSD80&show_article=1Received on Fri Mar 05 2010 - 13:11:11 CST