Data hierarchy in Oracle Data hierarchy in Oracle is

1
Data hierarchy in Oracle

• Data hierarchy in Oracle is specific (and
  demented!) see figure 3.1
• Normally, unit of storage is file
• Managed by OS
• Can grow / shrink
• Physical sections logically connected by OS
• In Oracle a DB one or more tablespaces
• One or more objects, indexes, clusters
• Fixed size gets full (Extended by DBA)
• Many admin functions at that level
• One file can support only ONE tablespace but
  reverse not true

2
Terminology

• File physical entity for storing data on disk
• Segment collection of extents for a particular
  object
• Extent unit of disk storage made up of
  contiguous blocks
• Blocks sizing of smallest piece of database data
  decided upon when db created
• (Block OS)

3
Create a tablespace

• Create tablespace FREDDY datafile
• path\freddy.dbf size 1000K
• Default storage (initial 10K next 10K
• Minextent 1 maxextents 100
• Pctincrease 0)
• Permanent

• 10 10 10 10 10 10 10 10 10 10
• 20 30 40 50 60 70 80 90 100
• 10 10 12 14.4 17.2 20.7 25 30 36 42.3
• 10 20 32 46.4 63.6 84.3 109.3 139.3 175.3 218.5

4
About tablespaces

• Kind of partitioning
• Easier to deal with small ones
• Inc. monitor perf.
• Oracle very strong at TS level
• Optimal seems to be around 2GB
• Strategy isolate aged data into one tablespace
  gt can be made read-only
• Should have same size extents
• Contiguous blocks

5
Reasoning behind TS creation

• Business relevance
• Size / type of objects
• Type of activity
• Volume of activity
• Backup
• Transportability
• Time-based changes in activity

6
Reasoning behind TS creation
P 148

• Business relevance
• Size / type of objects
• Type of activity
• Volume of activity
• Backup
• Transportability
• Time-based changes in activity

e.g. modular design / keep data together
Object of similar sizes reuse of space
e.g. index VS table / full scan VS rowID
Low I/O VS high I/O objects
TS is smallest unit for Bcup and restore
Cloning copy is done by TS as well
Eg DW versus OLTP systems
7
Block size

• Smallest unit of oracle DB
• Critical perf. Factor as inefficient space use
  leads to increase I/O activity
• Block overhead
• Integrity section (90 bytes)
• Runtime overhead (136 bytes)
• Transactional header (24 bytes and growing)
• Directory section (Pointers)
• And then..data
• Big is beautiful? Percentage of overhead
  decreases when block size increases, but no right
  answer here

Approx. 256 b
8
2 K, 4K or 16K?

• OLTP prefer smaller small granularity increase
  likelihood that data is in buffer (indexed
  access)
• DW or DSS prefer larger physically close data
  used together less movement
• In practice constraint on memory will dictate
• Buffer size now split in 3 areas
• KEEP pool
• RECYCLE pool
• DEFAULT pool

9
Then, there was a table

• Tables one physical space on disk segment
  same for any object
• Each segment space set out in tablespace
  initial extent
• When table grows allocate next extent set of
  adjacent blocks
• Until tablespace is full
• Look again at figure 20.3

10
What a segment looks like

• Each segment contains a header block
• Space directory for other blocks
• Properly sized tables few extents (low
  fragmentation)
• Additional extents are never lost until drop or
  delete commands are used
• Can also adjust pctfree value (to fill a Null
  value column)

11
Sizing extents

• Try to match the max capacity of your storage
  device to limit I/O operations
• eg minimal read size of OS (64K?)
• For single objects, It is beneficial to keep
  extents of same size (deletion)
• Try to limit the number of extents below 50
  (measure of the growth of the object)
• If too many, rebuild object with larger size
  extents

12
Roll back segments

• Special case of segment to hold the before image
  of DB while transactions take place
• Should be stored in separate TS because specific
  behaviour
• Cyclical rather than random access pattern
• Set of segments that gets written and written
  over
• Then written to disk
• Best used when Hot
• RB Segments grow to be big enough to hold
  complete transacs (cannot span segments)

13
Oracle space allocation

• DBA allocates space for table after 2 days of
  hard thinking
• Oracle ignores it blatantly as follows

Table name initial size next extent oracle
sizing next extent Smalltab 7K 7K 20K 20K Medi
umtab 103K 103K 120K 120K Total size after
growth smalltab 40K instead of 14K mediumtab
206K instead of 240K
14
Creating and using tables

