Monitor I/O performance on Solaris with DTrace script and Oracle Database 12c with pluggable database

Well, like all of you out there fellow Oracle DBAs, I am super excited about playing around with newest release of Oracle Database 12c. Since I am home alone in the next few weeks, besides painting the flat and similar house activities, guess what else I will be doing? 🙂 Of course, try to throttle as much CPU and IO possible from my home server running virtual machines.

What to do you need?

• Dtrace iopattern script written by the genius Brendan Gregg which comes along with DTraceToolkit version 0.99.
• Oracle Database
• SunOS 5.10 to run the script

Since 12c release come out recently, I have decided to create one pluggable database, create one external table and fetch the results from iopattern script. Before running the iopattern script, we will modify it in such a way that it will not print the column header names on every 20 lines and we will also comma separate the output and enclose it with quotes. This way we will easily fetch the output using an external table.

Open the script and go to the line# 156 and make sure LINES=1.

/*
  * Program start
  */
 dtrace:::BEGIN
 {
        /* starting values */
	diskcnt = 0;
	diskmin = 0;
	diskmax = 0;
	diskran = 0;
	diskr = 0;
	diskw = 0;
        counts = COUNTER;
        secs = INTERVAL;
	LINES = 1;
	line = 0;
	last_event[""] = 0;
 }

Then immediately bellow at line# 165 you need to make the second change to line=0.

/*
* Print header
*/
profile:::tick-1sec
/line = 0 /
{
/* print optional headers */
OPT_time ? printf("%-20s ", "TIME") : 1;
OPT_device ? printf("%-9s ", "DEVICE") : 1;
OPT_mount ? printf("%-12s ", "MOUNT") : 1;
OPT_file ? printf("%-12s ", "FILE") : 1;

/* print header */
printf("%4s %4s %6s %6s %6s %6s %6s %6s\n",
"%RAN", "%SEQ", "COUNT", "MIN", "MAX", "AVG", "KR", "KW");

line = LINES;
}

Third and last change is on lines#246,252,253 and 254 by adding double quote and comma to optional fields (OPT_time) and in the print data section.

	/* print optional fields */
	OPT_time   ? printf("\"%-20Y\",", walltimestamp) : 1;
	OPT_device ? printf("%-9s ", DEVICE) : 1;
	OPT_mount  ? printf("%-12s ", MOUNT) : 1;
	OPT_file   ? printf("%-12s ", FILENAME) : 1;

	/* print data */
	printf("\"%d\",\"%d\",\"%d\",\"%d\",\"%d\",\"%d\",\"%d\",\"%d\"\n",
            diskran, diskseq, diskcnt, diskmin, diskmax, diskavg,
            diskr, diskw);

Run the modified iopattern script with -v option to add the timestamp output and save the output to a file.

nohup ./iopattern -v 10 43200 > iopattern_hostname.dat &

If you have done the changes above, than after a few minutes the iopattern_hostname.dat should be populated with I/O performance information from your hostname.

"2013 Jul  5 15:22:10","53","47","5004","512","524288","326212","1535696","58415"
"2013 Jul  5 15:22:20","64","36","8585","512","524288","217093","1670752","149314"
"2013 Jul  5 15:22:30","59","41","5557","512","524288","241380","1188360","121555"
"2013 Jul  5 15:22:40","64","36","10825","512","524288","186383","1647872","322440"
"2013 Jul  5 15:22:50","66","34","10282","512","524288","190390","1666832","244877"
"2013 Jul  5 15:23:00","71","29","11472","512","524288","170426","1638032","271272"

Next we will create/clone a new pluggable database from an existing PDB.

column name format a10
select DBID, name, CDB, CON_ID, CON_DBID
from gv$database;

      DBID NAME       CDB     CON_ID   CON_DBID
---------- ---------- --- ---------- ----------
1274083969 DB12C      YES          0 1274083969
1274083969 DB12C      YES          0 1274083969

-- Check the existing PDBs
SQL> select inst_id, name, con_id
from gv$active_services
where con_id > 2 --Exclude ROOT and SEED
order by 2,1;

