Tracking ASM Metrics

by Jared Still on Jun 3, 2015 12:00:00 AM

Collecting ASM IO Metrics

If you are an Oracle DBA, then it is quite likely that Oracle ASM is used as the storage management for at least some of the databases you manage. Eventually you will want to see ASM metrics that can be used to track the performance of the storage over time. There are built-in data dictionary views that allow monitoring ASM IO performance at the database, instance, diskgroup and disk level. These are current time metric only however, so they must be collected and saved to be of much use. Point your browser at your favorite search engine and search for the terms Oracle, ASM and metrics. Quite likely near the top of that list relevant hits will be bdt's oracle blog. Bertrand Drouvot has created asm_metrics.pl, an excellent utility for monitoring and reporting on ASM IO metrics. I have used this utility several times now with good results. As good as asm_metrics.pl is, however, I have on several occasions wanted something slightly different. The asm_metrics.pl script output report format, while my preference is to save data in a CSV file. By saving all available metrics in this manner it is not necessary to decide ahead of time how the data is to be used and then chose the appropriate command line options. When all of the metrics are preserved as data there is then no limitation on the types of post-data-collection analysis that can be performed. If for instance, you would like to break down all of the data by disk for a particular disk group, the data will be at your disposal to do so. And so, the following collection of scripts was created. First, I will provide a brief description of each script, followed by detailed usage. asm-metrics-collector.pl This is the main Perl script used for collecting ASM metrics data. asm-metrics-aggregator.pl This Perl script can be used as a filter to aggregate previously collected metrics. asm-metrics-chart.pl Output of either of the previous scripts can be used as input to this script. asm-metrics-chart.pl will create a XLSX format Excel file with line charts for the columns you select. These scripts try not to make too many decisions for you. Their job is simply to get the data, perform some aggregations as needed, and chart the data. While there are many options that could be added to the scripts, I have attempted to keep from making them unnecessarily complicated. Standard Linux command line utilities can be used for many operations that would otherwise require a fair bit of code complexity. Don't have Linux? In this day of free hypervisors, anyone can install and use Linux.

Using the ASM Scripts

The following are more detailed descriptions of each script and their usage. For each script the --help option will provide some explanation of the options, while the --man option may be used to show extended help.

The Perl Environment

All recent versions of the Oracle RDBMS software ship with Perl installed. The Oracle based Perl installations already include the DBI and DBD::Oracle modules which are necessary for connecting to the database. If you don't already have a version of Perl with the DBI and DBD::Oracle modules installed, simply use the one installed with Oracle: [code language="bash"] $ORACLE_HOME/perl/bin/perl asm-metrics-collector.pl ...[/code] The asm-metrics-chart.pl script requires the module Excel::Writer::XLSX. If there is a local copy of Perl that you can add modules to, simply install the Excel::Writer::XLSX module. Otherwise the module can be installed into your home directory. Setting the PERL5LIB environment variable will allow the script to find the local copy of the module. asm-metrics-collector.pl This is the main Perl script used for collecting ASM metrics data.

Connecting to the database

