Oracle与sql简单优化与锁机制浅析

Oracle与sql简单优化与锁机制浅析系统运营二部徐海涛2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯oracle数据库的基本概念与原理对象的存储，segment、extent、blockSGA、PGA内存域，内存与存储的关系事务、undo、redo与ORA-01555关于锁机制2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯对象的存储oracle中的对象以segment的形式存储。我们可以在dba_segment这张视图中查询到所有我们创建的表和索引。segment由extent组成。其扩展是以extent为单位。一张表在初始化时会首先产生至少一个设定大小的extent，以后如果记录数逐渐增多，则需要扩展segment的空间，每次以设定大小扩展一个extent(即增加一个设定大小的extent到segment中)。extent由block组成。block是oracle存储中最基本的单位。一个block上会存储一条或多条数据记录，读取一条数据记录时至少需要读取出这条记录所在block。在blockheader上记录了一些非常重要的信息，包含块的类型(表还是索引)、关于块上活动和过时的事务信息、块在磁盘上的位置等等。一个segment属于一个唯一的tablespace，而一个tablespace则可以包含一个或多个数据文件。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯oracle的内存结构SGA内存域ORACLE使用的所有共享内存空间被称为SGA(systemglobalarea)的内存结构SGA主要包含下面的内存域：databuffer：用于放置datablock，ORACLE中所有的数据操作(增、删、查、改)都需要在databuffer中完成，读数据时需先将数据块从存储读到databuffer，修改数据的操作需在databuffer中完成修改然后在回写存储。优化物理读的一个办法就是增大databuffer，使数据在databuffer的停留时间变长，提高buffer的命中率，减少物理读，也就减小了I/O，不过这是不推荐的办法，最重要的还是要优化应用。sharedpool：用于放置缓存的sql语句、sql语句的执行计划、数据字典视图等，sql语句执行过程中需要保持在sharedpool中的语句本身和其执行计划，dll操作也需要在sharedpool中锁住相关的数据字典。javapool：用于存放java对象。largepool：用于分配一些大块的内存给进程应对一些特殊的需要，如语句的并行执行和备份会用到largepool，weblogicconnectionpool连接ORACLE数据库也是使用largepool存放connection的相关信息。redologbuffer：用于缓存redolog，redolog会先缓存到redologbuffer然后再写到日志组中。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯oracle的内存结构在oracle中几乎所有操作都是SGA完成的。不论增、删、查、改都是将需要的数据取到SGA中，在SGA中完成相关的操作。oracle通过后台进程(DBWn)将SGA中产生的变化同步到储存中，本身并不直接在存储上进行增、删、查、改的操作。PGA内存域针对每个oracle进程(process)分配的独占内存空间被称为PGA(processglobalarea)的内存结构，是在SGA之外独立分配的，一般情况下，session越多也就耗用越多的PGA。总体而言，PGA中需要关注的地方不是太多，在9i以上的版本，使用自动内存管理，用于hash和排序的内存空间从SGA挪到了PGA，为PGA的上限值(pga_aggregate_target)配置一个合理的值对sql语句的效率有较大影响。(oracle中另一部分非常重要的机制就是oracle中的后台进程，这里我们不作讨论，大家可以参看《oracleexpertone-on-one》等相关的书籍)2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯事务、undo、redo事务事务：单个逻辑工作单元执行的一系列操作。事务遵循如下的特性：原子性：一个事务要么完全发生，要么完全不发生一致性：事务把数据库从一个一致状态转变到另一个状态隔离性：在事务提交以前，其他事务察觉不到事务的影响持久性：一旦事务提交，它是永久的oracle的事务是隐式开始的，从第一条dml语句开始(第一条取得TX锁的语句开始的，后面我们将讨论oracle的锁机制，锁也是保证事务性的重要机制，通过锁保证了不同事务不能同时修改同一资源)，到显式以commit或者rollback结束。oracle缺省的事务隔离级别：readcommitted：只能读到其他事务已提交的变更，事务中的每一条语句都遵从语句级的读一致性(即只能读到每条语句开始时其他事务已提交的变更，执行过程中其他事务提交的变更不被体现)，保证不会脏读。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯事务、undo、redo事务需要注意的是完整性约束检查的点是在语句执行结束的时候开始的，也就是说只要有一行的修改违反完整性约束，则整体条语句失败。在oracle中频繁的commit并不是一个良好的习惯：oracle的所有变化都是在SGA中完成的，然后通过后台进程同步到存储中；但这一同步过程并不是只在commit的时候才发生，而是有一定量的数据被修改就会发生；实际上每次commit的消耗都是比较小的，因为大量修改的数据其实已经写到存储中了；过于频繁的commit反而带来冗余的checkpoint(简单来讲，检查内存和存储中的信息是否完全一致，不一致则调用相关的同步操作)的消耗；只需要在应该commit时候(需要被其他事务可见的时候)commit。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯事务、undo、redoredo所谓重做，顾名思义，就是重新做已经做过的动作。redolog(重做日志)对于oracle数据库是至关重要的，数据库中的所有的改变都会记录到redolog(比如dml、ddl操作等)，一旦数据库出现故障，oracle能够根据redolog“重做”，恢复到故障前的情况。由于重做基本上是不能避免的、也不是浪费，需要注意数据库过于频繁的dml操作会带来大量记录重做日志的消耗。当然这通常只能增加redolog的日志组或者提高archivelog的效率来满足应用的需要。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯事务、undo、redoundo撤销：也就是取消之前的操作，回滚到操作前的情况。oracle对于每次数据的修改，都会记录变化前的数据，这个数据会记录在rollbacksegment(回滚段)中。对应的dml操作会在改变的datablock和记录变更前数据的rollbackblock产生一个相对应的transactionslot，记录事务的相关信息。如果要回滚一个事务所做的dml操作，oracle根据该事务产生的所有transactionslot中的信息，在rollbacksegment中找到变更前的数据并回写到对应的datablock即可。(注意这个过程仍是首先在内存中完成，然后通过后台进程同步到存储上)如果事务没有结束，那么这个事务产生的回滚信息就不能被清理。但是如果事务已经提交或者回滚，那么这个事务产生的回滚信息就能够被清理重用。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯事务、undo、redoORA-01555由于存在回滚段的循环使用和读一致性的关系，这就使得open过长时间的cursor可能产生ORA-01555:snapshottooold的问题。ORA-01555产生的原因是因为不能读取到查询开始时的数据引起。由于读一致性，sql语句读取的数据必须是查询开始时的数据，在查询过程中产生的变更不能被这个查询所读取。对于cursor而言，就是opencursor的时候为查询开始的时候，close是查询结束。如果在查询执行或者opencursorfetch的过程中，原来查询的数据有被更改，则这个查询必须到回滚段中取相关修改前的数据。但因为回滚段是循环使用的，假设这个查询执行的时间过长或者opencursor的时间过长，就可能导致查询过程中被修改的数据的回滚信息已经被重用(因为更改这些数据的事务已经提交了，显然也不会被查询阻塞)，不能找到需要的修改前的数据，从而发生ORA-01555。更详细可以参见文档《关于ORA-01555的成因和应对措施.doc》或者其他相关的资料。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯关于锁机制锁(lock)：oracle中用于保护资源的共享机制，对于任何资源、对象的访问都需要对其进行加锁，用以保护对资源的并发访问时用户在存取同一数据库对象时的正确性(即无丢失修改、可重复读、不读“脏”数据)；锁也是保证oracle事务特性的重要机制，通过锁机制保证了不同的事务不能同时发起对同一资源的并发修改。在oracle中，锁简单来讲有两个维度：一个是锁的类别(lock_type)，这个维度表示了是在哪种资源、对象上的锁，比如JQ表示在job对象上的锁、TM表示对象锁(表锁)、TX表示事务锁(行锁)、TS表示表空间(tablespace)的锁等等。另一个是锁的模式(mode)，包含0-6。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯关于锁机制锁的模式(mode)：0：None1：null2：rowshare，即RS、行级共享锁3：rowexclusive，即RX、行级排它锁4：share，即S、共享锁5：sharerowexclusive，即SRX、共享行级排它锁6：exclusive，即X、排它锁2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯关于锁机制不同的锁模式(lockmode)的相容列表见下：2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯关于锁机制oracle中的不同操作需要对不同的对象加不同模式的锁；通过锁的类别来表示对某种对象加锁；而通过不同的锁的模式的相容规则，来控制哪些操作可以并行，哪些操作是互斥的；通过这样的锁机制来保证每个用户访问对象的正确性。一个操作可能需要对多种对象加锁(需要申请一种以上type的锁)，同时根据操作的不同申请不同的锁模式(lockmode)。比如：selectforupdate操作需要对表申请mode=3(即RX)的TM锁(locktype=TM)，然后对选到的行申请mode=6(即X)的TX锁(locktype=TX)(网上很多文档说是加mode=2的TM锁，是在8i库上，在9i或者10g的库实测加的是mode=3的TM锁，如果有分区则对对应分区增加的是mode=2的TM锁)；执行DML操作也是一样，需要对表增加mode=3的TM锁，对作dml操作的行增加mode=6的TX锁。那么根据锁相容的模式，mode=3的锁是相容的(即RX与RX是相容的)，但mode=6的锁是不相容的(即X与X是不相容的)；因此同时在一张表上执行dml操作和selectforupdate操作是不阻塞的(同时对一张表增加mode=3的TM锁是相容的)；但如果涉及到相同的行则会阻塞一方，直到另一方事务完成(同时对一行增加mode=6的TX锁是不相容的)。2019年7月31日©中国平安保险（集团）股份有限公司秘密版权所有，不得侵犯关于锁机制通过这个过程，我们可以简单理解oracle的锁机制是如何控制不同操作的相容和互斥。实际上，oracle的每种操作都有不同的锁策略(需要申请什么类型的锁