Monday, August 07, 2006

A brief description about Oracle architecture

First I'd like to discuss about common terminology that helps us in understanding the architecture.
In Oracle we can describe storage mechanism in 2ways logical storage structures and physical storage structures storage consist of DATA FILES which are nothing but the (OS)files on hard disc.
Logical Storage:
'DataBlock' is the smellest unit of storage for I/O operations in oracle DB. Whose size is (obviously) greater than OS block and its multiple of OS block. its size can be configured and by default its 2048. A datablock consist of contiguous OS blocks.

' The next level of logical space unit is Extent. It consists of contiguous DataBlocks. Database allocates space in terms of extents when ever its required.

Is the next level of logical storage unit. A segment is a set of extents that are allocate d for a specific type of datastructure (that means data and indexes will get stored in different segments).

'TableSpace' is the top level unit of logical storage structure in Oracle database.The data of a database is logically stored in tablespaces and physically stored in datafiles that are associated to a tablespace. A database is comprised of 1 or more logical storage units called tablespace. By default every DB creates a tablespace called 'System' when the database is created.Each tablespace is consist of 1 or more physical(OS) files called datafiles.

At the time of table creation one can mention its tablespace. A database object (table, view etc) can span a datafile but can't a tablespace.

Anatomy of DB Block:
Irrespective of its content(data, index or clustured data) each database block consist of below parts.
1.Common and variable header 2.Table directory 3.Row directory 4. Free space 5. Row data

Header contains the block information like address of the block, type of segment(data, index or rollback). Please note part of the header size is fixed but total header size is variable.

Table directory the name it self explains, it contains table information which having rows in the block.(this shows a block can hold data related to more than 1 table). But please not that a datablock can hold rows of different tables only if they are clustered else a data block can't hold data related to 2 different tables.
Note: If a data block contains data for cluster(having 2 or more tables) then datablock free space is managed by the cluster storage clause not by individual table pct*** factors.

Row directory this part of the block contains the information(address of each row) about the rows in the block. Please note that space allocated in the row directory can't be reclaimed when the row is deleted (place where we need to think about High Water Mark)

RowData This portion contains actual data (table data or index).

Free Space is used to accommodate new rows(insert) or updates to existing rows. Based on PCTFREE factor row insertion will be decided(i.e a new row will occupy the same block or other block).

PCTFREE and PCTUSED are the 2 space management parameters that allow the user to control the use of free space in a block.

PCTFREE decides the %of the block to be kept free for future updates to rows. 'pctfree 10' says that 10% of every block of the segment that used for the table kept free for future updates on the table.And the other important point is as and when a blocks pctfree reaches specified limit it'll get out of the free list.
Data will be filled in the block by sparing the % specified in pctfree. Now we deleted some rows in the block.Question arises as "Now is the block is ready for new insertions?"(as it has some free space apart from pctfree). This will be answered by PCTUSE. The block can't be considered for new insertions until and unless its usage falls below the PCTUSE. viz if you say PCTUSE 50, and the block is filled fully(sparing space for pctfree). The block will be considered for insertions if and only if its usage falls below 50%.

How Oracle allocates Extents?
A segment in a database is created with at least one extent. This is called segment's initial extent. As and when this initial extent is full and the database object need more space to accommodate its data, Oracle allocates an extent(incremental) to the segment. But how?..
Oracle searches for free contiguous block of size(= size of required extent + 1(if its > 5 blocks)) in the same tablespace of the segment. Lets say if extent requires 10 blocks. It searches for 10+1 contiguous block and if it finds the same then it allocates that space as an extent to the segment. If the required extent size <= 5 then it just searches for required blocks (no +1). Note: Infact this additional block is added to address internal fragmentation. We can just remeber this as below. If(No of blocks required > 5)
No of searched blocks = No of blocks required + 1
No of searched blocks = No of blocks required
case 1.
If oracle didn't find the exact match but finds a larger no of contiguous blocks.It allocates blocks to the extent as below.
i. If 'No of contiguous blocks available' > 'No of searched blocks' + 5
Then it just allocates 'No of searched blocks' from available to the extent & hence to segment.
ii. If 'No of contiguous blocks available' <= 'No of searched blocks' + 5 Then it allocates all the block to the extent and then to segment. case 2. If Oracle does't find a larger set of contiguous data blocks, It then coalesces free space in the tablespace so that it results in the formation of larger contiguous data blocks. Once again oracle start searching as mentioned in case 1. if it fails to find then Oracle returns an error. Note: Infact SMON(System Monitor back ground Process) will periodically coalesces the free space to create larger chunks of contiguous blocks.

Some thing about SGA: System Global Area

*Please note that above information is just my understanding, by reading various article on net with some of my own assumptions. Pease let me know if I am wrong anywhere.