Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id C181E1002F386A for ; Fri, 7 May 2021 22:56:18 +0200 (CEST) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 0D89E40CC5; Fri, 7 May 2021 20:56:17 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id EAFCC40D1C; Fri, 7 May 2021 20:56:16 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1620420976; bh=HEvWj+dVUWwAVk4OxKmiZKxnDy3IuD/TCxgy61f7jIA=; h=From:Sender:Sender:From; b=WxXETMuAh1H+J/pMOKHP43Qhq80ct/Rsve82ffRH7R7gBEreHg9iMjRaqEgEKh1mp qaF21XXpvHMAPjU6jnefFSFTA7kR7WlbNjFdOUzNOJTwJ49Cg6kUQc/Az7dTob4ouW zTUJah5tkVClouwki8dk44xNs+mtd59iajEgK9J0= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id DZqvxoecUgzl; Fri, 7 May 2021 20:56:16 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id C849A40CD9; Fri, 7 May 2021 20:56:14 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1620420975; bh=HEvWj+dVUWwAVk4OxKmiZKxnDy3IuD/TCxgy61f7jIA=; h=From:Sender:Sender:From; b=FT4/nkamw57IBlXzudJZGhuk8qVXRcUf4iGc6s4efpQ6AonLM1xJEEze1Jz9qUZfH RDdtg1IN+st/xFJ9wM3qe7nMqo0ws+wUAnGXQqcHZCJ6wtIm7xju8FjhT0PVflkK2h tJzDxp+ZTUg7EH06i9QJY7i8ErmrafNMFy1k5k7U= Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 07 May 2021 20:56:13 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 584CE40C9C for ; Fri, 7 May 2021 20:56:13 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=g/ur84QA; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id g8xscFrtXLGu for ; Fri, 7 May 2021 20:56:13 +0000 (UTC) Received: from mail-lj1-f171.google.com (mail-lj1-f171.google.com [209.85.208.171]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 2F688409DC for ; Fri, 7 May 2021 20:56:13 +0000 (UTC) Received: by mail-lj1-f171.google.com with SMTP id b7so13228609ljr.4 for ; Fri, 07 May 2021 13:56:13 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=cZzjz/JIyMG9wRz+cQzpJMUE9Sa/4mEoHK3yMVJwhAE=; b=fdpTN+sIFQOyOWdAyxFUXyX02ARqvr6iE9bYmyvHOnDT8zEpcPtvo7sJvtpCWT1nyp GULEGlddFomtk59cSpjY47iqTFtUv/Fh90eUePQ+oaXNcohW99LtFKXBrbiuDk0rv7QM Ojq+tboq9Zx+urRVd66D5sa6Z0SvZpaS95o0kgE5A2VwDoqPOvyJvXI9XYHBy50n4lEF eUkUCSSbP1oBdjIfay1xBVZ6uAcLaoLwkBDgAKWVEYiwo69UWcY48KvRRdNpkatS5dsF EMX1NXkVHcEPvEMChXM5GUspfedczXfN/biP7JrAbrYeNRvy/8dXtQ1C/l3V9ZP3AP8C x7aA== X-Gm-Message-State: AOAM532dTY82cqjkdoMuak9EYjVLmfMBwca+uLYY93ozWJkV0UExNZeg UoeuK+/8pt2aIXwvv0j2uVzsC9G7XQ40Nj523rDQXFO48j8= X-Google-Smtp-Source: ABdhPJxzfGm7lc1O6TVPCQHMB01msGcNybdLSwHl0n5Cj1Ey0pQ17qZb1KkEOaiw3wBYk47EOHdgNfgPxApHfbqMUro= X-Received: by 2002:a2e:3a11:: with SMTP id h17mr9441387lja.363.1620420972049; Fri, 07 May 2021 13:56:12 -0700 (PDT) MIME-Version: 1.0 References: <6201b367-d577-b30d-e6ce-30443eceff8d@bluewin.ch> In-Reply-To: From: Henry Poras Date: Fri, 7 May 2021 16:56:00 -0400 Message-ID: Subject: Re: sql monitor To: Sayan Malakshinov Cc: Lothar Flatz , ORACLE-L Content-Type: multipart/alternative; boundary="0000000000006cf6e805c1c3a81c" X-archive-position: 79867 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: henry.poras@gmail.com Precedence: normal Reply-To: henry.poras@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --0000000000006cf6e805c1c3a81c Content-Type: text/plain; charset="UTF-8" Sayan, I have checked SQL Monitor both for new executions of the query and within an ongoing execution. The trace is a good ideal. Don't think I'll get to it today, but I'll post once I have some output. Thanks again. Henry On Fri, May 7, 2021 at 3:51 PM Sayan Malakshinov wrote: > Hi Henry, > > Are you restarting that query? Or do you have just one still running > execution? > Also please try to trace sqlmonitor: > http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc > > Trace in-memory the SQL_MONITOR component (the target) and all its > sub-components at level high. Get high resolution time for each > trace: > alter session set events 'trace[sql_mon.*] memory=high, > get_time=highres'; > > On Fri, May 7, 2021 at 10:44 PM Henry Poras wrote: > >> Nothing in gv$sql_plan_monitor for my sql_id. >> Henry >> >> >> On Fri, May 7, 2021 at 3:38 PM Henry Poras wrote: >> >>> I have looked in ash and it helps, but sql monitor would be better. >>> Also, this got me wondering what is going on with the Monitor. I have >>> checked v$sql_monitor and nothing shows up with my sql_id. I have not >>> looked in v$sql_plan_monitor. I'll give it a shot, but will be surprised if >>> it is the outlier. >>> >>> Henry >>> >>> >>> On Fri, May 7, 2021 at 2:12 PM Sayan Malakshinov >>> wrote: >>> >>>> Hi, >>>> >>>> Have you checked v$sql_monitor? V$sql_plan_monitor? >>>> >>>> Best regards, >>>> Sayan Malakshinov >>>> Oracle performance tuning expert >>>> Oracle Database Developer Choice Award winner >>>> Oracle ACE Associate >>>> http://orasql.org >>>> >>>> On Fri, May 7, 2021, 21:06 Lothar Flatz wrote: >>>> >>>>> I have seen queries that just won't show up in sql monitor. I do not >>>>> know why. >>>>> One idea is to increase the shared_pool. >>>>> Alternatively you might query ash, which helps to some extend. >>>>> >>>>> At least you can group by sql_plan_line_id to find out where the time >>>>> is spent. >>>>> I guess you have a parallel query. You can still use runtime stats as >>>>> shown here: https://jonathanlewis.wordpress.com/2016/05/11/dbms_xplan/ >>>>> >>>>> Good luck >>>>> >>>>> Lothar >>>>> >>>>> >>>>> Am 07.05.2021 um 19:50 schrieb Henry Poras: >>>>> >>>>> Thanks Lothar and Mohamed. I've checked for both of those >>>>> possibilities. The query is not still parsing. I see, for example, multiple >>>>> entries on multiple lines of the execution plan in v$active_session_history >>>>> (and in_sql_execution is 'Y'). I've also been looking in gv$sql_monitor for >>>>> this sql_id and running dbms_sqltune.report_sql_monitor while the query is >>>>> still running (at ~5-10 minutes, 30 minutes, ...) with no luck. >>>>> >>>>> Henry >>>>> >>>>> On Fri, May 7, 2021 at 1:19 PM Lothar Flatz >>>>> wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> If a query is running a long time, details might age out. >>>>>> I have a long running query at one of my customers and I can see all >>>>>> details EM type report about 30 minutes. >>>>>> A monitor in Text format is visible a lot longer. >>>>>> You might create a Text format Monitor manually: >>>>>> >>>>>> set longchunk 50000 >>>>>> set long 50000 >>>>>> set lines 200 pages 0 >>>>>> set trimspool on >>>>>> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'9mmcywzzj5myn', >>>>>> report_level=>'ALL',Type=>'TEXT') as report >>>>>> from dual; >>>>>> >>>>>> Always keep in mind though, that in many cases it is not necessary to >>>>>> let a query finish to see its issue. >>>>>> 30 Minutes will often be enough . >>>>>> >>>>>> Regards >>>>>> >>>>>> Lothar >>>>>> >>>>>> >>>>>> Am 07.05.2021 um 18:50 schrieb Henry Poras: >>>>>> > I think I'm missing something silly here, but I don't know what. >>>>>> > >>>>>> > I'm having an issue with getting a sql_monitor report and I'm >>>>>> running >>>>>> > (well, ran) out of ideas on what to try next. I have a long running >>>>>> > query (~2 hours on 12.1.0.2) for which I am trying to get a report >>>>>> > using dbms_sqltune.report_sql_monitor. The result returned is >>>>>> always >>>>>> > empty. So I tried: >>>>>> > - setting _sqlmon_max_planlines to 1000 (execution plan is ~600 >>>>>> lines >>>>>> > using display_cursor('adaptive')) >>>>>> > - checked statistics_level which is correct. It is TYPICAL >>>>>> > - control_management_pack_access is DIAGNOSTIC+TUNING (also correct) >>>>>> > I thought it might be aging out of memory since it ran long, but >>>>>> > running the report and looking in gv$sql_monitor after 2, and 10-15 >>>>>> > minutes still showed nothing. Adding a MONITOR hint to the CTE and >>>>>> > body of the sql didn't help. Neither did running an ALTER SYSTEM >>>>>> SET >>>>>> > EVENTS 'sql_monitor [sql:...] force=true'. >>>>>> > I can't figure out why I am getting nothing. Anybody have any ideas? >>>>>> > >>>>>> > Thanks. >>>>>> > Henry >>>>>> >>>>>> >>>>> > > -- > Best regards, > Sayan Malakshinov > Oracle performance tuning engineer > Oracle ACE Associate > http://orasql.org > --0000000000006cf6e805c1c3a81c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Sayan,
I have checked SQL Monitor both for new executi= ons of the query and within an ongoing execution.

= The trace is a good ideal. Don't think I'll get to it today, but I&= #39;ll post once I have some output.=C2=A0

Thanks = again.
Henry
=C2=A0

On Fri, May 7, 2021 at 3:51 PM= Sayan Malakshinov <xt.and.r@gmail= .com> wrote:
Hi Henry,

Are you restarting that q= uery? Or do you have just one still running execution?
Also pleas= e try to trace sqlmonitor:=C2=A0http://tech.e2sn.com/oracle/tro= ubleshooting/oradebug-doc

Trace in-memory the SQL_MONITOR component (the = target) and all its
=C2=A0=C2=A0 =C2=A0 =C2=A0sub-components at level high. Get high resol= ution time for each
=C2=A0=C2=A0 =C2=A0 =C2=A0trace:
=C2=A0=C2=A0 =C2=A0 =C2=A0alter session set events &= #39;trace[sql_mon.*] memory=3Dhigh,
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 get_time=3Dhighres';

On Fri, May 7, 2021 at 10:44 PM Henry Poras <henry.poras@gmail.com&= gt; wrote:
Nothing in gv$sql_plan_monitor for my sql_id.
Henry


On Fri, May 7, 2021 at 3:38 PM Henry Poras <henry.poras@gmail.com> w= rote:
I have looked in ash and it helps, but sql monitor would be better= . Also, this got me wondering what is going on with the Monitor. I have che= cked v$sql_monitor and nothing shows up with my sql_id. I have not looked i= n v$sql_plan_monitor. I'll give it a shot, but will be surprised if it = is the outlier.

Henry


On Fri, May 7,= 2021 at 2:12 PM Sayan Malakshinov <xt.and.r@gmail.com> wrote:
Hi,=C2=A0

Have you checked v$sql_monitor? V$sql= _plan_monitor?=C2=A0

Best regards,
Sayan Malaks= hinov
Oracle performance tuning expert
Oracle Database Developer Cho= ice Award winner
Oracle ACE Associate
http://orasql.org

On Fri, May 7, 2021,= 21:06 Lothar Flatz <l.flatz@bluewin.ch> wrote:
=20 =20 =20
I have seen queries that just won't show up in sql monitor.=C2=A0 I do not know why.
One idea is to increase the shared_pool.
Alternatively you might query ash, which helps to some extend.

At least you can group by sql_plan_line_id to find out where the time is spent.
I guess you have a parallel query. You can still use runtime stats as shown here: https://jonathanlewis.wordpress.com/= 2016/05/11/dbms_xplan/

Good luck

Lothar


Am 07.05.2021 um 19:50 schrieb Henry Poras:
=20
Thanks Lothar and Mohamed. I've checked for both= of those possibilities. The query is not still parsing. I see, for example, multiple entries on multiple lines of the execution plan in v$active_session_history (and in_sql_execution is 'Y= 9;). I've also been looking in gv$sql_monitor for this sql_id and running dbms_sqltune.report_sql_monitor while the query is still running (at ~5-10 minutes, 30 minutes, ...) with no luck.

