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

From: Reen, Elizabeth <"Reen,>
Date: Mon, 28 Sep 2020 15:54:02 +0000
Message-ID: <>

              I realize now that there is not going to one place to get my answer. Jonathan’s comment on stats did ring a bell with me and I found that is a good part of my problem.

              I appreciate everyone’s input.


From: [External] <> On Behalf Of [External] Jonathan Lewis Sent: Monday, September 28, 2020 10:38 AM Cc: oracle-l-freelist
Subject: Re: Metrics Full Index Scans Per Sec is at 2,551.756

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,<;!!Jkho33Y!0WXacyRseisPvNLVJrJ_bB1qXpfFQZT49g9p3G9AfS2pIFG47ncldU_nYvqhVNk1wA$>,
        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.



Received on Mon Sep 28 2020 - 17:54:02 CEST

Original text of this message