Oracle数据库思维导图模板_ProcessOn思维导图、流程图

并发与多版本控制

并发控制

数据库提供的函数集合，允许多个人同时访问和修改数据

事务隔离级别

READ UNCOMMITED

允许脏读、不可重复读、幻象读

提供一个基于标准的定义以支持非阻塞读

Oracle默认提供非阻塞读，不允许脏读

READ COMMITED

允许不可重复读、幻象读

事务只能读取数据库中已经提交的数据

REPEATABLE READ

允许幻象读

不仅能给出一致的正确答案，还能避免丢失更新

得到一致的答案

Oracle中使用多版本控制，得到的答案相对于查询开始执行那个时间点是一致的

Oracle从不使用共享读锁

丢失更新：另一个可移植问题

SERIALIZABLE

READ ONLY

跟SERIALIZABLE很相似，但不允许修改

多版本控制读一致性的含义

写一致性

一致读和当前读

Oracle处理修改语句时会完成两类块获取

一致读

当前读

事务

数据库区别于文件系统的特性之一

ACID特征

原子性

事务中的所有动作要么都发生，要么都不发生

语句级原子性

过程级原子性

有WHEN OTHERS必须跟着RAISE/RAISE APPLICATION ERROR

事务级原子性

DDL与原子性

提交所有未完成的工作，结束当前已有的所有事务

完成DDL操作，如CREAT TABLE

如果DDL操作成功则提交，否则回滚DDL操作

一致性

事务将数据库从一种一致状态转变为下一种一致状态

隔离性

一个事务的影响在该事务提交前对其他事务都不可见

持久性

事务一旦提交其结果就是永久性的

事务控制语句

Oracle不需要专门的语句来开始事务

一定要显示地使用COMMIT或ROLLBACK来终止事务

COMMIT

结束事务，持久保存

ROLLBACK

结束事务，并撤销所有未提交的修改

SAVEPOINT

在事务中创建标记点，一个事务可以有多个

ROLLBACK TO <SAVEPOINT>

把事务回滚到标记点

SET TRANSACTION

设置不同的事务属性

持久性

COMMIT的WRITE扩展

除非是面向批处理的应用，否则都应当采用同步提交

慎用可延迟约束

可延迟约束会带来细微的副作用，可能导致物理实现的差别（非唯一和唯一索引）或查询计划的差别

不好的事务习惯

在循环中提交

性能影响

Snapshot To Old错误

可重启动的过程需要复杂的逻辑

使用自动提交

分布式事务

自治事务

redo与undo

redo

重做日志文件

数据库中最重要的恢复结构

归档重做日志文件

在线重做日志文件

undo

与redo相对，以便回到更改前的状态

Oracle创建表使用延迟段创建，等到insert执行后创建段，回滚时，段将持久存储

数据库并不会完全恢复原状，只是逻辑上相同而已

提交和回滚处理

提交次数与等待时间成正比

测量redo

传统路径INSERT生成的redo文件比直接路径INSERT大很多

不能关掉重做日志生成程序

在SQL中设置NOLOGGING

有些特定操作生成的redo会比平常少得多

必须非常谨慎地使用NOLOGGING模式

还是会生成一定数量的redo

不能避免所有后续操作生成redo

ARCHIVELOG模式数据库执行NOLOGGING操作后，必须尽快为受影响的数据文件建立一个新的基准备份，避免由于介质失败而丢失由NOLOGGING操作创建的数据

在索引上设置NOLOGGING

可以使用NOLOGGING执行

索引的创建和ALTER

表的批量INSERT

LOB操作（大对象更新）

通过CREATE TABLE AS SELECT创建表

各种ALTER TABLE操作，如MOVE、SPLIT

块清除

日志竞争

临时表和redo/undo

分析undo

INSERT生成的undo最少，update第二，Delete最多

数据库表

表类型

堆组织表

索引组织表

索引聚簇表

散列聚簇表

有序散列聚簇表

嵌套表

临时表

对象表

外部表

段

聚簇

表

表分区或子分区

索引

索引分区

lob分区、lob子分区、lob索引和lob段

嵌套表

回滚段和Type2 undo段

段空间管理

手动段空间管理

自动段空间管理

高水位线

包含了数据最右边的块

FREELIST

为有自由空间的对象维护HWM以下的块

每个对象都至少有一个相关的FREELIST，可以有多个

如果预计到有大量的用户在一个对象上执行大量的INSERT或UPDATE活动，就可以配置多个FREELIST，这对性能提升有好处

PCTFREE和PCTUSED

LOGGING和NOLOGGING

索引

B*树索引

索引组织表

B*树聚簇索引

降序索引

