参考MOS ID 262296.1
该脚本运行在database release 8.XX and above
以下脚本为统计在每个存储过程里SQL语句执行的次数,这个脚本可以帮助衡量包的使用,看是否需要将包PIN到内存。
set echo on
col STORED_OBJECT format a30
col kglnaown format a20
select /*+ ordered use_hash(d) use_hash(c) */
o.kglnaown ,
o.kglnaobj stored_object,
sum(c.kglhdexc) sql_executions
from sys.x$kglob o, sys.x$kglrd d, sys.x$kglcursor c
where o.inst_id = userenv('Instance')
and d.inst_id = userenv('Instance')
and c.inst_id = userenv('Instance')
and o.kglobtyp in (7, 8, 9, 11, 12)
and d.kglhdcdr = o.kglhdadr
and c.kglhdpar = d.kglrdhdl
and o.kglnaown not in ('SYS','SYSMAN')
group by o.kglnaown, o.kglnaobj;
运行该脚本前请重新校验核对。