Why exactly oracle huge page do not help I/O improvement ?

 

One-line explanation

HugePages make Oracle’s SGA memory easier for the OS and CPU to manage, but they do not make the disk, SAN, ASM, or file system read/write data any faster.

---

Why exactly they do not help I/O

Let’s break the full path into layers:

1. Storage / I/O layer

This is where physical I/O happens:

• Disk / SSD / SAN / NVMe
• HBA / storage controller
• ASM / file system
• I/O scheduler
• Oracle process issuing read/write request

This layer determines:

• IOPS
• MB/sec
• latency
• read/write service time

---

2. Memory layer

This is where HugePages work:

• Oracle SGA
• Buffer cache
• Shared pool
• Large memory mappings
• CPU virtual-to-physical address translation
• TLB and page tables

HugePages optimize this layer only.

---

The key reason: I/O speed depends on storage, not page size of memory

Suppose Oracle needs to read an 8 KB block from disk.

The physical read time depends on:

• storage latency
• queue depth
• controller speed
• disk type
• path congestion
• ASM/file system overhead

It does not depend on whether Oracle’s SGA is backed by:

• 4 KB OS pages, or
• 2 MB HugePages

Because the disk read still has to travel through the same storage path.

So:

HugePages do not change the number of milliseconds required for the disk to return the block.

---

Think of it with a simple analogy

Imagine:

• Disk = warehouse
• I/O path = truck
• Oracle memory = storage room inside office
• HugePages = bigger shelves inside the office

If the truck from warehouse is slow, making bigger shelves in the office does not make the truck arrive faster.

It only makes storage inside the office more efficient.

That’s exactly what HugePages do.

---

What HugePages actually improve

HugePages reduce overhead in memory management, such as:

• fewer page table entries
• fewer TLB misses
• lower CPU cost for memory translation
• less kernel overhead
• more stable large SGA allocation
• prevention of SGA swapping

All of that improves CPU efficiency and memory efficiency.

But physical I/O is still physical I/O.

---

Where confusion happens in Oracle

People often see better database performance after enabling HugePages and conclude:

“HugePages improved I/O.”

That is usually not technically correct.

What actually happened is:

1. Oracle used SGA more efficiently
2. CPU overhead reduced
3. Buffer cache became more stable
4. More queries were served from memory
5. Fewer requests needed to go to disk

So the number of physical reads may reduce, but the speed of each disk read does not increase.

That is the difference.

---

Important distinction: “less I/O” vs “faster I/O”

These are not the same.

HugePages may help reduce I/O demand indirectly

Example:

• large buffer cache
• efficient memory use
• fewer evictions
• more cache hits

Then Oracle may perform:

• fewer physical reads
• fewer storage accesses

That means:

• overall workload improves
• response time improves

But still:

HugePages did not make storage faster.
They only helped Oracle avoid going to storage as often.

---

Why HugePages cannot increase IOPS or throughput

Because IOPS and throughput are limited by things like:

• disk capability
• SSD/NVMe speed
• SAN bandwidth
• storage controller cache
• network storage latency
• queue depth
• block size and access pattern
• concurrent sessions doing reads/writes

HugePages do not change any of those.

They do not:

• add more storage channels
• reduce disk seek time
• improve flash response time
• reduce redo write latency at hardware level
• increase HBA throughput
• modify ASM rebalance performance directly

So if your bottleneck is pure storage, HugePages won’t solve it.

---

A technical view: where HugePages sit in the flow

When Oracle reads data:

Without HugePages

Plain Text

Disk -> kernel I/O stack -> Oracle buffer cache in normal 4 KB pages

With HugePages

Plain Text

Disk -> kernel I/O stack -> Oracle buffer cache in 2 MB HugePages

Notice:

• the disk path is identical
• the I/O stack is mostly identical
• only the memory backing of the SGA changes

So the data arrives through the same storage mechanism.

