Monitoring Exadata Storage Cells - Oracle Exadata Recipes: A Problem-Solution Approach - page 391

13-7. Reporting and Summarizing metrichistory Using
Oracle and SQL
Problem
You wish to summarize historical Exadata Storage Server metrics in order to recognize performance trends using
Oracle tables and SQL.
Solution
In this recipe, you will learn how to load metrichistory information into an Oracle table or tables to use SQL to report
on your metrics. We will provide sample scripts for extracting metrichistory information to a flat file and build an
Oracle external table from this table.
To begin, scope your analysis to one or more metrics, run the list metrichistory command from dcli or
cellcli , and save the output to a text file:
[oracle@cm01dbm01 cellscr]$ dcli -g ./cell_group list metrichistory \
where name='DB_IO_LOAD' > ./db_io_load.dat
Next, create an external table using this data and displayed in Listing 13-4:
Listing 13-4: lst13-04-bld-ext-tab.sql
SQL> create table celliorm.db_metrics_ext
(cell_server varchar2(100),
metric_name varchar2(100),
metric_dtl varchar2(256),
dtl_value number,
dtl_unit varchar2(36),
sample_time varchar2(256))
organization external
(type oracle_loader
default directory my_dir
access parameters
(records delimited by newline
fields terminated by whitespace)
location ('db_io_load.dat')
) reject limit 100000000
/
Table created
This script assumes that you have created an Oracle directory named my_dir that contains your extract file and
also assumes you have a schema named celliorm created; you can change these names to meet your needs.
At this point, you can run SQL statements against this external table to report your performance metrics or,
optionally, load this external table into a more “permanent” table and use this for a historical storage cell metrics
repository.