CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle System Statistics
Version 11.1
 
General Information
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

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 ..,

Notes

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 select name, value from gv$parameter
where name like '%samp%';
Collect System Statistics Demo with no load on the system conn / as sysdba

exec dbms_stats.gather_system_stats();

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

Collect System Statistics Demo with a load on the system
conn / as sysdba

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

exec dbms_stats.delete_system_stats;

GRANT gather_system_statistics TO uwclass;

conn uwclass/uwclass

-- gather workload over the next 15 minutes
exec dbms_stats.gather_system_stats('INTERVAL', 15);

-- download airplanes.sql
@airplanes.sql

-- remove index
drop index ix_program_id;

SELECT *
FROM airplanes;

conn sh/sh

-- run demos on Rollups/Cube page

exec dbms_stats.gather_system_stats('STOP');

conn / as sysdba

SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';
Invalidate Existing Execution Plans SQL> ALTER SYSTEM FLUSH SHARED_POOL;

Estimating the time to perform a Full Table Scan (or index fast full scan) to completion
exec dbms_stats.gather_table_stats('UWCLASS', 'AIRPLANES');

SELECT blocks
FROM user_segments
WHERE segment_name = 'AIRPLANES';
-- returns 1088

/*
SELECT mreadtim * HWM / MBRC
FROM dual;
*/


SELECT 5.121 * 1088 / 8
FROM dual;
-- 696.456 ms.
 
Related Startup Parameters

When workload statistics are not collected these two parameters control the execution and optimizer MBRC.
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;

_db_file_exec_read_count
multiblock read count for regular clients

_db_file_optimizer_read_count
multiblock read count for regular clients
 
Related Topics
DBMS_AUTO_TASK_ADMIN
Startup Parameters
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [176 users online]    © 2010 psoug.org