Oracle Database Internals

1
Oracle Database Internals
2
Agenda

• Connecting To A Database
• What is a Database?
• SELECT Statement Processing

3
Connect to The Listener
Sql Net Driver
Sql Net Listener
Application Logic
connect
Local Tnsnames.ora
Directory Onames or Ldap
4
SQL Net Connection
Dispatcher Tcp/port
Driver (Request)
Dispatcher Ip/key
Sql Net Listener
Dispatcher other
5
The Listener Hand-off
Shadow Process
Listener Dispatcher
Sql Net Driver
(new) Dedicated
To Database
6
What Is A Database?
Shared Memory
Background Shadow Processes
Control File(s)
7
Disk-Memory Flow (for sharing)
Shadow Processes
Data Base Writer
(non-shareable) Data Files
8
Process (shadow background)
TYPE OS PID ORACLE_USERID COMMAND
PROGRAM ---- -----------
---------------- ---------------
----------------------- BKGD 334978
--------------- oracle_at_parsley
(DBW0) BKGD 337940
--------------- oracle_at_parsley (PMON) BKGD
338526 ---------------
oracle_at_parsley (CKPT) BKGD 339183
--------------- oracle_at_parsley (SMON) BKGD
339290 ---------------
oracle_at_parsley (RECO) BKGD 339318
--------------- oracle_at_parsley (ARC0) BKGD
339703 ---------------
oracle_at_parsley (LGWR) user 324343 tkcape
--------------- APT.EXE user 338498
apt_web SELECT ...
d\inetsrv\inetinfo.exe - - - - - user
339670 vidconf_user --------------- lt
VCWizard Proc No 1gt
9
An Oracle Environment
System Catalog
Redo Logs
Database Processes
Schema Data
DB Engine(s)
Rollback Segments
10
Shadow Receives a Message
SQL Engine
Shadow - Pga
SELECT
Step 1 Figure out what to do (seen this
before?)
11
Searches the SQL Area
(Shared Pool)
SQL Hash Q
SELECT ..
SQL Area
Pga
SELECT..
Plan
Library Cache
1) Hash Search 2) Exact Compare 3) Uses previous
Object (goto execute)
12
SQL Statements Count
select name, address from app.employee
select name, address from app.employee
select name, address from employee select
address from emp where name Emke, Larry
select address from emp where name Drake,
Rick select address from emp where name
EMP select sysdate from dual
select to_char(sysdate, dd-mon-yy) from dual
13
The Parsing Begins
System Catalog
Get Table and Column Definitions
Executes Recursive SQL
(Shared Pool)
The Optimizer
14
Read A Unique Row (Rowid)
Hash Page Chains
Cursor
Rowid
x
L
SCN
Data Block
Hash Header Blocks
Free Page List (LRU)
15
Row Id Format
(Relative) Data Block Address ( RDBA, DBA)
FFFF.BBBBBBBB Rowid (Two Formats) Old
BBBBBBBB.RRRR.FFFF (Local-Internal) UROWID
OOOO,FFFF,BBBBBBBB,SSSS (Remote) Note Old
rowid used exclusively for all internal
operations
16
Data Block Format
17
Deferred Buffer Clean-out
RBS Header
Data Buffer
Cache Layer
Cache Layer
Transaction Layer Fixed
Transaction Table
Transaction Layer Variable
Table Directory
Row Directory
Free Space
Row Data
18
Cache Layer
dba - Data Block Address csc - Read Consistent
SCN itc - No. of Transactions in this block
flag - Is block on the free-list type - data
or index type of block fsl - Free space
Lock fnx - Pointer to next block on free
list ver - format (version)
(Cache Layer is 20 bytes)
19
Transaction Layer
xid - Transaction Id uba - Undo Block
Address flag - State/status of the
transaction(slot) lck - number of rows locked in
block scn - change number for this transaction
Fixed
tran 2
initrans
tran 3
tran 4
Var.
tran 5
maxtrans
(24 bytes for each entry)
20
Data Layer
(14 bytes)
Table Directory (normally only 1 table)
Maximum of 178 rows per 2k block - 736 per 8k
block Maximum block size is 64k. (2-byte
row pointers).
os
os
os
Row 2
Column Data
hdr
Row 3
Row 1
21
Row Data Structure
Row Header
First Column of Data
Row Flag (1)
Lock byte (1)
of Cols (1)
Clstr Ky ix (1)
Column length (1 - 3)
Column Data

