select ksppinm name, ksppstvl value, ksppdesc description
from x$ksppi x, x$ksppcv y
where (x.indx = y.indx)
and ( ksppinm like '&1' or ksppdesc like '&1' )
order by name;

首先由隐含参数获知该数据库只有一个share pool
NAME                                          VALUE                DESCRIPTION
--------------------------------------------- -------------------- ----------------------------------------------------------------------
_kghdsidx_count                               1                    max kghdsidx count

开启两个数据库会话
Session 1
获取share pool latch的内存地址
SQL> select addr,child# from v$latch_children where name ='shared pool' and child#=1;

ADDR                 CHILD#
---------------- ----------
0000000060103670          1

在session 2中执行如下
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x0000000060103670 4 1
BEFORE: [060103670, 060103674) = 00000000
AFTER:  [060103670, 060103674) = 00000001
SQL> oradebug dump processstate 10
Statement processed.
SQL> oradebug tracefile_name
/oracle10g/diag/rdbms/test11g/test11g/trace/test11g_ora_1061.trc
SQL> oradebug poke 0x0000000060103670 4 0;
BEFORE: [060103670, 060103674) = 00000001
AFTER:  [060103670, 060103674) = 00000000

然后在session 1中执行，其中select语句一直都在hang，直到session 2中执行复位操作
SQL> set timing on
SQL> select count(*) from v$version;   

  COUNT(*)
----------
         5

Elapsed: 00:01:03.04