Database Block Size
Minimize block visits by:
- Using a large block size
- Packing rows tightly
- Preventing row migration
Packing rows tightly与Preventing row migraion是一对矛盾体.如果packing rows tightly过量,就会导致row migration.
DB_BLOCK_SIZE Parameter
The database block size:
- Is defined by the DB_BLOCK_SIZE parameter.
- Is set when the database is created.
- Is the minimum I/O unit for data file reads.
- Is 2KB or 4KB default,but up to 64KB is allowed.
- Cannot be changed easily.
- Should be an integer multiple of the OS block size.
- Should be less than or equal to the OS I/O size.
SQL> show parameter DB_BLOCK_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
Small Block Sixze Pros and Cons
- Pros
- -Reduces block contention
- -Is good for small rows
- -Is good for random access
- Cons
- -Has a relatively overhead
- -Has a small number of rows per block
- -Can cause more index blocks to be read
Large Bolck Size Pros and Cons
- Pros
- -Less overhead
- -Good for sequential access
- -Good for very large rows
- -Better performance of index reads
- Cons
- -Increase block contention
- -Uses more space in the buffer cache
实际应用中如何决定块的大小使用,如何选择?
OLTP系统,建议使用小块,因为OLTP系统大并发;随机访问的比较多.(宜选择4K or 8K)
OLAP系统,建议使用大块,因为用户少,但是每一个用户使用的资源巨多.(宜选择8K,16,32K)
PCTFREE and PCTUSED
现在已经很少使用手工管理方式管理PCTFREE and PCTUSED参数了,基本上都是在使用自动管理方式.在之前使用手工方式管理的时候,PCTFREE and PCTUSED参数都需要指定,现在使用自动管理方式,只要设定PCTFREE一个参数即可.
Guidelines
- PCTFREE(recommend use it)
- -Default is 10
- -Zero if no UPDATE activity
- -PCTFREE = 100 * UPD / (Average row length)
- -Default is 40
- -Set if rows are deleted
- PCTUSED = 100 - PCTFREE - 100 * Rows * (Average row length) / Blcok size
Migration and Chaining
Chaining的发生,就是说一条记录太大,一块都无法放下一条记录,只能将此记录数据一分为几块.
Migration的发生,就是说一条记录,相对较大,将大出来一小部分给放到另外的块中.
Detecting Migration And Chaining
了解了Migration and Chaining情况的发生,都会增加I/O的读写次数,实际生产过程中,我们就要尽可能及时发现并且杜绝Migration and Chaining的发生.
任务:1、如何查看一张表中有多少记录发生了Chaining?
2、如何查看一张表中有多少记录发生了Migration?
- Use the ANALYZE command to detect migration and chaining
- Detect migration and chaining by using Statspack:
Selecting Migratied Rows
这个脚本的执行,需要事先执行一个脚本,创建一张表,表名称:chained_rows。
Eliminating Migrated Rows
如何消除那些migrated rows,下面提供三种方法,具体如下:
其中,前两种属于推到重建型,第三种属于小修小补型.
- Export/Import
- Export the table
- Drop or truncate the table
- Import the table
- Move table command
- Alter Table Employees Move
- Copying migrated rows
- Find migrated rows to new table
- Copy migrated rows to new table
- Delete migrated rows from original table.
- Copy rows from new table to orginal table.
问题:在前两种推到重建的方法中,表之前的约束关系是如何处理的?