01-01 三大数据库概念差异(65 页).ppt

1、Oracle-DB2-MS SQL使用与管理比较,赵元杰 Mob2011.9,2020/7/16,2/65,Big Dogs in the Yard,Oracles 10g/11g IBMs DB2 MSs SQL Server Sybases ASE(SAP),2020/7/16,3/65,内容提要(1),产品名称差别 数据类型差别 访问数据库方法差别 存储过程术语差别 术语类似比较 实例、服务器及数据库 Oracle 实例 Oracle 处理模式 DB2 实例 DB2 处理模式,2020/7/16,4/65,内容提要(2),数据字典差别 并行类型差别 缓冲区与缓冲

2、池 Oracle缓冲区 DB2缓冲池 数据库 Oracle存储层次结构 DB2存储层次结构 表空间类似 表空间设置 Oracle数据文件 DB2包容器(Containers),2020/7/16,5/65,内容提要(3),分区差别 表比较 索引比较 Oracle B*Tree 索引 Oracle Bitmap索引 DB2索引 DB2索引结构 日志差别 Oracle 日志组 Oracle 归档日志 Oracle 回滚段 Oracle 读的一致性 DB2循环日志 DB2日志保留,2020/7/16,6/65,内容提要(4),备份方法差别 恢复类型比较 实用程序比较 优化器比较 设计方面差别分析 管

3、理方面差别分析 故障解决的差别 小结,2020/7/16,7/65,产品名称差别,Oracle Instance File Database Tablespace Schema Table Index View Trigger Redo Log Rollback Segment Stored Procs SQL Plus Roles,DB2 Instance Container Database Tablespace Schema Table Index View Trigger Log N/A(表示无对应) Stored Procs DB2 CLP Groups,MS SQL Instanc

4、e/Server Files/File Groups Database NA(类似数据库) Owner Table Index View Trigger Transaction Log NA Stored Procs SQL Query Analyzer Roles,2020/7/16,8/65,DB数据类型差别,Oracle CHAR(N) NCHAR(N) VARCHAR2(N) NVARCHAR2(N) NUMBER(P,S) DATE TIMESTAMP RAW(N) BLOB, CLOB, NCLOB, BFILE LONG, LONG RAW ROWID,DB2 Unix/Win

5、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,MS SQL CHAR(n) NCHAR(n) VARCHAR(n) NVARCHAR TEXT NTEXT IMAGE(n) BIT BINARY VARBINARY TINYINT,SMALLINT,INT, BIGINT D

6、ECIMAL(p,s) FLOAT REAL NUMERIC MONEY, SMALLMONEY SMALLDATETIME, DATETIME TIMESTAMP SQL_VARIANT UNIQUEIDENTIFIER,2020/7/16,9/65,访问数据库差别,Oracle Enterprise Manager GUI SQL*Plus iSQL*Plus http/https 10g SQL Developer,DB2 Unix/Win Control Center GUI Command center Command line processor Command window Sc

7、ript center Visual Explain,MS SQL Enterprise Mgr. GUI Profiler Query Analyzer Isql,2020/7/16,10/65,存储过程术语差别,Oracle 存储过程 PL/SQL写成 函数 PL/SQL写成 包(Package) 一组 PL/SQL 块 9i/10g 本地编译 可将PL/SQL代码编译C代码,使其运行更快,DB2 UDB 存储过程 外部-C,Java,Cobol写成 SQL Package 预编译访问计划,MS SQL 存储过程 T-SQL 包 数据转换定义对象,2020/7/16,11/65,数据库结构

8、的差别,Oracle Data Block Dictionary Alert Log Redo Log Archive log Segments Statement Cache,DB2 UDB Data Page Catalog Diag log Log Files Log Retain Tables, IX Package Cache,MS SQL Page Data Dictionary Error log Transaction Log Transaction Log Backup Tables, IX Procedure Cache,2020/7/16,12/65,实例,服务器及数据库

9、差别,Oracle Instance-Server ID 一组进程 一个活动数据库 Init.ora parameter file(8i) SPFILEsid.ora (9i/10g) (服务器初始化参数文件),DB2 UDB Instance-DB2 实例 一组进程 一个或多个活动数据库 实例配置文件 (DBM) 数据库配置文件 DB2SET 注册变量,MS SQL Instance/Server 一组进程 一个或多个活动数据库 配置对象 Config Values Parameters,2020/7/16,13/65,Oracle 实例,2020/7/16,14/65,Oracle 处理模