Henry

On Fri, May 7, 2021 at 1:19 P= M Lothar Flatz <l.flatz@bluewin.ch> wrote:
Hi,

If a query is running a long time, details might age out.
I have a long running query at one of my customers and I can see all
details EM type report about 30 minutes.
A monitor in Text format is visible a lot longer.
You might create a Text format Monitor manually:

set longchunk 50000
set long 50000
set lines 200 pages 0
set trimspool on
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=3D>'9mmcywzzj5myn&#= 39;,
=C2=A0=C2=A0=C2=A0 report_level=3D>'ALL',Type=3D>&#= 39;TEXT') as report
from dual;

Always keep in mind though, that in many cases it is not necessary to
let a query finish to see its issue.
30 Minutes will often be enough .

Regards

Lothar


Am 07.05.2021 um 18:50 schrieb Henry Poras:
> I think I'm missing something silly here, but I don'= t know what.
>
> I'm having an issue with getting a sql_monitor report an= d I'm running
> (well, ran) out of ideas on what to try next. I have a long running
> query (~2 hours on 12.1.0.2) for which I am trying to get a report
> using dbms_sqltune.report_sql_monitor. The result returned is always
> empty. So I tried:
> - setting _sqlmon_max_planlines to 1000 (execution plan is ~600 lines
> using display_cursor('adaptive'))
> - checked statistics_level which is correct. It is TYPICAL
> - control_management_pack_access is DIAGNOSTIC+TUNING (also correct)
> I thought it might be aging out of memory since it ran long, but
> running the report and looking in gv$sql_monitor after 2, and 10-15
> minutes still showed nothing. Adding a MONITOR hint to the CTE and
> body of the sql didn't help. Neither did running an ALTE= R SYSTEM SET
> EVENTS 'sql_monitor [sql:...] force=3Dtrue'.
> I can't figure out why I am getting nothing. Anybody hav= e any ideas?
>
> Thanks.
> Henry




--
Best regards,
Sayan Malaksh= inov
Oracle performance tuning engine= er
Oracle ACE Associate
http://orasql.org
=
--0000000000006cf6e805c1c3a81c-- -- http://www.freelists.org/webpage/oracle-l