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.
For more details about Replay API, please refer to https://docs.dside-software.com/docs/replay-api/