How to Monitor When Oracle Management Packs Are Activated or Used
Oracle provides multiple internal views, advisor logs, and AWR/ADDM activity indicators that reveal accidental or unauthorized use of:
- Diagnostics Pack
- Tuning Pack
Below is a complete monitoring framework.
✅ 1. Check if Diagnostic/Tuning Features Were Used (Primary Indicator)
Oracle logs all usage in DBA_FEATURE_USAGE_STATISTICS.
Run:
SELECT name, detected_usages, total_samples, last_usage_date
FROM dba_feature_usage_statistics
WHERE name IN (
'Automatic Workload Repository',
'ADDM',
'SQL Tuning Advisor',
'SQL Access Advisor',
'ASH',
'AWR Baseline',
'Real-Time SQL Monitoring'
)
ORDER BY last_usage_date DESC;
What to look for:
- DETECTED_USAGES > 0 → feature was used
- LAST_USAGE_DATE not null → pack was accessed
- If you disabled packs but this still updates → OEM or some script is still invoking features
This is the most authoritative licensing indicator.
✅ 2. Monitor the Management Pack Parameter (Active Status)
Run:
SHOW PARAMETER control_management_pack_access;
Expected safe value:
NONE
If it changes to:
DIAGNOSTICDIAGNOSTIC+TUNING
➡️ Packs are active and billable.
For automated monitoring, query:
SELECT value
FROM v$parameter
WHERE name='control_management_pack_access';
✅ 3. Check if AWR Snapshots Are Being Generated (Diagnostics Pack Usage)
If snapshots exist, AWR is active → Diagnostic Pack is considered used.
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;
If you see new snapshots after disabling:
➡️ Something is still collecting AWR data.
✅ 4. Monitor ADDM Execution (Diagnostics Pack)
SELECT task_name, created, status
FROM dba_advisor_tasks
WHERE advisor_name='ADDM';
If any task appears → Diagnostics Pack used.
✅ 5. Monitor SQL Tuning Advisor Activity (Tuning Pack)
SELECT task_name, created, status
FROM dba_advisor_tasks
WHERE advisor_name='SQL TUNING ADVISOR';
If tasks ran → Tuning Pack usage triggered.
Also check:
SELECT COUNT(*) FROM DBA_ADVISOR_LOG WHERE advisor_name='SQL TUNING ADVISOR';
✅ 6. Monitor SQL Access Advisor Activity (Tuning Pack)
SELECT task_name, created, execution_start
FROM dba_advisor_tasks
WHERE advisor_name='SQL ACCESS ADVISOR';
Any appearance = Tuning Pack usage.
✅ 7. Detect Automatic SQL Monitoring (Diagnostics + Tuning Pack)
SQL Monitoring is part of Tuning Pack.
Check ASH/SQL Monitor usage:
SELECT sql_id, status, sid, username, elapsed_time
FROM v$sql_monitor;
If rows appear → Tuning Pack active.
✅ 8. OEM Activity Monitoring (Common Source of Accidental Usage)
OEM may trigger pack usage even if DBAs don't run anything manually.
Check OEM repository:
For AWR/ASH report generation:
SELECT * FROM mgmt$awr_reports ORDER BY time_start DESC;
For SQL Tuning Advisor tasks created by OEM:
SELECT task_name, created
FROM dba_advisor_tasks
WHERE task_name LIKE 'SYS_AUTO_SQL%';
If OEM is generating:
- AWR Reports
- ADDM Reports
- SQL Tuning tasks
➡️ Packs are being used even if parameter says NONE.
✅ 9. Enable Audit Monitoring for Parameter Changes
Track if someone re-enabled packs:
AUDIT ALTER SYSTEM;
Then check:
SELECT username, timestamp, sql_text
FROM dba_audit_trail
WHERE sql_text LIKE '%control_management_pack_access%';
This is extremely valuable during SOX audits.
📈 10. Recommended Scheduled Monitoring Script
Run daily/weekly through OEM/cron.
SELECT 'FEATURE_USAGE', name, detected_usages, last_usage_date
FROM dba_feature_usage_statistics
WHERE detected_usages > 0
UNION ALL
SELECT 'AWR_SNAP', snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
WHERE begin_interval_time > SYSDATE-1;
Generate alerts if ANY usage is detected.
🎯 Summary – What You Must Monitor
| Check Area | What It Detects | Indicates Usage |
|---|
| DBA_FEATURE_USAGE_STATISTICS | Core features | Primary licensing indicator |
| DBA_HIST_SNAPSHOT | AWR snapshots | Diagnostics Pack |
| ADDM Tasks | Diagnostics Pack | Yes |
| SQL Tuning Advisor | Tuning Pack | Yes |
| SQL Access Advisor | Tuning Pack | Yes |
| SQL Monitoring | Tuning Pack | Yes |
| OEM Activity | Auto usage triggers | Very common |
| Audit of parameter change | Re-enabling packs | Compliance breach |
