[20190507]sga_target=0注意修改_kghdsidx_count设置.txt

--//昨天遇到一例视图定义太复杂导致长时间分析sql语句出现library cache lock等待事件的情况.
--//加上大量使用非绑定变量语句,导致硬解析增加,导致问题更加严重.

--//顺便解析当时同事发现仅仅1个CPU特别忙.实际上因为仅仅1个shared pool latch在工作.

1.环境:
> @ ver1
PORT_STRING        VERSION        BANNER
------------------ -------------- ----------------------------------------------------------------
IBMPC/WIN_NT-8.1.0 10.2.0.3.0     Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
--//注32位版本.

2.解析:

SELECT addr
        ,latch#
        ,child#
        ,level#
        ,name
        ,gets
        ,sleeps
        ,immediate_gets
        ,immediate_misses
        ,spin_gets
    FROM V$LATCH_CHILDREN
   WHERE name LIKE 'shared pool'
ORDER BY addr;

ADDR         LATCH#     CHILD#     LEVEL# NAME              GETS     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS
-------- ---------- ---------- ---------- ----------- ---------- ---------- -------------- ---------------- ----------
05AA3858        213          1          7 shared pool   25235287        182              0                0       7384
05AA38C0        213          2          7 shared pool        151          0              0                0          0
05AA3928        213          3          7 shared pool        151          0              0                0          0
05AA3990        213    &nb