Prev: use tlm
Next: Cannot change ARCHIVELOG-MODE via EM
From: moongeegee on 4 Mar 2010 07:13
I have some old SQL scripts that use PCTFREE and PCTUSED.
As now we have 10g, should I modify the scripts using ASSM instead of
PCTFREE
and PCTUSED?
Thanks in advance.
From: John Hurley on 4 Mar 2010 08:59
On Mar 4, 7:13 am, moongeegee <moongee...(a)gmail.com> wrote:

snip

> I have some old SQL scripts that use PCTFREE and PCTUSED.
> As now we have 10g, should I modify the scripts using ASSM instead of
> PCTFREE
> and PCTUSED?
> Thanks in advance.

Have you looked at the Oracle documentation?

The short answer is that you scripts don't have to change although
some of the parameters may start getting ignored.

Are you going to modify things manually instead of using some kind of
tool and/or script that re-creates only the relevant parameters
applicable to your changing environment?
From: Mark D Powell on 4 Mar 2010 09:31
On Mar 4, 8:59 am, John Hurley <johnbhur...(a)sbcglobal.net> wrote:
> On Mar 4, 7:13 am, moongeegee <moongee...(a)gmail.com> wrote:
>
> snip
>
> > I have some old SQL scripts that use PCTFREE and PCTUSED.
> > As now we have 10g, should I modify the scripts using ASSM instead of
> > PCTFREE
> > and PCTUSED?
> > Thanks in advance.
>
> Have you looked at the Oracle documentation?
>
> The short answer is that you scripts don't have to change although
> some of the parameters may start getting ignored.
>
> Are you going to modify things manually instead of using some kind of
> tool and/or script that re-creates only the relevant parameters
> applicable to your changing environment?

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.

HTH -- Mark D Powell --
From: Mladen Gogala on 4 Mar 2010 10:49
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
From: mfullerton on 4 Mar 2010 20:30
On Mar 4, 9:49 am, Mladen Gogala <n...(a)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.
 |  Next  |  Last
Pages: 1 2
Prev: use tlm
Next: Cannot change ARCHIVELOG-MODE via EM