PRF - AWR Performance Analysis Workflow (Jira, DETAILED, ITIL-ALIGNED)

PRF - AWR Performance Analysis Workflow (Jira, DETAILED, ITIL-ALIGNED) Spec: Jira issue: host, ORACLE_SID/PDB, and start/end date-time fields Jira-driven, dispatcher + per-issue job include, matching the ServiceNow project's structure. Includes documented risk-mitigation and best-practice controls
Jira Process Decision SQL/Execute ITIL Alignment Risk Mitigation Best Practice Close OK Close Failed Follow-up
1	PRE-FLIGHT: validate Jira API token via /rest/api/2/myself
2	PRE: shepherd PRF intake queue toward 'In Progress'
→	ITIL alignment: the shepherd's approval walk is a real ITIL change-model gate, even though this analysis itself is read-only
3	PRE: search Jira for PRF issues ready to execute
4	PRE: collect issues found
5	PRE: end play when queue is empty
6	PRE: build job list from Jira issues
7	PRE: WARN about issues with an unusable spec
8	PRE: keep only jobs with a usable spec
9	PRE: nothing valid to process
10	PRE: jobs queued
→	Unknown host — skip this issue
11	JOB: resolve Oracle home/user from oracle_instance_map
12	JOB: set per-job facts
13	JOB: comment: analysis started
14	JOB: ensure base + per-job working directory exists and is writable
15	JOB: write snapshot-resolution SQL + wrapper (explicit ORACLE_SID export)
16	JOB: resolve snapshot IDs as oracle
17	JOB: fail cleanly if no bracketing snapshots were found
→	Risk mitigation: failing cleanly with a clear reason (outside retention window, no snapshots yet) rather than a confusing downstream error
18	JOB: show resolved snapshot range
19	JOB: write AWR report SQL + top-5 CPU SQL_ID query
20	JOB: write execution wrapper (explicit ORACLE_SID export)
21	JOB: run AWR report + top-5 query as oracle
22	JOB: parse top-5 SQL_IDs from output
23	JOB: determine whether top-5 extraction succeeded
24	JOB: show top SQL_IDs found
25	JOB: write and run SQL Tuning Advisor for each SQL_ID (ALTER SESSION SET CONTAINER first)
→	Risk mitigation: execute_tuning_task only produces advice — this entire analysis carries zero write risk to production data
26	JOB: attach AWR report + each tuning report to the issue
27	JOB: post analysis summary comment
28	JOB: get available transitions for DONE
29	JOB: transition to 'Done'
30	JOB: get available transitions for FAILED
31	JOB: transition to 'Failed'
32	JOB: open follow-up task for the DBA team to implement fixes
→	Best practice: any recommended fix is routed through a separate follow-up task, never applied automatically — analysis and implementation stay cleanly separated
33	JOB: open DBA repair follow-up on total analysis failure
34	JOB: clean up temp files

Read our manifesto: why we believe this kind of work is becoming automated →