MacLochlainns Weblog

Using Oracle’s Resource Manager requires you to understand the IO dynamics. The first step requires you to run the CALIBRATE_IO procedure from the DBMS_RESOURCE_MANAGER package.

Oracle provides some great examples about how to use the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package in the Oracle Database Database PL/SQL Packages and Types Reference. The CALIBRATE_IO procedure returns the best answer when you provide a valid number of files, which you can capture by querying the V$ASM_DISK view.

The following code queries the view and assigns the value to a session level variable:

CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
VARIABLE files NUMBER
 
BEGIN
  SELECT COUNT(DISTINCT name) disks
  INTO :files
  FROM v$asm_disk;
END;
/

When you have the number of files, you can calibrate the IO with the following anonymous block. The query should always work but just in case the NVL function on line 9 assigns the default number of files.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DECLARE
  lv_num_physical_disks BINARY_INTEGER; — v$asm_disk
  lv_max_latency BINARY_INTEGER := 10;
  lv_max_iops BINARY_INTEGER;
  lv_max_mbps BINARY_INTEGER;
  lv_actual_latency BINARY_INTEGER;
BEGIN
  /* Assign actual files to anonymous block variable. */
  lv_num_physical_disks := NVL(:files,2);
 
  /* Run the calibrate_io procedure. */
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
      num_physical_disks => lv_num_physical_disks
    , max_latency => lv_max_latency
    , max_iops => lv_max_iops
    , max_mbps => lv_max_mbps
    , actual_latency => lv_actual_latency);
END;
/

You can query the results like this:

SELECT max_iops
,      max_mbps
,      max_pmbps
,      latency
,      num_physical_disks
FROM   dba_rsrc_io_calibrate;

It should show results like these:

MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
-------- -------- --------- ------- ------------------
    8894      443       294       9                 18

Hope this helps those using the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package.

Who can resist a free Rich Nimeiec book on SQL Tuning? O.K., those who know everything can resist. If you’re like me, this is an opportunity to learn from Rich. Click on the book image or this link to get a free copy, or if you want to pay $10 for a copy click here to buy Quick Start Guide to Oracle Query Tuning: Tips for DBAs and Developers from Amazon.com.

The book is four chapters long, is a 129 pages in length, and is in a PDF format. The outline is:

1. Query Tuning: Developer and Beginning DBA
2. Query Tuning: Basics for DBAs and Developers
3. Advanced Performance Tuning
4. Tips for Tuning When You Have Everything Tuned

Enjoy reading it. His more comprehensive book is Oracle Database 11g Release 2 Performance Tuning Tips & Techniques (Oracle Press) and it’s $30, but it’s written for an advanced audience (more or less OCA or higher).

Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the FROM clause), but then they get errors like this:

SELECT	 instance.get_type()
         *
ERROR AT line 4:
ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier

The problem is how Oracle treats runtime views, which appears to me as a casting error. Somewhat like the ORDER BY clause irregularity that I noted in July, the trick is complete versus incomplete syntax. The following query fails and generates the foregoing error:

1
2
3
4

SELECT instance.get_type() AS object_type
,      instance.to_string() AS object_content
FROM  (SELECT dependent()AS instance
       FROM   dual);

If you add a table alias, or name, to the runtime view on line 4, it works fine:

1
2
3
4

SELECT cte.instance.get_type() AS object_type
,      cte.instance.to_string() AS object_content
FROM  (SELECT dependent() AS instance
       FROM   dual) cte;

That is the trick. You use an alias for the query, which assigns the alias like a table reference. The reference lets you access instance methods in the scope of a query. Different columns in the query’s SELECT-list may return different results from different methods from the same instance of the object type.

You can also raise an exception if you forget the open and close parentheses for a method call to a UDT, which differs from how Oracle treats no argument functions and procedures. That type of error would look like this:

SELECT cte.instance.get_type AS object_type
       *
ERROR AT line 1:
ORA-00904: : invalid identifier

It is an invalid identifier because there’s no public variable get_type, and a method is only found by using the parenthesis and a list of parameters where they’re required.

The object source code is visible by clicking on the expandable label below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

DROP TYPE dependent_t;
DROP TYPE base_t;
 
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Create an object.
CREATE OR REPLACE TYPE base_t IS OBJECT
( TYPE       VARCHAR2(20)
, CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT
, MEMBER FUNCTION get_type RETURN VARCHAR2
, MEMBER FUNCTION to_string RETURN VARCHAR2) 
INSTANTIABLE NOT FINAL;
/
 
-- Create an object body.
CREATE OR REPLACE TYPE BODY base_t IS
CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT IS
BEGIN
  RETURN;
END base_t;
MEMBER FUNCTION get_type RETURN VARCHAR2 IS
BEGIN
  RETURN self.TYPE;
END;
MEMBER FUNCTION to_string RETURN VARCHAR2 IS
BEGIN
  RETURN self.TYPE;
END to_string;
END;
/
 
-- Create a subtype.
CREATE OR REPLACE TYPE dependent UNDER base_t
( child  VARCHAR2(40)
, CONSTRUCTOR FUNCTION dependent RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION dependent
  ( child  VARCHAR2 ) RETURN SELF AS RESULT
, OVERRIDING MEMBER FUNCTION get_type RETURN VARCHAR2
, OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2) 
INSTANTIABLE NOT FINAL;
/
 
-- Create a subtype body.
CREATE OR REPLACE TYPE BODY dependent IS
  CONSTRUCTOR FUNCTION dependent RETURN SELF AS RESULT IS
  BEGIN
    /* Assign subtype name to type. */
    self.TYPE := 'DEPENDENT';
    RETURN;
  END dependent;
  CONSTRUCTOR FUNCTION dependent
  ( child  VARCHAR2 ) RETURN SELF AS RESULT IS
    lv_dependent DEPENDENT := dependent();
  BEGIN
    /* Assign default constructor to self instance. */
    self := lv_dependent;
    /* Assign parameters to object instance. */
    self.TYPE  := 'DEPENDENT';
    self.child := child;
    RETURN;
  END dependent;
  OVERRIDING MEMBER FUNCTION get_type RETURN VARCHAR2 IS
  BEGIN
    RETURN self.TYPE;
  END;
  OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN self.TYPE;
  END to_string;
END;
/

As always, I hope this helps those solving problems.