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.

On Mar 4, 7:13*am, moongeegee <moongee... (AT) gmail (DOT) com> wrote:

snip

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?

On Mar 4, 8:59*am, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> 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.

HTH -- Mark D Powell --

On Thu, 04 Mar 2010 06:31:29 -0800, Mark D Powell wrote:

You are right. I discussed that with Tanel Poder few days ago:

http://www.orafaq.com/maillist/oracl...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

On Mar 4, 9:49*am, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
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.

On PCTFREE parameter: I think it is better to avoid chained rows and
not only for performance reasons:
- There are some bugs related to chained rows/row migration. For
example 9.2.0.8 has a bug that may cause UPDATE statement to fail if
this UPDATE leads to row migration.
- Once a block becomes full you may also have problem when several
transactions attempt to update this block simultaneously as by default
INITRANS = 1.

I suggest analyzing all large tables looking whether significant
UPDATE activity leading to row expansion is possible. For example many
historic tables would contain following columns:
START_DATE
COMPLETION_DATE
COMPLETION_CODE
ERROR_DESCRIPTION

Only START_DATE gets populated during INSERT, other columns are
populated through UPDATE. This leads to row expansion and chained
rows.

The most extreme example I've seen is following: a table contained
only two columns, USER_ID and PASSWORD_HISTORY. PASSWORD_HISTORY
column was VARCHAR(4000) and contained last 20 or so passwords in
encrypted format (string 30 characters long) separated by dot.
When the system went live each user had only one password in the
PASSWORD_HISTORY, as passwords expired 30 days after creation they
were recorder in the PASSWORD_HISTORY column.
So rows expanded and expanded again and it reached a point where
UPDATE based on unique index would run for very long time - longer
than Apps Server timeout. So users couldn't reset their passwords and
couldn't login to the system. We rebuilt the table with plenty of free
space per block and it has been stable since then.

On ASSM: I know one reason to use ASSM and one reason not to use it:
- Use ASSM as it allows shrinking of segments in 10g. This is very
useful feature as shrinking is done online and no additional space is
required when it is running (not like index rebuild/table move).
However shringing is approx 10 times slower than ALTER TABLE MOVE/
ALTER INDEX REBUILD.
- Don't use ASSM: If you have large uncomitted DELETE in ASSM
tablespace then single-row inserts are very slow. Typical scenario:
something goes wrong during purge, for example Apps Support may decide
to change retention period from 90 days to 30 days, this results in
enormous DELETE, UNDO runs out of space, DELETE fails and being rolled
back. While this is happening the application is almost dead as single-
row inserts run for several seconds instead of milliseconds. We have
many systems that keep data somewhere between 2 and 100 days in non-
partitioned tables, the data gets purged nightly, this purge is often
single biggest source of instability.

On Fri, 05 Mar 2010 04:19:16 -0800, vsevolod afanassiev wrote:

Speaking of the chained rows, how many people here have actually used the
ANALYZE utility to list chained rows and base the analysis on facts? At
one time, there was a query, using th VSIZE function to get you a rough
estimate for the number of chained rows. That was replaced by ANALYZE
table list chained rows into <chained rows table, created by utlchain>,
the utility to produce the list of chained rows. I use it only when the
developers complain that "reads from the table are slow".



--
http://mgogala.byethost5.com

On Mar 5, 7:40*am, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
Since it is easy to do this in dbconsole, I do it about every month or
two. There are a few tables that get mass updated irregularly, and it
lets me know if something has gone unpredictable. There are a couple
tables that have a data pattern that makes shrink worthwhile (though
less than the tool predicts). It's not obsessive to fix growing
problems before people complain. It's just my preciousssssss.

jg
--
@home.com is bogus.
http://www.wired.com/threatlevel/201...midt-cyberwar/

On Mar 4, 5:30*pm, "mfuller... (AT) gmail (DOT) com" <mfuller... (AT) gmail (DOT) com>
wrote:
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
--
@home.com is bogus.
http://www.breitbart.com/article.php...show_article=1