Oracle 11g中的IO Calibrate（IO校準）--Automatic Degree of Parallelism(DOP)

In this Document

APPLIES TO:

PURPOSE

In 11.2.0.2 Automatic Degree of Parallelism can only be used if  I/O statistics are gathered.

SCOPE

For DBAs 

The AutoDOP is not a feature to use more parallelism. It is a feature that restricts the parallel to maximize throughput,
so it is expected that with AutoDOP not all queries will run in parallel and the ones that do run in parallel may not run with full parallelism, as this is the technical specifications of the feature.

Ask Questions, Get Help, And Share Your Experiences With This Article

DETAILS

When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether a statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics.

AutoDOP is used when PARALLEL or PARALLEL(AUTO) statement level hint is used regardless of the value of the PARALLEL_DEGREE_POLICY (see documentation).

IO Calibration

If I/O calibration has not been run to gather the required statistics, the explain plan includes the following text in its notes section:  ": skipped because of IO calibrate statistics are missing"

explain plan for 
select /*+ parallel */ * from emp;

Plan hash value: 2873591275 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |   532 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

 Note 
 ----- 
 - dynamic sampling used for this statement (level=2) 
 - automatic DOP: skipped because of IO calibrate statistics are missing

The Oracle PL/SQL package DBMS_RESOURCE_MANAGER.CALIBRATE_IO is used to execute the calibration. The duration of the calibration is dictated by the NUM_DISKS variable as well as the number of nodes in the RAC cluster.

SET SERVEROUTPUT ON
DECLARE
   lat INTEGER;
   iops INTEGER;
   mbps INTEGER;
BEGIN
    --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (28, 10, iops, mbps, lat);
   DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
   DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
   dbms_output.put_line('max_mbps = ' || mbps);
END;
/

NUM_DISKS - To get the most accurate results, its best to provide the actual number of physical disks that are used for this database. The Storage Administrator can provide this value. Keep in mind that when ASM is used to manage the database files, say in the DATA diskgroup, then only physical disks that make up the DATA diskgroup should be used for the NUM_DISKS variable; i.e.; do not include the disks from the FRA diskgroup. In the example above the DATA diskgroup is made up of 28 physicals (presented as 4 LUNs or ASM disks)

LATENCY– Maximum tolerable latency in milliseconds for database-block-sized IO requests.

You find more information about CALIBRATE_IO in Note: 727062.1  Configuring and using Calibrate I/O.

In order to verify whether the calibration run was successful, query V$IO_CALIBRATION_STATUS after you executed DBMS_RESOURCE_MANAGER.CALIBRATE_IO call.

select * from V$IO_CALIBRATION_STATUS;

STATUS   CALIBRATION_TIME
-------- ----------------------------- 
READY    25-NOV-10 08.53.08.536

The execution plan now shows that the feature automatic degree of parallelism can be used:

explain plan for 
select /*+ parallel */ * from emp;

Plan hash value: 2873591275 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |   532 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Note 
----- 
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 2 

 There is known issue with DBMS_RESOURCE_MANAGER.CALIBRATE_IO.

Note: 10180307.8 DBRM DBMS_RESOUCE_MANAGER.CALIBRATE_IO REPORTS VERY HIGH MAX_PMBPS If CALIBRATE_IO can not be used you can set the relevant value manual:

 You have to restart the database after this change.

Tuning Parameters

When you use AutoDOP, you may want to adjust some tuning parameters.  See Document 1549214.1 Setup, Monitor, And Tune Parallelism In The Database for information about these parameters.   The parallel_servers_target should always be smaller than parallel_max_servers, with parallel_servers_target anywhere from 75% to 50% of parallel_max_servers.  If you start seeing a lot of DOP downgrades, you should make the distance between the values for these two parameters greater.

Overview I/O Calibration

Oracle Database 11g introduces an I/O Calibration mechanism, whereby you can run I/O calibration tests either through the Enterprise Manager Performance page or a PL/SQL package. Oracle’s I/O calibration is a variation on the Clarion tool. In an Oracle database, the I/O workload is of two basic types—small random I/O and large sequential I/O. OLTP applications usually experience the small random I/O workload, where the speed with which small I/O requests are serviced is paramount. Thus, disk spinning and seeking times are of critical importance. OLAP applications, on the other hand, employ the large sequential I/O in general. For these types of applications, the critical factor is the capacity of the I/O channel. The larger the I/O channels between the database server and the storage system, the larger the I/O throughput. Oracle uses the following two metrics, each measuring the efficacy of one type of I/O workload:

• IOPS (I/O per second)  The IOPS rate is the number of small random I/Os the system can perform in a second and depends on the spin speed of disks. You can increase the IOPS rate by increasing the number of disks in the storage array or by using faster disk drives, which have a high RPM and lower seek time.
• MBPS (megabytes per second)  This metric measures the data transfer rate between the server and the storage array and depends on the capacity of the I/O channel between the two systems. A larger I/O channel means a higher MBPS rate.

