BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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.


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;

   ' alter table '||owner||'.'||table_name||
   ' pctused '||least(round(100-
   ' '||
   ' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||
   Remote DBA_tables
avg_row_len > 1
avg_row_len < .5*&blksz
table_name not in
 (select table_name from Remote DBA_tab_columns b
 data_type in ('RAW','LONG RAW','BLOB','CLOB','NCLOB')
order by

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.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter