Transcript IS 4424: Developing the Entreprise Database

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
Block size
• Smallest unit of storage in 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
}
Approx. 256 b
• Big is beautiful? Percentage of overhead decreases
when block size increases, but no right answer here
2 K, 4K, 8K 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 also dictate
• Buffer size + now split in 3 areas:
– KEEP pool
– RECYCLE pool
– DEFAULT pool
Block size and row size
• Table with average row length = 2236 b
(10m records)
• Block size 4k = 4006 b => one row per
block
• 8 k => 3 rows per block
Opening and closing the DB
Startup open NAME;
Or
Startup mount NAME;
Alter database open;
Shutdown;
Else – use OEM menus
page846
When DB is started
1. System Global Area (SGA) created:
a) Data block buffer cache created
•
•
•
•
•
•
Size is key for performance of DB
fraction of whole DB
DB_BLOCK_BUFFERS / DB_BLOCK_SIZE
Least Recently Used (LRU) method
Too small means fight for space between objects
=> increase in misses => increase in I/O
Too big = crippling for other operations
When DB created (2)
b) Shared SQL Pool created:
•
•
•
•
•
Stores DD cache: info about data structure
Stores library cache: info about SQL statements
run
LRU algorithm
Too small => no re-use and low performance
SHARED_POOL_SIZE in init.ora
Parameter files: INIT.ORA
• Initialisation file for each DB
• MYDB = > initmydb.ora
• Contains sizing of SGA and number of
parameters, DB name and block size
• Read at startup means changes only
implemented at next startup
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
Data hierarchy in Oracle
• Data hierarchy in Oracle is specific (and demented!)
• 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 only
One file can support only ONE tablespace
See figure 3.1
About tablespaces
• Kind of partitioning
– Except fixed size
• Extended by dba
– Oracle very strong at TS level
• Optimal seems to be around 2GB
• Strategy = isolate aged data into one
tablespace => can be made read-only
• Should store similar objects together
Reasoning behind TS creation
•
•
•
•
•
•
•
Business relevance
Size of objects
Type of activity
Volume of activity
Backup
Transportability
Time-based changes in activity
P 148
Reasoning behind TS creation
•
•
•
•
•
•
•
Business relevance e.g. modular design / keep data togegther
Size of objects Object of similar sizes – reuse of space
Type of activity e.g. index VS table / full scan VS rowID
Volume of activity Low I/O VS high I/O objects
Backup TS is smallest unit for Bcup and restore
Transportability Cloning copy is done by TS as well
Time-based changes in activity
DW env. Aged archived system better – access time related
Bias towards more rather than less = > compromise
Also perf reading
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 20 30
10
40
10
50
10
60
10
70
10
80
10
90
10
100
10 10 12
10 20 32
14.4
46.4
17.2
63.6
20.7
84.3
25
109.3
30
139.3
36
175.3
42.3
218.5
Object creation
• 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
• see figure 20.3
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)
Sizing extents
• Try to match the max capacity of your storage
device to limit I/O operations
• 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
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
Mediumtab
7K
103K
7K
103K
20K
120K
Total size after growth smalltab = 40K instead of 14K
mediumtab = 206K instead of 240K
20K
120K
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
Constraints
• Candidate key:
Create table fred (
name
varchar2(10) NOT NULL
firstname
varchar2(10) NOT NULL
age
number
Constraint FRED_UQ unique (name, firstname)
);
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
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
Check constraint
Create table fred (
ID
number NOT NULL
name
varchar2(10)
firstname
varchar2(10)
age
number CHECK (age
between 18 and 65)
);
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
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 table…cluster fred (name)
What this cluster looks like
12 Cork
fred adam
13 Midleton Bob Fitz
14 Bally… Brian O’reilly
Table 1
Table 2
Cluster Key
French
Irish
English
Finally…
• Users can have different logical views on a
database
• These views are called…..?
• They allow for a re-organisation of physical
DB objects for each users
• Each ? can contain elements from different
tablespaces
• Has limited size allocated to each user
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 reusability 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
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 => ?????
Extent size should be multiple of 64K
example
•
•
•
•
Table with 10 extents 64 K each
I/O device buffer = 64K => 10 reads
If extent = 640K => ???? Reads
If 8 extents 80K each => ??? Reads
– Reads cannot span extents => 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
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