Maximum of 255 columns in a block row piece
Max in table is 1,000 (v8).
Length (1)
Short Data
0xFE (1)
Length field (2)
Long Data .
0xFF (null)
(A One Byte Length is 0 - 250)
22
Need Rollback
Hash Page Chains
Cursor
Rowid
x
L
SCN
Data Block
Hash Header Blocks
Free Page List (LRU)
23
Apply Undo Block Changes
(UBA) Segment
Cursor
SCN
Rowid
24
Undo Processing
Data Layer
Tx Layer
Cache Layer
Old SCN
New SCN
Undo Change vector
Logical Undo Data
Data
Data
Logical Undo
copy
change vectors
change vectors
25
Row transferred back to user
Temporary Tablespace
Array
Schema Data
(Share Pool)
SQL PL/Sql Java
Rollback Segments
Cols
Vars
Plan
Pl/Sql
26
Questions?
27
(No Transcript)
28
(No Transcript)
29
State Change Model
Data Layer
Tx Layer
Cache Layer
Old SCN
New SCN
Data blk
Data blk
copy
Change vector
Undo blk
Undo blk
Rbs Hdr
Rbs Hdr
change vectors
change vectors
Chg1
Chg2
Chg3
Redo records
30
Execution Begins (in general)
Temporary Tablespace
Array
Schema Data
(Share Pool)
SQL PL/Sql Java
Rollback Segments
Cols
Vars
Plan
Pl/Sql
31
Roll Back Segments (Undo Data)
Rollback Tablespace
rbs2
rbs3
rbs1
Our RBS
rbs1
rbs2
rbs3
Header
B1
B2

Transaction Table (assigns tx id)
32
Table Data Stored In Segments
File 1
Tablespace
A Table(Index) is a .
collection of segments A Segment is
. a collection of
. continuous blocks. A Segment
. is always in 1 data
file. A Table can span .
more than 1 data file. A Tablespace is
. a collection of data files.
2
1
3
Free List
File 2
4
6
5
33
Engine Context Switch
SQL
Plan
SELECT, INSERT, UPDATE, DELETE, EXEC, CREATE,
DROP, ALTER
PL/SQL
Execute a trigger , or a procedure or function
(sysdate, count, like, to_char) ltuser proceduregt,
ltuser functiongt, declare block, package_member.
Obj Mod
Java
Cls Mod
Invoke a Java class method (procedure or function)
34
PCTFREE and PCTUSED
Header
Header
Rows may be inserted again
Free Space
Rows can be inserted until free space falls
below PCTFREE
Free Space
Rows are only deleted or updated until
space USED falls below PCTUSED
RowData
RowData
35
The Conversation Begins
Application Logic
SQL Net Driver
Shadow Process
36
Basic Memory Structures
Shared Memory (SGA)
SQL PL/Sql Java
Data Buffer Pool
Shadow Process
Shared Pool
User Pga
Log Buffer
Qs CBs
37
Schema and Security
The Database
Userid/Schema
SYS
grant create session to ltuseridgt
38
SQL SELECT Requested
SELECT ltcol-1gt, . FROM lttable-1gt WHERE
Application Logic
SQL Net Driver
Shadow Process
39
The OS Down Under
Multi-Processing Paging OS
Process Run Queue
Memory Page (free) Queue
Device I/O Queues
(Unix / MVS / VMS / NT)
40
Two Roads Diverge
41
Update A Row Column
Consider UPDATE EMPLOYEE.PERSONAL_DATA
SET NAME Emke, Lawrence E.
WHERE NAME Emke, Lawrence
42
DBW0 - Database Writer
Log Buffer
Chg1
Chg2
Chg2

DB1
UD1
UH1
Dirty Page Q.
(in SCN order)
DBW0
43
DBW0 and LGWR
Log Buffer
DB1
UD1
UH1
Dirty Page Q.
(in SCN order)
DBW0
LGWR
44
Archive Log Processing
ARCH
LGWR
45
Some not covered

• Free space and Free lists (INSERT, DELETE)
• Logfile switches, Continuous Checkpoints
• Rollback, RBS Wrap, Shrinking the RBS, SSTO
• Crash restart processing (redo-undo processing)
• Forward Recovery Processing
• Indexes (all types), Constraints (RI), Sorts
• Optimizer, performance tuning, lock contention
• Java objects, marshalling, maps, directories,
  libraries
• Roles, privileges, data_types, Procs, Funs, Pkgs
• Initialization parameters, events, traces, dumps,
  catalog
• other object types, etc..