Databases Reference
In-Depth Information
How It Works
Auto DOP requires I/O calibration statistics to be present in your database. In 11gR2, this is typically achieved by
executing the following
DBMS_RESOURCE_MANAGER.CALIBRATE_IO
procedure as documented in Oracle's documentation
Listing 21-8.
lst21-08-io-calibrate-drm.sql
SQL> set serveroutput on
declare
lat integer;
iops integer;
mbps integer;
begin
dbms_resource_manager.calibrate_io(&&num_disks,10,iops,mbps,lat);
dbms_output.put_line('Max IOPs = '||iops);
dbms_output.put_line('Latency = '||lat);
dbms_output.put_line('Max MBPS = '||iops);
end;
/
In the above script, you would enter the number of disks on your Exadata Storage Servers (36 for a Quarter Rack,
84 for a Half Rack, and 168 for a Full Rack) and a desired I/O latency, which in the script above is set to 10 milliseconds
When complete, the
resource_io_calibrate$
table is populated with the values calculated by the procedure.
The odd thing about this procedure is the 10-millisecond latency input parameter; after all, how would you
know what your maximum disk latency truly is, other than trusting published benchmarks? The answer is that you
cannot know this, especially considering potential variations with different workloads. This fact renders
dbms_resource_manager.calibrate_io
relatively useless, but Auto DOP still requires for I/O calibration to be performed.
Luckily, the only column in
resource_io_calibrate$
that Auto DOP requires is max_pmbps. As such, simply
inserting a value of 200 into this column and restarting your database will enable Auto DOP to properly and
automatically parallelize your queries.
■
You may wonder where the number 200 comes from; this is the number that oracle uses internally to test auto
Dop on exadata and is referenced in My oracle support note 1297112.1.
Note
21-6. Measuring Impact of Auto DOP and Parallel Statement
Queuing
Problem
You have implemented Auto DOP on your Exadata databases and wish to measure the impact of parallel statement
queuing on your database.
Solution
In this recipe, we will show you how to implement Auto DOP with parallel statement queuing and demonstrate
how to measure the impact of parallel statement queuing. Specifically, we will present a test case with multiple
Search WWH ::
Custom Search