Re: sql monitor
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Tue, 19 May 2020 18:02:40 -0400
Message-ID: <CAMHX9JLTwAqQTf6QBVb1VOC_56XkPvMFGh115yxJmxN+nqpsfA_at_mail.gmail.com>
Date: Tue, 19 May 2020 18:02:40 -0400
Message-ID: <CAMHX9JLTwAqQTf6QBVb1VOC_56XkPvMFGh115yxJmxN+nqpsfA_at_mail.gmail.com>
The force=true for sql_monitor event is about enabling *immediate* monitoring for SQL statements that would otherwise get monitored after 5 seconds of (CPU+I/O wait) time. It would be similar to using a SQL patch to inject the MONITOR hint for that SQL_ID (although it uses a different mechanism).
It does not lift the _sqlmon_max_planlines restriction, so you'd need to increase that for your large plans.
Note that the sql_monitor force=true seems to be buggy in later versions - it works in 12c, but doesn't seem to do anything in 18c & 19c.
-- Tanel Poder https://tanelpoder.com On Mon, May 18, 2020 at 2:59 PM Henry Poras <henry.poras_at_gmail.com> wrote:Received on Wed May 20 2020 - 00:02:40 CEST
> Has anyone gotten "alter systems set events 'sql_monitor [sql:xxxxxxx]
> force-true'" to work? I'm on 12.1.0.2, enabled monitoring for a sql_id
> whose plan is greater than the default for monitoring (> 300 lines). I can
> see the sql running, but it isn't being monitored.
>
> Anyone else see this?
>
> Thanks.
>
> Henry
>
>
-- http://www.freelists.org/webpage/oracle-l