Primer on StructurePrimer on Structure & & Storage AnalysisStorage Analysis

This presentation is supposed to give a simple and brief overview for storage

calculations. THREE simple methods have been discussed for TABLE size calculation.

~ Nilendu Misra

nilendu@innocent.com

Data Block

Data block is the smallest unit logical space allocation

Smallest unit of I/O Data block size can ONLY be specified at

creation time Always multiple of OS block size To see check DB_BLOCK_SIZE in init.ora

Oracle Block

Data Block = Header + “Actual” data

Header contains ‘information’about the data

A row should ideally be contained within a single

block

Header Data

Row

Free Space

Extent & Segment

An extent is a specific number of contiguous data blocks

Several extents make one segment(e.G table, rollback segment)

All extents for the same segment are on the same tablespace

Rollback segment has ATLEAST 2 extents

Free Space Management : PCTFREE Mention while creating or altering segment FREELIST - list of blocks ready for insertion % of the block kept free for updates of rows

already in that block PCTFREE = 10 (default) means the block

(say 2K) allows row insertion till it is 80% full (1.8K). The rest (0.2K) is kept free

Pctused

When % of the block used falls below PCTUSED insertion is again allowed on the block

PCTUSED = 40 (default), in the previous example means when the block will contain 0.8K then again insertion will start

Why Consider these Parameters?

Disk I/O is the around 50 times slower than reading from memory (SGA)

We should prevent multiple I/Os while reading a record (e.g., Row Chaining)

This parameters put a big role in the total space consumed by the segment

Read-in blocks take up memory in SGA Defining depends on the application (e.g.,

UPDATE intensive)

CONT.

e.g., In UPDATE intensive system PCTFREE should be high to accommodate the INCREASE in length due to UPDATE

In SELECT intensive system (say DSS) PCTFREE should be low. (As no UPDATE)

A Table can be INSERTed / DELETEd upon frequently. So HIGH PCTUSED is better

Row chaining– Row is ‘spread across’ more than one block

Designing Parameters

Knowledge of application data. Knowledge of operations on individual Table

(particularly Large ones)

Specifically the frequency of UPDATEs, INSERTs, DELETEs has to be known

A row with LONG datatype normally ‘spreads across’ multiple data blocks

Row Chaining

When a row is stored over multiple blocks Because it does not get enough space on a

single block to fit in With large rows (with LONG,LONG RAW) I/O is badly hit for scanning multiple blocks

EXTENT Calculation

MAXEXTENTS depend on data block size For 2K block, MAXEXTENT = 121 When one extent is filled, NEXT extent is

allocated NEXT extent size = (current extent size) * (1 +

PCTINCREASE/100) PCTINCREASE = 0 =>same sized extents (as

in rollback segment)

SEGMENT Types

DATA segment INDEX segment ROLLBACK segment TEMP segment

Tablespace

Largest logical storage unit Has one or more datafile(s) where data is

actually stored DBA can CREATE/ALTER/DROP Associated with one or more user(s) Each user MUST have one DEFAULT &

one TEMPORARY tablespace

TABLESPACE (Cont.)

Size of a tablespace = sum of the datafiles To increase ADD datafile ONLY way to remove DATAFILE ---->

DROP USER ...; Can take ONLINE/OFFLINE (except

SYSTEM) Create separate for each segment type

Datafile

One datafile is associated with ONE & ONLY ONE tablespace

SIZE can be ALTERED after creation CANNOT be DROPPED on the fly For big database AUTOEXTEND ON MAX no/size of datafile is restricted by OS

Syntax : Create TablespaceCREATE TABLESPACE test

DATAFILE ‘/data/oracle/test1.dbf’ SIZE 100M REUSE DEFAULT STORAGE (INITIAL 500K NEXT 1024K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 10) <TEMPORARY>;

Add Datafile

ALTER TABLESPACE “RBS” ADD DATAFILE ‘/data/oracle/rbs4.dbf’ SIZE 100M;

Resize Datafile

ALTER DATABASE DATAFILE ‘/oracle/datafile/test.dbf’

RESIZE 100m;

Autoextend Datafile

Alter database datafile

‘/Data/oracle/test.dbf’

Autoextend on

Next 100k

<Maxsize 10m>

<Unlimited>;

To See Size of Datafile

Select file_name “name”,

Sum(bytes)/1024/1024 “size”

From sys.dba_data_files

Group by file_name;

Tablespace Size

Select sum(bytes)/1024/1024 “size”,

Tablespace_name

From sys.dba_data_files

Group by tablespace_name;

Estimate Table Size - Method 1 Row/Data size from Table Structure(DDL)

Table emp (name varchar2(30),

salary number (8,2),

join_date date);

Step 1 : each row size ->20B + 6B + 7B = 43B

Step 2 : estimated rows -> 10000

Step 3 : table size -> 43 * 10000B = 0.5 MB

Step 4 : yearly growth rate -> 43 * 2000 = 100 KB

Moral : Always be conservativeMoral : Always be conservative

Estimate Table Size - Method 2 From Avg Row Size of existing Data

Step 1 :Initial 40,Next 50,Extents 4,PctIncrease 50

Step 2 :Size : (90 + 50*1.5 + 50*1.5*1.5)= 287.5

Step 3 :Size with MaxExtents (121 for 2K DB Block) : It’s a G.P. Series.

S = Sum of N terms = [A(RN -1)/(R-1)]

where A = NEXT; R = (100+PctIncrease)/100;

N= (MAXEXTENTS - 1) =>

Total Size = ( S + INITIAL)

Moral : Set PctIncrease Judiciously!Moral : Set PctIncrease Judiciously!

Estimate Table Size - Method 3Row/Data size from Analyzed table Info

Step 1 :ANALYZE TABLE EMP ESTIMATE[COMPUTE] STATISTICS;

Step 2 :SELECT (NUM_ROWS*AVG_ROW_LEN) FROM USER_TABLES WHERE TABLE_NAME = ‘EMP’;

To Check Extent Growth

Select substr(owner || ‘.’|| segment_name,1,25) “object”,

Maxextents

From sys.Dba_extents

Where maxextents -extents <= 5

Order by extents; Will give you the segment which is

approaching MAXEXTENTS