• Table creation with CREATE TABLE statement
• Syntax
• Create table NAME (
• field1 datatype special constraint
• field2 datatype etc
• )
• Special constraint e.g. NOT NULL or PRIMARY KEY
  or CHECK

15
Constraints

• Candidate key
• Create table fred (
• name varchar2(10) NOT NULL
• firstname varchar2(10) NOT NULL
• age number
• Constraint FRED_UQ unique (name, firstname)
• )

16
Primary Key

• Create table fred (
• name varchar2(10)
• firstname varchar2(10)
• age number
• Constraint FRED_PK Primary Key (name, firstname)
• )
• Same apart always NOT NULL can only have one
  of them in a table
• If only one field just list primary key as
  special constraint

17
Foreign key aka referential integrity constraint

• Create table fred (
• ID number NOT NULL
• name varchar2(10)
• firstname varchar2(10)
• age number
• Constraint FRED_PK Primary Key (ID), foreign key
  (age) references OTHERTAB(age)
• )
• Use ON DELETE CASCADE to maintain constraint
• Can use DEFERRAL mode

18
Check constraint

• Create table fred (
• ID number NOT NULL
• name varchar2(10)
• firstname varchar2(10)
• age number CHECK (age between 18 and 65)
• )

19
Deleting data

• Drop table statement remove table
• Or truncate table statement empty it
• Warning neither can be rolled back
• Otherwise
• Alter table NAME Add ()
• Alter table NAME modify ()
• drop column fieldname
• See rules on handout

20
Now what? Clusters

• To store different tables in physically close
  areas on disk
• Create a cluster to store both objects
• Cluster key is any useful field that links the
  tables
• Can keep name of field or pick new one
• e.g. Create cluster fred (name datatype)
• then assign tables create tablecluster fred
  (name)

21
What this cluster looks like

• 12 Cork fred adam French
• Midleton Bob Fitz Irish
• Bally Brian Oreilly English

Table 1
Table 2
Cluster Key
22
Finally

• Users can have different logical views on a
  database
• These views are called Schemas
• They allow for a re-organisation of physical DB
  objects for each users
• Each schema can contain elements from different
  tablespaces
• Has limited size allocated to each user

23
Summary storage in Oracle

• Keywords tablespace / datafiles / database
  objects / segments / extents
• When you create an object
• Specify size / tablespace info storage clause
• Otherwise default values
• Pctincrease should be used with care (affects
  re-usability of reclaimed space i.e. extents)
• Wise use of tablespace eases admin (like
  partitioning)
• sizing follows sizing of device e.g. 2GB
• Many utilities to work with tablespaces
• Isolate processes e.g. a report running inside
  a TS
• Also for archiving (move to read only huge
  perf. gains)

24
I/O Performance issues

• Extent sizing a key
• Oracle has 2 ways to read data
• By rowID typically thru an index
• Full table scan
• In second case, oracle reads several blocks at a
  time
• Set with DB_FILE_MULTIBLOCK_READ_COUNT
• Limited only by I/O device buffer capacity
• E.g. block 4K, I/O buffer 64K gt ?????
• Extent size should be multiple of 64K

25
example

• Table with 10 extents 64 K each
• I/O device buffer 64K gt 10 reads
• If extent 640K gt ???? Reads
• If 8 extents 80K each gt ??? Reads
• Reads cannot span extents gt 16 reads or an
  increase of 60 in I/O operations
• Cl either use extents much larger than buffer or
  use multiple of buffer size

26
And for roll back segments

• Should be kept small enough
• Current wisdom is to ensure TS for rollback is
  normally 50 free
• Not always possible to use only one RB size
• For different types of processes e.g. TPS
• Use two different TB to isolate activites
• Easier trouble shooting
• Discarded rollback segments go into the log file

27
Data utilities

• ORACLE is king of data handling
• Export to transfer data between DBs
• Extract both table structure and data content
  into dump file
• Import corresponding facility
• SQLloader automatic import from a variety of
  file formats into DB files
• Needs a control file

28
Control files using SQLloader

• Data tranfers in and out of DB can be automated
  using the loader
• Create a data file with the data(!)
• Create a control file to guide the operation
• Load creates two files
• Log file
• bad transactions file
• Also a discard file if control file has selection
  criteria in it

29
Querying the Oracle DB

• 2 main methods
• Full scan TABLE ACCESS FULL
• Direct access TABLE ACCESS BY ROWID
• When spelling out queries drop HINT using
  syntax / HINT /
• This invokes the OPTIMISER
• RBO
• CBO

