API d.side Replay

by laurent | Aug 24, 2022 | Non classifié(e)

Introduction

The data collected by the Replay capture job via the DSIDE_REPLAY.GATHER procedure is typically used by D.SIDE Interactive Replay. It is also easy to transfer this data using the provided export/import tools. Now, you can even use this data without going through the Replay interface. The DSIDE_REPLAY_API PL/SQL package provides functions that allow you to extract the collected data from the Replay schema. Most of these functions return a list of (SNAPSHOT, VALUE), which can easily be leveraged directly in SQL or by third-party analysis tools.

Installation

To access the API, simply create the DSIDE_REPLAY_API package using the provided script, dsreplay_api.sql, which can be requested from our support team.

SQL> @dsreplay_api.sql
Package dropped.

Type dropped.

Type dropped.

Type created.

Type created.

Package created.

Package body created.

SQL>

Data Extraction Using the API

Using the API vian SQL Queries

Once the DSIDE_REPLAY_API package is created, you have access to many functions that allow you to explore the collected data. Here are three usage examples:

CPU peak reached on the machine over the last 3 days:

SELECT MAX(VALUE) FROM TABLE(DSIDE_REPLAY_API.GET_HOST_CPU)
WHERE SNAP_DATE>SYSDATE-3;

Number of queries executed per hour over the last 24 hours:

SELECT to_char(snap_date, 'YYYYMMDDHH24'), sum(VALUE)
FROM TABLE(DSIDE_REPLAY_API.GET_EXECUTE_COUNT)
WHERE SNAP_DATE>SYSDATE-1
GROUP BY to_char(snap_date, 'YYYYMMDDHH24')
ORDER BY to_char(snap_date, 'YYYYMMDDHH24');

“TOP 10” most resource-intensive queries over the last 7 days:

column minsnap new_value beginsnap;
SELECT MIN(SNAP_ID) minsnap FROM TABLE(DSIDE_REPLAY_API.GET_SNAPS_LIST) WHERE SNAP_DATE>SYSDATE-7;
SELECT * FROM TABLE(DSIDE_REPLAY_API.GET_PERIOD_QUERIES(limit=>10, begin_snap=>&beginsnap));

Integration with Third-Party Tools

As we’ve seen, extracting the collected data vian SQL is very straightforward. It is also possible to feed the results of these extractions into third-party tools. For example, you can inject the collected data into a database designed for Grafana, allowing you to create charts using your usual tools.

REST Services

Finally, in the same way, it is possible to interface the D.SIDE API with a REST architecture. For example, Oracle offers ORDS (Oracle REST Data Services):https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/index.html

Note:Using the D.SIDE API requires a minimum version of Oracle. The Replay collection schema must be created on an Oracle 11g or higher database. It is therefore not compatible with Oracle 10g.

← Précédent Suivant →