Database Reference
In-Depth Information
SQL Adapter
The SQL adapter is the one you used in the preceding example. Because you are already familiar with its basic
capabilities, let's just review its additional options.
If your SQL is too long, you might prefer to upload a SQL file instead of specifying SQL code inline. The uploaded
SQL file is distributed with your metric extension to the agents that collect your metrics, and these agents then run
SQL from the file.
If you are selecting a predefined set of name-value measurements, you can use transposed results instead of
creating a two-column metric with the name as the key column and value as the data column. For example, say you
want to collect four metrics by using the query in Listing 10-3. You would need to create the first column as a key.
However, you would have no control of column naming, and alert messages could be only generic.
Listing 10-3.
SQL Statement to Collect Physical Reads and Writes
select name, value
from v$sysstat
where name in ('physical read total IO requests',
'physical read total bytes',
'physical write total IO requests',
'physical write total bytes')
order by name;
NAME VALUE
------------------------------------- -----------
physical read total IO requests 2285653
physical read total bytes 21313147392
physical write total IO requests 1245633
physical write total bytes 27849077248
Note that the
ORDER BY
clause is added to make sure the results are always in the same order (in this case, the
same order as in the
IN
list). Now you just need to create four data columns, and you can specify more-descriptive
names such as
Total Physical Read Requests
and a custom alert message:
Total Physical Read Requests is too high -
%value%
(warning threshold is
%warning_threshold%
and critical threshold is
%critical_threshold%
)
As you can see in this example, you can include the thresholds in your alert message.
You can also specify bind variables in SQL text instead of literals. However, this is useful only if you generate
the value of the variable dynamically, and metric extensions don't really provide much flexibility there (other than
predefined variables that are not very useful as SQL parameters). If you end up in this situation, you are probably
better off to redesign and merge those metric extensions into one by using additional key column(s). However, if you
have multiple metric extensions using the same SQL but different literals, then bind variables are the way to go.
It's also possible to use a PL/SQL block returning a cursor (this is what the
Out
parameter is used for in the
advanced properties) instead of plain SQL. It's useful if you need to use dynamic SQL or perform metric collection
differently based on certain conditions, such as the Oracle database version or the version of the application you are
extracting the data from.
The last example also demonstrates an important limitation of metric extensions: they are not very useful
when working with data that requires additional processing based on a number of previous collections (such as the
cumulative counters used in the example). We are not really interested in the total number of IO requests since the
start of the database instance, but instead in the average number of IO requests over the collection period. Even
though the formula would be very simple (the difference between two collected values divided by the collection
interval), metric extensions do not provide a way of calculating such ratios. Of course, you can use a PL/SQL block to
store previously collected counters and calculate the ratio per interval, but it's not as trivial and also introduces much
more significant measurement intrusion when your measurements actually cause the changes to the database.
