Oracle性能优化

Oracle独特的锁定策略

并发控制是数据库最基本的特性，并发控制由锁机制来实现，而Oracle的锁机制几乎与其他所有数据库都不一样。

1，       oracle只在修改时才对数据加行级锁。

2，       如果只是读数据，oracle绝对不会对数据锁定。

3，       写入器不会阻塞读取器。即读不会被写阻塞。

4，       写入器想写某行数据，但另一个写入器已经锁定了这行数据，此时该写入器才会被阻塞。读取器绝对不会阻塞写入器。

 

 

Oracle的锁机制有一个副作用，如果想保证一次最多只能有一个用户访问一行数据（即串行访问），开发人员就要自己做些工作了。

• 常用的解决方式就是使用select for update将查询串行化。

 

，Oracle采用了一种多版本、读一致的并发模型，Oracle利用这种机制提供了以下特性：读一致查询：对于一个时间点，查询会产生一致的结果。

 

继续用表T做演示，我们在表T上打开一个游标，然后清空表T，看一下会发生什么

游标中的数据还在，而且，并不是打开游标的时候，Oracle偷偷将数据复制到了某一个位置，而是这些数据作为表T的前映像，被保存在回滚段中。

 

小结

 

• 读不会被写阻塞

• 读一致查询：对于一个时间点，查询会产生一致的结果

 

 

Oracle体系结构

文件

• 参数文件，即spfile，默认位置为$ORACLE_HOME/dbs

• 跟踪文件和警告文件，默认位置为$ORACLE_BASE/admin/$ORACLE_SID

• 数据文件，即我们常说的datafile

• 临时文件，即我们常说的临时表空间

• 控制文件，即control file，里面记录了数据库的配置信息

• 重做日志文件，即redo log

 

使用参数文件配置数据库

借助警告文件诊断数据库问题

重做日志文件与数据库性能

重做日志用来保护数据库，几乎所有的数据库操作都会生成重做日志，以防止突然的掉电或者是磁盘损坏

 

内存结构

• 系统全局区SGA：这是一个很大的共享内存段，几乎所有Oracle进程都要访问这个区中的某一点。SGA中最重要的部分就是数据库的缓存。

• 进程全局区PGA：这是一个进程或线程专用的内存，其他进程/线程不能访问。

• 用户全局区UGA： UGA）：这个内存区与特定的会话相关联。它可能在SGA中分配，也可能在PGA中分配，这取决于是用共享服务器还是用专用服务器来连接数据库。如果使用共享服务器，UGA就在SGA中分配；如果使用专用服务器，UGA就会在PGA（即进程内存区）中。

 

 

小结

 

• 可以使用spfile配置数据库

• 数据库出现问题，首先要查看警告日志

• 重做日志需要放在性能最好的IO设备上

• SGA是Oracle最重要的内存结构

 

 

Oracle的锁类型

• DML锁：DML代表数据操纵语言。一般来讲，这表示SELECT、INSERT、UPDATE、MERGE和DELETE语句。DML锁机制允许并发执行数据修改。例如，DML锁可能是特定数据行上的锁，或者是锁定表中所有行的表级锁。

• DDL锁：DDL代表数据定义语言，如CREATE和ALTER语句等。DDL锁可以保护对象结构定义。

• 内部锁和闩：Oracle使用这些锁来保护其内部数据结构。例如，Oracle解析一个查询并生成优化的查询计划时，它会把库缓存“临时闩”，将计划放在那里，以供其他会话使用。闩（latch）是Oracle采用的一种轻量级的低级串行化设备，功能上类似于锁。不要被“轻量级”这个词搞糊涂或蒙骗了，你会看到，闩是数据库中导致竞争的一个常见原因。轻量级指的是闩的实现，而不是闩的作用。

 

 

DML锁

• DML锁（DML Lock）用于确保一次只有一个人能修改某一行，而且你正在处理一个表时别人不能删除这个表。在你工作时，Oracle会透明程度不一地为你加这些锁。

• TX锁（事务锁）：事务发起第一个修改时会得到TX锁，而且会一直持有这个锁，直至事务执行提交或回滚。

• Oracle中的锁定过程如下： (1) 找到想锁定的那一行的地址。(2) 到达那一行。(3) 锁定这一行（如果这一行已经锁定，则等待锁住它的事务结束，除非使用了NOWAIT选项）。

• TM (DML Enqueue)锁：TM锁用于确保在修改表的内容时，表的结构不会改变。例如，如果你已经更新了一个表，会得到这个表的一个TM锁。这会防止另一个用户在该表上执行DROP或ALTER命令。如果你有表的一个TM锁，而另一位用户试图在这个表上执行DDL，他就会得到以下错误消息： drop table dept

 

