ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
System Statistics are used by the CBO to make better decisions based upon its knowledge of server's subsystem capabilities
System Statistics Sampled
CPUSPEED
Workload CPU speed in millions of cycles/second
CPUSPEEDNW
Noworkload CPU speed in millions of cycles/second
IOSEEKTIM
Seek time + latency time + operating system overhead time in milliseconds
IOTFRSPEED
Rate of a single read request in bytes/millisecond
MAXTHR
Maximum throughput that the I/O subsystem can deliver in bytes/second
MBRC
Average multiblock read count sequentially in blocks
MREADTIM
Average time for a multi-block read request in milliseconds
SLAVETHR
Average parallel slave I/O throughput in bytes/second
SREADTIM
Average time for a single-block read request in milliseconds
Dependencies
AUX_STATS$
Dynamic Sampling Levels from the Optimizer Dynamic Sampling initialization parameter
optimizer_dynamic_sampling=2
The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter:
Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query;
(2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes;
(4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate.
The number of blocks sampled is the default number of dynamic sampling blocks.
For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns.
The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
Dynamic Sampling Levels if set by the Dynamic Sampling optimizer hint
Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).
Levels 2, 3, 4, 5, 6, 7, 8, and 9: The number of blocks sampled is 2, 4, 8, 16, 32, 64, 128, or 256 times the default number of dynamic sampling blocks respectively.
Level 10: Read all blocks in the table.
If there is no single-table predicate (a WHERE clause that evaluates only one table), then the optimizer trusts the existing statistics and ignores this hint.
For example, the following query will not result in any dynamic sampling if employees is analyzed:
SELECT /*+ dynamic_sampling(e 1) */ COUNT(*)
FROM employees e;
If there is a single-table predicate, then the optimizer uses the existing cardinality statistic and estimates the selectivity of the predicate using the existing statistics.
To apply dynamic sampling to a specific table, use the following form of the hint:
SELECT /*+ dynamic_sampling(employees 1) */ *
FROM employees
WHERE ..,
Note: These comments are untested but should be considered
A null MBRC may indicate that db_file_multiblock_read_count is set to 0.
The default value for iotfrspeed is 10. If you get that rerun.
When mreadtim is small than sreadtim workload stats are ignored by the query optimizer and the noworkload stats are used.
mreadtim is computed based on X$KCFIO, mbrc is computed based on V$SYSSTAT (X$KSUSGSTA)
Determine current dynamic sampling setting
conn / as sysdba
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%samp%';
Collect System Statistics Demo with no load on the system
conn / as sysdba
SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN'
ORDER BY 1;
SELECT blocks
FROM user_segments
WHERE segment_name = 'AIRPLANES';
BLOCKS
-------
1088
/*
SELECT mreadtim * HWM / MBRC
FROM dual;
*/
SELECT 5.121 * 1088 / 8
FROM dual;
5.121*1088/8
------------
696.456
Related Startup Parameters
When workload statistics are not collected these two parameters control the execution and optimizer MBRC
col name format a40
col description format a80
SELECT x.ksppinm NAME, y.ksppstvl VALUE, ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = SYS_CONTEXT('USERENV', 'Instance')
AND y.inst_id = SYS_CONTEXT('USERENV', 'Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '_db_file%'
ORDER BY 1;
NAME VALUE DESCRIPTION
------------------------------------ -------
--------------------------------------------
_db_file_direct_io_count 1048576 Sequential I/O buf size
_db_file_exec_read_count 128 multiblock read count for regular clients
_db_file_format_io_buffers 4 Block formatting I/O buf count
_db_file_noncontig_mblock_read_count 11 number of noncontiguous db blocks to be
prefetched
_db_file_optimizer_read_count 8 multiblock read count for regular clients
pdb_file_name_convert PDB file name convert patterns and strings
for create cdb/pdb