Two important terms need clarification in this discussion: throughput and latency. The throughput of a system determines how fast it can transfer data and is measured by the MBPS metric. The channel capacity determines the overall throughput of the system, and it thus puts the ceiling on the amount of data transfer. Latency refers to the lag between the time an I/O request is made and when the request is serviced by the storage system. High latency indicates a system that’s overloaded and you can reduce latency by striping data across multiple spindles, so different disks can service the same I/O request in parallel.

Oracle recommends that you use the new I/O Calibration tool to determine I/O metrics in a database. It takes about 10 minutes to run the tools and you should pick a time when the database workload is light to avoid overstressing the storage system. You can run only a single calibration task at a time. If you perform the task in an RAC environment, the workload is generated simultaneously from all instances in the system. You can either run the tool with Enterprise Manager or through PL/SQL.

Calibrating I/O Using PL/SQL

You can also use the new procedure CALIBRATE_IO from the DBMS_ RESOURCE_MANAGER package to run the I/O Calibration task. Here is an example:

begin
  exec dbms_resource_manager.calibrate_io(-
  num_disks         => 1,   -
  max_latency       => 10, -
  max_iops          => :max_iops, -
  max_mbps          => :max_mbps, -
  actual_latency    => :actual_latency);
 end;
 /
In the CALIBRATE_IO procedure, the following are the key parameters:

• num_disks: Approximate number of disks in the storage array.
• max_latency: Maximum tolerable latency (in milliseconds) for an I/O request.
• max_ios: Maximum number of random DB block-sized read requests that can be serviced.
• max_mbps: Maximum number of randomly distributed 1MB reads that can be serviced (in megabytes per second).
• actual_latency: Average latency of DB block-sized I/O requests at max_iops rate (in milliseconds).

The procedure only works if asynchronous I/O is enabled. If asynchronous I/O is not enabled, the procedure returns the following error.
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 453
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153
ORA-06512: at line 6
You can use the FILESYSTEMIO_OPTIONS static initialization parameter to enable or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform.
FILESYTEMIO_OPTIONS can be set to one of the following values:

• ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.
• DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.
• SETALL: enable both asynchronous and direct I/O on file system files.
• NONE: disable both asynchronous and direct I/O on file system files.

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Usage Notes

• Only users with the SYSDBA privilege can run this procedure. Qualified users must also turn on timed_statistics, and ensure asynch_io is enabled for datafiles. This can be achieved by setting filesystemio_options to either ASYNCH or SETALL. One can also query the asynch_io status by means of the following SQL statement:
  col name format a50
  SELECT name, asynch_io FROM v$datafile f,v$iostat_file i
  WHERE f.file#        = i.file_no
  AND   filetype_name  = 'Data File';
• Only one calibration can be run at a time. If another calibration is initiated at the same time, it will fail.
• For an Oracle Real Application Clusters (Oracle RAC) database, the workload is simultaneously generated from all instances.

For timed_statistics paramter,please refer to following illustration :

TIMED_STATISTICS specifies whether or not statistics related to time are collected.Values:

• true: The statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.
• false: The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system.

Starting with release 11.1.0.7.0, the value of the TIMED_STATISTICS parameter cannot be set to false if the value of STATISTICS_LEVEL is set to TYPICAL or ALL.
On some systems with very fast timer access, Oracle might enable timing even if this parameter is set to false. On these systems, setting the parameter to true can sometimes produce more accurate statistics for long-running operations.

The [G]V$IO_CALIBRATION_STATUS views show the current status of the calibration runs. During a run the status of 'IN PROGRESS' is displayed. Once a run is complete the status switches to 'READY' and the calibration time is displayed.Besides,NOT AVAILABLE mean Calibration results not available and CALIBRATION_TIME tell us End time of the last calibration run
SQL> SELECT * FROM v$io_calibration_status;
STATUS        CALIBRATION_TIME
------------- -------------------------------
IN PROGRESS
SQL> SELECT * FROM v$io_calibration_status;
STATUS        CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY         28-JUL-2008 14:37:38.410
1 row selected.

Once you execute the CALIBRATE_IO procedure, you can query the V$IO_ CALIBRATION_STATUS and the DBA_RSRC_IO_CALIBRATE views to check the results. Here’s a sample query:

SQL> select max_iops, max_mbps, max_pmbps, latency
     from dba_rsrc_io_calibrate;

MAX_IOPS      MAX_MBPS      MAX_PMBPS     LATENCY
----------   ------------  ------------  ----------
133           12             6             64
Oracle Database 11g collects I/O statistics in three different dimensions to provide a consistent set of statistics for I/O calls.These dimensions are RDBMS components grouped into 12 functional groups.

• The V$IOSTAT_ FUNCTION view provides the details. I/O statistics are collected for each consumer group that’s part of the currently enabled resource plan.
• The V$IOSTAT_CONSUMER_GROUP view has the details.
• Individual file level I/O statistics are collected and stored in the V$IOSTAT_ FILE view for details.

參考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

                 http://www.oracle-base.com/articles/11g/resource-manager-enhancements-11gr1.php
                 http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams259.htm#REFRN10218
                 http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_resmgr.htm#ARPLS67599

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面寶典》讀者群       小麥苗的微店 .............................................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2148709/，如需轉載，請註明出處，否則將追究法律責任。