Oracle SQL Monitoring Script (DBA-Friendly, Real-World Monitoring Guide)

Estimated Reading Time: 6–7 minutes

Monitoring is the backbone of every production database. SQL-level monitoring helps DBAs detect slow queries, high load SQLs, blocked sessions, and performance issues before they become incidents.
This article provides a production-ready SQL monitoring script, explains each part of it, and shows how DBAs can integrate it into daily checks.

---

 

 Table of Contents  

1. What Does SQL Monitoring Mean for DBAs?  

2. Production-Ready SQL Monitoring Script  

3. Script Output Explained  

4. Performance Metrics You Must Watch  

5. Real-World DBA Scenario  

6. Automation Options  

7. FAQ  

8. Final Thoughts  

 

---

1. What Does SQL Monitoring Mean for Oracle DBAs? 

SQL monitoring helps identify:

• High buffer gets SQL

• High CPU-consuming SQL

• SQL with long execution time

• SQL causing row locks

• SQL creating IO bottlenecks

• SQL generating alerts or session waits

These insights allow proactive tuning and avoid outages.

---

2. Production-Grade Oracle SQL Monitoring Script 

Below is a clean, ready-to-run script used by senior DBAs in real production environments:

SET LINES 200
SET PAGES 200

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.status,
    p.spid AS os_pid,
    q.sql_id,
    q.sql_text,
    ROUND(s.cpu_time/1000000,2) AS cpu_sec,
    s.blocking_session,
    s.event,
    s.wait_class,
    s.last_call_et AS seconds_since_last_call
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
LEFT JOIN v$process p ON s.paddr = p.addr
WHERE s.username IS NOT NULL
ORDER BY s.cpu_time DESC FETCH FIRST 20 ROWS ONLY;

---

3. What This Script Reveals 

Each column has a purpose:

Column	Meaning
SID / SERIAL#	Unique session ID
USERNAME	Which user is running SQL
STATUS	ACTIVE / INACTIVE
OS_PID (spid)	OS-level process identifier
SQL_ID	Query identifier
SQL_TEXT	Actual SQL query
CPU_SEC	CPU usage of query
BLOCKING_SESSION	Detect blocking
EVENT	What the session is waiting on
WAIT_CLASS	IO, CPU, Network, etc.
LAST_CALL_ET	Query running time

This helps diagnose real production issues in minutes.

For official Oracle reference, visit the Oracle Database Documentation:

https://docs.oracle.com/en/database/

---

4. Add-on: Top SQL by Buffer Gets 

SELECT sql_id,
       buffer_gets,
       executions,
       ROUND(buffer_gets/DECODE(executions,0,1,executions)) AS gets_per_exec,
       sql_text
FROM v$sql
ORDER BY buffer_gets DESC FETCH FIRST 10 ROWS ONLY;

Use this to find high-cost SQL.

---

5. Add-on: Top SQL by IO 

SELECT sql_id,
       disk_reads,
       direct_writes,
       sql_text
FROM v$sql
ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;

Useful for IO-heavy systems and slow storage layers.

---

6. Real-World DBA Use Case

Example scenario:

• Application suddenly slows down

• Sessions start waiting on "db file sequential read"

• Your script shows high CPU session + SQL_ID

• SQL belongs to a reporting job running full table scans

• You tune it (add index / rewrite SQL / fix stats)

• Performance normalizes in seconds

This is exactly what impresses recruiters and decision-makers.

---

7. How To Automate These Checks 

DBAs usually automate this:

• Add SQL scripts to a shell script

• Run at intervals using cron or OEM

• Forward output to email / Teams / Slack

• Integrate with CloudWatch / SignalFx

• Store SQL history for future tuning

I can prepare your automation version in bash or PowerShell if you want.

---

8. Interview Questions Based on This Topic 

Expect these in interviews:

1. How do you identify top CPU SQL?

2. What is SQL_ID and why is it important?

3. Difference between buffer gets and disk reads?

4. How will you find blocking sessions?

5. Which wait events show IO bottlenecks?

---

9. Final Summary

A good SQL monitoring script gives DBAs visibility into session health, CPU load, IO bottlenecks, and blocking in real time. This script is production-ready and can be used for performance tuning, health checks, or automated monitoring.

 FAQ – Oracle SQL Monitoring

Q1: What is SQL monitoring in Oracle?
SQL monitoring helps identify CPU-heavy SQLs, IO waits, blocking sessions, and real-time performance problems.

Q2: Which Oracle versions support this script?
The script works on 11g, 12c, 18c, and 19c as long as v$session and v$sql views are available.

Q3: When should DBAs run this script?
During slow application response, high CPU alerts, IO bottlenecks, blocking chains, or performance degradation.

Q4: Can this script be automated?
Yes—using cron, OEM, Python, or n8n workflows for scheduled reporting.

Chetan Yadav is a Senior Oracle, PostgreSQL, MySQL & Cloud DBA with 14+ years of experience supporting high-traffic production environments across AWS, Azure, and on-premise systems. His core expertise includes Oracle RAC, ASM, Data Guard, performance tuning, HA/DR design, monitoring frameworks, and real-world troubleshooting.

He also trains DBAs globally through deep-dive technical content, hands-on sessions, and automation workflows using n8n, AI tools, and modern monitoring stacks. His mission is to help DBAs solve real production problems and grow into high-paying remote roles worldwide.

Chetan regularly publishes expert content across Oracle, PostgreSQL, MySQL, and Cloud DBA technologies—including performance tuning guides, DR architectures, monitoring tools, scripts, and real incident-based case studies.

---

Explore More Technical Work

LinkedIn (Professional Profile & Articles)
https://www.linkedin.com/in/chetanyadavvds/

YouTube – Oracle Foundations Playlist
https://www.youtube.com/playlist?list=PL5TN6ECUWGROHQGXep_5hff-2ageWTp4b

Telegram – LevelUp_Careers DBA Tips
https://t.me/LevelUp_Careers

Instagram – Oracle/Cloud Learning Reels
https://www.instagram.com/levelup_careers/

Facebook Page – OracleDBAInfo
https://www.facebook.com/OracleDBAInfo

These platforms feature guides, scripts, diagrams, troubleshooting workflows, and real-world DBA case studies designed for database professionals worldwide.