support/oracle
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
oracle/vg/sp_sql_capture_histograms.sql
root 93039b8489 2026-03-12 20:23:15
2026-03-12 21:23:47 +01:00

142 lines
7.9 KiB
SQL
Raw Permalink Blame History

@@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
Reference in New Issue View Git Blame Copy Permalink