oracle常用脚本--Parse count

parse count (total)：总计解析次数，包括硬解析和软解析

select ss.snap_id as snap_id,
       to_char(sn.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "snap_date",
       (sum(decode(stat_name, 'parse count (total)', value, 0)) -
       lag(sum(decode(stat_name, 'parse count (total)', value, 0)), 1)
        over(order by ss.snap_id)) /
       ((to_date(to_char(sn.end_interval_time, 'yyyymmddhh24miss'),
                 'yyyymmddhh24miss') -
       to_date(to_char(sn.begin_interval_time, 'yyyymmddhh24miss'),
                 'yyyymmddhh24miss')) * 24 * 60 * 60) "parse count (total)"
  from SYS.DBA_HIST_sysstat ss, SYS.DBA_HIST_snapshot sn
 where ss.snap_id = sn.snap_id
   and ss.stat_name in ('parse count (total)')
   and ss.dbid = sn.dbid
   and ss.instance_number = sn.instance_number
   and sn.instance_number = (select instance_number from v$instance)
   and sn.dbid = (select dbid from v$database)
   group by ss.snap_id,
          sn.begin_interval_time,
          sn.end_interval_time 

 

select ss.snap_id as snap_id,

       to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi:ss')as "snap_date",

       (sum(decode(stat_name,'parse count (total)',value,0))-

       lag(sum(decode(stat_name,'parse count (total)',value,0)),1)

        over(orderby ss.snap_id))/

       ((to_date(to_char(sn.end_interval_time,'yyyymmddhh24miss'),

                 'yyyymmddhh24miss')-

       to_date(to_char(sn.begin_interval_time,'yyyymmddhh24miss'),

                 'yyyymmddhh24miss'))*24*60*60) "parse count (total)"

  from SYS.DBA_HIST_sysstat ss, SYS.DBA_HIST_snapshot sn

 where ss.snap_id = sn.snap_id

   and ss.stat_name in('parse count (total)')

   and ss.dbid = sn.dbid

   and ss.instance_number = sn.instance_number

   and sn.instance_number =(select instance_number from v$instance)

   and sn.dbid =(select dbid from v$database)

   groupby ss.snap_id,

          sn.begin_interval_time,

          sn.end_interval_time