@@header set term off /* * * Author : Vishal Gupta * Purpose : Display sql capture histograms (equi-width buckets) from statspack repository * Paraemter: 1 - bucket number (default value 20) * 2 - threshold multiplier (default value 10) * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 05-Aug-07 Vishal Gupta First Draft * * */ set term on set lines 200 set pages 50 define bucket_numbers="&&1" define threshold_multiplier="&&2" COLUMN executions_bucket_ceil HEADING "Executions|Bucket Ceil" FORMAT 999,999 ON COLUMN parse_calls_bucket_ceil HEADING "ParseCalls|Bucket Ceil" FORMAT 999,999 ON COLUMN disk_reads_bucket_ceil HEADING "DiskReads|Bucket Ceil" FORMAT 999,999,999 ON COLUMN buffer_gets_bucket_ceil HEADING "BufferGets|Bucket Ceil" FORMAT 999,999,999 ON COLUMN sharable_mem_bucket_ceil HEADING "SharableMem|Bucket Ceil" FORMAT 999,999,999 ON COLUMN version_count_bucket_ceil HEADING "Version|Bucket Ceil" FORMAT 999,999 ON COLUMN executions_Count HEADING "Executions|Count" FORMAT 999,999 ON COLUMN parse_calls_Count HEADING "ParseCalls|Count" FORMAT 999,999 ON COLUMN disk_reads_Count HEADING "DiskReads|Count" FORMAT 999,999 ON COLUMN buffer_gets_Count HEADING "BufferGets|Count" FORMAT 999,999 ON COLUMN sharable_mem_Count HEADING "SharableMem|Count" FORMAT 999,999 ON COLUMN version_count_Count HEADING "Version|Count" FORMAT 999,999 ON COLUMN executions_percent HEADING "Executions|% Records" FORMAT 999.99 ON COLUMN parse_calls_percent HEADING "ParseCalls|% Records" FORMAT 999.99 ON COLUMN disk_reads_percent HEADING "DiskReads|% Records" FORMAT 999.99 ON COLUMN buffer_gets_percent HEADING "BufferGets|% Records" FORMAT 999.99 ON COLUMN sharable_mem_percent HEADING "SharableMem|% Records" FORMAT 999.99 ON COLUMN version_count_percent HEADING "Version|% Records" FORMAT 999.99 ON with /* Define the constants for this script */ constants as (select NVL(&bucket_numbers,20) bucket_numbers -- number of width balanced histograms , NVL(&threshold_multiplier,10) threshold_multiplier -- statspack parameter threshold multipler from dual), /* Define the constants for this script */ buckets as (SELECT ss.SNAP_ID , WIDTH_BUCKET(ss.executions , 0, sp.EXECUTIONS_TH * c.threshold_multiplier, c.bucket_numbers ) executions_BUCKET_num , WIDTH_BUCKET(ss.parse_calls , 0, sp.PARSE_CALLS_TH * c.threshold_multiplier, c.bucket_numbers ) parse_calls_BUCKET_num , WIDTH_BUCKET(ss.disk_reads , 0, sp.DISK_READS_TH * c.threshold_multiplier, c.bucket_numbers ) disk_reads_BUCKET_num , WIDTH_BUCKET(ss.buffer_gets , 0, sp.BUFFER_GETS_TH * c.threshold_multiplier, c.bucket_numbers ) buffer_gets_BUCKET_num , WIDTH_BUCKET(ss.sharable_mem , 0, sp.SHARABLE_MEM_TH * c.threshold_multiplier, c.bucket_numbers ) sharable_mem_BUCKET_num , WIDTH_BUCKET(ss.version_count, 0, sp.VERSION_COUNT_TH * c.threshold_multiplier, c.bucket_numbers ) version_count_BUCKET_num , WIDTH_BUCKET(ss.executions , 0, sp.EXECUTIONS_TH * c.threshold_multiplier, c.bucket_numbers ) * ( (sp.EXECUTIONS_TH * c.threshold_multiplier)/c.bucket_numbers) executions_BUCKET_ceil , WIDTH_BUCKET(ss.parse_calls , 0, sp.PARSE_CALLS_TH * c.threshold_multiplier, c.bucket_numbers ) * ( (sp.PARSE_CALLS_TH * c.threshold_multiplier)/c.bucket_numbers) parse_calls_BUCKET_ceil , WIDTH_BUCKET(ss.disk_reads , 0, sp.DISK_READS_TH * c.threshold_multiplier, c.bucket_numbers ) * ( (sp.DISK_READS_TH * c.threshold_multiplier)/c.bucket_numbers) disk_reads_BUCKET_ceil , WIDTH_BUCKET(ss.buffer_gets , 0, sp.BUFFER_GETS_TH * c.threshold_multiplier, c.bucket_numbers ) * ( (sp.BUFFER_GETS_TH * c.threshold_multiplier)/c.bucket_numbers) buffer_gets_BUCKET_ceil , WIDTH_BUCKET(ss.sharable_mem , 0, sp.SHARABLE_MEM_TH * c.threshold_multiplier, c.bucket_numbers ) * ( (sp.SHARABLE_MEM_TH * c.threshold_multiplier)/c.bucket_numbers) sharable_mem_BUCKET_ceil , WIDTH_BUCKET(ss.version_count, 0, sp.VERSION_COUNT_TH * c.threshold_multiplier, c.bucket_numbers ) * ( (sp.VERSION_COUNT_TH * c.threshold_multiplier)/c.bucket_numbers) version_count_BUCKET_ceil FROM stats$sql_summary ss , stats$statspack_parameter sp , v$database d , v$instance i , constants c WHERE ss.dbid = d.dbid AND ss.instance_number = i.instance_number AND sp.dbid = d.dbid AND sp.instance_number = i.instance_number ), executions_buckets as ( select rownum lv_rownum, a1.* from (select executions_BUCKET_num, executions_BUCKET_ceil, count(1) count from buckets group by executions_BUCKET_num, executions_BUCKET_ceil order by 1) a1 ) , parse_calls_buckets as ( select rownum lv_rownum, a2.* from (select parse_calls_BUCKET_num, parse_calls_BUCKET_ceil, count(1) count from buckets group by parse_calls_BUCKET_num, parse_calls_BUCKET_ceil order by 1) a2 ) , disk_reads_buckets as ( select rownum lv_rownum, a3.* from (select disk_reads_BUCKET_num, disk_reads_BUCKET_ceil, count(1) count from buckets group by disk_reads_BUCKET_num, disk_reads_BUCKET_ceil order by 1) a3 ) , buffer_gets_buckets as ( select rownum lv_rownum, a4.* from (select buffer_gets_BUCKET_num, buffer_gets_BUCKET_ceil, count(1) count from buckets group by buffer_gets_BUCKET_num,buffer_gets_BUCKET_ceil order by 1) a4 ) , sharable_mem_buckets as ( select rownum lv_rownum, a5.* from (select sharable_mem_BUCKET_num, sharable_mem_BUCKET_ceil, count(1) count from buckets group by sharable_mem_BUCKET_num, sharable_mem_BUCKET_ceil order by 1) a5 ) , version_count_buckets as ( select rownum lv_rownum, a6.* from (select version_count_BUCKET_num, version_count_BUCKET_ceil, count(1) count from buckets group by version_count_BUCKET_num, version_count_BUCKET_ceil order by 1) a6 ) select e.executions_bucket_ceil , p.parse_calls_BUCKET_ceil , d.disk_reads_BUCKET_ceil , b.buffer_gets_BUCKET_ceil , s.sharable_mem_BUCKET_ceil , v.version_count_BUCKET_ceil -- , e.count executions_count -- , p.count parse_calls_count -- , d.count disk_reads_count -- , b.count buffer_gets_count -- , s.count sharable_mem_count -- , v.count version_count_count , ROUND((e.count / total_count)*100,2) executions_percent , ROUND((p.count / total_count)*100,2) parse_calls_percent , ROUND((d.count / total_count)*100,2) disk_reads_percent , ROUND((b.count / total_count)*100,2) buffer_gets_percent , ROUND((s.count / total_count)*100,2) sharable_mem_percent , ROUND((v.count / total_count)*100,2) version_count_percent from executions_buckets e , parse_calls_buckets p , disk_reads_buckets d , buffer_gets_buckets b , sharable_mem_buckets s , version_count_buckets v , (select count(1) total_count from stats$sql_summary ss , v$instance i , v$database d WHERE ss.dbid = d.dbid AND ss.instance_number = i.instance_number ) where e.lv_rownum = p.lv_rownum (+) and e.lv_rownum = d.lv_rownum (+) and e.lv_rownum = b.lv_rownum (+) and e.lv_rownum = s.lv_rownum (+) and e.lv_rownum = v.lv_rownum (+) order by 1 / @@footer