10、式,2020/7/16,15/65,DB2实例,2020/7/16,16/65,DB2 处理模式,2020/7/16,17/65,SQL Server/Instance,D B M C O N F I G,Master DB,Model DB,MSDB,Temp DB,ProdDB1,ProdDB2,ProdDB3,2020/7/16,18/65,系统数据字典/目录,基本表 SYS.xxxxx$ 系统视图 SYS.GV_$ or GV$ SYS.V_$ or V$ ALL_ DBA_ USER_,SYSIBM.xxxx SYSCAT 只读的数据表 SYSSTAT 可更新的视图 用于访问路径维护

11、,Oracle,DB2,Master DB System tables 索引数据库超集 数据字典表 dbo.sysxxxxx 每个 DB都有 信息模式视图,MS SQL,2020/7/16,19/65,并行类型,Oracle I/O CPU Cluster Intra-cluster Inter-cluster Parallel Query,DB2 EE/EEE I/O CPU Intra-partition Inter-partition Parallel Recovery Parallel Backup,有的选件仅对分区对象有效,SQL Server I/O CPU Parallel Qu

12、ery Parallel Backup,2020/7/16,20/65,缓冲池/缓冲区,Oracle 缓冲区的可用性 8i 块大小(8k-安装) 9i/10g 多个块大小 在初始化参数中确定 8i DB_BLOCK_BUFFERS 9i以上DB_CACHE_SIZE DB_BLOCK_LRU_LATCHES BUFFER_POOL_KEEP= (BUFFERS:14000, LRU_LATCHES:1) BUFFER_POOL_RECYCLE= (BUFFERS:2000, LRU_LATCHES:3) 对象由 DDL 指定,DB2 定义为 4,8,16,32K 为一个页(Page) 主要或扩

13、展存储 对象由 DDL 指定 表空间分配,MS SQL 实例内定义 由 SQL Server动态分配 可 sp_configure修改 可手工配置 min, max 与工作集,2020/7/16,21/65,数据库(Database),Oracle 一组结构集合与信息的管理 可以有一个或多个 SID 同时只有一个 DB 安装或打开(mounted/opened),DB2 对象逻辑组 一个或多个数据库或实例( database/instance) 每个数据库都有自己的目录: SYSCATSPACE TEMPSPACE USERSPACE 数据库配置文件,MS SQL 对象逻辑组 每个实例有一个或

14、多个 在数据库内定义的目录 主数据库与模式库 配置对象,2020/7/16,22/65,Oracle 存储层次,2020/7/16,23/65,DB2 存储层次,2020/7/16,24/65,MS SQL存储层次,2020/7/16,25/65,表空间(Oracle),Oracle 表空间 两种类型 系统表空间 非系统表空间 非系统表空间分为 永久 (Permanent)表空间 临时 (Temporary)表空间 表空间有两种扩展方式 字典映射(Dictionary mapped) 本地管理(Locally managed),2020/7/16,26/65,系统,Tablespace APP

15、_ DATA,app04.dbf 2M,app03.dbf2M,app01.dbf1M,app02.dbf1M,Oracle 数据文件,2020/7/16,30/65,DB2 包容器,物理存储设备 一个包容器分配给一个独立的表空间 一个表空间可以有 1包容器 Directory name SMS Only D:MYTS Raw Device DMS Only E: File name DMS Only D:SODADBSODA.UTILITY.DMS,SMS,DMS,Directory,/db/payroll/tbsp1/cont,File,Raw Device,2020/7/16,31/65

16、,MS SQL 数据文件,Filegroup/Filegroup2 文件的集合 Primary Data Files Secondary Data Files Log Data Files 在一个数据库中使用 每个数据库有多个文件组,2020/7/16,32/65,数据库结构-存储结构,数据库存储覆盖物理结构与逻辑结构 物理结构包括数据文件、日志文件等 逻辑结构是用于管理的存储划分,2020/7/16,33/65,数据库结构-存储块,Variable,Fixed,2020/7/16,34/65,分区(Partitioning),Oracle 表定义 1-64k 的分区数 range, hash

