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.
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.
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.
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;
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.