HugePages only change how the buffer cache memory is organized after the data is read.

---

Another important point: Oracle block size does not change

Oracle typically reads database blocks like:

• 8 KB
• 16 KB

HugePages are usually:

• 2 MB

HugePages do not mean Oracle starts reading 2 MB from disk per block request.

They only mean many Oracle blocks can reside inside one large memory page.

So:

• database I/O request size remains the same
• storage call remains the same
• disk latency remains the same

Only the memory container is larger.

---

Example to make it very clear

Suppose you have:

• Oracle block size = 8 KB
• OS normal page size = 4 KB
• HugePage size = 2 MB

A query needs one block from disk.

That block still comes as 8 KB logical DB data.

HugePages just mean that once Oracle stores it in SGA, that SGA is allocated using large pages.

The read from storage is still governed by:

• storage latency
• controller path
• queue
• filesystem/ASM

Not by HugePages.

---

Then why do DBAs strongly recommend HugePages?

Because they help overall Oracle performance and stability, especially on large-memory servers.

They are recommended because they:

1. Reduce memory overhead

Large SGA with normal pages requires massive page table management.

2. Reduce CPU usage

Less TLB/page table overhead.

3. Prevent SGA swap problems

HugePages pin memory and keep SGA resident.

4. Improve scalability

Better for very large SGAs and many sessions.

These benefits can absolutely improve the database server performance.

But that is different from saying:

“HugePages increase disk I/O.”

They don’t.

---

When users feel I/O improved

Sometimes after enabling HugePages, these metrics improve:

• query response time
• transactions per second
• CPU utilization
• latch/mutex behavior
• physical reads per second (may go down)
• average latency from app side

This creates the impression that I/O got better.

What really happened is one or more of these:

Case A: More cache hits

Oracle keeps useful blocks in memory better.

Case B: Less CPU wasted

System has more CPU left to process workload.

Case C: Less memory fragmentation

Large SGA behaves more predictably.

Case D: Better throughput

More work finishes per second, so the system looks healthier.

Again: this is better database efficiency, not faster storage media.

---

The simplest technical explanation

HugePages help with:

• memory mapping
• address translation
• TLB efficiency
• large SGA stability

HugePages do not help with:

• disk seek
• SSD service time
• SAN latency
• physical read speed
• write throughput
• redo log device latency

---

Final answer in one architect-level statement

HugePages do not increase Oracle physical I/O because they optimize how the SGA is allocated and accessed in memory, while physical I/O performance is determined by the storage subsystem, I/O path, and access patterns. HugePages can improve overall database performance indirectly by reducing CPU and memory-management overhead, and by improving cache efficiency, but they do not make disk reads or writes inherently faster.

---

Very short interview answer

If someone asks you in interview or review:

Why don’t HugePages increase I/O?

You can answer:

Because HugePages optimize Oracle memory usage, not the storage path. Physical I/O speed depends on disks, SAN, ASM/filesystem, and I/O latency. HugePages only reduce page-table/TLB overhead for SGA, so they may improve overall performance indirectly, but not disk IOPS or throughput directly.

Oracle/OS huge page help to increase IO - Yes or No ?

 

Short Answer

No — HugePages do not directly increase disk I/O throughput or IOPS.

What they do improve is memory-management efficiency for Oracle’s SGA, which can indirectly improve overall database performance in workloads where CPU overhead, memory translation, and buffer cache efficiency matter.

So the precise answer is:

• Direct disk I/O improvement: No
• Indirect database performance improvement: Yes, sometimes
• Can query response time improve? Yes
• Can storage subsystem become faster? No

---

Why the Answer Is “No”

When people say “I/O” in Oracle, they often mix up two different things:

1. Disk I/O

This means:

• reading from storage
• writing to storage
• IOPS
• throughput (MB/s)
• latency from disk/ASM/SAN/NVMe

HugePages do not make:

• disks spin faster
• SSDs respond faster
• SAN latency lower
• ASM perform more physical reads/sec by itself

So if your system is bottlenecked on:

• slow storage,
• high read latency,
• redo write latency,
• log file sync due to storage,
• db file sequential/scattered read delays,

then HugePages will not fix that bottleneck.

---

Then Why Do People Say HugePages Help Performance?

Because Oracle uses HugePages mainly for the SGA (System Global Area), and that changes how efficiently memory is managed, not how storage hardware performs.

HugePages help by reducing:

• number of memory pages
• page table overhead
• TLB misses
• kernel memory management overhead
• swapping risk for SGA

This can reduce CPU consumption and improve scalability, especially for large SGAs.

That means:

• buffer cache access becomes cheaper from a CPU/memory-translation perspective
• shared pool access can be more efficient
• the system spends less overhead managing memory mappings

So the database may perform better overall — but that is not the same as disk I/O becoming faster.

---

The Correct Mental Model

Think of it like this:

Without HugePages

Oracle SGA is built using many small OS pages (typically 4 KB).

With HugePages

Oracle SGA is backed by large pages (typically 2 MB).

This means the OS and CPU can manage Oracle’s large memory regions more efficiently.

But the database blocks are still:

• 8 KB / 16 KB Oracle blocks,
• read from the same disks,
• through the same storage path,
• at the same storage latency.

So:

HugePages optimize memory handling, not disk mechanics.

---

Where HugePages Can Indirectly Reduce I/O

Here’s the subtle but important part.

HugePages do not speed up physical I/O directly, but they can help Oracle make better use of memory, and that can reduce the need for physical I/O indirectly in some cases.

Example 1: Large Buffer Cache Works Better

If you have a large SGA and buffer cache, HugePages help Oracle manage that memory more efficiently.

Result:

• more stable SGA usage
• lower CPU overhead
• better scalability under load

If the buffer cache is effective, more reads are served from memory instead of disk.

So you may observe:

• fewer physical reads
• better response time
• lower pressure on storage

But the key point is:

HugePages did not make a single disk read faster.
They helped Oracle use memory better, which may reduce the number of disk reads required.

---

Example 2: Less Swapping = Less Disaster

If HugePages are not used properly and memory pressure happens, parts of memory management may become inefficient, and in bad configurations there can be swapping-related problems.

HugePages pin SGA memory and prevent it from being swapped.

That means:

• Oracle buffer cache stays resident
• shared memory remains stable
• performance doesn’t collapse due to memory pressure

Again, that is not “faster disk I/O”; it is avoiding performance degradation.

---

When HugePages Help the Most

HugePages are most useful when:

• SGA is large (tens or hundreds of GB)
• Oracle host has many concurrent sessions
• CPU overhead matters
• TLB misses are significant
• page table size is large
• multiple Oracle instances are running
• you need stable, predictable performance

In such systems, HugePages can improve:

• CPU efficiency
• memory translation overhead
• latency consistency
• overall throughput of the DB server

That may look like I/O improvement from the application side because transactions become faster — but the actual storage path has not changed.

---

When HugePages Will Not Help Much

HugePages usually give little or no visible benefit if:

• SGA is small
• workload is purely storage-bound
• storage latency is the dominant bottleneck
• system is already under-tuned at SQL/query level
• bad execution plans are causing excess reads
• indexes/statistics/partitioning are poor

In those cases, if someone asks:

“Can HugePages increase I/O?”

The right answer is:

No, not in the way you are probably hoping.
You need to fix storage, SQL, or database design.

---

Direct I/O vs Buffered I/O: Important Distinction

If Oracle uses Direct I/O / ASM

Data may bypass the OS page cache and move between storage and Oracle-managed memory.

HugePages help the Oracle memory side (SGA), but:

• they still do not change storage latency
• they still do not increase IOPS capacity

If Oracle uses filesystem buffered I/O