17、, list 等 分区指定表空间 提高并行查询性能 容易管理,DB2 UDB EEE 增强并行 易于数据管理 节点控制分区数 Hash key 指示分区数 表空间分配给节点组,MS SQL 联合服务器 分区视图 分布的分区视图 通过键值来分布 每个服务器独立管理,2020/7/16,35/65,表,Oracle 在一个表空间中定义一个或多个表 表和索引彼此分开 索引结构表(Index Organized Tables) DB2 在一个表空间中定义一个或多个表 索引直接定义在表上 定义主键使索引自动创建 MS SQL 数据库内可定义一个到多个表 唯一列或主键自动创建索引 一个表可创建多达 249

18、非集群索引( non-clustered ),2020/7/16,36/65,索引,Oracle Unique Non-unique Partitioning Function based Bit Map Indexed Organized Table (IOT),DB2 Unique Non-unique Clustering,MS SQL Unique Clustering 类似 Oracle IOT Non-Clustering,2020/7/16,37/65,Index entry header,Key column length,Key column value,ROWID,Root

19、,Branch,Leaf,Index entry,Oracle B树索引(B-Tree Index),2020/7/16,38/65,Oracle 位图索引(Bitmap Index),2020/7/16,39/65,索引,DB2 索引的存放与表在一起. 表创建时表空间必须先建立好 表的所有索引存放在同一个表空间 表空间在创建索引前先创建完成 索引可以与表在同一个表空间,2020/7/16,40/65,DB2 索引结构,2020/7/16,41/65,MS SQL索引结构,2020/7/16,42/65,日志(Logging),重做日志用于实例 NoArchive Mode Full DB r

20、ecovery Archive Mode Recover DB, TS, DF, block 3 Redo log states Active Inactive Archived Redo Logs 创建 Groups 组数量 组成员数 (mirrors) Rollback Segments,在数据库定义 Circular No roll-forward recovery Log Retain Fully recoverable Dual Logs (7.2) 3 log file states Active Online Archived Offline Archived User exit

21、 called for Archiving,Oracle,DB2,在数据库中定义 事务日志文件 DB 备份 事务日志备份 清除日志,MS SQL,2020/7/16,43/65,Oracle日志文件组与成员,2020/7/16,44/65,Oracle 归档日志,2020/7/16,45/65,Update transaction,Old image,New image,Rollback Segment,Table,回滚段(Oracle),2020/7/16,46/65,事务回滚,事务恢复,回滚段,读一致性,回滚段目的:,2020/7/16,47/65,语句开始前的映象,新映象,Table,S

22、ELECT * FROM table,Oracle 读一致性,2020/7/16,48/65,DB2 循环日志,冲突恢复 日志文件重用 再次需要 默认方法,1,Primary,1,2,“n”,3,“n”,secondary,2020/7/16,49/65,DB2 日志保留(Log Retain),日志未使用 前滚恢复,联机归档: 包含提交信息和扩展事务. 存储在归档子目录里,12,13,14,15,16,活动： 包含未提交的信息和 未扩展的事务,脱机归档: 文件从子目录中移动. 通常为界质脱机,2020/7/16,50/65,DB2 日志文件( 1 ),日志文件 是用来记录事务对数据库的更新操

23、作的文件 日志文件主要有两种格式： 以记录为单位的日志文件 以数据块为单位的日志文件 记录日志文件内容： 各个事务的开始标记（BEGING TRANSACTION） 各个事务的结束标志（COMMIT或ROLLBACK） 各个事务的所有更新操作,日志记录,2020/7/16,51/65,DB2 日志文件( 2),日志记录（log record） 每个事务的开始标记、每个事务的结束标记和每个更新操作均作为日志文件中的一个日志记录。 日志记录主要包括以下内容： 事务标识（标明是哪个事务） 操作的类型（插入、删除或修改 操作对象（记录内部标识 更新前数据的旧值（对插入操作而言，此项为空值 更新后数据的

