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
|