确定系统中的常量sql语句（literal sql），它们往往都是可以使用并且应该使用绑定变量的。下面通过查询v$sqlarea视图，列出超过4个执行实例的sql语句的前40个字符，这里假设你的系统中前40个字符相同的语句被认为是没有使用绑定变量的常量sql。很明显，如果使用更长的字符串或者更多的执行实例作为过滤条件，得到的结果sql语句会少很多。然后你可以根据得到的结果，建议程序开发人员对这些常量sql语句尽量使用绑定变量。
SQL>Select hash_value, substr(sql_text,1,80)
from v$sqlarea
where substr(sql_text,1,40)
in (select substr(sql_text,1,40) from v$sqlarea having count(*) > 4 group by substr(sql_text,1,40))
order by sql_text;
如果你的系统中存在大量的常量sql语句，当你将它们改为充分使用绑定变量后，对shared pool latch和library cache latch的争用将会显著减少。更改sql语句，使用绑定变量，这通常需要修改应用程序。另外一个不需要改动应用的方法是，修改初始化参数cursor_sharing，将其值改为similar而不是force，这个参数允许系统对一些只有常量值不一样的sql语句共享游标，以减少latch争用、内存占用和硬分析。
注意：在oracle8i早期版本中，使用cursor_sharing可能导致bug。在使用了物化视图的环境中，请慎用该参数，否则可能导致长久的library cache pin等待。另外，使用cursor_sharing = force可能导致优化器生成错误的执行计划。这或多或少的会对系统性能造成影响。从oracle9i起，优化器可以通过窥视pga中的信息来进行变量绑定，以此生成合适的执行计划，该特性可以通过隐含参数_optim_peek_user_binds来开启，并且该特性只对那些需要硬分析的sql语句有效，这意味着会基于绑定变量的第一个值来生成执行计划。
当一个新的sql语句到达时，oracle首先在库缓存中检查是否已经有相同的语句存在。如果已经存在，则可以花费较小的代价执行该语句，这就是所谓的软分析。硬分析通常意味着较坏的性能，而软分析过多也不是什么好事。在软分析期间，需要持有library cache latch，并且oracle依然需要对语句进行语法和语义检查，除非该语句已经在会话的游标缓存中。你可以通过设置参数session_cached_cursors来减少library cache latch的持有时间。但是，减少软分析的最佳方法还是优化应用程序。最好是分析一次，执行多次（很像java的宣传口号），而不要分析一次，执行一次。你可以通过v$sqlarea的parse_calls列来查找分析过多的sql语句。
过大的共享池也可能引起Shared pool latch争用。从oracle9i起，由于引入了多个子共享池的特性，过大的共享池不再是一种坏事。在9i之前，过大的共享池通常会引起shared pool latch争用。共享池中可用内存分成不同的内存块(chunk)，不同大小范围的块由不同的可用列表(freelist)来管理。在共享池中分配空间时，需要扫描可用列表，扫描期间，需要持有shared pool latch。过大的共享池会使得可用列表过长，从而使得shared pool latch的持有时间变长。在高并发环境中，latch持有时间过长就可能造成latch争用（表现为较高的sleeps和misses值），尤其是大量使用常量sql的系统，对这样的系统，不要一味想着加大共享池，更重要的是想一想你为什么会需要保存这么多不能共享的语句到共享池中。
通过alter session set events ’immediate trace name heapdump level 2’可以转存共享池信息，从中可以看到共享池的可用列表信息。在生成的跟踪文件中查找bucket，你可以发现内存块(chunk)分配到不同的bucket上。另外，你也可以通过下面的方法生成一个查询来列出共享池的可用内存管理信息。该查询只要生成一次，就可以在生成该跟踪文件的数据库中重复使用，但不要在其他不同版本的数据库中使用，否则可能得到错误的结果。该查询在oracle10gR1中不可用，因为它限制了case分支数不能超过128（具体信息参考oracle metalink 编号#131557.1和bug号#3503496），或者你可以通过使用decode和sign函数来完成该功能。
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump 2
Statement processed.
SQL> oradebug tracefile_name
/u01/oracle/admin/gzwebdb/udump/gzwebdb_ora_10065.trc
SQL>
如果你发现数据库的共享池可用列表过长，并且系统中使用了常量sql，你或许应该考虑减少shared_pool_size。这会降低系统中对shared pool latch的争用。但要注意共享池不能太小，否则可能导致ora-04031错误。另外，通过使用dbms_shared_pool.keep将常用对象钉在内存中也是个好主意，v$db_object_cache中保存了钉在内存中的对象的信息。
语句版本数过多也可能引起Library cache latch争用。对于字符完全一致但是由于引用不同的对象而不能共享的sql语句，oracle使用多个子游标来指向该语句的不同版本。例如，系统中有三个名叫customer的表，但是属于不同的模式。则对于语句select * from customer，不同的模式执行该语句，语句字符上完全一样，其hash值完全一样，但是该语句无法共享，因为它引用的对象不同。所以会生成该语句的不同子版本。当一个sql语句有多个子版本时，oracle需要比较该语句的所有存在的子版本，在此期间需要持有library cache latch，这样可能导致library cache latch争用。解决这种情况也很简单，在系统中，尽量不要使用相同的对象名。下面的查询列出了v$sqlarea中子版本超过20的所有sql语句：
SQL>Select version_count, sql_text from v$sqlarea where version_count > 20 order by version_count, hash_value;
注意：在oracle8i中，语句的版本过多，可能导致和sql执行监控相关的bug(参考oracle metalink 编号#62143.1)。这个bug会导致sql无法共享。可以通过将隐含参数_sqlexec_progression_cost设置为0来禁用sql执行监控特性，该参数同时会禁止v$session_longops中的数据。Oracle提供了视图v$sql_shared_cursor，其中可以看到为什么无法共享一个已经存在子游标。每个列都限制了游标无法共享的一个原因。
下面来介绍下Cache buffers lru chain latch。除了hash chain，缓冲头同样组成一个列表，这个列表指向其他的列表比如lru，lruw和ckpt-q。Lru和lruw列表并不是什么新东西，他们是数据缓冲区中最早的两个链表。Lru列表包含了不同状态的缓存块，而lruw就是俗称的“脏表”，只包含脏数据块。Lru和lruw列表是互斥的，他们合称一个工作集(a working set)。每个工作集由一个cache buffers lru chain latch保护。换句话说，数据缓冲区中工作集的个数是由cache buffers lru chain latch的个数决定的。通过内部视图x$kcbwds (kernel cache buffer working sets descriptors)可以知道工作集的个数。我们注意到x$kcbwds 的set_latc的值就是v$latch_children的addr列。
SQL> Select set_id, set_latch
from x$kcbwds
order by set_id;
一般来讲，当进程需要查找可用的缓存空间时，需要访问lru列表。后台进程DBWn则会将lruw列表中的干净块移到lru列表中，也会将lru中的脏块移到lruw列表中。在一个工作集中进行以上的任何操作都需要先获得cache buffers lru chain latch。
各个数据缓冲区中（包括不同块大小的缓冲区，keep池和recycle池），每个缓冲区至少需要有一个cache buffers lru chain latch，而一个DBWn进程可能需要多个latch。否则，一个工作集就可能变得很长。在oracle9i和oracle10g中，cache buffers lru chain latch的个数默认是cpu个数的4倍，如果，db_writer_processes大于4，则等于cpu的个数乘以db_writer_processes。可以通过隐含参数_db_block_lru_latches来调节cache buffers lru chain latch的个数。Cache buffers lru cahin latch的争用，主要表现为由于低效的sql语句导致数据缓冲区过度活跃。全表扫描和对某些选择性较差的大索引的反复扫描是造成cache buffers lru cahin latch争用的主要原因。解决办法是，查找latch free等待事件中关于cache buffers lru chain latch相关的sql语句（在oracle10g中，已经变成一个独立的cache buffers lru chain等待事件），优化这些sql，降低其物理读和逻辑读。
下面来关注Row cache objects latch。Row cache objects latch用来保护数据字典缓冲区（row cache的名字主要是因为其中的信息是按行存储的，而不是按块存储）。进程在装载、引用或者清除数据字典缓冲区中的对象时必须获得该latch。在oracle8i之前，这是一个独立latch。从oracle9i起，由于引入了多个子共享池的新特性，存在多个row cache objects子latch。Oracle10g中，该latch也有了一个独立的等待事件：row cache objects。
从oracle7.0起，数据字典缓冲成为了共享池的一部分。而在7.0之前，每个数据字典对象都是由独立的dc_*初始化参数控制。Oracle7.0的这个改变也意味着，不能再直接的调整数据字典缓冲，而只能通过调整shared_pool_size来间接的调整。V$rowcache视图包含了每个数据字典对象的统计信息。你可以通过下面的查询发现最热的数据字典对象。对数据字典缓冲区的调节手段是有限的。最好的办法是降低对前面的查询结果中一些热点数据字典对象的访问。举个例子，如果对某些sequence访问频繁，可以将考虑将这些sequnce缓存在内存中。包含多个基表连接或者基于视图的视图可能导致该latch争用。一般的解决办法是增加shared_pool_size的值。
SQL>Select cache#, type, parameter, gets, getmisses, modifications mod
from v$rowcache
where gets > 0
order by gets;
没有评论:
发表评论