- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- is the value of pctfree correct for oracle table?
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2004 04:48 AM
тАО12-29-2004 04:48 AM
thanks
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2004 10:30 AM
тАО12-29-2004 10:30 AM
Re: is the value of pctfree correct for oracle table?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2004 11:56 AM
тАО12-29-2004 11:56 AM
Re: is the value of pctfree correct for oracle table?
The setting of PCTFREE and PCTUSED depends on a variety of factors, including performance requirements, rowlengths and on the row insertion and update pattern. It only half the data exist on initial insertion then more room may be needed in pctfree while less could be needed for rows that are never updated.
You can query pct_free, pct_used, freelists, freelist_groups from dba_tables and will see that the pct_free set to the default of 10 or another value if specified. PCTFREE is valid for automatic/bitmapped free space management. It can still be used to reserve extra space in a block to prevent row migration.
It is best, once you have decided on the broad principles of what performance you want, and analyze the table several times over a period to see if you have migration taking place and to correct it, either by recreating the rows or by adjusting the parameters further. When a row is chained or migrated, performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for that row.
You don't want to have a situation where a block is taken off the freelist (ie. an insert would violate the pctfree value) whilst *under* the pctused value.
A high value for PCTFREE may improve performance because blocks have to be reorganized less frequently and chaining is also reduced. There is more space for growth of existing rows. A low value for PCTFREE may reduce performance since reorganization becomes more often and chaining would be increased.
Attached is a document of storage parameters PCTFREE and PCTUSED derived from metalink.
Indira A
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2004 03:19 PM
тАО12-29-2004 03:19 PM
Re: is the value of pctfree correct for oracle table?
Have a look at the attached procedure.
This will show you the allocated space for youe tables.
The output will be as follows:
exec show_space( 'T' )
Free Blocks.............................19
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5
hope this helps!
happy new year 2005!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2004 03:19 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2004 04:20 PM
тАО12-29-2004 04:20 PM
Re: is the value of pctfree correct for oracle table?
1. Reserves less room for updates to expand existing table rows
2. Allows inserts to fill the block more completely
3. May save space, because the total data for a table or index is stored in fewer blocks (more rows or entries for each block)
4. A small PCTFREE might be suitable, for example, for a segment that is rarely changed.
A larger PCTFREE has the following effects:
1. Reserves more room for future updates to existing table rows
2. May require more blocks for the same amount of inserted data (inserting fewer rows for each block)
3. May improve update performance, because Oracle does not need to chain row pieces as frequently, if ever
4. A large PCTFREE is suitable, for example, for segments that are frequently updated.
Check the views ALL_OBJECT_TABLES, DBA_OBJECT_TABLES, USER_OBJECT_TABLES for lot of valuabe information.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch289.htm#1297181
sks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-29-2004 09:58 PM
тАО12-29-2004 09:58 PM
Re: is the value of pctfree correct for oracle table?
As Indira said, to know that you must analyze if you have chaining on that table. To do that:
Check if you already have the SYSTEM.CHAINED_ROWS created. If not, run @$ORACLE_HOME/rdbms/admin/utlchain.sql
Then, analyze it:
SQL> analyze table
Finaly, check in chained_rows table for chaining:
SQL> select count(*), table_name from chained_rows group by table_name order by 1 desc
Note that you must consider the number of chained rows as a problem only if it is a high percentage of the table total rows...
If you have a high percentage, then you should increase pctfree from 10 to 15 for exemple. Note that this will only prevent future chaining: if you want me to tell you how to correct the present chained rows, tell me!
Regards,
Eric Antunes