并发与多版本控制
并发控制
数据库提供的函数集合，允许多个人同时访问和修改数据
事务隔离级别
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来终止事务
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
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。
体系结构
数据库
实例
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 )
会话的用户权限和优化统计信息
共享服务器进程或多线程配置
以上这些结构除了堆栈区外大部分将存在SGA中,如果有 large pool,他们就会被存在large pool,否则它们就会被存放在共享池(share pool)中
