-- Copy and paste these directly into SQL*Plus, SQL Developer, or Toad
-- =====================================
-- IMMEDIATE PROBLEM IDENTIFICATION
-- =====================================
-- 1. Find blocking sessions (run first when users report "system is slow")
SELECT blocking_session, sid, username FROM v$session WHERE blocking_session IS NOT NULL;
-- 2. Long-running queries (>5 minutes)
SELECT sid, serial#, username, machine, ROUND(last_call_et/60,2) as minutes_running, sql_id
FROM v$session
WHERE status = 'ACTIVE' AND type = 'USER' AND last_call_et > 300
ORDER BY last_call_et DESC;
-- 3. Current active LIMS sessions
SELECT sid, serial#, username, machine, program, status, sql_id
FROM v$session
WHERE username IS NOT NULL
AND (UPPER(program) LIKE '%IIS%' OR UPPER(program) LIKE '%W3WP%' OR UPPER(username) LIKE '%LIMS%')
ORDER BY logon_time DESC;
-- 4. Tablespace usage over 80%
SELECT tablespace_name,
ROUND(((total_mb - free_mb) / total_mb) * 100, 2) as pct_used,
total_mb, free_mb
FROM (
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024, 2) as total_mb
FROM dba_data_files GROUP BY tablespace_name
) total
JOIN (
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024, 2) as free_mb
FROM dba_free_space GROUP BY tablespace_name
) free USING (tablespace_name)
WHERE ((total_mb - free_mb) / total_mb) * 100 > 80
ORDER BY pct_used DESC;
-- 5. Top wait events causing performance issues
SELECT event, total_waits, ROUND(time_waited/100, 2) as time_waited_sec
FROM v$system_event
WHERE wait_class != 'Idle' AND time_waited > 0
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;
-- =====================================
-- DATABASE PERFORMANCE ANALYSIS
-- =====================================
-- 6. Most expensive SQL statements (last hour)
SELECT sql_id, executions,
ROUND(elapsed_time/1000000, 2) as elapsed_seconds,
ROUND(cpu_time/1000000, 2) as cpu_seconds,
disk_reads, buffer_gets,
SUBSTR(sql_text, 1, 80) as sql_snippet
FROM v$sql
WHERE last_active_time > SYSDATE - 1/24
AND executions > 0 AND elapsed_time > 1000000
ORDER BY elapsed_time DESC
FETCH FIRST 15 ROWS ONLY;
-- 7. Connection count by application
SELECT program, machine, username, COUNT(*) as connection_count,
COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) as active_count
FROM v$session
WHERE type = 'USER' AND username IS NOT NULL
GROUP BY program, machine, username
HAVING COUNT(*) > 3
ORDER BY connection_count DESC;
-- 8. Database performance metrics snapshot
SELECT metric_name, value, metric_unit
FROM v$sysmetric
WHERE group_id = 2
AND metric_name IN (
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Buffer Cache Hit Ratio',
'SQL Service Response Time',
'Physical Reads Per Sec',
'User Transaction Per Sec'
)
ORDER BY metric_name;
-- =====================================
-- LOCK AND BLOCKING ANALYSIS
-- =====================================
-- 9. Detailed blocking session information
SELECT
s1.sid as blocked_sid,
s1.username as blocked_user,
s1.machine as blocked_machine,
s1.program as blocked_program,
s2.sid as blocking_sid,
s2.username as blocking_user,
s2.machine as blocking_machine,
s2.program as blocking_program,
o.object_name,
o.object_type
FROM v$session s1
JOIN v$session s2 ON s1.blocking_session = s2.sid
LEFT JOIN v$locked_object lo ON s1.sid = lo.session_id
LEFT JOIN dba_objects o ON lo.object_id = o.object_id
WHERE s1.blocking_session IS NOT NULL;
-- 10. Long-running transactions (potential blockers)
SELECT
s.sid, s.serial#, s.username, s.machine,
t.start_time,
ROUND((SYSDATE - t.start_time) 24 60, 2) as runtime_minutes,
ROUND(t.used_ublk * 8192 / 1024 / 1024, 2) as undo_mb
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE (SYSDATE - t.start_time) 24 60 > 30
ORDER BY runtime_minutes DESC;
-- =====================================
-- SPACE AND STORAGE MONITORING
-- =====================================
-- 11. Tablespace usage with autoextend info
SELECT
df.tablespace_name,
ROUND(df.total_mb, 2) as total_mb,
ROUND(df.max_mb, 2) as max_mb,
ROUND(NVL(fs.free_mb, 0), 2) as free_mb,
ROUND((df.total_mb - NVL(fs.free_mb, 0)), 2) as used_mb,
ROUND(((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb) * 100, 2) as pct_used,
CASE WHEN df.max_mb > df.total_mb THEN 'YES' ELSE 'NO' END as can_extend
FROM (
SELECT tablespace_name,
SUM(bytes)/1024/1024 as total_mb,
SUM(maxbytes)/1024/1024 as max_mb
FROM dba_data_files GROUP BY tablespace_name
) df
LEFT JOIN (
SELECT tablespace_name, SUM(bytes)/1024/1024 as free_mb
FROM dba_free_space GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;
-- 12. Large tables (potential growth issues)
SELECT owner, table_name,
ROUND(bytes/1024/1024, 2) as size_mb,
num_rows,
last_analyzed
FROM dba_segments s
JOIN dba_tables t ON s.segment_name = t.table_name AND s.owner = t.owner
WHERE s.segment_type = 'TABLE'
AND s.bytes > 10010241024 -- Larger than 100MB
AND s.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY bytes DESC;
-- =====================================
-- SESSION AND SQL TROUBLESHOOTING
-- =====================================
-- 13. Current SQL being executed by active sessions
SELECT
s.sid, s.serial#, s.username, s.machine, s.status,
s.sql_id, sq.sql_text,
ROUND(s.last_call_et/60, 2) as minutes_active
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.status = 'ACTIVE'
AND s.type = 'USER'
AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC;
-- 14. Sessions waiting on specific events
SELECT
sid, serial#, username, machine,
event, wait_class,
ROUND(wait_time_micro/1000000, 2) as wait_seconds,
blocking_session
FROM v$session
WHERE wait_class NOT IN ('Idle')
AND status = 'ACTIVE'
AND type = 'USER'
ORDER BY wait_time_micro DESC;
-- 15. Kill session command generator (for problem sessions)
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' as kill_command,
sid, serial#, username, machine, program, status,
ROUND(last_call_et/60, 2) as minutes_idle
FROM v$session
WHERE status = 'INACTIVE'
AND type = 'USER'
AND last_call_et > 3600 -- Idle for more than 1 hour
AND username IS NOT NULL;
-- =====================================
-- UNDO AND TEMPORARY SPACE
-- =====================================
-- 16. UNDO tablespace usage
SELECT
tablespace_name,
status,
ROUND(bytes/1024/1024, 2) as size_mb,
ROUND(maxbytes/1024/1024, 2) as max_mb,
autoextensible
FROM dba_data_files
WHERE tablespace_name LIKE '%UNDO%'
ORDER BY tablespace_name;
-- 17. Temporary tablespace usage
SELECT
s.sid, s.serial#, s.username, s.machine,
ROUND(t.blocks * 8192 / 1024 / 1024, 2) as temp_mb,
t.tablespace as temp_tablespace
FROM v$tempseg_usage t
JOIN v$session s ON t.session_addr = s.saddr
ORDER BY temp_mb DESC;
-- =====================================
-- RECENT ERRORS AND ALERTS
-- =====================================
-- 18. Recent database errors (Oracle 11g+)
SELECT
timestamp,
message_text
FROM v$diag_alert_ext
WHERE component_id = 'rdbms'
AND message_level <= 16 -- Errors and warnings
AND timestamp > SYSDATE - 1 -- Last 24 hours
ORDER BY timestamp DESC
FETCH FIRST 20 ROWS ONLY;
-- 19. Invalid objects that might cause LIMS errors
SELECT owner, object_name, object_type, status, last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, object_type, object_name;
-- 20. Recent failed login attempts
SELECT username, timestamp, returncode, client_id, spare1
FROM dba_audit_trail
WHERE action_name = 'LOGON'
AND returncode != 0 -- Failed logins
AND timestamp > SYSDATE - 1 -- Last 24 hours
ORDER BY timestamp DESC;
-- =====================================
-- USAGE INSTRUCTIONS
-- =====================================
/*
QUICK TROUBLESHOOTING WORKFLOW:
1. IMMEDIATE ISSUES:
- Run queries 1-5 first when users report problems
- Look for blocking sessions, long queries, space issues
2. PERFORMANCE PROBLEMS:
- Run queries 6-8 to identify expensive SQL
- Check wait events for bottlenecks
3. LOCKING ISSUES:
- Use queries 9-10 for detailed blocking analysis
- Generate kill commands if needed (query 15)
4. SPACE PROBLEMS:
- Queries 11-12 for tablespace and table growth
- Monitor UNDO and TEMP usage (16-17)
5. APPLICATION ERRORS:
- Check recent errors (18) and invalid objects (19)
- Review failed logins (20)
SAVE FREQUENTLY USED QUERIES:
Create a .sql file with your most common queries for quick access.
*/