Connections can be made either via bequeath or TNS as SYSDBA or any user with appropriate privileges. The following example makes a bequeath connection to the local instance specified in $ORACLE_SID. As no username or database is specified in the options this connection is the same as '/ as sysdba'. [code language="bash"] asm-metrics-collector.pl -interval 10 --iterations 20 --delimiter , [/code] This is the most basic usage of this script. The --interval parameter refers to the number of seconds between snapshots. With this basic usage, not all columns are captured. The --help and --man options display the optional columns. This next example shows how to include optional columns in the output: [code language="bash"] asm-metrics-collector.pl -interval 5 -iterations 5 -delimiter , --opt-cols DISK_NAME COLD_BYTES_WRITTEN PATH > asm_metrics.csv[/code] And here is an example that causes all available columns to be output: [code language="bash"] asm-metrics-collector.pl -interval 5 -iterations 5 -delimiter , --opt-cols ALL-COLUMNS > asm_metrics_allcols.csv[/code] All output is to STDOUT and so must be redirected as needed. You may have noticed there is no password on the command line and indeed, there is no provision for a password on the command line. In the cause of security, the password must either be entered from the keyboard or sent to the script via STDIN. Following are some examples of connecting with a user that requires a password. This first example will require you to type in the password as the script appears to hang: [code language="bash"]asm-metrics-collector.pl --database orcl --username scott --sysdba > my-asm.csv [/code] The following two examples get the password from a file: [code language="bash"] asm-metrics-collector.pl --database orcl --username scott --sysdba < password.txt > my-asm.csv cat password.txt | asm-metrics-collector.pl --database orcl --username scott --sysdba > my-asm.csv [/code] And just for fun, this method works with the Bash Shell: [code language="bash"]asm-metrics-collector.pl --database orcl --username scott --sysdba <<< scott > my-asm.csv[/code]

asm-metrics-aggregator.pl

By default the output of asm-metrics-collector.pl will include a row for each disk in each diskgroup. Should you wish to observe and chart the read and write times at diskgroup level, the presence of one row per disk causes that operation to be somewhat difficult. The following brief shell script will read output created by asm-metrics-collector.pl, aggregate the chosen columns at the requested level and write it to STDOUT in CSV format. The aggregation level is determined by the --grouping-cols option, while the columns to aggregate are specified with --agg-cols option. [code language="bash"] INPUT_DATAFILE='logs/asm-oravm-data-20150523-172525.csv' OUTPUT_DATAFILE='logs/asm-oravm-aggtest.csv' ./asm-metrics-aggregator.pl \ --grouping-cols DISKGROUP_NAME \ --agg-cols READS WRITES READ_TIME WRITE_TIME \ --display-cols DISPLAYTIME ELAPSEDTIME DBNAME DISKGROUP_NAME READS \ WRITES READ_TIME AVG_READ_TIME WRITE_TIME \ AVG_WRITE_TIME BYTES_READ BYTES_WRITTEN \ -- ${INPUT_DATAFILE} \ > ${OUTPUT_DATAFILE} [/code] You may be wondering about the purpose of the double dashes "--" that appear in the command line. This is how the Perl option processor Getopt::Long knows that there are no more options available on the command line. As the --display-cols option can take several arguments, some method must be used indicating the end of the arguments where there is following text that is is not to be processed as part of the option. The "--" is the option list terminator, and will be well known to long time Unix and Linux users. This script also does one set of calculations behind the scenes; the average read and write times are calculated at the current aggregation level. What if you don't know which columns are available in the input file? Simply use the --list-available-cols option with an input file and the script will output the available columns and exit. [code language="bash"] > ./asm-metrics-aggregator.pl --list-available-cols logs/asm-oravm-data-20150523-172525.csv DISPLAYTIME SNAPTIME ELAPSEDTIME INSTNAME DBNAME GROUP_NUMBER DISK_NUMBER DISKGROUP_NAME READS WRITES READ_TIME AVG_READ_TIME WRITE_TIME AVG_WRITE_TIME BYTES_READ BYTES_WRITTEN DISK_NAME READ_ERRS [/code] As you have probably noticed, input to this script is from STDIN.

asm-metrics-chart.pl

Now it all gets more interesting as we can visualize the data collected. There are many methods available to accomplish this. I chose Excel as it is ubiquitous and easy to work with. The previously discussed Perl module Excel::Writer::XLSX makes it relatively easy to create Excel files complete with charts, directly from CSV data. The following command line will create the default Excel file asm-metrics.xlsx with line chart for reads and writes. The --worksheet-col options specifies that each diskgroup will be shown on a separate worksheet. [code language="bash"]asm-metrics-chart.pl asm-metrics-chart.pl --worksheet-col DISKGROUP_NAME \ --chart-cols READS WRITES -- logs/asm-oravm-20150512_01-agg-dg.csv[/code] This next example provides a non-default name for the Excel file: [code language="bash"] ./asm-metrics-chart.pl \ --worksheet-col DISKGROUP_NAME \ --spreadsheet-file oravm-asm-metrics.xlsx \ --chart-cols READS AVG_READ_TIME WRITES AVG_WRITE_TIME \ -- logs/asm-oravm-aggtest.csv [/code]

