Database Reference
In-Depth Information
Enabling I/O calibration using dbms_resource_manager package will enable the AutoDOP feature. Calibration
results are visible in v$io_calibration_status view. Note that there is no documented method to remove the
calibration,
5
and so this change must be tested in a non-production database before calibrating I/O in production.
To calibrate I/O, you can choose any instance of a cluster and trigger I/O calibration. The database engine uses
just one instance to calibrate I/O, but since I/O resource is common to all nodes, performing calibration in one node
is good enough.
SET SERVEROUTPUT ON
DECLARE
l_lat INTEGER;
l_iops INTEGER;
l_mbps INTEGER;
BEGIN
-- Calibrate I/O and print statistics
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 20, l_iops, l_mbps, l_lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || l_iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || l_lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || l_mbps);
END;
/
6
If the estimated run time exceeds the value of the parameter parallel_min_time_threshold, then the statement is
reparsed with a higher DOP. The parameter value of parallel_min_time_threshold defaults to a value of AUTO, which
indicates a threshold of 10 seconds. If you prefer more queries to be considered for parallelism, then you can decrease
the value of the parallel_min_time_threshold parameter to less than 10 seconds.
After the completion of I/O calibration, regenerating event 10053 trace file for the same SQL statement shows
that AutoDOP was enabled for this statement execution. The optimizer uses calibrated I/O statistics to convert
the cost to time using I/O calibration statistics. In this example, the estimated time is 38 seconds, exceeding
parallel_min_time_threshold parameter, and so optimizer considers AutoDOP.
kkeCostToTime: using io calibrate stats
maxmbps=10(MB/s) maxpmbps=11(MB/s)
block_size=8192 mb_io_count=4 mb_io_size=30247 (bytes)
tot_io_size=422(MB) time=38387(ms)
AutoDOP: Table/Index(#76100) access (scan) cost=14638.09 estTime=38386.60 unit=10000.00
dop=3 -> maxdop=3
Best:: AccessPath: TableScan
Cost: 14638.09 Degree: 1 Resp: 14638.09 Card: 159987.00 Bytes: 0
Following lines from the trace file shows that optimizer is triggering a reparse of the SQL statement with a
statement-level parallelism of 3.
AUTODOP PLANS EVALUATION
***************************************
Compilation completed with DOP: 1.
Cost_io: 59742.00 Cost_cpu: 1227068251.35
Card: 318995.00 Bytes: 1279807940.00
5
It is possible to manually delete rows from an underlying table. Please contact Oracle Support to remove I/O calibration if needed.
6
Optimizer estimated cost is roughly a number normalized to single block reads.
