Oracle vs. DB2 Unix/Windows Examining Architectural Differences Jeff Mucher Sr. Software Consultant
[email protected] © 2001 Quest Software, Inc.
Agenda ! ! ! ! !
Basic Components & Terminology Architectural Layout Storage Management Logging and Backup/Recovery Options Miscellaneous Comparisons
© 2001 Quest Software, Inc.
Components of DBMS Oracle ! ! ! ! ! ! ! ! ! ! ! !
DB2 Unix/Windows
Instance File Database Tablespace Schema Table Index View Trigger Rollback Segment Stored Procs SQL Plus
! Roles
! ! ! ! ! ! ! ! ! ! ! ! !
© 2001 Quest Software, Inc.
Instance Container Database Tablespace Schema Table Index View Trigger NA Stored Procs DB2 CLP Groups ?
Data Types DB2 Unix/Windows
Oracle • • • • • • • • • • •
CHAR(N) NCHAR(N) VARCHAR2(N) NVARCHAR2(N) NUMBER(P,S) DATE RAW(N) BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID
• • • • • • • • • • • • • • • •
CHAR(n) VARCHAR(n) LONG VARCHAR CLOB GRAPHIC(n) VARGRAPHIC(n) LONG VARGRAPHIC DBLOB BLOB (n) SMALLINT, INTEGER, BIGINT DECIMAL(p,s), NUMERIC(p,s) REAL DOUBLE, FLOAT DATE TIME TIMESTAMP
© 2001 Quest Software, Inc.
Accessing/Mgmt DB Oracle
DB2
! OEM - GUI tool set – – – – –
DBA Mgmt Pack(free) Change Mgmt Pack Diagnostics Pack Tuning Pack Recovery Manager
! Sqlplus ! Svrmgrl
! Control Center GUI tool set – – – – –
Command center Command line processor Command window Script center Visual Explain
! DB2 Command Line
© 2001 Quest Software, Inc.
Product Options Oracle
DB2
! Personal Edition ! Standard Edition ! Enterprise Edition
! ! ! ! ! !
Everyplace Satellite Edition Personal Edition Workgroup Edition Enterprise Edition (EE) Enterprise Extended Edition (EEE)
© 2001 Quest Software, Inc.
Common Terms Different Meanings Oracle ! Stored Procedure – PL/SQL
DB2 ! Stored Procedure – External - C,Java,Cobol – SQL
! Package – grouping of PL/SQL blocks
! Package – Pre-compiled access plan
© 2001 Quest Software, Inc.
Different Terms Similar Meaning Oracle ! Data Block ! Dictionary ! Alert Log ! Redo Log ! Archive log ! Segments ! Statement Cache
DB2 ! Data Page ! Catalog ! Diag log ! Log Files ! Log Retain ! Space Consuming Objects ! Package Cache
© 2001 Quest Software, Inc.
Instance & Database Oracle
DB2
! Instance – Server ID – A collection of Processes – one active databases – Init.ora parameter file
! Instance – db2instance – A collection of Processes – one to many active databases – Instance (DBM) Configuration File – Data Base Configuration file
© 2001 Quest Software, Inc.
Instance Oracle SID Parameter file
D B M C O N F I G
Control files
Password file
Archived log files
Data files
DB2
Redo log files
Database
D B M C O N F I G
© 2001 Quest Software, Inc.
Instance_1 Catalog
PRODDB1
DBCONFIG
Log BP’s Catalog
PRODDB2
Log
DBCONFIG BP’s
Instance_2 Catalog
TESTDB1
DBCONFIG
Log BP’s Catalog Log
TESTDB2
DBCONFIG BP’s
Oracle Process Model
© 2001 Quest Software, Inc.
Oracle SGA
© 2001 Quest Software, Inc.
Spotlight on Oracle
© 2001 Quest Software, Inc.
DB2 Process Model F Fenced UDF Fenced DARI
I R E
Local Client Pgm
W
Per Connection Processes
db2gds db2wdog db2sysc
Remote Client Pgm
L
db2resyn
L
I/O Servers Bufferpool Page Cleaners
db2agent
db2ipccm
Remote Listeners Per Instance Processes
db2pfchr db2pclnr
db2agntp
db2agntp
db2agent
A
Per Database Processes
db2agntp
db2loggr Logger
db2dlock Deadlock Detector
Backup/Restore
db2agntp
Agent Pool
© 2001 Quest Software, Inc.
Media Writers
Buffer Manipulators
DB2 Memory Database Global Memory Utility Heap (util_heap_sz) backbufsz restbufsz
Buffer Pools (b u f f p a g e) Extended Memory Cache(estore_seg_sz)
Locklist(locklist) Shared Sort Memory
dbheap logbufsz catalog cache_sz pckcachesz
Application Global Memory App_ctl_heap_sz
Local client
Agent Private Memory
Agent / Application Shared Memory
aslheapsz
applheapsz Query heap sz
stmtheap sortheap
stat_heap_sz agent_stack_sz rqrioblk udf_mem_sz
drda_heap_sz
© 2001 Quest Software, Inc.
Remote client Application Shared Memory
rqrioblk
Spotlight on DB2
© 2001 Quest Software, Inc.
System Dictionary/Catalog Oracle
DB2
! Base tables
! SYSIBM.xxxx ! SYSCAT
– SYS.xxxxx$
! System views – – – – –
SYS.GV_$ or GV$ SYS.V_$ or V$ ALL_ DBA_ USER_
– Read-only views defined for catalog base tables
! SYSSTAT
© 2001 Quest Software, Inc.
– Updateable set of views – Primarily used for access path manipulation
Parallelism 9i Real Application Cluster (OPS)
DB2 Enterprise Extended Edition Fast Communication Manager
Log
CPU1
CPU2
CPU3
CPU4
DB Part 0
DB Part 1
DB Part 2
DB Part 3
Data
Log
Data
Log
© 2001 Quest Software, Inc.
Data
Log
Data
Types of Parallelism Oracle ! I/O ! CPU ! Cluster
DB2 EE ! I/O ! CPU – Intra-partition
– Intra-cluster
DB2 EEE ! I/O ! CPU –Intra-partition –Inter-partition
– Inter-cluster
! Parallel Query Some options only on partitioned objects
© 2001 Quest Software, Inc.
Bufferpools Oracle ! 3 bufferpools available – 8i one fixed block (8k) – 9i multi block size
! Defined in init.ora DB_BLOCK_BUFFERS = 20000 DB_BLOCK_LRU_LATCHES = 6 BUFFER_POOL_KEEP = (BUFFERS:14000, LRU_LATCHES:1) BUFFER_POOL_RECYCLE = (BUFFERS:2000, LRU_LATCHES:3)
DB2 ! Defined within a database 4,8,16,32K page ! Main or extended storage ! Defined via DDL ! Tablespaces assigned
! Objects assigned via DDL © 2001 Quest Software, Inc.
Databases Oracle
DB2
A structured collection and management of information. ! May have more than one for a SID ! Only one DB mounted/opened
! Logical grouping of DB2 objects ! Typically one or two database/instance ! Catalog for each database defined within database • SYSCATSPACE • TEMPSPACE • USERSPACE ! Database configuration file
!
© 2001 Quest Software, Inc.
Storage Management
DB2
Oracle !
! Container
Files – – – –
– – – –
Cooked Raw Resize Auto extend
© 2001 Quest Software, Inc.
Device Directory File Resize
Oracle Storage Hierarchy Database
Tablespace
Logical Segment
Data file
Physical
Extent Oracle block © 2001 Quest Software, Inc.
O/S block
Oracle Data Files ALTER ALTER TABLESPACE TABLESPACE app_data app_data ADD ADD DATAFILE DATAFILE ‘/DISK6/app04.dbf’ ‘/DISK6/app04.dbf’ SIZE SIZE 200M 200M AUTOEXTEND AUTOEXTEND ON ON NEXT NEXT 10M 10M MAXSIZE MAXSIZE 500M; 500M; Tablespace APP_ DATA app01.dbf 1M
app02.dbf 1M
app03.dbf 2M
© 2001 Quest Software, Inc.
app04.dbf 2M
DB2 Containers ! Physical storage device ! A container is assigned to an individual tablespace ! A TS may have > 1 container
SMS Directory
– Directory name
/db/payroll/tbsp1/cont
• SMS Only • D:\MYTS
– Raw Device
File
• DMS Only • E:
– File name • DMS Only • D:\SODADB\SODA.UTILITY.DMS © 2001 Quest Software, Inc.
DMS
Raw Device
Tablespaces !
Oracle –
Two Categories • •
–
Two types of Non-System • •
–
System tablespace Non-System tablespace Permanent Temporary
Two types of tablespaces extent management • •
Dictionary mapped Locally managed © 2001 Quest Software, Inc.
System & Non-System ! System Tablespace contains: – Data dictionary information – System rollback segment
! Non-SYSTEM Tablespace contains: – – – –
Rollback segments Temporary segments Application data Application indexes
© 2001 Quest Software, Inc.
Tablespace Storage Settings Oracle DB2 – INITIAL – PAGESIZE – NEXT – EXTENTSIZE – MINEXTENTS – PREFETCHSIZE – MAXEXTENTS – PCTINCREASE – FREELIST & GROUPS
© 2001 Quest Software, Inc.
Tablespaces ! DB2 – Two Categories of Tablespace Management • SMS – System Managed Space • DMS – Database Managed Space
– Three Types of Tablespace • Regular SMS & DMS • Temporary (System & User) SMS & DMS • Long DMS
– Three Space Allocation Methods • Directory – SMS • File – DMS • Device - DMS
© 2001 Quest Software, Inc.
Tablespaces SMS DMS
! System Managed – No finite storage specified • O/S’s file manager allocates space as needed • Good for small tables
! Database Managed – Space is pre-allocated – Better suited for large tables – Faster I/O
Add Containers to TS
X
Separate Indexes from Data
X
Space allocated as Needed
X
High performance in heavy OLTP
X
High performance in decision support
X
Ease of administration for small tables
X
X
Flexibility of Administration
X
File or Device containers
X
© 2001 Quest Software, Inc.
Partitioning Oracle ! Table Definition – 1-64k partitions – Partitioning on range, hash, both or list – Partitions assigned to TS
! Enhances Parallel Query ! Easier Data Management
EEE Only ! Enhances Parallelism ! Easier Data Management ! Nodegroup controls number of partitions ! Hash key dictates partition selection within Nodegroup ! TS assigned to Nodegroup
© 2001 Quest Software, Inc.
DB2 NODEGROUP ! A defined set of Data Partitions ! A tablespace exists within a Nodegroup – More than 1 tablespace can be in a Nodegroup – Rows are distributed across partitions of Nodegroup • Partitioning Map controls data placement – Hash function places rows on a given partition
• Data should be evenly distributed across Partitions in Nodegroup
© 2001 Quest Software, Inc.
Tables ! Oracle – One to many tables defined in tablespaces • Tables and Indexes are independent of each other
– Index Organized Tables
! DB2 – One to many tables can be defined within a tablespace • Indexes directly tied to table definition
© 2001 Quest Software, Inc.
Indexes ! Oracle – – – – –
! DB2
Unique Non-unique Partitioning Function based Bit Map
© 2001 Quest Software, Inc.
– Unique – Non-unique – Clustering
Oracle B-Tree Index Index entry Root
Branch
Index entry header Key column length Key column value ROWID
Leaf
© 2001 Quest Software, Inc.
Oracle Bitmap Index Table
File 3 Block 10 Block 11 Block 12
Index
key
end start ROWID ROWID
bitmap
© 2001 Quest Software, Inc.
Comparing B-Tree and Bitmap Indexes B-tree
Bitmap
Suitable for high-cardinality columns
Suitable for low-cardinality columns
Updates on keys relatively inexpensive
Updates to key columns very expensive
Inefficient for queries using OR predicates
Efficient for queries using OR predicates
Useful for OLTP
Useful for DSS
© 2001 Quest Software, Inc.
Indexes ! DB2 – Index placement is dependent on table definition. Tablespace must be specified when table created. • All indexes for a table use same tablespace • Tablespace is predefined before indexes are created • Indexes can be defined in same tablespace as table
© 2001 Quest Software, Inc.
DB2 Index Structure B-Tree with bi-directional pointers at Leaf Node Facilitates reverse scans
Root Node ‘E’ ‘N’ ‘Z’ Intermediate Nodes ‘F’ ‘L’ ‘N’
(‘F’,rid)
(‘G’,rid) (‘I’,rid) (‘K’,rid)
Data Pages © 2001 Quest Software, Inc.
(‘M’,rid) (‘N’,rid)
Leaf Nodes
Logging ORACLE
DB2
! Redo Logs apply to Instance
! Defined at database
– NoArchive Mode • Full DB recovery
– Archive Mode • Recover DB, TS, DF • 3 Redo log states – Active – Inactive – Archived
! Redo Logs created in – Groups – Number of logs – Members of groups (mirrors) © 2001 Quest Software, Inc.
– Circular • No roll-forward recovery
– Log Retain • Fully recoverable • Dual Logs (7.2) • 3 log file states – Active – Online Archived – Offline Archived
• User exit called for Archiving
Redo Log Groups & Members
Group 1 Member
Member
Group 2
Group 3
Member
Member
Member
Member
© 2001 Quest Software, Inc.
Disk 1
Disk 2
Oracle Archive Logging Instance SGA Database buffer cache
DBWR
Redo log buffer
CKPT
LGWR
ARCH
Control files Parameter file Password file
Redo log files
Data files
Database © 2001 Quest Software, Inc.
Archived log files
Circular Logging DB2
! ! ! !
Crash Recovery only Log files are reused Secondary as needed Default method
1 “n”
Primary
2
1
3 “n”
© 2001 Quest Software, Inc.
S E C O N D A R Y
Log Retain ! Log files not reused ! Roll Forward Recovery
Online Archival Contains information for committed and externalized transactions. Stored in the active log subdirectory
12 13
14 Offline Archival Files moved from active Log subdirectory. Usually offline media
15 Active – Contains information For non-committed or Non-externalized Transactions.
© 2001 Quest Software, Inc.
16
Backups Oracle
DB2
! Database – Cold – Offline – OS level • • • •
All datafiles All control files All online redo log files The init.ora file
! Database Online/Offline ! Tablespace ! Components
! Tablespace – Hot – Online – OS level • • • •
Archive log list Alter tablespace abc begin backup; Alter system switch logfile; Alter database backup controlfile to 'file_name';
– – – – –
! RMAN – backup/recovery utility ! Export Database – Logical ! Standby Database © 2001 Quest Software, Inc.
Full/Incremental/Delta Copy DB Configuration Tablespace definitions Backup History File Active/Archive Logs
Recovery Info DB2
Oracle ! Control File
Recovery History File ! Updated:
• Database name • Data file location • Redo log file location • Tablespace names • Current log sequence number • Checkpoint information • Log history • Backup information
! RMAN - Recovery Catalog
– – – –
Backup of DB/TS Restore/Roll Forward of DB/TS Drop/Load/Reorg/Stats of a table Quiesce/Alter TS
! Contains – – – – –
© 2001 Quest Software, Inc.
Part of DB which was copied When DB was copied Location of the copy Time of last restore DDL of tables
Types of Recovery Oracle
DB2 ! Crash
! Instance Recovery – Uses Redo logs to recover from System/Instance failures
! NoArchive Mode – Full Database (cold)
! Archive Mode – Point in time – Automatic
– Uses logs to recover from System/Instance failures
! Version (non-recoverable DB) – Image copy (TOCOPY) ! Roll-Forward – Image copy plus log apply
! Import/Load (logical)
! Import (logical) © 2001 Quest Software, Inc.
Utilities Oracle
DB2
! IMPORT ! EXPORT ! SQL*LOADER ! ANALYZE ! DB_VERIFY ! RMAN
! ! ! ! ! ! ! !
© 2001 Quest Software, Inc.
BACKUP IMPORT EXPORT LOAD RESTORE REORG (Table) REORGCHK RUNSTATS
Reorganizing Data Oracle ! Import ! Export
DB2 ! Table only within DB ! REORGCHK – Determines when Reorg is required
© 2001 Quest Software, Inc.
Loading Data DB2
Oracle
! Load ! SQL*Loader Utility – Insert/Replace – Insert/Append/Replace/Truncate – RUNSTATS – Direct=True|False – Usually faster than import • By-pass SGA build data block • No Triggers • No RI
– Good for large amounts of data – No Triggers/RI
! Import
– Parallel=True|False • Multiple load files • Drop all Indexes
– Recoverable/Unrecoverable © 2001 Quest Software, Inc.
– Can dynamically create table – Insert process • Update • Replace
– Good for small amounts of data
DB2 Import vs. Load IMPORT
LOAD
Creation of table & IX’s supported with IXF
Table & indexes must exist
WSF format support
WSF format not supported
Can import into tables, views, and aliases
Can import into tables only
Tablespace(s) containing table and indexes are online for duration of import
Tablespace(s) containing table and indexes are offline for duration of load
Triggers will be fired
Triggers are not supported
All constraints are validated during import
Unique constraints are validated during load. All other constraints must be checked by the Set Constraints command
Keys of each row are inserted into the index one at a time during import
Indexes are built after the data has been loaded and keys sorted
Up to date statistics are required, RUNSTATS needs to be executed
Statistics can be gathered during the load
Ability to import into host databases with Connect
Cannot load into host databases. Load utility not usable with Connect
© 2001 Quest Software, Inc.
Optimizer Oracle ! Rules - Hints ! Cost based ! Choose
DB2 ! Cost based more sophisticated than Oracle – Seven levels of optimization – Adjusted based on query complexity
! No Rules
© 2001 Quest Software, Inc.
Optimization Class Guidelines
Level 0 1 2
Recommendation Minimal amount of optimization. Only recommended for very simple SQL accessing well indexed tables. Only nested loop joins and IX scans enabled. Similar to 0 except Merge Scan and TS scan enabled. Recommended for very complex queries which are infrequently executed in a decision support or OLAP environment.
3
Closest to Oracle optimizer. Recommended for queries with 4 or more joins.
5
DEFAULT – Most cost effective method for mix of simple and complex queries. Optimization will be automatically reduced for complex dynamic SQL if optimizer determines that the resources are not necessary.
7
Same as 5 except optimization not reduced for complex dynamic SQL
9
Used to determine whether more comprehensive optimization can generate better access plan for very complex long running queries using large tables © 2001 Quest Software, Inc.
EXPLAIN DB2
Oracle
EXPLAIN_INSTANCE PLAN_TABLE EXPLAIN_STATEMENT
EXPLAIN_OPERATOR
EXPLAIN_PREDICATE
EXPLAIN_STREAM
EXPLAIN_OBJECT
© 2001 Quest Software, Inc.
EXPLAIN_ARGUMENT
Monitoring Oracle
DB2
! Performance Snapshots - V$ – – – – –
Instance/Database Memory Disk User/Session Contention
! Snapshot Monitor – Show status of database counters at instant in time – Buff, Lock, Sort, Stmt, Tbl, UOW ! Event Monitor – Status after the event
! UTLBSTAT and UTLESTAT – Collects Stats over time
! TKPROF – Formats SQL trace data from applications
© 2001 Quest Software, Inc.
• • • • • • •
Databases Tablespaces Connections Tables Statements Transactions Deadlocks
Summary To be a successful cross platform DBA: ! Have a sound foundation of relational principles ! Understand the nuances of the individual platforms ! Understand that the principles of database administration are similar regardless of the RDMS
© 2001 Quest Software, Inc.
© 2001 Quest Software, Inc.
