Creating REST APIs for AWR with ORDS and SQL

The Automatic Workload Repository (AWR) for the Oracle Database is a kind of a performance archive for the activity in your instance.

On a regular basis, a SNAPSHOT is taken.

You can then reference points in time, via a START and END point via these SNAPSHOTs and the database will generate some reports for you.

This feature requires the Enterprise Edition of the database AND the Diagnostic Pack. Please follow along with this tutorial with that in mind. If you’re on Autonomous Database, as I’m demonstrating today, you’re ‘in the clear.’

In this post you’ll learn how to:

• use SQL and PL/SQL to build REST APIs
• generate LINKs in your HTTP Responses
• generate HTML output for your HTTPS Responses

Snapshots and Reports

The API will allow you to GET a list of AWR Snapshots. Those snapshots will include links to reports generated based on the previous 5 snapshots. 5 is an arbitrary numbers, once you see the SQL, feel free to change it to meet your needs. Note as the delta increases, so will the amount of time to generate the report.

So there will be two URI templates in our AWR Module:

How does this work?

At the end of this post, you’ll find the SQL script necessary to publish this AWR module in your own ORDS/Database environment. I suggest you run that now, then come back and follow along.

Ready?

Let’s look at the SQL, in detail for the GET handler on the snapshots template..

SELECT SNAP_ID,DBID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME , 
   '../awr/report/'||DBID||'/'||INSTANCE_NUMBER||'/'||(SNAP_ID-1)||'/'||SNAP_ID "$report-1",
   '../awr/report/'||DBID||'/'||INSTANCE_NUMBER||'/'||(SNAP_ID-2)||'/'||SNAP_ID "$report-2",
   '../awr/report/'||DBID||'/'||INSTANCE_NUMBER||'/'||(SNAP_ID-3)||'/'||SNAP_ID "$report-3",
   '../awr/report/'||DBID||'/'||INSTANCE_NUMBER||'/'||(SNAP_ID-4)||'/'||SNAP_ID "$report-4",
   '../awr/report/'||DBID||'/'||INSTANCE_NUMBER||'/'||(SNAP_ID-5)||'/'||SNAP_ID "$report-5"
FROM dba_hist_snapshot 
WHERE INSTANCE_NUMBER=2 
ORDER BY SNAP_ID DESC

So the data is coming from DBA_HIST_SNAPSHOT. Meaning, we’re assuming in this API definition, that the schema you are publishing it in, has access to the DBA_ views.

“$report-1” .. “$report-5” – these are column ALIASES. The $ in the name tells ORDS that the string coming back from the query should be presented in the JSON response as a LINK. The text coming after the $ tells ORDS how the link should be labeled via the “rel” attribute.

../awr/report/ – this is saying that the link to be generated, needs to have a relative path one level back from where we are at.

Remember, the Module has two templates –

‘||DBID||’/’||INSTANCE_NUMBER||’/’||(SNAP_ID-1)||’/’||SNAP_ID – as part of the link being generated, we’re taking values from the result set and inserting them. If you look at the picture above, you can see the URI template for the report is

/report/:dbid/:inst/:snapbegin/:snapend – these are the input values for the call we’re going to make when generating the reports.

Modeling the Templates

When I start on a new set of REST APIs, I spend a few minutes drawing out the ‘paths’ that my API consumers are going to ‘walk.’

Where do they start? Where will they go from there? Since REST is stateless, LINKs are critical for navigating from point to point in your application or whatever.

So I need to start with a list of snapshots, those are key to generating the reports. And then I need a way to get from a list of those snapshots to the reports themselves. What’s necessary to run the report? Those inputs will need to be baked into the links coming into that endpoint.

What do I build first? I’ll be honest, many times I’ll build the ‘end page’ (AWR Report) first, and then circle back and cover the ‘index’ type pages (SNAPSHOT listing).

Often times I’ll build dummy HANDLER blocks just to get a feel for what things will look like, once I’m happy with the module templates and handler layouts, I’ll go back and start writing the bulk of the handler code.

Further disclaimer – this module was written by @krisrice, but I’m sure he has a similar process, except better as he’s a professional (10x!) developer.

The Reports

This is where the fun begins – generating the HTML.

Let’s look at the PL/SQL Block –