反向键索引

位图索引

位图联结索引

基于函数的索引

应用域索引

Oracle SQL语句性能优化方法

SQL语句尽量用大写的

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

当在SQL语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。这样一来,就可以减少解析的时间并减少那些由列歧义引起的语法错误

SELECT子句中避免使用‘*‘，ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表

使用where而非having

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描.注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。用OR, 需要将返回记录最少的索引列写在最前面

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的

Order By语句加在索引列，最好是主键PK上

避免使用耗费资源的操作。带有DISTINCT,UNION,MINUS,INTERSECT的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强

通常来说，如果语句能够避免子查询的使用，就尽量不用子查询。因为子查询的开销是相当昂贵的

用EXISTS替代IN——在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率

用NOT EXISTS替代NOT IN——在子查询中，NOT IN子句将执行一个内部的排序和合并，对子查询中的表执行一个全表遍历，因此是非常低效的。为了避免使用NOT IN，可以把它改写成外连接（Outer Joins）或者NOT EXISTS。

开发成功的Oracle应用

PL/SQL

在oracle中使用一个连接

使用绑定变量

并发控制

实现锁定

修改时对数据加行级锁，不对读数据锁定

读不会被写阻塞，写不会阻塞读

多版本控制

闪回

读一致性和非阻塞读

rollback、redo

体系结构

数据库

实例

instance=sga+background process

进程(process)

后台进程(background process)

缓冲区(buffer)

主机(host)

服务器(server)

客户机(client)

连接Oracle

专用服务器

共享服务器

TCP/IP连接原理

oracle服务器三种安装模式

基于主机方式

客户端-服务器两层方式

客户端-应用服务器-服务器三层模型

文件

控制文件(control files)

重做日志文件(redo log files)

数据文件(data file)

初始化参数文件(parameeter files)

密码文件(password files)

归档重做日志文件(archived redo log files)

静态参数文件(pfile)、动态服务器参数文件(spfile)

在windows nt 操作系统的默认路径为：$oracle_home/database

spfile 文件的创建:create spfile [=’spfile name’] from pfile [=’pfile name’]

想要修改spfile 文件中的参数,最好的方法是使用 alter system set 命令来完成

内存结构

进程全局区和用户全局区

PGA，进程专用的内存区

手动PGA内存管理

SORT_AREA_SIZE

对信息排序的RAM总量

SORT_AREA_RETAINED_SIZE

保存已排序数据的内存总量

HASH_AREA_SIZE

存储散列表所用的内存量

自动PGA内存管理

易用性

能动态调整使用内存量

内存控制

WORKAREA_SIZE_POLICY

MANUL

AUTO

PGA_AGGREGATE_TARGET

为用户设置比较大的合适的排序区，可以提高用户访问数据的效率

UGA

系统全局区

固定SGA

Oracle内部使用这个区来找到SGA其他区

重做缓冲区

LGWR启动对这个区的刷新输出情况

每3秒一次

无论何时有人提交请求

要求LGWR切换日志文件

重做缓冲区1/3满，或者包含了1MB的缓存重做数据

块缓冲区缓存

脏缓存块

保存被修改过的缓存块

命中缓存块

保存最近被访问的缓存块

空闲缓存块

没有数据，等待被写入数据

通过2个列表（DIRTY、LRU）来管理缓存块

DIRTY保存已经被修改，但还没有写入到数据文件中的脏缓存块

LRU保存所有的缓存块

数据高速缓存的工作原理过程

A、ORACLE在将数据文件中的数据块复制到数据高速缓存中之前，先在数据高速缓存中找空闲缓存块，以便容纳该数据块。Oracle 将从LRU列表的尾部开始搜索，直到找到所需的空闲缓存块为止。

B、如果先搜索到的是脏缓存块，将该脏缓存块移动到DIRTY列表中，然后继续搜索。如果搜索到的是空闲缓存块，则将数据块写入，然后将该缓存块移动到DIRTY列表的头部。

C、如果能够搜索到足够的空闲缓存块，就将所有的数据块写入到对应的空闲缓存块中。则搜索写入过程结束。

D、如果没有搜索到足够的空闲缓存块，则ORACLE就先停止搜索，而是激活DBWn进程，开始将DIRTY列表中的脏缓存块写入到数据文件中。

E、已经被写入到数据文件中的脏缓存块将变成空闲缓存块，并被放入到LRU列表中。执行完成这个工作后，再重新开始搜索，直到找到足够的空闲缓存块为止。

高速缓冲区很小的话，不停地写，将造成很大的I/O开销

配置缓冲池

默认池（Default pool）

