An Oracle PL/SQL procedure to calculate IO cost of Full Table Scan.
DECLARE
v_owner_name VARCHAR2 (30) := 'SCOTT'; -- Schema
v_table_name VARCHAR2 (30) := 'EMP'; -- Table name
v_cost_model VARCHAR2 (30);
v_block_size NUMBER; -- db_block_size
v_dbforc NUMBER; --_db_file_optimizer_read_count
v_dbfmbrc NUMBER; -- db_file_multiblock_read_count
v_blocks NUMBER;
v_cost NUMBER;
v_io_cost NUMBER; -- from explain plan
v_#MRDs NUMBER; -- number of multi block reads
v_sreadtim NUMBER; -- single block read time
v_mreadtim NUMBER; -- multi block read time
v_msg VARCHAR2 (2000);
TYPE typ_sys_stat_tab IS TABLE OF NUMBER
INDEX BY VARCHAR2 (30);
v_sys_stat typ_sys_stat_tab;
BEGIN
-- db_block_size: 8192 (default)
SELECT VALUE
INTO v_block_size
FROM v$parameter
WHERE name = 'db_block_size';
-- Session Value of _db_file_optimizer_read_count: 8 (default)
SELECT b.ksppstvl "Session Value"
INTO v_dbforc
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm = '_db_file_optimizer_read_count';
SELECT b.ksppstvl "Session Value"
INTO v_dbfmbrc
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm = 'db_file_multiblock_read_count';
-- table block# used
SELECT blocks
INTO v_blocks
FROM all_tables
WHERE OWNER = v_owner_name AND table_name = v_table_name;
-- get system statistics parameters
FOR r1 IN (SELECT pname, pval1
FROM sys.aux_stats$)
LOOP
v_sys_stat (r1.pname) := r1.pval1;
END LOOP;
/*
_OPTIMIZER_COST_MODEL (hidden) parameter:
'CHOOSE' - default: chooses CPU costing based on the presence of statistics in aux_stats$,
'CPU' - forces CPU costing based on statistics in aux_stats$,
'IO' - forces IO costing
*/
SELECT b.ksppstvl "Session Value"
INTO v_cost_model
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm = '_optimizer_cost_model';
v_msg := 'Full Table Scan: ' || v_owner_name || '.' || v_table_name;
v_msg := v_msg || CHR (10) || ' number of used table blocks: ' || v_blocks;
v_msg := v_msg || CHR (10) || ' db_block_size: ' || v_block_size;
v_msg := v_msg || CHR (10) || ' _optimizer_cost_model: ' || v_cost_model;
v_msg := v_msg || CHR (10) || ' _db_file_optimizer_read_count (session value): ' || v_dbforc;
v_msg := v_msg || CHR (10) || ' db_file_multiblock_read_count (session value): ' || v_dbfmbrc;
IF v_cost_model = 'IO'
THEN
v_cost := CEIL (v_blocks * 0.5965 / POWER (v_dbforc, 0.6582));
v_msg :=
v_msg
|| CHR (10)
|| ' Formaula: io_cost = ceil(blocks * 0.5965/ _db_file_optimizer_read_count ^ 0.6582'
|| CHR (10)
|| ' _db_file_optimizer_read_count: '
|| v_dbforc;
ELSE -- 'CHOOSE' or 'CPU'
IF v_sys_stat ('FLAGS') = 0 OR v_sys_stat ('MBRC') IS NULL
THEN
IF v_sys_stat ('FLAGS') = 0
THEN
v_msg := v_msg || CHR (10) || 'System Statistics type: Default NOWORKLOAD';
ELSE
v_msg := v_msg || CHR (10) || 'System Statistics type: Gathered NOWORKLOAD';
END IF;
v_msg := v_msg || CHR (10) || ' IOSEEKTIM : ' || v_sys_stat ('IOSEEKTIM');
v_msg := v_msg || CHR (10) || ' IOTFRSPEED: ' || v_sys_stat ('IOTFRSPEED');
v_sreadtim := v_sys_stat ('IOSEEKTIM') + v_block_size / v_sys_stat ('IOTFRSPEED');
v_msg := v_msg || CHR (10) || 'SREADTIM = IOSEEKTIM + db_block_size / IOTFRSPEED';
v_msg := v_msg || CHR (10) || ' single-block I/O read time: ' || v_sreadtim;
v_mreadtim := v_sys_stat ('IOSEEKTIM') + v_dbforc * v_block_size / v_sys_stat ('IOTFRSPEED');
v_msg := v_msg || CHR (10) || 'MREADTIM = IOSEEKTIM + mbrc * db_block_size / IOTFRSPEED';
v_msg := v_msg || CHR (10) || ' multi-block I/O read time: ' || v_mreadtim;
v_#MRDs := v_blocks / v_dbforc;
v_msg := v_msg || CHR (10) || '#MRDs = table block# used / _db_file_optimizer_read_count';
v_msg := v_msg || CHR (10) || ' #MRDs: ' || v_#MRDs;
v_cost := GREATEST (1, CEIL (v_#MRds * v_mreadtim / v_sreadtim));
ELSE
v_msg := v_msg || CHR (10) || 'System Statistics type: Gathered WORKLOAD';
v_msg := v_msg || CHR (10) || ' use SYS.AUX_STATS$.MBRC to calculate #MRDs';
v_msg := v_msg || CHR (10) || ' SREADTIM : ' || v_sys_stat ('SREADTIM');
v_msg := v_msg || CHR (10) || ' MREADTIM : ' || v_sys_stat ('MREADTIM');
v_msg := v_msg || CHR (10) || ' MBRC : ' || v_sys_stat ('MBRC');
v_#MRDs := v_blocks / v_sys_stat ('MBRC');
v_msg := v_msg || CHR (10) || '#MRDs = table block# used / MBRC';
v_msg := v_msg || CHR (10) || ' #MRDs: ' || v_#MRDs;
v_cost := GREATEST (1, CEIL (v_#MRds * v_sys_stat ('MREADTIM') / v_sys_stat ('SREADTIM')));
END IF;
DBMS_OUTPUT.put_line (v_msg);
DBMS_OUTPUT.put_line ('-----------------');
END IF;
DBMS_OUTPUT.PUT_LINE ('io_cost = MAX(1, CEIL(blocks / MBRC * MREADTIM / SREADTIM))');
DBMS_OUTPUT.put_line ('COST calculated: ' || v_cost);
DBMS_OUTPUT.put_line ('-----------------');
-- EXPLAIN PLAN
--EXECUTE IMMEDIATE 'ALTER SESSION SET "_table_scan_cost_plus_one" = FALSE';
-- EXECUTE IMMEDIATE 'alter session set db_file_multiblock_read_count=' || v_mbrc;
EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR SELECT * FROM ' || v_owner_name || '.' || v_table_name;
SELECT IO_COST
INTO v_io_cost
FROM plan_table
WHERE operation = 'TABLE ACCESS'
AND options = 'FULL'
AND object_name = UPPER (v_table_name)
AND plan_id = (SELECT MAX (plan_id) FROM plan_table);
DBMS_OUTPUT.put_line ('COST from explain plan: ' || v_io_cost);
END;
REFERENCES
- How To Calculate CPU Cost (Doc ID 457228.1)
- How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated? (Doc ID 1398860.1)
- Cost-Based Oracle Fundamentals By Jonathan Lewis
Oracle Tech Notes 