Ok, we do have an existing PDB called pdb12c and we will clone this one into a new pluggable database called pdbmon.

   INST_ID NAME           CON_ID
---------- ---------- ----------
         1 pdb12c              3
         2 pdb12c              3

-- Datafiles for existing PDB
column name format a80
select name, con_id, plugged_in
from v$datafile
where con_id > 2
order by 2;

NAME                                                                                 CON_ID PLUGGED_IN
-------------------------------------------------------------------------------- ---------- ----------
+DATA/DB12C/E0D4B3F29C873F47E0436538A8C09AF4/DATAFILE/system.278.820060571                3          0
+DATA/DB12C/E0D4B3F29C873F47E0436538A8C09AF4/DATAFILE/users.281.820060717                 3          0
+DATA/DB12C/E0D4B3F29C873F47E0436538A8C09AF4/DATAFILE/sysaux.279.820060571                3          0

Before we clone the existing PDB, let's check the state of the database

column name format a10
select name, inst_id, open_mode, con_id
from gv$pdbs
order by 1,2,4;

NAME          INST_ID OPEN_MODE      CON_ID
---------- ---------- ---------- ----------
PDB$SEED            1 READ ONLY           2
PDB$SEED            2 READ ONLY           2
PDB12C              1 MOUNTED             3
PDB12C              2 MOUNTED             3

Why CON_ID column is important? Well as per Oracle documentation

• 0 – The data pertains to the entire CDB
• 1 – The data pertains to the root
• 2 – The data pertains to the seed
• 3 – 254 – The data pertains to a PDB. Each PDB has its own container ID.

which means that each new pluggable database will have CON_ID > 2 and in my case the cloned PDB will have CON_ID=4 since I already have existing PDB with CON_ID=3. Now let’s clone existing PDB12C into PDBMON.

SQL> CREATE PLUGGABLE DATABASE pdbmon FROM PDB12C
STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 100M);

ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

Cause: An operation was attempted that can only be performed in the root container.
Action: Switch to the root container to perform the operation.

Ok, we need to switch to the root container and than clone the existing PDB.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> CREATE PLUGGABLE DATABASE pdbmon FROM PDB12C
STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 100M);   2

Pluggable database created.

Let’s check if the datafiles are created as well as metadata about PDBMON.

SQL> select inst_id, name, con_id
from gv$active_services
where con_id > 2 --Exclude ROOT and SEED
order by 2,1;

   INST_ID NAME           CON_ID
---------- ---------- ----------
         1 pdb12c              3
         2 pdb12c              3
         1 pdbmon              4
         2 pdbmon              4

SQL> column name format a80
select name, con_id, plugged_in
from v$datafile
where con_id > 2
order by 2;

NAME                                                                                 CON_ID PLUGGED_IN
-------------------------------------------------------------------------------- ---------- ----------
+DATA/DB12C/E0D4B3F29C873F47E0436538A8C09AF4/DATAFILE/system.278.820060571                3          0
+DATA/DB12C/E0D4B3F29C873F47E0436538A8C09AF4/DATAFILE/sysaux.279.820060571                3          0
+DATA/DB12C/E0D4B3F29C873F47E0436538A8C09AF4/DATAFILE/users.281.820060717                 3          0
+DATA/DB12C/E0DFB405AB58276FE0436638A8C0096D/DATAFILE/system.286.820109577                4          0
+DATA/DB12C/E0DFB405AB58276FE0436638A8C0096D/DATAFILE/sysaux.284.820109577                4          0
+DATA/DB12C/E0DFB405AB58276FE0436638A8C0096D/DATAFILE/users.285.820109577                 4          0

Create one user and check the state of newly cloned PDB.

alter session set container=CDB$ROOT;

column name format a10
select name, inst_id, open_mode, con_id
from gv$pdbs
order by 1,2,4;

NAME          INST_ID OPEN_MODE      CON_ID
---------- ---------- ---------- ----------
PDB$SEED            1 READ ONLY           2
PDB$SEED            2 READ ONLY           2
PDB12C              1 MOUNTED             3
PDB12C              2 MOUNTED             3
PDBMON              1 MOUNTED             4
PDBMON              2 MOUNTED             4

