-- These are SQL Server specific - DO NOT USE WITH ORACLE:
EXEC xp_readerrorlog 0, 1, 'login failed', 'LIMS'
EXEC xp_readerrorlog 0, 1, 'timeout', 'LIMS'
EXEC xp_readerrorlog 0, 1, 'deadlock'
-- What they mean (for reference):
-- 0 = current error log file (1=previous, 2=before that, etc.)
-- 1 = SQL Server log (2=SQL Agent log)
-- 'login failed' = search term
-- 'LIMS' = second search term (both must be present)
-- Oracle uses different views for troubleshooting:
-- v$session - Active sessions and blocking
-- v$sql - SQL statements and performance
-- v$system_event - Wait events and performance bottlenecks
-- v$diag_alert_ext - Alert log errors (Oracle 11g+)
-- dba_data_files/dba_free_space - Tablespace usage
ETW is Windows' built-in, high-performance event logging system. Think of it as "x-ray vision" for your applications - it shows you exactly what's happening inside Windows, IIS, and your applications at a very detailed level.
# Start detailed IIS event recording
logman create trace "LIMS-IIS" -p "Microsoft-Windows-IIS-Logging" -o C:\temp\iis-trace.etl -ets
# Breakdown:
# logman = Windows performance toolkit command
# create trace = Start a new trace session
# "LIMS-IIS" = Name for your trace session
# -p "Microsoft-Windows-IIS-Logging" = Provider (what to monitor)
# -o C:\temp\iis-trace.etl = Output file (binary format)
# -ets = Start immediately
# Stop the trace (after reproducing the issue)
logman stop "LIMS-IIS" -ets
# Convert binary trace to readable CSV
tracerpt C:\temp\iis-trace.etl -o C:\temp\iis-trace.csv -of CSV
# ASP.NET events
logman create trace "LIMS-ASPNET" -p "Microsoft-Windows-ASP.NET" -o aspnet.etl -ets
# Windows HTTP service events
logman create trace "LIMS-HTTP" -p "Microsoft-Windows-HttpService" -o http.etl -ets
# File system access
logman create trace "LIMS-FileIO" -p "Microsoft-Windows-Kernel-File" -o fileio.etl -ets
---
-- Overall database performance snapshot
SELECT
metric_name,
value,
metric_unit
FROM v$sysmetric
WHERE group_id = 2 -- Current metrics
AND metric_name IN (
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Buffer Cache Hit Ratio',
'Soft Parse Ratio',
'SQL Service Response Time',
'User Transaction Per Sec',
'Physical Reads Per Sec',
'Physical Writes Per Sec',
'Redo Generated Per Sec'
)
ORDER BY metric_name;
-- Active LIMS sessions with current activity
SELECT
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.module,
s.action,
s.status,
s.sql_id,
sq.sql_text,
s.last_call_et/60 as minutes_active,
s.blocking_session,
s.wait_class,
s.event,
s.p1text,
s.p1
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.username IS NOT NULL
AND s.type = 'USER'
AND (UPPER(s.program) LIKE '%IIS%'
OR UPPER(s.program) LIKE '%W3WP%'
OR UPPER(s.machine) LIKE '%LIMS%'
OR UPPER(s.username) LIKE '%LIMS%')
ORDER BY s.last_call_et DESC;
-- Top resource-consuming SQL in the last hour
SELECT
sql_id,
executions,
elapsed_time/1000000 as elapsed_seconds,
cpu_time/1000000 as cpu_seconds,
disk_reads,
buffer_gets,
rows_processed,
ROUND(elapsed_time/executions/1000000, 3) as avg_elapsed_seconds,
SUBSTR(sql_text, 1, 100) as sql_snippet
FROM v$sql
WHERE last_active_time > SYSDATE - 1/24 -- Last hour
AND executions > 0
AND elapsed_time > 1000000 -- More than 1 second total
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;
-- Detailed lock information with blocking chains
SELECT
LEVEL as lock_level,
LPAD(' ', (LEVEL-1)*2) || s1.username || '@' || s1.machine as blocked_user,
s1.sid as blocked_sid,
s2.username || '@' || s2.machine as blocking_user,
s2.sid as blocking_sid,
o.object_name,
o.object_type,
DECODE(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive',
l.locked_mode) as lock_mode,
s1.sql_id as blocked_sql_id,
s2.sql_id as blocking_sql_id
FROM v$lock l
JOIN v$session s1 ON l.sid = s1.sid
JOIN v$session s2 ON l.id1 = s2.blocking_session_serial#
LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.type IN ('TX', 'TM') -- Transaction and DML locks
START WITH l.block = 1
CONNECT BY PRIOR l.sid = s2.blocking_session;
-- Tablespace usage with growth rate analysis
WITH space_usage AS (
SELECT
tablespace_name,
ROUND(SUM(bytes)/1024/1024/1024, 2) as total_gb,
ROUND(SUM(maxbytes)/1024/1024/1024, 2) as max_gb
FROM dba_data_files
GROUP BY tablespace_name
),
free_space AS (
SELECT
tablespace_name,
ROUND(SUM(bytes)/1024/1024/1024, 2) as free_gb
FROM dba_free_space
GROUP BY tablespace_name
)
SELECT
su.tablespace_name,
su.total_gb,
su.max_gb,
NVL(fs.free_gb, 0) as free_gb,
su.total_gb - NVL(fs.free_gb, 0) as used_gb,
ROUND(((su.total_gb - NVL(fs.free_gb, 0)) / su.total_gb) * 100, 2) as pct_used,
CASE
WHEN su.max_gb > su.total_gb THEN 'Can extend'
ELSE 'At max size'
END as extension_status
FROM space_usage su
LEFT JOIN free_space fs ON su.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;
-- Top wait events causing performance issues
SELECT
event,
wait_class,
total_waits,
total_timeouts,
ROUND(time_waited/100, 2) as time_waited_seconds,
ROUND(average_wait, 2) as avg_wait_ms,
ROUND((time_waited/SUM(time_waited) OVER ())*100, 2) as pct_total_wait
FROM v$system_event
WHERE wait_class NOT IN ('Idle')
AND time_waited > 0
ORDER BY time_waited DESC
FETCH FIRST 15 ROWS ONLY;
-- UNDO usage and rollback segment contention
SELECT
us.username,
us.sid,
us.serial#,
us.status,
rs.segment_name,
ROUND(rs.bytes/1024/1024, 2) as undo_mb,
us.start_time,
ROUND((SYSDATE - us.start_time) * 24 * 60, 2) as runtime_minutes
FROM v$rollstat rs
JOIN v$rollname rn ON rs.usn = rn.usn
LEFT JOIN v$transaction t ON t.xidusn = rs.usn
LEFT JOIN v$session us ON t.ses_addr = us.saddr
WHERE rs.bytes > 0
ORDER BY rs.bytes DESC;
-- Object growth tracking (run periodically to build history)
SELECT
owner,
object_type,
COUNT(*) as object_count,
ROUND(SUM(
CASE
WHEN object_type = 'TABLE' THEN
(SELECT SUM(bytes)/1024/1024 FROM dba_segments WHERE segment_name = object_name AND owner = o.owner)
ELSE 0
END
), 2) as total_size_mb
FROM dba_objects o
WHERE owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN', 'MDSYS', 'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS', 'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'OLAPSYS')
GROUP BY owner, object_type
HAVING COUNT(*) > 10
ORDER BY owner, object_count DESC;
-- Find long-running transactions that might be blocking LIMS
SELECT
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
t.start_time,
ROUND((SYSDATE - t.start_time) * 24 * 60, 2) as runtime_minutes,
t.used_ublk as undo_blocks,
ROUND(t.used_ublk * 8192 / 1024 / 1024, 2) as undo_mb,
s.sql_id,
s.blocking_session
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE (SYSDATE - t.start_time) * 24 * 60 > 30 -- Running > 30 minutes
ORDER BY runtime_minutes DESC;
-- Monitor connection usage patterns
SELECT
machine,
program,
username,
COUNT(*) as connection_count,
COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) as active_connections,
COUNT(CASE WHEN status = 'INACTIVE' THEN 1 END) as inactive_connections,
MIN(logon_time) as first_connection,
MAX(logon_time) as last_connection
FROM v$session
WHERE type = 'USER'
AND username IS NOT NULL
GROUP BY machine, program, username
HAVING COUNT(*) > 5 -- Show only applications with multiple connections
ORDER BY connection_count DESC;
# Search Oracle alert log for LIMS-related issues
# Alert log location: $ORACLE_BASE/diag/rdbms/[db_name]/[instance]/trace/alert_[instance].log
# Database errors
grep -i "ora-\|error\|failed" alert_*.log | tail -20
# Performance issues
grep -i "checkpoint\|archive\|lgwr\|dbwr" alert_*.log | tail -20
# Space issues
grep -i "tablespace\|space\|extent" alert_*.log | tail -20
# Connection issues
grep -i "listener\|connection\|authentication" alert_*.log | tail -20
-- Check critical parameters for LIMS workload
SELECT name, value, description
FROM v$parameter
WHERE name IN (
'shared_pool_size',
'buffer_cache_size',
'pga_aggregate_target',
'processes',
'sessions',
'cursor_sharing',
'optimizer_mode',
'db_file_multiblock_read_count',
'log_buffer'
)
ORDER BY name;
-- Find unused indexes (potential performance drag)
SELECT
i.owner,
i.index_name,
i.table_name,
i.num_rows,
i.last_analyzed,
CASE
WHEN u.index_name IS NULL THEN 'UNUSED'
ELSE 'USED'
END as usage_status
FROM dba_indexes i
LEFT JOIN v$object_usage u ON i.index_name = u.index_name AND i.owner = u.name
WHERE i.owner NOT IN ('SYS', 'SYSTEM')
AND i.index_type = 'NORMAL'
ORDER BY i.owner, i.table_name, i.index_name;
-- Check table statistics freshness (critical for LIMS performance)
SELECT
owner,
table_name,
num_rows,
last_analyzed,
ROUND(SYSDATE - last_analyzed, 2) as days_since_analyzed,
CASE
WHEN last_analyzed IS NULL THEN 'NEVER ANALYZED'
WHEN SYSDATE - last_analyzed > 7 THEN 'STALE'
WHEN SYSDATE - last_analyzed > 1 THEN 'OLD'
ELSE 'CURRENT'
END as stats_status
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
AND (last_analyzed IS NULL OR SYSDATE - last_analyzed > 1)
ORDER BY days_since_analyzed DESC NULLS FIRST;
Save this as a .sql file for regular health checks:
-- Oracle LIMS Health Check
-- Run this regularly to spot issues early
PROMPT =================================
PROMPT ORACLE LIMS HEALTH CHECK
PROMPT =================================
PROMPT
PROMPT 1. DATABASE STATUS
SELECT instance_name, status, database_status FROM v$instance;
PROMPT
PROMPT 2. TABLESPACE USAGE (>80% = WARNING)
SELECT tablespace_name,
ROUND(((total_space - free_space) / total_space) * 100, 2) as pct_used
FROM (
SELECT tablespace_name,
SUM(bytes) as total_space
FROM dba_data_files
GROUP BY tablespace_name
) total
JOIN (
SELECT tablespace_name,
SUM(bytes) as free_space
FROM dba_free_space
GROUP BY tablespace_name
) free USING (tablespace_name)
WHERE ((total_space - free_space) / total_space) * 100 > 80
ORDER BY pct_used DESC;
PROMPT
PROMPT 3. BLOCKING SESSIONS
SELECT blocking_session, sid, serial#, username, machine, program
FROM v$session
WHERE blocking_session IS NOT NULL;
PROMPT
PROMPT 4. LONG RUNNING QUERIES (>5 min)
SELECT sid, serial#, username,
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;
PROMPT
PROMPT 5. TOP WAIT EVENTS
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;
These Oracle troubleshooting techniques will help you identify database-related issues that commonly affect LIMS applications. The queries focus on connection management, performance bottlenecks, and resource contention - the most common Oracle issues in LIMS environments.