30
Example

• Accessing the worker field in the worker table
• By default full scan ( when no where statement)
• else
• Select / FULL(worker)/
• From worker
• Where lodging Rose Hill
• Select / ROWID(worker)/
• For low hit ratio
• need index or rowid value

31
Effect of Primary key constraints

• No index by default
• When primary key added gt index created
• Name given will be same as name of primary key
  (e.g. TABLE_PK)
• PK indexes are of UNIQUE kind
• Other non-unique indexes can be created on other
  fields
• CREATE INDEX name ON table(field) TABLESPACE
  indexes

32
Using the index

• Query with where clause on PK
• First the PK index is accessed in INDEX UNIQUE
  SCAN mode
• Matching Rowid is retrieved and returned
• Second access table with ROWID as argument in
  TABLE ACCESS BY ROWID mode
• When value sought is in index, no need to read
  table at all

33
Index Range Scan

• If query based on range of values / non-unique
  index
• E.g. field likeM
• More than one value returned gt IRS
• Less efficient
• Especially if values are not in index gt IRS
  followed by table rowid access
• Another reason why index creation important

34
Multiple index scans

• When more than one value in WHERE clause
• Two indexes can be used to produce two scans of
  range values
• Then concatenation of scans
• Result of the query
• Broadly similar to cross-reference of bitmap
  indexes

35
(No Transcript)
36
Database creation

• Using a script
• Connect internal as sysdba
• create database NAME.
• Using the OEM GUI
• Parameters stored in a small DB
• Access to other utility pgms eg RMAN
• Can accommodate several DBAs
• Key decisions on sizing
• Eg assign a block size once and for all

37
Opening and closing the DB

• Startup open NAME
• Or
• Startup mount NAME
• Alter database open
• Shutdown
• Else use OEM menus

38
When DB is started
page846

• System Global Area (SGA) created
• Data block buffer cache created
• Size is key for performance of DB
• typically 2 of whole DB
• DB_BLOCK_BUFFERS / DB_BLOCK_SIZE
• Least Recently Used (LRU) method
• Too small means fight for space between objects
  gt increase in misses gt increase in I/O
• Too big crippling for other operations

39
When DB is started (2)

• Shared SQL Pool created
• Stores DD cache info about data structure
• Stores library cache info about SQL statements
  run
• LRU algorithm
• Too small gt no re-use and low performance
• SHARED_POOL_SIZE in init.ora

40
Parameter files INIT.ORA

• Initialisation file for each DB
• MYDB gt initmydb.ora
• Contains sizing of SGA and number of background
  processes, DB name and block size
• Read at startup means changes only implemented at
  next startup

41
The control file

• Each DB also has a control file
• Contains Dbname, name and location of files and
  redo logs, timestamp of DB creation
• Can be mirrored for added security
• Identifies all files that must be opened when Db
  is loaded
• Also used in DB recovery

42
The REDO function
See page 873 add more stuff on backups

• Very Hot redo is located in buffer on-line redo
• Semi-Hot redo is kept in redo log files
  archived redo
• Limit to how much can reasonably be held
• So ORACLE organises hot backups of the DB
• Redo files up to that point can then be discarded
• Needs to run in ARCHIVELOG mode

43
Backups

• When DB is offline
• Datafiles
• Control file
• On-line redo
• Init.ora
• Stores a complete workable DB
• Does not work when DB is running
• Not to be trusted after abort shutdown

44
On-line backups

• When running in ARCHIVELOG mode
• 3 distinct redo log files
• When one full moves to next one
• After 3, overwrite first one
• Archiver background process backs up each redo
  file before overwriting it to disk
• Automatic recovery from on-line backup
• When DB started all failed transactions rerun or
  roll back
• Else, limited back up

45
Recovery manager

• Takes redo logs on by one and checks transactions
• Warning redo files cannot be skipped in the
  sequence of recovery
• Options
• Level 0 all blocks ever used
• Level 1 all blocks used since last full Bup
• Level 2 only those changed last Bup
• Huge impact on Bup performance
• See screen shot

46
Other functions

• Archiving
• Locking
• Logwriting
• Data writing
• Recovering
• Job queues
• System monitor
• Process monitor

47
After Oracle documentation
48
Database creation non-data objects

• DBs contain number of compulsory objects
• Create / assign tablespaces
• System tablespace
• Data dictionary
• Names and locations of all tablespaces, indexes
  and clusters of DB
• Files name / location (multiple instances)
• Rollback segments
• Data tablespaces are separate