-- Switch to the root container
alter session set container=CDB$ROOT;

-- Create the user "C##MON" and grant privileges
create user c##mon identified by oracle;
grant create session to C##MON container=ALL;
grant dba to C##MON container=ALL;

-- Open the PDBMON database. Note: In my case I am using RAC DB, so I have to open the DB on both nodes.
alter session set container=PDBMON;

NAME          INST_ID OPEN_MODE      CON_ID
---------- ---------- ---------- ----------
PDBMON              1 MOUNTED             4
PDBMON              2 MOUNTED             4

-- From both nodes
SQL> alter database open;

Database altered.

NAME          INST_ID OPEN_MODE      CON_ID
---------- ---------- ---------- ----------
PDBMON              1 READ WRITE          4
PDBMON              2 READ WRITE          4

Next edit your tnsnames.ora files and add entry for PDMON database.

vi $ORACLE_HOME/network/admin/tnsnames.ora

PDBMON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBMON)
    )
  )

Connect to the PDMON and create the external table in order to fetch the output from iopattern script.

$ sqlplus c##mon/oracle@pdbmon

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 7 00:36:52 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

-- Create the directory which holds the output on iopattern. 
-- In my case I use ACFS so the file is available from both RAC nodes.
SQL> create directory dir_monitoring
as
'/u01/app/oracle/nakinov';

Directory created.

-- Create the external table
SQL> create table iopattern_hostname
    (
       sample_time          date,
      pct_random        number(3),
      pct_sequential    number(3),
      io_count          number(38),
       io_min           number(38),
       io_max           number(38),
       io_avg           number(38),
       io_kr            number(38),
       io_kw            number(38)
    )
    organization external
    ( default directory dir_monitoring
      access parameters
      ( records delimited BY newline fields terminated by "," optionally enclosed by '"'
        LDRTRIM
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL FIELDS
        (
         sample_time        CHAR(22) date_format DATE mask "YYYY MON  DD HH24:MI:SS",    
         pct_random         CHAR(3),
         pct_sequential     CHAR(3),
         io_count           CHAR(255),
         io_min             CHAR(255),
         io_max             CHAR(255),
         io_avg             CHAR(255),
         io_kr              CHAR(255),
         io_kw              CHAR(255)
        )
      )
      location ('iopattern_hostname.dat')
  );


Table created.

-- Check if all is working
SQL> set autotrace on
SQL> set lines 300
SQL> column st format a21
column io_count format 999999
select to_char(sample_time,'dd.mm.yyyy hh24:mi:ss') st, io_count
from iopattern_hostname
order by sample_time
FETCH FIRST 10 ROWS ONLY;

ST                    IO_COUNT
--------------------- --------
05.07.2013 15:22:10       5004
05.07.2013 15:22:20       8585
05.07.2013 15:22:30       5557
05.07.2013 15:22:40      10825
05.07.2013 15:22:50      10282
05.07.2013 15:23:00      11472
05.07.2013 15:23:10      12389
05.07.2013 15:23:20      17184
05.07.2013 15:23:30      15731
05.07.2013 15:23:40       7404

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3575133555

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |  8168 |   366K|    29   (0)| 00:00:01 |
|*  1 |  VIEW                        |                    |  8168 |   366K|    29   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK    |                    |  8168 |   175K|    29   (0)| 00:00:01 |
|   3 |    EXTERNAL TABLE ACCESS FULL| IOPATTERN_HOSTNAME |  8168 |   175K|    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "SAMPLE_TIME")<=10)


Statistics
----------------------------------------------------------
         20  recursive calls
          0  db block gets
         42  consistent gets
          0  physical reads
          0  redo size
        940  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

Since “FETCH FIRST 10 ROWS ONLY” is also a new 12c feature for top-n queries, I wonder if analytical function can be avoided (2 – filter(ROW_NUMBER() OVER ( ORDER BY “SAMPLE_TIME”)<=10)). Download both original and modified iopattern script as well as sample output data.

More about managing multitenant environments on the official Oracle documentation about 12c..