所有数据默认都在这里缓存，除非你在建表的时候指定 Store(buffer_pool keep) or Store(buffer_pool recycle)。使用LRU算法管理

保持池（Keep pool）

缓存需要多次重用的数据，长期保存内存中,缺省值为0

回收池（Recycle pool）

用来缓存很少重用的数据，用完就释放，缺省值为0

共享池

用于存放SQL语句、PL/SQL代码、数据字典、资源锁和其他控制信息

数据字典缓存

库缓冲区

共享SQL区

私用SQL区（共享模式时）

结果高速缓存

锁与其他控制结构

大池

Java池

流池

自动SGA内存管理

SGA_TARGET

自动调优的SGA参数

db_cache_size

shared_pool_size

large_pool_size

java_pool_size

手动SGA参数

log_buffer

streams_pool

db_nk_cache_size

db_keep_cache_size

db_recyle_cache_size

自动内存管理

专用服务器进程的内存结构

排序区(sort area)

用于处理SQL语句所需的排序

游标状态区(cursor state)

当前所使用的sql语句的处理状态

会话信息区(session information )

会话的用户权限和优化统计信息

堆栈区(stack space)

其他的会话变量

共享服务器进程或多线程配置

以上这些结构除了堆栈区外大部分将存在SGA中,如果有 large pool,他们就会被存在large pool,否则它们就会被存放在共享池(share pool)中

Oracle进程

服务器进程

专用服务器连接

客户连接和服务器进程一对一映射

好处

远程执行

地址空间隔离

共享服务器连接

强制要求必须使用Oracle Net

好处

减少操作系统进程/线程数

刻意地限制并发度

减少系统所需的内存

数据库常驻连接池

连接与会话

连接是从客户到Oracle实例的一条路径

会话是实例中存在的一个逻辑实体

专用服务器与共享服务器

专用服务器在非OLTP环境中，长时间运行事务情况下使用

共享服务器在事务持续时间尽量短，事务可以频繁执行情况下使用

除非系统负载过重，或者需要使用某个新特性，否则首选专用服务器

专用服务器设置少，调优更容易

后台进程

保证数据库运行所需的实际维护任务

中心任务进程

PMON（进程监视器）

出现异常中止的连接之后完成清理

SMON（系统监视器）

完成所有系统级任务

数据库“垃圾收集器”

清理临时空间

合并空闲空间

针对原来不可用的文件恢复活动的事务

执行RAC中失败节点的实例恢复

清理OBJ$

收缩回滚段

“离线”回滚段

RECO（分布式数据库恢复）

恢复由于两段之间的崩溃或连接丢失等原因而保持准备状态的事务

CKPT（检查点进程）

更新数据文件的文件首部，以辅助真正建立检查点的进程（DBWn）

DBWn（数据库块写入器）

负责将脏块写入磁盘的后台进程

可以配置多个DBWn

最好的情况下，使用异步I/O将块写至磁盘

DIAG（诊断性进程）

负责监视实例的整体状况，捕获处理实例失败时所需的信息

FBDA（闪回数据归档进程）

能够查询很长时间之前的数据

DBRM（数据库资源管理器进程）

GEN0（通用任务执行进程）

分担另外某个进程的阻塞处理

其他中心进程

工具后台进程

CJQ0和Jnnn进程（作业队列）

QMNC和Qnnn（高级队列）

EMNC（事件监视器进程）

MMAN（内存管理器）

MMON、MMNL、Mnnn（可管理性监视器）

CTWR（修改跟踪进程）

RVWR（恢复写入器）

DMnn、DWnn（数据泵主进程/工作进程）

其他工具后台进程

从属进程

I/O从属进程

为不支持异步I/O的系统或设备模拟异步I/O

Pnnn（并行查询执行服务器）

锁和闩

锁机制用于管理对共享资源的并发访问

锁定问题

丢失更新

锁定策略

悲观锁定

乐观锁定

使用版本列的乐观锁定

使用校验和的乐观锁定

网络传输少，CPU密集型操作

阻塞

SELECT FOR UPDATE

增加NOWAIT子句

阻塞的INSERT

主键/唯一值使用系统自带函数或者创建唯一值的函数生成

阻塞的Merge、Update和Delete

SELECT FOR UPDATE NOWAIT

死锁

原因

外键未加索引

外键不需要加索引的情况

没有从父表删除行

没有更新父表的唯一键/主键值

没有从父表联结子表

表上的位图索引遭到并发更新

锁升级

Oracle不会升级锁，会执行锁转换

锁类型

DML锁

确保一次只有一个人能够修改某一行

TX锁

TM锁

DDL锁

排他DDL锁

共享DDL锁

可中断解析锁

内部锁和闩