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