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

              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.

Liz

From: [External] oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> 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:

select

        sp.sql_id, sp.child_number, sp.id<https://urldefense.com/v3/__http:/sp.id__;!!Jkho33Y!0WXacyRseisPvNLVJrJ_bB1qXpfFQZT49g9p3G9AfS2pIFG47ncldU_nYvqhVNk1wA$>,
        sp.operation || '-' || sp.options,
        sq.executions, sq.sql_text
from
        v$sql_plan sp,
        v$sql sq
where
        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
        sq.executions

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.

Regards
Jonathan Lewis

On Mon, 28 Sep 2020 at 14:37, Reen, Elizabeth <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> 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

--
http://www.freelists.org/webpage/oracle-l