事务在发起第一个修改的时候，才会对涉及到的行加锁

 

 

并发控制和多版本

 

• 开发多用户的数据库驱动应用时，最大的难题之一是：一方面要力争最大的并发访问，与此同时还要确保每个用户能以一致的方式读取和修改数据。这里我们将进一步详细地讨论Oracle如何获得多版本读一致性。

• 多版本是指，Oracle能同时物化多个版本的数据，这也是Oracle提供数据读一致视图的机制（读一致视图即read-consistent view，是指相对于某个时间点有一致的结果）。多版本有一个很好的作用，即数据的读取器绝对不会被数据的写入器所阻塞。换句话说，写不会阻塞读。这是Oracle与其他数据库之间的一个根本区别。在Oracle中，如果一个查询只是读取信息，那么永远也不会被阻塞。它不会与其他会话发生死锁，而且不可能得到数据库中根本不存在的答案。

 

事务隔离级别

ANSI/ISO SQL标准定义了4种事务隔离级别，这些隔离级别是根据3个现象定义的，以下就是给定隔离级别可能允许或不允许的3种现象：

• 脏读：即读取未提交的数据，也就是脏数据。如果允许脏读，将影响数据完整性，另外外键约束会遭到破坏，而且会忽略惟一性约束。

• 不可重复读：这意味着，如果你在T1时间读取某一行，在T2时间重新读取这一行时，这一行可能已经有所修改。也许它已经消失，有可能被更新了，等等。

• 幻像读：这说明，如果你在T1时间执行一个查询，而在T2时间再执行这个查询，此时可能已经向数据库中增加了另外的行，这会影响你的结果。与不可重复读的区别在于：在幻像读中，已经读取的数据不会改变，只是与以前相比，会有更多的数据满足你的查询条件。

 

Oracle的方法

• REPEATABLE READ可以保证由查询得到读一致的结果，READ COMMITTED不能提供一致的结果，而READ UNCOMMITTED级别用来得到非阻塞读。

• Oracle明确地支持READ COMMITTED和SERIALIZABLE隔离级别。

• 不过，在Oracle中，READ COMMITTED则有得到读一致查询所需的所有属性。另外，Oracle还秉承了READ UNCOMMITTED的精神。提供脏读的目的是为了支持非阻塞读，也就是说，查询不会被同一个数据的更新所阻塞，也不会因为查询而阻塞同一数据的更新。不过，Oracle不需要脏读来达到这个目的，而且也不支持脏读。但在其他数据库中必须实现脏读来提供非阻塞读。

• 那么Oracle是如何实现这个壮举的呢？我们知道回滚段里保存了数据库之前的状态，用来在回滚的时候恢复表中的数据，在Oracle里，回滚段还有一个重要的作用，就是提供读一致的查询。

• 当事务A读到一行数据时，如果发现这行数据被其他事务改动过，读取器并不会被阻塞，而是到回滚段中去读这行的前映像，找到这行数据被修改之前的状态，也就是事务A开始时的状态，这样就提供了读一致的查询。

 

 

 

REDO和UNDO，提交和回滚

• 这里将介绍Oracle数据库中最重要的两部分数据：redo与undo。

• redo是Oracle在在线或归档重做日志文件中记录的信息，万一出现失败时可以利用这些数据来重放事务。

• undo是Oracle在undo段中记录的信息，用于取消或回滚事务。

 

 

 

Commit的时候数据库做了什么？

• 你可能认为，一个事务越大，commit需要的时间就越长。

• 而实际上并不是这样，不论事务有多大，commit的响应时间一般都很平，这是因为在数据库中执行commit之前，困难的工作都已经做了，我们已经修改了数据库中的数据，所以99.9%的工作都已经完成。

• 在commit之前，数据库已经在SGA中生成了undo块，在SGA中生成了已修改数据块，在SGA中生成了对于前两项的缓存redo。取决于前三项的大小，以及这些工作花费的时间，前面的某些数据可能已经刷新输出到磁盘。

• 数据库并不是在用户commit的时候才去修改数据，实际上在commit之前，数据就已经修改完毕了，实际上大多数的工作在commit之前就做完了。

• 所以不要担心大事务会导致过长的commit时间，而故意去割裂事务。在真正需要提交的时候提交，就行了

 

执行COMMIT时，余下的工作只是：

• 为事务生成一个SCN。SCN是Oracle使用的一种简单的计时机制，用于保证事务的顺序，可以把SCN看作一个钟摆，每次有人COMMIT时，SCN都会增1。

• LGWR将所有余下的缓存重做日志条目写到磁盘，并把SCN记录到在线重做日志文件中。这一步就是真正的COMMIT。如果出现了这一步，即已经提交。事务条目会从V$TRANSACTION中“删除”，这说明我们已经提交。

