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.

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.

 

 

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