Re: Metrics Full Index Scans Per Sec is at 2,551.756

From: Jonathan Lewis <>
Date: Mon, 28 Sep 2020 15:38:18 +0100
Message-ID: <>

 To find the SQL ID (if there is something particularly critical) then you could query v$sql joined to v$sql_plan.
Something like:


        sp.sql_id, sp.child_number,,
        sp.operation || '-' || sp.options,
        sq.executions, sq.sql_text
        v$sql_plan sp,
        v$sql sq
        sp.operation = 'INDEX'
-- and  sp.options like 'FULL SCAN DESC%'
and     sp.options like 'FAST%',
and     sq.child_number = sp.child_number
and     sq.sql_id = sp.sql_id
order by


I've put two options into the WHERE clause - given the suggestion that it's actually recording FAST FULL SCANS, but the one I've commented out will get the descending FULL SCANS, and if you wan t all full scans (with, ot without (MIN/MAX) just change this to 'FULL SCAN%';

There are several dictionary-related SYS-recursive queries which operate fairly frequently to find "the most recent" row, and they may be responsible (e.g. during stats collection, or optimising for partitioned tables) for most of the metric report.

Jonathan Lewis

On Mon, 28 Sep 2020 at 14:37, Reen, Elizabeth <> wrote:

> OEM is alerting on this metric. From what I understand we
> are traversing the index in reverse order. OEM does not give any more
> information than this. Where is the alert coming from? I can’t find any
> information on it. I would like to find the indexes this is happening on
> and possibly the sql id.
> Thanks,
> Liz

Received on Mon Sep 28 2020 - 16:38:18 CEST

Original text of this message