• V$LOCK中记录这我们的会话持有的锁，这些所都将被释放，而排队等待这些锁的每一个人都会被唤醒，可以继续完成他们的工作。

• 如果事务修改的某些块还在缓冲区缓存中，则会以一种快速的模式访问并清理。块清理是指清除存储在数据库块首部的与锁相关的信息，实质上讲，我们在清除块上的事务信息。

 

那么Rollback的时候又做了什么？

• 撤销已做的所有修改。其完成方式如下：从undo段读回数据，然后实际上逆向执行前面所做的操作，并将undo条目标记为已用。如果先前插入了一行，ROLLBACK会将其删除。如果更新了一行，回滚就会取消更新。如果删除了一行，回滚将把它再次插入。

• 会话持有的所有锁都将释放，如果有人在排队等待我们持有的锁，就会被唤醒。

• 与commit相比，rollback作出的工作则要多得多，rollback的开销很大，因为你花了大量的时间做工作，还要花大量的时间撤销这些工作。

 

那么Rollback的时候又做了什么？

• 撤销已做的所有修改。其完成方式如下：从undo段读回数据，然后实际上逆向执行前面所做的操作，并将undo条目标记为已用。如果先前插入了一行，ROLLBACK会将其删除。如果更新了一行，回滚就会取消更新。如果删除了一行，回滚将把它再次插入。

• 会话持有的所有锁都将释放，如果有人在排队等待我们持有的锁，就会被唤醒。

• 与commit相比，rollback作出的工作则要多得多，rollback的开销很大，因为你花了大量的时间做工作，还要花大量的时间撤销这些工作。

 

 

 

日志竞争

• 如果在Statspack看到日志文件同步事件的等待时间比较长，或者日志文件并行写事件中写次数比较大，就说明你遇到了重做日志的竞争。

• 导致日志竞争的一个原因就是过于频繁的提交，提交得太频繁，不仅是不好的编程实践，还会引入大量日志文件同步等待。

• 除了过于频繁的提交之外，常见的原因还有：

¤ redo放在一个慢速设备上，磁盘性能不佳，该购买速度更快的磁盘了。

¤ redo与其他频繁访问的文件放在同一个设备上。redo设计为要采用顺序写，而且要放在专用的设备上。如果系统的其他组件试图与LGWR同时读写这个设备，你就会遭遇某种程度的竞争。

¤ redo采用了一种慢速技术，如RAID-5。RAID-5很合适读，但是用于写时表现则很差。前面已经了解了COMMIT期间会发生什么，我们必须等待LGWR以确保数据写到磁盘上。倘若使用的技术会导致这个工作变慢。

 

小结

 

• 在真正需要提交的时候，才提交，Oracle里，不需要靠故意割裂事务来换取高性能

• 日志竞争会导致数据库性能下降

 

 

使用AUTOTRACE分析SQL性能

 

AUTOTRACE是SQL*Plus中一个工具，可以显示所执行查询的执行计划以及所用的资源。

• 通过设置AUTOTRACE系统变量可以控制这个报告：

¤ SETAUTOTRACE OFF：不生成AUTOTRACE报告，这是默认设置。

¤ SETAUTOTRACE ON EXPLAIN：AUTOTRACE报告只显示优化器执行路径。

¤ SETAUTOTRACE ON STATISTICS：AUTOTRACE报告只显示SQL语句的执行统计信息。

¤ SET AUTOTRACEON：AUTOTRACE报告既包括优化器执行路径，又包括SQL语句的执行统计信息。

¤ SETAUTOTRACE TRACEONLY：这与SET AUTOTRACE ON类似，但是不显示用户的查询输出。

 

 

 

说的是exists和in的区别，

in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。这样的话，in适合内外表都很大的情况，exists适合外表结果集很小的情况。

小结

 

• 传说中的“准则”并不可靠，得到真相的最佳途径是亲自测试

• 很多因素都会影响SQL的执行路径，这些因素错综复杂，很难理清，所以，在优化SQL的时候，最好可以使用真实的生产数据来进行测试，使用“伪造”的数据进行测试，很可能会得到和生产环境完全不同的结果

 

 

 

使用STATSPACK分析数据库性能

 

使用AWR分析数据库性能

 

使用ASH分析数据库性能

 

使用Oracle EM分析数据库性能

 

到这里，我们已经看到了如何使用AUTOTRACE显示SQL的执行计划，如何使用Statspack分析数据库的整体性能，如何使用10046事件跟踪session，如何使用Oracle10G引入的新工具AWR和ASH，还简单介绍了OracleEM的Performance部分，有了这些工具的帮助，大多数的数据库问题，都可以迎刃而解了。