Wednesday, May 27, 2026

Alert Fix : 1 index(es) with too many extents in oracle database - oracle performance issue

🔍 What it Means

Oracle stores segments (tables, indexes) in extents (contiguous blocks).
Over time, due to growth, rebuilds, and DML, an index may end up with:
- Hundreds or thousands of small extents
This is usually flagged when:

extents > threshold (e.g., 1000 or configurable limit)

⚠️ Why It Matters

Generally not critical in modern Oracle (ASSM + LMT) environments.
But can still cause:
- Slight performance overhead in segment management
- Longer backup/restore times
- Inefficient disk usage in older systems
- Red flag in audits / compliance reports

👉 In most modern systems, it's more of a hygiene issue than a real performance problem.

✅ Identify the Problem Index

Run:

SELECT owner, segment_name, segment_type, extents

FROM dba_segments

WHERE segment_type = 'INDEX'

AND extents > 500 -- adjust threshold

ORDER BY extents DESC;

Or specifically:

SELECT owner, index_name

FROM dba_indexes

WHERE index_name IN (

SELECT segment_name

FROM dba_segments

WHERE extents > 500

);

🛠️ Fix Options

✅ Option 1: Rebuild the Index (Recommended)

ALTER INDEX schema_name.index_name REBUILD;

Benefits:

Reduces number of extents
Compacts index
Eliminates fragmentation

👉 If large index:

ALTER INDEX schema_name.index_name REBUILD ONLINE;

✅ Option 2: Rebuild with Storage Clause

To control extent allocation:

ALTER INDEX schema_name.index_name REBUILD

STORAGE (INITIAL 100M NEXT 100M);

👉 Helps avoid many small extents in future.

✅ Option 3: Move to Different Tablespace

If fragmentation is due to tablespace issues:

ALTER INDEX schema_name.index_name

REBUILD TABLESPACE new_tablespace;

✅ Option 4: Coalesce (Less Effective)

ALTER INDEX schema_name.index_name COALESCE;

Merges adjacent leaf blocks
Does NOT reduce extents
Safer but less impactful

🧠 Root Cause Analysis

Common reasons:

Autoallocate tablespace creating many uneven extents
Frequent index growth/shrink cycles
Old dictionary-managed tablespaces (DMT)
Poor storage parameters
Partitioned index mismanagement

🎯 Best Practices (Going Forward)

✅ Use:

Locally Managed Tablespaces (LMT)
AUTOALLOCATE or UNIFORM extent size

✅ For large indexes:

Predefine proper extent sizes:

CREATE INDEX idx_name
ON table_name(column)
TABLESPACE ts_name
STORAGE (INITIAL 128M NEXT 128M);

✅ Monitor periodically:

SELECT segment_name, extents

FROM dba_segments

WHERE extents > 500;

🚨 When You Can Ignore It

You can safely ignore if:

Database uses ASSM + LMT
No performance issues observed
Extents are auto-managed efficiently

✅ Recommendation for You (Database Architect Perspective)

Given your role, I’d suggest:

✅ Validate index size vs extents ratio
✅ Rebuild only if:
- Extents are very high (>1000)
- Index is frequently accessed
✅ Automate check in health scripts
✅ Standardize storage clauses for large indexes

💬 Summary

Issue	Action
Too many extents	Rebuild index
Fragmentation	Rebuild or move
Minor issue	Ignore if no perf impact

Tuesday, May 26, 2026

Is CPU issue ? troubleshooting workflow for oracle database performance issue with automation

✅ ✅ 1. CPU Troubleshooting Framework (Like iostat for CPU)

Use:

vmstat 2 5

top

📊 ✅ 2. CPU Metrics Explained (vmstat / top)

us sy id wa st

Metric	Meaning
us	User CPU (app/DB queries)
sy	System CPU (kernel/syscalls)
id	Idle CPU
wa	IO wait
st	Steal (VM contention)

✅ ✅ 3. CPU Baseline Table (Production Standard)

📊 CPU Utilization

Metric	Good ✅	Warning ⚠️	Critical 🚨
idle (id)	> 40%	20–40%	< 20%
user (us)	< 50%	50–70%	> 70%
system (sy)	< 20%	20–30%	> 30%
iowait (wa)	< 5%	5–15%	> 20%
steal (st)	0	1–5	> 5

📊 Run Queue (Very Important)

r (run queue)

Metric	Good ✅	Warning ⚠️	Critical 🚨
r vs CPU cores	≤ cores	1.5x cores	2x+ cores

✅ ✅ 4. CPU Health Decision Matrix

Condition	Meaning
High us	CPU-heavy queries
High sy	kernel/system overhead
High wa	not CPU → disk issue
High r	CPU contention
High st	VM issue

✅ ✅ 5. CPU Scoring Model (Like Disk)

🎯 Formula

Score = 100 
- user penalty 
- system penalty 
- run queue penalty 
- iowait penalty

📊 Penalties

✅ User CPU

<50 → 0
50–70 → -15
70 → -30

✅ System CPU

<20 → 0
20–30 → -15
30 → -30

✅ Run Queue

≤ cores → 0
1–2x cores → -20
2x cores → -40

✅ IO Wait

<5 → 0
5–15 → -20
15 → -40

✅ Final Status

Score	Status
80–100	✅ Healthy
60–80	⚠️ Warning
40–60	🔶 Degraded
<40	🚨 Critical

✅ ✅ 6. CPU Monitoring Script (PRODUCTION READY)

📜 `cpu_health.sh`

#!/bin/bash

echo "===== CPU Health Check ====="
date

vmstat 2 3 | tail -1 | awk '
{
r=$1
us=$13
sy=$14
id=$15
wa=$16
st=$17

score=100

# user penalty
if (us > 70) score -= 30
else if (us > 50) score -= 15

# system penalty
if (sy > 30) score -= 30
else if (sy > 20) score -= 15

# iowait penalty
if (wa > 15) score -= 40
else if (wa > 5) score -= 20

# status
status="HEALTHY"
if (score < 40) status="CRITICAL"
else if (score < 60) status="DEGRADED"
else if (score < 80) status="WARNING"

printf "RunQ=%d User=%d%% Sys=%d%% Idle=%d%% IOwait=%d%% Status=%s Score=%d\n",r,us,sy,id,wa,status,score
}
'

Identify CPU Bottleneck (Root Cause)

✅ Step 1: Check top processes

top -o %CPU

✅ Step 2: Per process CPU

ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%cpu | head

✅ Step 3: Thread-level (VERY IMPORTANT for DB)

top -H

✅ Step 4: Per process breakdown

pidstat -u 2

✅ ✅ 8. CPU Issue Patterns (DBA Mapping)

🔴 Pattern 1: High USER CPU

us > 70%

👉 Cause:

Complex SQL
Full scans
bad execution plans

✅ DB Mapping:

SELECT sql_id, cpu_time

FROM v$sql

ORDER BY cpu_time DESC FETCH FIRST 10 ROWS;

🔴 Pattern 2: High SYSTEM CPU

sy > 30%

👉 Cause:

excessive context switching
kernel overhead
I/O interrupts

✅ Check:

vmstat 1

Look at:

cs (context switches)
in (interrupts)

🔴 Pattern 3: High RUN QUEUE

r >> CPU cores

👉 Cause:

CPU contention
too many concurrent queries

🔴 Pattern 4: High IO WAIT

wa > 20%

👉 Cause:

NOT CPU problem
👉 disk bottleneck (go back to iostat)

🔴 Pattern 5: High STEAL (VM)

st > 5%

👉 Cause:

host contention (cloud/VM issue)

✅ ✅ 9. CPU Alert Script (Automation)

📜 `cpu_alert.sh`

#!/bin/bash

HOST=$(hostname)

ALERT=$(vmstat 2 2 | tail -1 | awk '
{
us=$13; sy=$14; wa=$16; st=$17

if (us>70 || sy>30 || wa>15 || st>5)
print "CPU Issue: user="us"% sys="sy"% wa="wa"% st="st"%"
}
')

if [ ! -z "$ALERT" ]; then
echo "$ALERT" | mail -s "CPU Alert on $HOST" your_email@company.com
fi
``

✅ ✅ 10. DBA Troubleshooting Flow

🔥 When CPU alert triggers:

1. Check:

vmstat 1 5

2. Identify pattern:

High us → SQL issue
High sy → OS/kernel
High wa → disk issue

3. Identify process:

top

4. Map to DB:

SELECT sql_id, cpu_time FROM v$sql ORDER BY cpu_time DESC;

5. Fix:

Tune query ✅
Index ✅
Reduce parallelism ✅
Limit sessions ✅

✅ ✅ 11. Golden Rules (CPU)

✅ Healthy

idle > 40%
wa < 5%
r <= cores

🚨 Critical CPU

idle < 10%
us > 70%
r > 2x cores

⚠️ Trick Case (VERY IMPORTANT)

CPU looks high BUT wa is high
👉 NOT CPU issue → DISK issue

🎯 Final Outcome

Now you have:

✅ CPU baseline table
✅ Health scoring model
✅ Monitoring script
✅ Alerting system
✅ DB correlation
✅ Troubleshooting flow

Is storage issue ? troubleshooting workflow for oracle database performance issue with automation

✅ 1. AUTO EMAIL ALERT SCRIPT (Real Production Use)

This script:

Detects disk bottlenecks
Sends email alert automatically

📜 Script: `disk_alert_mail.sh`

#!/bin/bash

HOST=$(hostname)
DATE=$(date)

ALERT=$(iostat -xm 2 2 | awk '
$1 ~ /^(sd|dm)/ {
util=$NF
await=$(NF-3)
queue=$(NF-4)

if (util>95 || await>50 || queue>10) {
printf "%-8s util=%s%% await=%sms queue=%s\n", $1, util, await, queue
}
}
')

if [ ! -z "$ALERT" ]; then
echo "Disk Alert on $HOST at $DATE

Critical disks detected:

$ALERT
" | mail -s "🚨 Disk I/O Alert on $HOST" your_email@company.com
fi

✅ Setup Cron (every 5 min)

crontab -e

Add:

*/5 * * * * /path/disk_alert_mail.sh

✅ ✅ 2. AUTO MAP dm-* → MOUNT → DB FILE (Game-Changer)

This solves your biggest pain: 👉 “Which DB object is causing this?”

📜 Script: `disk_to_db_map.sh`

#!/bin/bash

echo "==== Disk → Mount → Files ===="

for dev in $(lsblk -dn -o NAME | grep dm-); do
echo ""
echo "Device: $dev"

mount=$(lsblk -n -o MOUNTPOINT /dev/$dev 2>/dev/null)

if [ ! -z "$mount" ]; then
echo "Mount: $mount"
echo "Files:"
lsof +D $mount 2>/dev/null | head -10
else
echo "No mount (maybe ASM/LVM raw)"
fi
done

✅ Output Example

Device: dm-xx
Mount: /u02
Files:
oracle  db  datafile1.dbf
oracle  db  users01.dbf

✅ ✅ 3. CORRELATE iostat + ORACLE (Find exact SQL)

✅ Step 1: Identify heavy disk (from iostat)

Example:

dm-69 → /u02 → USERS tablespace

✅ Step 2: Find SQL causing reads

SELECT sql_id,
executions,
disk_reads,
buffer_gets,
ROUND(disk_reads/executions) avg_reads
ORDER BY disk_reads DESC
FETCH FIRST 10 ROWS ONLY;

✅ Step 3: Active session (real-time)

SELECT sid, serial#, sql_id, event
FROM v$session
WHERE wait_class='User I/O';

✅ Step 4: Wait event mapping

Wait Event	Meaning
`db file scattered read`	Full table scan 🚨
`db file sequential read`	Index lookup
`direct path read`	Large scan
`log file sync`	Commit slow

✅ ✅ 4. ADVANCED ALL-IN-ONE SCRIPT (MOST POWERFUL)

👉 Combines:

Disk health
Alert detection
Mapping

📜 `ultimate_disk_analyzer.sh`

#!/bin/bash

echo "===== Ultimate Disk Analyzer ====="
date
echo ""

iostat -xm 2 2 | awk '
/Device/ {
printf "%-8s %-6s %-6s %-6s %-10s\n","Dev","Util","Await","Queue","Status"
next
}

$1 ~ /^(sd|dm)/ {
util=$NF
await=$(NF-3)
queue=$(NF-4)

status="OK"

if (util>95 || await>50 || queue>10)
status="CRITICAL"
else if (util>80 || await>20 || queue>5)
status="WARNING"

printf "%-8s %-6.1f %-6.1f %-6.1f %-10s\n",$1,util,await,queue,status
}'

✅ ✅ 5. Real DBA Troubleshooting Flow (What YOU should do)

🔥 Step-by-step when alert triggers:

1. Run:

iostat -xm 2 5

2. Identify:

High %util
High await
High queue

3. Map:

lsblk

df -h

4. Check DB:

SELECT * FROM v$session WHERE event LIKE '%read%';

5. Fix:

Query tuning ✅
Indexing ✅
Move data ✅
Balance disks ✅

✅ ✅ 6. PRO TIPS (REAL WORLD)

🔴 When to PANIC

await > 100 ms
queue > 20
util = 100% sustained

🟢 When it's OK

util = 100% BUT await < 5
👉 (fast SSD under load)

🧠 Golden formula:

Problem = High Util + High Await + High Queue

🎯 Final Outcome

You now have:

✅ Auto alert system
✅ Disk → DB mapping
✅ SQL root cause detection
✅ Health scoring
✅ Full troubleshooting workflow

Wednesday, May 27, 2026

Alert Fix : 1 index(es) with too many extents in oracle database - oracle performance issue

🔍 What it Means

⚠️ Why It Matters

✅ Identify the Problem Index

🛠️ Fix Options

✅ Option 1: Rebuild the Index (Recommended)

✅ Option 2: Rebuild with Storage Clause

✅ Option 3: Move to Different Tablespace

✅ Option 4: Coalesce (Less Effective)

🧠 Root Cause Analysis

🎯 Best Practices (Going Forward)

🚨 When You Can Ignore It

✅ Recommendation for You (Database Architect Perspective)

💬 Summary

Tuesday, May 26, 2026

Is CPU issue ? troubleshooting workflow for oracle database performance issue with automation

✅ ✅ 1. CPU Troubleshooting Framework (Like iostat for CPU)

📊 ✅ 2. CPU Metrics Explained (vmstat / top)

✅ ✅ 3. CPU Baseline Table (Production Standard)

📊 CPU Utilization

📊 Run Queue (Very Important)

✅ ✅ 4. CPU Health Decision Matrix

✅ ✅ 5. CPU Scoring Model (Like Disk)

🎯 Formula

📊 Penalties

✅ User CPU

✅ System CPU

✅ Run Queue

✅ IO Wait

✅ Final Status

✅ ✅ 6. CPU Monitoring Script (PRODUCTION READY)

📜 cpu_health.sh

✅ Step 1: Check top processes

✅ Step 2: Per process CPU

✅ Step 3: Thread-level (VERY IMPORTANT for DB)

✅ Step 4: Per process breakdown

✅ ✅ 8. CPU Issue Patterns (DBA Mapping)

🔴 Pattern 1: High USER CPU

🔴 Pattern 2: High SYSTEM CPU

🔴 Pattern 3: High RUN QUEUE

🔴 Pattern 4: High IO WAIT

🔴 Pattern 5: High STEAL (VM)

✅ ✅ 9. CPU Alert Script (Automation)

📜 cpu_alert.sh

✅ ✅ 10. DBA Troubleshooting Flow

🔥 When CPU alert triggers:

1. Check:

2. Identify pattern:

3. Identify process:

4. Map to DB:

5. Fix:

✅ ✅ 11. Golden Rules (CPU)

✅ Healthy

🚨 Critical CPU

⚠️ Trick Case (VERY IMPORTANT)

🎯 Final Outcome

Is storage issue ? troubleshooting workflow for oracle database performance issue with automation

✅ 1. AUTO EMAIL ALERT SCRIPT (Real Production Use)

📜 Script: disk_alert_mail.sh

✅ Setup Cron (every 5 min)

✅ ✅ 2. AUTO MAP dm-* → MOUNT → DB FILE (Game-Changer)

📜 Script: disk_to_db_map.sh

✅ Output Example

✅ ✅ 3. CORRELATE iostat + ORACLE (Find exact SQL)

✅ Step 1: Identify heavy disk (from iostat)

✅ Step 2: Find SQL causing reads

✅ Step 3: Active session (real-time)

✅ Step 4: Wait event mapping

✅ ✅ 4. ADVANCED ALL-IN-ONE SCRIPT (MOST POWERFUL)

📜 ultimate_disk_analyzer.sh

✅ ✅ 5. Real DBA Troubleshooting Flow (What YOU should do)

🔥 Step-by-step when alert triggers:

1. Run:

2. Identify:

3. Map:

4. Check DB:

5. Fix:

✅ ✅ 6. PRO TIPS (REAL WORLD)

🔴 When to PANIC

📜 `cpu_health.sh`

📜 `cpu_alert.sh`

📜 Script: `disk_alert_mail.sh`

📜 Script: `disk_to_db_map.sh`

📜 `ultimate_disk_analyzer.sh`