请教关于SQL执行计划的瓶颈

samt007

douglas537 发表于 2017-3-1 17:07
你的問題出在找1筆資料要花費123.76MB
這樣看花7秒就不意外了
另外INDEX RANGE SCAN MTL_SYSTEM_ITEMS_TL ...

其实返回的数据结果并不多，只是因为是全模糊查询，扫描索引的压力比较大。

samt007

douglas537 发表于 2017-3-1 17:07
你的問題出在找1筆資料要花費123.76MB
這樣看花7秒就不意外了
另外INDEX RANGE SCAN MTL_SYSTEM_ITEMS_TL ...

通过看执行计划的实际返回行数可以看出，通过MTL_SYSTEM_ITEMS_TL 扫描之后，返回给MTL_SYSTEM_ITEMS_B的数据只有1行呢。。。。

lelepapa

看起来像是io问题。

samt007

lelepapa 发表于 2017-3-14 14:09
看起来像是io问题。

感觉也像，不过后来检查到IO并没有问题。

newkid

7楼建议的测试结果如何? 可以尝试写SQL模拟计划中的每一步，取一个ROWID要多少时间，根据ROWID回表又花多少时间，......

samt007

newkid 发表于 2017-3-14 22:24
7楼建议的测试结果如何? 可以尝试写SQL模拟计划中的每一步，取一个ROWID要多少时间，根据ROWID回表又花多少 ...

嗯嗯，找时间写一个PLSQL跟踪一下时间。主要最近开始要忙项目了。。。。

lelepapa

日常都是这个等待吗，还是只是这个sql有这个等待

juan025

MTL_SYSTEM_ITEMS_XX
好熟悉的表名呀

从monitor结果看,你的数据库是RAC环境,等待事件中出现了GC CURRENT GRANT BUSY

看下是否因为这个事件引发的等待耗时?

gc current grantbusy[1]

A current block was requested and a grant message received. The busyhint implies that the request was blocked because others were ahead of it or itcould not be handled immediately.

The most likely scenario for a current busy grant occurs when acurrent buffer is shared by several instances and a request is made forexclusive access, e.g. to modify it. In this case, all the holders sharingaccess to the data would have to release the block first before the exclusiveaccess can be granted.


[1] Please note that a cr grant busy may theoretically happen only invery rare cases and has not been observed empirically. Consequently, the eventis not discussed separately in this document. Any occurrence of “gc cr grant busy” in the top 5 waitevents in a Statspack or AWR report should be reported to development.