OS page cache uses normal pages, not HugePages in the Oracle HugePages sense.

Again:

• HugePages help Oracle SGA
• not the OS page cache I/O path

So in both cases: storage I/O itself does not become faster because HugePages were enabled.

---

Why Some DBAs Believe HugePages Improved I/O

Because after enabling HugePages they may observe:

• lower DB CPU usage
• faster SQL execution
• fewer stalls
• lower latency at application level
• more stable throughput

This leads to the impression:

“HugePages improved I/O”

But the real reason is often:

1. Oracle memory management became more efficient
2. SGA became more stable
3. Buffer cache behavior improved
4. CPU spent less time on page translation / kernel overhead
5. More work completed per second

That’s an overall performance gain, not a storage subsystem gain.

---

Simple Yes/No by Scenario

Scenario A: Slow SAN / slow disk / high read latency

Will HugePages increase disk I/O speed?
No

Scenario B: Very large SGA, CPU overhead high, many TLB misses

Will HugePages improve DB performance?
Yes

Scenario C: Buffer cache becomes more effective and physical reads drop

Did HugePages increase I/O?
No directly
They helped reduce the need for some physical I/O.

Scenario D: Oracle server was unstable due to memory fragmentation/swapping risk

Can HugePages make performance much better?
Yes But again, not because disk became faster.

---

Best Technical Statement You Can Use

If you want a precise architect-level answer, use this:

HugePages do not directly increase Oracle physical I/O throughput, IOPS, or reduce storage latency. They improve SGA memory efficiency by reducing page table overhead, TLB misses, and swapping risk. This can indirectly improve overall database performance and, in some workloads, reduce physical I/O demand by improving memory utilization and buffer cache effectiveness.

---

Final Conclusion

Answer:

No, HugePages do not directly increase I/O.

Detailed meaning:

• Do they increase disk speed? → No
• Do they reduce storage latency? → No
• Do they improve Oracle memory efficiency? → Yes
• Can that indirectly improve DB performance and reduce some physical reads? → Yes
• Should you configure HugePages for large Oracle databases? → Absolutely yes, but for memory efficiency and stability, not because they “make I/O faster.”

---

Practical DBA Rule

If the bottleneck is:

• storage → fix storage / SQL / access path
• memory management / large SGA / CPU overhead → HugePages help
• both → HugePages help one side only (memory side)

Relationship between huge page and OS Page

 

1. Normal OS Page vs HugePage (Fundamentals)

Default Linux memory pages

• Standard page size: 4 KB
• Every memory access uses page tables to translate virtual → physical
• Large SGA → millions of page table entries (PTEs)

HugePages (HugeTLB)

• Larger page size: typically 2 MB (or 1 GB on some systems)
• Reduces:
  • Page table entries
  • TLB (Translation Lookaside Buffer) misses
  • CPU overhead

---

2. How Oracle Uses HugePages Internally

SGA allocation path

When HugePages are enabled (USE_LARGE_PAGES=TRUE|ONLY):

1. Oracle requests SGA memory during startup
2. Kernel checks:
   • Are HugePages available (/proc/meminfo)?
3. If yes:
   • Allocates SGA entirely from HugePages pool
   • Pins memory → not swappable
4. If not:
   • Falls back to normal 4KB pages (unless ONLY)

Key behavior

• Only SGA uses HugePages
• PGA, stack, processes → still use normal pages

---

3. Interaction with OS Page Cache & IO

This is where most confusion happens.

A. Buffered I/O (filesystem)

• Uses OS page cache
• Pages are normal 4KB pages
• Reads:

  Disk → OS Page Cache → Oracle buffer cache
  

✅ HugePages are NOT used in OS page cache

---

B. Direct I/O (O_DIRECT / ASM / Filesystem with DirectIO)

• Bypasses OS page cache
• Data flows:

Disk → Oracle SGA buffer cache (HugePages)

Important insight:

