Primer on Structure

1
Primer on Structure Storage 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_at_innocent.com
2
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

3
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
Free Space
Row
4
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

5
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

6
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

7
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)

8
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

9
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

10
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

11
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 gtsame sized extents (as in
  rollback segment)

12
SEGMENT Types

• DATA segment
• INDEX segment
• ROLLBACK segment
• TEMP segment

13
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

14
TABLESPACE (Cont.)

• Size of a tablespace sum of the datafiles
• To increase ADD datafile
• ONLY way to remove DATAFILE ----gt DROP USER ...
• Can take ONLINE/OFFLINE (except SYSTEM)
• Create separate for each segment type

15
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

16
Syntax Create Tablespace

• CREATE TABLESPACE test DATAFILE
  /data/oracle/test1.dbf SIZE 100M REUSE DEFAULT
  STORAGE (INITIAL 500K NEXT 1024K MINEXTENTS 2
  MAXEXTENTS 50 PCTINCREASE 10) ltTEMPORARYgt

17
Add Datafile

• ALTER TABLESPACE RBS ADD DATAFILE
  /data/oracle/rbs4.dbf SIZE 100M

18
Resize Datafile

• ALTER DATABASE DATAFILE /oracle/datafile/test.dbf
  
• RESIZE 100m

19
Autoextend Datafile

• Alter database datafile
• /Data/oracle/test.dbf
• Autoextend on
• Next 100k
• ltMaxsize 10mgt
• ltUnlimitedgt

20
To See Size of Datafile

• Select file_name name,
• Sum(bytes)/1024/1024 size
• From sys.dba_data_files
• Group by file_name

21
Tablespace Size

• Select sum(bytes)/1024/1024 size,
• Tablespace_name
• From sys.dba_data_files
• Group by tablespace_name

22
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 -gt20B 6B 7B 43B
• Step 2 estimated rows -gt 10000
• Step 3 table size -gt 43 10000B 0.5 MB
• Step 4 yearly growth rate -gt 43 2000 100 KB
• Moral Always be conservative

23
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 501.5 501.51.5) 287.5
• Step 3 Size with MaxExtents (121 for 2K DB
  Block) Its a G.P. Series.
• S Sum of N terms A(RN -1)/(R-1)
• where A NEXT R (100PctIncrease)/100
• N (MAXEXTENTS - 1) gt
• Total Size ( S INITIAL)
• Moral Set PctIncrease Judiciously!

24
Estimate Table Size - Method 3

• Row/Data size from Analyzed table Info
• Step 1 ANALYZE TABLE EMP ESTIMATECOMPUTE
  STATISTICS
• Step 2 SELECT (NUM_ROWSAVG_ROW_LEN) FROM
  USER_TABLES WHERE TABLE_NAME EMP

25
To Check Extent Growth

• Select substr(owner . segment_name,1,25)
  object,
• Maxextents
• From sys.Dba_extents
• Where maxextents -extents lt 5
• Order by extents
• Will give you the segment which is approaching
  MAXEXTENTS