Modifying the Data with Command Line Tools

Suppose you have two disk groups, DATA and FRA. The DATA disk group has five disks and FRA has two disks. In addition there are two RAC databases with two instances each. For each iteration the data collected by asm-metrics-collector.pl will have 5 rows per DATA, 2 rows per FRA, multiplied by the number of instances, so 40 rows per iteration. Should you wish to see only the DATA rows for a single instance, and then aggregate these, it can be done via a combination of command line tools and these scripts. First let's get the list of columns and number them so we know how to find the instance: [code language="bash"] > ./asm-metrics-aggregator.pl --list-available-cols logs/asm-oravm-20150512_01.csv|nl 1 DISPLAYTIME 2 SNAPTIME 3 ELAPSEDTIME 4 INSTNAME 5 DBNAME 6 GROUP_NUMBER 7 DISK_NUMBER 8 HEADER_STATUS 9 REDUNDANCY 10 OS_MB 11 TOTAL_MB 12 FREE_MB 13 HOT_USED_MB 14 COLD_USED_MB 15 DISKGROUP_NAME 16 DISK_NAME 17 FAILGROUP 18 LABEL 19 PATH 20 UDID 21 PRODUCT 22 CREATE_DATE 23 MOUNT_DATE 24 REPAIR_TIMER 25 PREFERRED_READ 26 VOTING_FILE 27 SECTOR_SIZE 28 FAILGROUP_TYPE 29 READS 30 WRITES 31 READ_ERRS 32 WRITE_ERRS 33 READ_TIME 34 AVG_READ_TIME 35 WRITE_TIME 36 AVG_WRITE_TIME 37 BYTES_READ 38 BYTES_WRITTEN 39 HOT_READS 40 HOT_WRITES 41 HOT_BYTES_READ 42 HOT_BYTES_WRITTEN 43 COLD_READS 44 COLD_WRITES 45 COLD_BYTES_READ 46 COLD_BYTES_WRITTEN [/code] So now that we know that INSTANCE is column #4, let's see what the available instances are. [code language="bash"] > cut -f4 -d, logs/asm-oravm-20150512_01.csv | sort -u INSTNAME oravm1 oravm2 [/code] To get the headers and the data for only instance oravm1, and only for the DATA diskgroup, aggregated by diskgroup: [code language="bash"] (head -1 logs/asm-oravm-20150512_01.csv ; grep ',oravm1,.*,DATA,' logs/asm-oravm-20150512_01.csv ) | \ ./asm-metrics-aggregator.pl --grouping-cols DISKGROUP_NAME \ --agg-cols READS WRITES READ_TIME WRITE_TIME \ --display-cols DISPLAYTIME ELAPSEDTIME DBNAME DISKGROUP_NAME READS \ WRITES READ_TIME AVG_READ_TIME WRITE_TIME \ AVG_WRITE_TIME BYTES_READ BYTES_WRITTEN \ -- > new-file.csv [/code] That should get you started on modifying the data via standard command line utilities.

To Do

These scripts do not know about any of the ASM enhancements found in 12c, so there is some room for improvement there. So far they have fit my needs, but you may have some ideas to make these scripts better. Or (heaven forbid) you found a bug. Either way, please try them out and let me know. The scripts can all be found in asm-metrics on github. Discover more about Pythian's expertise in Oracle and Jared Still.

No Comments Yet

Let us know what you think

Share this