• With HugePages:
  • Buffer cache resides in HugePages
  • Data blocks (8 KB, 16 KB) are packed inside HugePages

👉 HugePages improve:

• Memory lookup efficiency
• Buffer cache access speed

👉 But they do NOT change I/O block size

---

4. Relationship: HugePages vs DB Block vs OS Page

Layer	Typical Size
Oracle DB block	8 KB (default)
OS normal page	4 KB
HugePage	2 MB

Internal packing

A single HugePage (2 MB) holds:

2 MB / 8 KB = 256 Oracle blocks

So:

• Oracle still uses 8 KB blocks
• HugePages just back the memory region

---

5. Does HugePages Increase IO Performance?

Direct Impact:

❌ Does NOT increase disk IO throughput directly
✅ Reduces CPU overhead during memory access

Indirect Impact:

• Faster buffer cache lookups
• Reduced TLB misses
• Better scalability for large SGA
• Less kernel overhead → more CPU for DB work

👉 So overall DB performance improves, but IO bandwidth stays same

---

6. How HugePages Reduce Kernel Overhead

Without HugePages (example 500 GB SGA):

500 GB / 4 KB ≈ 131 million pages

With HugePages (2 MB):

500 GB / 2 MB ≈ 256,000 pages

✅ Reduction:

• Page table size drastically reduced
• Fewer TLB entries needed
• Less CPU spent on memory translation

---

7. Key Kernel Components Involved

1. TLB (Translation Lookaside Buffer)

• Cache of virtual→physical mappings
• HugePages → fewer entries needed → less misses

2. Page Tables

• Shrink significantly with HugePages

3. Buddy Allocator / HugeTLB pool

• Pre-reserved memory (vm.nr_hugepages)
• Prevents fragmentation issues

---

8. Why HugePages Do NOT Affect OS Page Cache

Because:

• Page cache is managed by kernel VM subsystem
• HugePages are:
  • Preallocated
  • Not swappable
  • Not part of regular memory pool

👉 They are isolated memory region

---

9. How HugePages Size Is Increased

Step 1: Calculate requirement

HugePages = (SGA size) / HugePage size

Example:

SGA = 100 GB
HugePage = 2 MB

100 GB / 2 MB = 51200 pages

---

Step 2: Configure kernel

vm.nr_hugepages = 51200

---

Step 3: Reload

sysctl -p

---

Step 4: Verify

cat /proc/meminfo | grep Huge

Important fields:

HugePages_Total
HugePages_Free
HugePages_Rsvd
HugePages_Surp

---

10. Advanced Behavior (Very Important)

Partial allocation

If HugePages are insufficient:

• Oracle may split:
  • Part SGA → HugePages
  • Rest → normal pages ❌ (bad)

👉 Causes:

• Performance inconsistency
• Memory fragmentation

✅ Best practice:

USE_LARGE_PAGES=ONLY

---

11. Summary (Core Concepts)

What HugePages DO

✅ Optimize SGA memory management
✅ Reduce CPU overhead
✅ Improve scalability
✅ Prevent swapping

What HugePages DO NOT DO

❌ Do not change DB block size
❌ Do not increase disk IO speed
❌ Do not affect OS page cache

---

12. Simple Mental Model

Think of it like this:

• Regular pages → many small boxes (slow to manage)
• HugePages → fewer large containers (efficient)

Oracle:

• Stores data blocks inside containers
• Still processes blocks same way

---

13. DBA Insight (Important for you as Architect)

Given your role:

HugePages matter most when:

• SGA > 16–32 GB
• High OLTP concurrency
• CPU contention exists

They matter less when:

• IO bound system (slow storage)
• Small SGA

How to Identify and fix Index is Causing Performance Issue in oracle database ?

 

🔎 PART 1 — How to Identify Index is Causing Performance Issue

---

✅ Step 1: Find Slow SQL (Entry Point)

