 |
|
Setting pctfree and pctused Based
on Average Row Length
Oracle Tips by Burleson Consulting
|
It is very important that the Remote DBA understand
how the row length affects setting the values for pctfree and
pctused. You want to set pctfree such that room is left
on each block for row expansion, and you want to set pctused so
that newly linked blocks have enough room to accept rows.
Here we see the trade-off between effective
space usage and performance. If you set pctused to a high
value—say, 80—a block will quickly become available to accept new
rows, but it will not have room for a lot of rows before it becomes
logically full again. Remember the rule for pctused. The lower
the value for pctused, the more space will be available on each
data block, and subsequent insert operations will run faster.
The downside is that a block must be nearly empty before it becomes
eligible to accept new rows.
The script shown here will generate the table
alteration syntax. Please note that this script only provides general
guidelines, and you will want to leave the default pctused=40
unless your system is low on disk space, or unless the average row
length is very large.
pctused.sql
rem
pctused.sql
set heading off;
set pages 9999;
set feedback off;
spool pctused.lst;
column db_block_size new_value blksz noprint
select value db_block_size from v$parameter where name='db_block_size';
define spare_rows = 2;
select
' alter table '||owner||'.'||table_name||
' pctused '||least(round(100-
((&spare_rows*avg_row_len)/(&blksz/10))),95)||
' '||
' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
';'
from
Remote DBA_tables
where
avg_row_len > 1
and
avg_row_len < .5*&blksz
and
table_name not in
(select table_name from Remote DBA_tab_columns b
where
data_type in ('RAW','LONG RAW','BLOB','CLOB','NCLOB')
)
order by
owner,
table_name
;
spool off;
Now that we understand the table storage
parameters and their effect on performance, let’s talk about buffer
busy waits and see how they relate to object parameters.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