BEGIN
FOR cur_rep IN (SELECT output
                      FROM   
                      TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(:dbid,:inst,:snapbegin,:snapend )))
      LOOP
        htp.prn(cur_rep.output);
      END LOOP;
END;

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML() – the package.function call will return lines of HTML.

If we look at what this does BEFORE ORDS gets ahold of the output –

The FOR LOOP iterates these rows. We iterate the output using HTP.PRN, it’s nice because it handles escaping text as necessary for HTML rendering.

Walking the API, Generating a Report

So let’s browse the snapshots, and GET a report.

The Code

After you’ve enabled your schema, whilst logged in as that user, run this script.

 
-- Generated by ORDS REST Data Services 20.4.3.r0501904
-- Schema: ADMIN  Date: Tue Mar 16 06:05:04 2021 
--
 
DECLARE
  l_roles     OWA.VC_ARR;
  l_modules   OWA.VC_ARR;
  l_patterns  OWA.VC_ARR;
 
BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'awr',
      p_base_path      => '/awr/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'awr',
      p_pattern        => 'snapshots',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'awr',
      p_pattern        => 'snapshots',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select SNAP_ID,DBID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME , 
   ''../awr/report/''||DBID||''/''||INSTANCE_NUMBER||''/''||(SNAP_ID-1)||''/''||SNAP_ID "$report-1",
   ''../awr/report/''||DBID||''/''||INSTANCE_NUMBER||''/''||(SNAP_ID-2)||''/''||SNAP_ID "$report-2",
   ''../awr/report/''||DBID||''/''||INSTANCE_NUMBER||''/''||(SNAP_ID-3)||''/''||SNAP_ID "$report-3",
   ''../awr/report/''||DBID||''/''||INSTANCE_NUMBER||''/''||(SNAP_ID-4)||''/''||SNAP_ID "$report-4",
   ''../awr/report/''||DBID||''/''||INSTANCE_NUMBER||''/''||(SNAP_ID-5)||''/''||SNAP_ID "$report-5"
from dba_hist_snapshot 
where INSTANCE_NUMBER=2 
order by SNAP_ID desc');
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'awr',
      p_pattern        => 'report/:dbid/:inst/:snapbegin/:snapend',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'awr',
      p_pattern        => 'report/:dbid/:inst/:snapbegin/:snapend',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'begin
FOR cur_rep IN (SELECT output
                      FROM   
                      TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(:dbid,:inst,:snapbegin,:snapend )))
      LOOP
        htp.prn(cur_rep.output);
      END LOOP;
end;');
 
 
 
  l_modules(1) := 'awr';
 
  ORDS.DEFINE_PRIVILEGE(
      p_privilege_name => 'stuff.awr',
      p_roles          => l_roles,
      p_patterns       => l_patterns,
      p_modules        => l_modules,
      p_label          => 'awr_stuff',
      p_description    => 'generates AWR reports, who can do what',
      p_comments       => NULL);  
 
  l_roles.DELETE;
  l_modules.DELETE;
  l_patterns.DELETE;                                    
 
COMMIT;
 
END;

Bonus Time!

What if we wanted to do something like…create a new SNAPSHOT on demand?

That’s right, you don’t have to wait for the next scheduled snapshot, if you’re about to do a performance test, you can create a snapshot (on demand), do your work, then create another snapshot.

I added this to what Kris put together. I could probably just redirect the location to the base /snapshots URI, or I could build a new /snapshots/:id template and GET handler to show the details, but I’m done working on this example for today 🙂

Here’s the SQL for the POST block.

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
    :status := 201;
    SELECT name INTO :snapshot
    FROM ( SELECT DISTINCT TO_CHAR(s.snap_id)
                      || '/'
                      || TO_CHAR(s.dbid)
                      || '/'
                      || TO_CHAR(s.instance_number) AS name
                      , 'PERFORMANCE_SNAPSHOT'  AS TYPE
                      , s.snap_id
                      , s.dbid
                      , s.instance_number
           FROM awr_pdb_snapshot s
       ORDER BY s.snap_id DESC
	FETCH FIRST 1 ROWS ONLY);
END;

Don’t forget to add your Parameters. I didn’t include this BONUS handler to the script above as I haven’t really tested it as much as I should have.