Start from Top SQL, not from the index.

SELECT sql_id, executions, elapsed_time, buffer_gets

FROM v$sql

ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

👉 Pick the problematic SQL

---

✅ Step 2: Check Execution Plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', NULL, 'ALLSTATS LAST'));

🔍 Look for:

Symptom	Meaning
FULL TABLE SCAN	Index missing or ignored ❌
INDEX RANGE SCAN with high cost	Index inefficient ❌
INDEX FULL SCAN	Poor selectivity ❌

---

✅ Step 3: Check Index Access Cost

In plan, observe:

• Cost
• Rows
• Bytes
• Buffer Gets

👉 If index scan:

• Reads too many blocks → Index is not selective

---

✅ Step 4: Check Clustering Factor (Critical)

SELECT index_name, clustering_factor, num_rows

FROM dba_indexes

WHERE index_name = 'INDEX_NAME';

Interpretation:

• CF ≈ number of rows → ❌ Random reads (BAD index)
• CF ≈ number of blocks → ✅ Efficient index

👉 High CF → more I/O → slower query

---

✅ Step 5: Check Selectivity (Very Important)

SELECT num_distinct, num_rows

FROM dba_tab_col_statistics

WHERE table_name = 'TABLE_NAME'

AND column_name = 'COLUMN_NAME';

👉 Formula:

Selectivity = num_distinct / num_rows

Result:

Value	Meaning
High (close to 1)	✅ Good index
Low (<0.1)	❌ Bad index

👉 Example:

• status = 'ACTIVE' → bad index
• customer_id → good index

---

✅ Step 6: Check If Index is Used Properly

ALTER INDEX schema.index_name MONITORING USAGE;

Then:

SELECT * FROM v$object_usage WHERE index_name = 'INDEX_NAME';

👉 Result:

• USED = NO → Index useless
• USED = YES but slow → Index is problem

---

✅ Step 7: Check Logical Reads (Performance Impact)

SELECT *

FROM v$segment_statistics

WHERE object_name = 'INDEX_NAME'

AND statistic_name = 'logical reads';

👉 High logical reads = heavy I/O → possible issue

---

✅ Step 8: Check Index BLEVEL (Depth)

SELECT index_name, blevel

FROM dba_indexes

WHERE index_name = 'INDEX_NAME';

BLEVEL	Meaning
0–2	✅ Good
3–4	⚠️ Ok
>4	❌ Too deep

---

✅ Step 9: Validate Stats

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA','INDEX_NAME');

👉 Bad stats = wrong execution plan

---

✅ Step 10: Compare With Full Table Scan

Force test:

SELECT /*+ FULL(table_name) */ ..

👉 If FULL scan is faster → Index is hurting performance

---

🚨 Conclusion (Index Causing Issue If):

• High clustering factor
• Low selectivity
• High logical reads
• Not used or incorrectly used
• Execution plan shows inefficiency

---

✅ PART 2 — How to Check Index is GOOD or BAD

---

🎯 Step-by-Step Evaluation Framework

---

✅ Step 1: Usage Check

SELECT * FROM v$object_usage

WHERE index_name = 'INDEX_NAME';

Result	Decision
USED frequently	✅ Good
NOT USED	❌ Drop candidate

---

✅ Step 2: Query Alignment

👉 Check if index matches WHERE clause:

WHERE col1 = :1 AND col2 = :2

👉 Good index:

(col1, col2)

👉 Bad index:

(col3, col4)

---

✅ Step 3: Selectivity Check

• High distinct values → ✅ Good
• Low distinct values → ❌ Bad

---

✅ Step 4: Clustering Factor

• Low CF → ✅ Good
• High CF → ❌ Bad

---

✅ Step 5: Size vs Benefit

SELECT bytes/1024/1024 MB

FROM dba_segments

WHERE segment_name='INDEX_NAME';

👉 Large + rarely used → ❌ Bad

---

