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,
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@xxxxxxxxxxxxx>
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
