Oracle System Stats
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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 /*+ DYNAMIC_SAMPLING (@<query_block><tablespace><level>) */
  • Level 0: Do not use dynamic sampling.
  • 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';

PNAME            PVAL1
---------------- ----------
CPUSPEED
CPUSPEEDNW       757.249919
IOSEEKTIM                10
IOTFRSPEED             4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

exec dbms_stats.gather_system_stats();

SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';

PNAME            PVAL1
---------------- ----------
CPUSPEED
CPUSPEEDNW             1745
IOSEEKTIM                10
IOTFRSPEED             4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
Collect System Statistics Demo with a load on the system

conn / as sysdba

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

exec dbms_stats.delete_system_stats;

exec dbms_stats.gather_system_stats('INTERVAL', 15);

SELECT line, count(*)
FROM source$
GROUP BY line
HAVING COUNT(*) > 100

conn uwclass/uwclass@pdbdev

-- download airplanes.sql and create the airplanes table
@airplanes.sql

-- remove index
drop index ix_program_id;

SELECT *
FROM airplanes;

conn sh/sh@pdbdev

-- run demos on Rollups/Cube page of the library

exec dbms_stats.gather_system_stats('STOP');

conn / as sysdba

col pval1 format 99999.999

SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';

PNAME                 PVAL1
---------------- ----------
CPUSPEEDNW         1745.000
IOSEEKTIM            10.000
IOTFRSPEED         4096.000
SREADTIM               .403
MREADTIM               .888
CPUSPEED           1633.000
MBRC                 29.000
MAXTHR
SLAVETHR

Estimating the time to perform a Full Table Scan (or index fast full scan) to completion conn sys@pdbdev as sysdba

exec dbms_stats.gather_table_stats('UWCLASS', 'AIRPLANES');

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

Related Topics
Built-in Functions
Built-in Packages
DBMS_AUTO_TASK_ADMIN
DBMS_STATS
Startup Parameters
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved