FAQ
Hi,

To enable auto dop on a new system, I ran the calibrate_io procedure from the dbms_resource_manager package.
However the results are a bit strange.
The actual latency is reported as 0 and so is the max_mbps.
The max_pmbps however is set to 186.

Monitoring with collectl showed network troughput of arround 230 MB/s

Db version is 11.2.0.2 (64bit) on OL 5.4 (64bit).
Storage is NetApp, accessed via dNFS (using 2 1 Gbit/s connections)

I searched MOS and google, but no real bugs popped up.
What can explain the actual latency and the max_mbps to be set to 0?


SQL> @io_callibration.sql
SQL> set serveroutput on
SQL> set timing on
SQL> set echo on
SQL>
SQL> DECLARE
2
3 l_actual_latency integer;
4 l_max_iops integer;
5 l_max_mbps integer;
6
7 BEGIN
8
9 dbms_resource_manager.calibrate_io
10 ( num_physical_disks => 30,
11 max_latency => 20,
12 max_iops => l_max_iops,
13 max_mbps => l_max_mbps,
14 actual_latency => l_actual_latency
15 );
16
17 dbms_output.put_line ('max_iops = ' || l_max_iops);
18 dbms_output.put_line ('actual_latency = ' || l_actual_latency);
19 dbms_output.put_line('max_mbps = ' || l_max_mbps);
20
21 END;
22 /

max_iops = 11750
actual_latency = 0
max_mbps = 0

PL/SQL procedure successfully completed.

Elapsed: 00:53:20.45
SQL>
SQL>
SQL> select * from gv$io_calibration_status;

INST_ID STATUS CALIBRATION_TIME
---------- ------------- ---------------------------------------------------------------------------
1 READY 05-OCT-11 02.56.40.929 PM

Elapsed: 00:00:00.00
SQL>
SQL> set linesize 150
SQL> column start_time format a30
SQL> column end_time format a30
SQL> column num_physical_disks format 9999 heading NPD
SQL> select * from dba_rsrc_io_calibrate;

START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NPD
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -----
05-OCT-11 02.03.20.489842 PM 05-OCT-11 02.56.40.928773 PM 11750 0 186 0 30

Elapsed: 00:00:00.00


Kind regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

Search Discussions

  • D'Hooge Freek at Oct 5, 2011 at 10:56 pm
    It seems to depend on the number of disks and / or the latency.

    When running with 1 disk and a latency of 20, the max_mbps is filled in (but the actual_latency is still 0):

    START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NPD
    ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -----
    05-OCT-11 04.43.02.766110 PM 05-OCT-11 04.57.38.945132 PM 13061 224 190 0 1

    Elapsed: 00:00:00.00

    When I run the calibration on a different 11.2.0.2 system, which is using ASM instead of dNFS

    SQL> select * from dba_rsrc_io_calibrate;

    START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NPD
    ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -----
    05/10/11 18:21:51,323672 05/10/11 18:47:22,379584 10405 491 187 18 1

    Both max_mbs and latency are given now.

    The numbers of the test with latency 20 and 1 disk, are matching what I see when doing full table scan speed tests, so I will leave it for now.

    Thanks to everyone who responded.


    Kind regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge@uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
    ---
    From: goran bogdanovic
    Sent: woensdag 5 oktober 2011 21:01
    To: D'Hooge Freek
    Cc: oracle-l@freelists.org
    Subject: Re: dbms_resource_manager.calibrate_io, strange numbers

    really strange results ... did you try to run orion and compare the results?

    afaik, calibrate_io should be based on orion logic/implementation

    regards,
    goran
  • Randolf Geist at Oct 6, 2011 at 7:05 am
    Freek,

    you might want to have a look at the MOS document "Automatic Degree of Parallelism in 11.2.0.2 [ID 1269321.1]" where it is also mentioned that the calibration routine at present might sometimes produce questionable results.

    For your information the only relevant parameter for Auto DOP in 11.2.0.2 at present seems to be MAX_PMBPS, it looks like that the others are ignored for the Auto DOP calculation anyway - at least what the MOS document seems to suggest by setting / hacking this parameter manually e.g. on Exadata environments.

    I've blogged about that recently: http://oracle-randolf.blogspot.com/2011/07/cost-is-time-next-generation.html

    Hope this helps,
    Randolf
    To enable auto dop on a new system, I ran the calibrate_io procedure from the dbms_resource_manager package.
    However the results are a bit strange.
    The actual latency is reported as 0 and so is the max_mbps.
    The max_pmbps however is set to 186.
    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Oct 8, 2011 at 5:45 am
    If you just need this for auto dop, my recommendation would just
    follow that note and use 200 for MAX_PMBPS (commands below).

    delete from resource_io_calibrate$;
    insert into resource_io_calibrate$ values(current_timestamp,
    current_timestamp, 0, 0, 200, 0, 0);
    commit;
    (bounce instances to take affect)


    On Thu, Oct 6, 2011 at 12:04 AM, Randolf Geist
    wrote:
    Freek,

    you might want to have a look at the MOS document "Automatic Degree of Parallelism in 11.2.0.2 [ID 1269321.1]" where it is also mentioned that the calibration routine at present might sometimes produce questionable results.

    For your information the only relevant parameter for Auto DOP in 11.2.0.2 at present seems to be MAX_PMBPS, it looks like that the others are ignored for the Auto DOP calculation anyway - at least what the MOS document seems to suggest by setting / hacking this parameter manually e.g. on Exadata environments.

    I've blogged about that recently: http://oracle-randolf.blogspot.com/2011/07/cost-is-time-next-generation.html
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 5, '11 at 1:25p
activeOct 8, '11 at 5:45a
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase