Oracle Database Production Performance & Diagnostic Toolkit

A collection of production-safe, non-destructive SQL diagnostic scripts designed to audit enterprise database health, isolate query bottlenecks, and track tablespace physical block fragmentation.

🚀 The Core Problem

This repository provides a lightweight, read-only framework to safely diagnose Oracle instance performance drops in under 60 seconds without installing invasive software logging processes or native agents.

---

🎁 Free Teaser: Buffer Cache Hit Ratio Check

Copy and paste this safe, read-only script into Oracle SQL Developer or TOAD. It measures how cleanly your working data blocks fit inside your System Global Area (SGA) RAM memory buffers relative to physical disk reads.

SELECT 
    ROUND((1 - (physical_reads / (consistent_gets + db_block_gets))) * 100, 2) AS buffer_cache_hit_ratio_pct
FROM (
    SELECT 
        SUM(CASE WHEN name = 'physical reads' THEN value ELSE 0 END) as physical_reads,
        SUM(CASE WHEN name = 'consistent gets' THEN value ELSE 0 END) as consistent_gets,
        SUM(CASE WHEN name = 'db block gets' THEN value ELSE 0 END) as db_block_gets
    FROM 
        v\$sysstat
    WHERE 
        name IN ('physical reads', 'consistent gets', 'db block gets')
);

Target Result: Production instances should comfortably maintain a hit ratio above 95.00%. If your result drops below 90.00%, your queries are starving for memory allocation and thrashing physical storage media.

---

⚡ Unlock the Full Automation Bundle ($59)

While buffer cache percentages signal general SGA instance health, pinpointing which exact lines of execution code are forcing full table scans requires the full diagnostic suite.

The complete, production-ready toolkit includes the critical administrative scripts required to fix your performance entirely:

📦 What's Included in the Full Premium Kit:

• 01_oracle_missing_and_unused_indexes.sql: Locates massive user data segments missing index scaffolding and displays dead indexes chewing up update I/O overhead.
• 03_oracle_tablespace_fragmentation.sql: Identifies high allocation footprints and severe block fragmentation anomalies across datafiles.
• 04_oracle_heavy_query_tracker.sql: Interrogates library caches (v$sql) to rank your top 5 heaviest query strings by cumulative CPU seconds and isolates their unique SQL_ID profiles.
• Comprehensive Markdown Guide: Step-by-step documentation detailing exactly how to safely resolve the critical thresholds flagged by the queries.

👉 Download the Full Production Oracle Toolkit on Gumroad for $59

---

Maintained by @Leo05434-db. For enterprise database engineering architecture, performance tuning, or scale optimization, contact: leo05434@proton.me.