✅ Step 6: DML Overhead Check

👉 Too many indexes on high DML table:

• Slows INSERT/UPDATE/DELETE

SELECT index_name

FROM dba_indexes

WHERE table_name = 'TABLE_NAME';

👉 More indexes ≠ better

---

✅ Step 7: Redundancy Check

👉 Duplicate indexes = waste

---

✅ Step 8: BLEVEL + Leaf Blocks

👉 Small, compact index = good
👉 Huge fragmented index = bad

---

✅ FINAL DECISION MATRIX

Condition	Good Index ✅	Bad Index ❌
Usage	Frequent	Not used
Execution Plan	Index scan	Full scan
Selectivity	High	Low
Clustering Factor	Low	High
BLEVEL	≤2	>4
Size	Optimized	Huge unused
DML Impact	Low	High

---

🔥 REAL-WORLD QUICK CHECK SCRIPT

SELECT

i.index_name,

i.blevel,

i.clustering_factor,

i.num_rows,

i.distinct_keys,

s.bytes/1024/1024 MB

FROM dba_indexes i

JOIN dba_segments s

ON i.index_name = s.segment_name

WHERE i.owner = 'SCHEMA_NAME'

ORDER BY s.bytes DESC;

---

💡 Architect-Level Insight (Important)

👉 Don’t judge index in isolation

Always evaluate:

SQL → Execution Plan → Index → Data Pattern

---

✅ Recommended Approach (Best Practice)

1. Identify top SQL (AWR/V$SQL)
2. Analyze execution plan
3. Validate index usage
4. Check clustering factor + selectivity
5. Decide: KEEP / REBUILD / DROP
   
   

INDEX HEALTH CLASSIFICATION SCRIPT

set lines 300 pages 500

col  OWNER  for a10

col  INDEX_NAME  for a50

col TABLE_NAME for a50

WITH idx_stats AS (

    SELECT 

        i.owner,

        i.index_name,

        i.table_name,

        i.blevel,

        i.clustering_factor,

        i.num_rows,

        i.distinct_keys,

        s.bytes/1024/1024 AS size_mb,

        CASE 

            WHEN i.num_rows > 0 

            THEN ROUND(i.distinct_keys / i.num_rows, 4)

            ELSE 0

        END AS selectivity

    FROM dba_indexes i

    JOIN dba_segments s

        ON i.owner = s.owner

        AND i.index_name = s.segment_name

    WHERE i.owner  IN ('RITRS')

AND CLUSTERING_FACTOR >700

),

usage_stats AS (

    SELECT 

        index_name,

        CASE 

            WHEN used = 'YES' THEN 'USED'

            ELSE 'NOT_USED'

        END AS usage_status

    FROM v$object_usage

)

SELECT 

    i.owner,

    i.index_name,

    i.table_name,

    i.size_mb,

    i.blevel,

    i.clustering_factor,

    i.num_rows,

    i.distinct_keys,

    i.selectivity,

    NVL(u.usage_status, 'UNKNOWN') AS usage_status,

    CASE 

        WHEN NVL(u.usage_status, 'NOT_USED') = 'NOT_USED'

            THEN 'DROP_CANDIDATE'

        WHEN i.selectivity < 0.05

            THEN 'BAD_INDEX_LOW_SELECTIVITY'

        WHEN i.clustering_factor > i.num_rows * 0.9

            THEN 'BAD_INDEX_HIGH_CF'

        WHEN i.blevel > 4

            THEN 'REBUILD_REQUIRED'

        WHEN i.size_mb > 500 

             AND NVL(u.usage_status, 'NOT_USED') <> 'USED'

            THEN 'REVIEW_BIG_UNUSED_INDEX'

        ELSE 'GOOD_INDEX'

    END AS index_health_status

FROM idx_stats i

LEFT JOIN usage_stats u

    ON i.index_name = u.index_name

ORDER BY i.size_mb DESC