存储属性优化.pptVIP

* * Guidelines for Setting the Values of PCTFREE and PCTUSED PCTFREE = 100 × UPD / (Average row length) PCTUSED = 100 – PCTFREE – 100 × Rows × (Average row length) / block size where: UPD = Average amount added by updates, in bytes. This is determined by subtracting the average row length of the insertcurrent average row length. Average row length = Get this value from the AVG_ROW_LEN column of DBA_TABLES, which you retrieve after running ANALYZE Rows = Number of rows to be deleted before free list maintenance occurs The formula for PCTUSED allows inserts into the table when there is enough space in the block for row updates and for at least one more row. PCTUSED is relevant only in tables that undergo deletes. In many tables you may be able to pack rows into blocks more tightly by setting PCTUSED to be higher than the default (40%). For tables with many inserts, changing PCTUSED may improve block storage performance. Blocks that are densely populated can cause free list contention, but fewer blocks are required, tables are smaller, and read operations are faster. Choose a value for PCTUSED in order to find a balance between those two different kinds of performance concerns. If you change PCTFREE and PCTUSED for existing tables, there is no immediate impact on blocks. However, future DML activity uses the new rules for tables. * * * * ANALYZE ... LIST CHAINED ROWS You can identify migrated and chained rows in a table or cluster by using the ANALYZE command with the LIST CHAINED ROWS option. This command collects information about each migrated or chained row and places this information into a specified output table. To create the table that holds the chained rows, execute the utlchain.sql script: create table CHAINED_ROWS ( owner_name varchar2(30), table_name varchar2(30), cluster_name varchar2(30), partition_name varchar2(30), head_rowid rowid, analyze_timestamp date ); If you create this table manually, it must have the same column names, data type