24、新值（对删除操作而言，此项为空值） 数据块日志文件的记录内容： 事务标识和被更新的数据块,2020/7/16,52/65,DB2 日志文件( 3),日志文件的作用：主要是数据库恢复 事务故障恢复和系统故障必须用日志文件 在动态转储方式中必须建立日志文件，后援副本和日志文件综合起来才能有效地恢复数据库 在静态转储方式中，也可以建立日志文件,介质 故障 恢复,正常运行,2020/7/16,53/65,DB2 日志文件( 4 ),登记日志文件(logging)必须遵循两条原则： 登记的次序严格按并发事务执行的时间次序。 必须先写日志文件，后写数据库。,2020/7/16,54/65,SQL Serv

25、er 事务日志,MS SQL Server事务日志 事务日志（Transaction logs）是数据库结构中非常重要但又经常被忽略的部分 事务日志是针对数据库改变所做的记录，它可记录针对数据库的任何操作，并将记录结果保存在独立的文件中 每个数据库都拥有至少一个事务日志以及一个数据文件 事务日志与事务恢复 根据事务日志记录可以将数据文件恢复成事务前的状态 出于性能上的考虑，SQL Server将用户的改动存入缓存中，这些改变会立即写入事务日志，但不会立即写入数据文件 事务日志会通过一个标记点来确定某个事务是否已将缓存中的数据写入数据文件。,2020/7/16,55/65,SQL Server

26、事务日志,2020/7/16,56/65,三种数据库备份差异,数据库-冷备份 脱机 OS 级 All datafiles All control files All online redo log files The init.ora file 表空间 热备份 Online OS级 Archive log list Alter tablespace abc begin backup; Alter system switch logfile; Alter database backup controlfile to file_name; RMAN 备份与恢复实用程序 Export Databas

27、e 逻辑 EXPDP Database-逻辑 备用数据库/数据卫士(Data Guard),数据库 联机/脱机 表空间 组成： 全部/增量拷贝 数据库配置 表空间定义 备份历史文件 活动/归档日志,Oracle,DB2,MS SQL,Database Data Files Log Files Trans Log Components Full Differential,2020/7/16,57/65,三种数据库恢复信息,控制文件包含内容： Database name Data file location Redo log file location Tablespace names 当前日志顺

28、序号 检查点信息 Log history 备份历史 RMAN - Recovery Catalog,恢复历史文件 最新的内容: DB/TS备份信息 DB/TS 前滚、恢复信息 Drop/Load/Reorg/表状态 Quiesce/Alter TS 包容器 DB 拷贝一部分 DB 被拷贝时 拷贝所在路径 最新回复时间 表的DDL,Oracle,DB2,SYSDEVICES-包含元数据 逻辑备份文件名 Size Type 物理文件名,MS SQL,2020/7/16,58/65,恢复类型,实例恢复 使用重做日志从失败处恢复 非归档模式 全库拷贝 归档模式 时间点恢复 自动 Import (log

29、ical),冲突 使用重做日志从失败处恢复 版本 (非可恢复DB) Image copy (TOCOPY) 前滚(Roll-Forward) 映像加日志应用 Import/Load (logical),Oracle,DB2,MS SQL,简单 整个恢复或恢复差异即可 Full 可恢复奥某个时间点 (trans log) Bulk-Logged 时间点丢失的大块恢复,2020/7/16,59/65,实用程序,IMPORT EXPORT SQL*LOADER ANALYZE DB_VERIFY RMAN IMPDP EXPDP,BACKUP IMPORT EXPORT LOAD RESTORE R

30、EORG (Table) REORGCHK RUNSTATS,Oracle,DB2,MS SQL,BACKUP RESTORE EXPORT IMPORT BCP DBCC CREATE STATISTICS,2020/7/16,60/65,优化程序,Oracle 基于规则-提示 基于代价 选择(二者之一,现在建议CBO,DB2 基于代价比Oracle久 7层优化 复杂查询可调 没有规则,MS SQL Cost based Hints,2020/7/16,61/65,SQL语句解释,EXPLAIN_INSTANCE,EXPLAIN_STATEMENT,EXPLAIN_OPERATOR,EXPLAIN_STREAM,EXPLAIN_OBJECT,EXPLAIN_ARGUMENT,EXPLAIN_PREDICATE,Oracle,DB2,PLAN_TABLE,2020/7/16,62/65,数据库监控,执行快照 - V$ Instance/Database Memory Disk User/Session Contention UTLBSTAT&UTLESTAT 搜集某