Database Physical and logical structures
Block
Smallest storage unit called a block. Size can be {2k,4k,8k,16k & 32k} – Default 8k.
Standard block and Nonstandard block. Default is standard block. Db_block_size =8192. Once it’s configured we cannot change this.
Each block has pctused, pctfree & high water mark.
PCTused – For insert – 40%
PCTfree – For Update – 10%
High Water Mark – Separate the used & free space of block
Multiple block size (9i onwards)
You can configure the size of the block for the database.
Db_nk_cache_size = nk
Db_16k_cache_size =16k
Block utilization parameter:
================
Initran 1 Initial transaction
Maxtran 256 – maximum transaction
Pctused – inserted records
Pctfree – Updated records
================
Every block has block header 1% and pctfree 10% pctused 40%, The remaining 49% depends on “pctused”. If it’s <40% then it will use 49+40 =89%. Grather than 40% will not use.
Eg: If u keep pctused to 40% and pctfree 20.so u can insert rows till 40 %.if the limit exceeds 40%,still also u can insert rows in the datablock till the limit reaches 80% (100%-20%) as u have kept pctfree to 20%.Now if one goes on deleting the rows,the block is not said to be free unless and until pctused falls below 40%.As soon as pctused falls below 40% from deleting the rows, that block can be used to insert the rows.In this way the cycle continous.So it is recommended that u never sum up pctused+pctfree=100.Always have some gap between them this helps in reducing ur Oracle server for allocation and disallocation of freelists.
Block level problems:
Row chaining
Row migration
High water mark
http://www.akadia.com/services/ora_chained_rows.html
Row chaining
The insert of data will create this. When the data is not fitting into a single block oracle will use more blocks depends on the data type size.
Row Migration
When there is an update in the already allocated data, which has no space left in the block will move the row completely into somewhere, which create a forward address.
High Water mark
The high water mark level is just a line separate the used blocks and free blocks.
The blocks above the HWM level is free blocks, below is used one.
The delete command never resets the HWM level, truncate does.
Extent:
The huge continuous of block is called extend. Default Extent size for system is 64KB. By default oracle will create a system auto allocation variable extent. In case if we create a data block with 16K or higher by default will be 1MB. For uniform default is 1MB.
Default Storage parameter:
================
Minextent 1
Maxextent 256
Initran 100k – First extent
Next 200k – second extent size
Pctincrease 50% Next extent size {0% for uniform extent}
================
Oracle recommends the extent should be a uniform extent. Non-uniform will create a more fragmentation.
You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM).
If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE is the best choice. It’s also good if it is not important for over space allocation and deallocation.
If you want exact control over unused space, then UNIFORM is a good choice.
Formula for third extent size: Next + [next * pctincrease/ 100]
Ex: 200+(200*50/100) = 300 – Non-uniform extent
Ex: 200+(200*0/100) = 200 – Uniform extent
A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.
For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Ensure that each extent contains at least five database blocks, given the database block size. Temporary tablespaces that manage their extents locally can only use this type of allocation.
For system-managed extents, Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. If the tablespaces are created with ‘segment space management auto’, and if the database block size is 16K or higher, then Oracle manages segment size by creating extents with a minimum size of 1M. This is the default for permanent tablespaces.
Segment:
A segment is a set of extents that contains all the data for a table within a table space. A segment may or may not be contiguous on disk. The segments also can span more data files, but within table space.
Tables:
To store a data like SQL table.
