这是台交易和查询混合的库

zengmuansha

这个库有段时间CPU飙高.看了下AWR 很多cursor 等待时间 尤其是平均等待时间太长
版本11.2.0.1

knowwinter

你有几个SQL的version count 有点高啊 先找找这个原因

knowwinter

latch: cache buffers chains 有热块啊

harryzhang

parse time elapsed        207,570.59        67.15
sql execute elapsed time        145,897.96        47.20

老高了，估计猜中bug了。

PS,AWR跨度好大~~

zengmuansha

knowwinter 发表于 2013-9-9 14:02
你有几个SQL的version count 有点高啊 先找找这个原因

version count  表示啥意思呢?

zengmuansha

knowwinter 发表于 2013-9-9 14:04
latch: cache buffers chains 有热块啊

应该是它导致cursor pin x on s的等待事件吧 AWR能看到哪个热块不?

knowwinter

cursor: pin S wait on X 这个在以前叫library cache pin,10g之后libraray cache pin 被mutex取代，cursor: pin S wait on X 是因为有会话持有对象的X锁（独占锁）没有释放，而另一个会话对这个对象请求一个S锁（共享锁），这两个锁是不兼容的，一个X锁的请求会被一个S锁的持有者所阻塞，而一个S锁的请求会被一个X锁的持有者或者一个X锁的请求者所阻塞或被队列，所以找到那个阻塞者，查出原因释放掉锁。

这种争用通常情况下是由于SQL的高版本数造成的，一般你的SQL版本数有超过100，甚至超过1000的话，那么这些争用基本就是由于高版本数造成的。

一个sql 在第一次被执行解析的时候数据库会根据它的语句文本为它生成一个hash value，这是为了方便数据库在内存中找到相同的SQL而共享，同时它会生成一个父cursor和一个child cursor，父cursor代表了这个SQL的hash value，而child cursor则代表了sql的metadata，所谓的metadata就是包含了这条SQL所有可执行的信息，当同一个账户再次执行同一个SQL语句的时候，数据库先会对这个SQL解析，生成一个hash value，然后通过这个hash value在shared pool中找是否有相同的hash value的sql，如果有则进一步将这个SQL与之前的那个SQL的child cursor做一系列的匹配，如果所有的条件都匹配，那么这条SQL就能共享之前的那条SQL。

而如果你用另外一个用户，这个用户拥有跟之前用户一样表名的表，你同样执行了之前那个用户一模一样的sql语句，这条SQL语句在解析的时候会生成跟之前SQL同样的hash value，因为这两条SQL的语句文本是一样的，这时数据库会在内存中找到同它一样hash value的sql的父cursor，然后与它的child cursor进行匹配，由于这是两个不同schema下的表，虽然表名相同，但是它们各自的object id是不同的，所以你会得到一个mismatch，这时它会扫描下一个child cursor进行匹配，由于我们现在只有一个child，当所有的匹配都失败后数据库会为这个sql的父cursor创建一个新的child cursor，所以这时对于同一个sql我们有了两个child cursor，也就是两个version，如果这时你有100个child cursor，那么数据库会逐个扫描匹配一遍，这就非常消耗cpu了，用来扫描的时间变长，持有锁的时间就会变长，high version count的sql一般都会引起这种类型的内存争用，所以先看看能不能把那些高版本数的SQL解决了，然后看看锁是不是就降低了。

sql child cursor不匹配不能共享的原因当然不只那些，有很多，具体你可以查看v$sql_shared_cursor的相关字段信息，关于对高版本数sql的troubleshooting，你可以查看这篇文章Troubleshooting High Version count地址：http://www.ask600.com/troubleshooting-high-version-count.html，其中提供了相关脚本来找出sql没有共享的原因。

knowwinter

你的Redo NoWait %:99.98 你可以适当的增加redo buffer的大小来解决
Buffer Nowait %:99.98 和latch: cache buffers chains 是由于热块造成的，从你的AWR报告来看，你的HIST_TRANS表的逻辑读请求和物理读请求是最多的，占了所有的绝大部分比例，所以热块基本应该就在这张表上，解决热块问题，这两个指标应该就好了。

zengmuansha

knowwinter 发表于 2013-9-10 15:40
cursor: pin S wait on X 这个在以前叫library cache pin,10g之后libraray cache pin 被mutex取代，cursor: ...

非常感谢老兄 的通俗易懂 精确细致地说明!