Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 9B9E1100340F4D for ; Wed, 8 Aug 2018 20:35:32 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1E62528999; Wed, 8 Aug 2018 14:35:31 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1533753331; bh=wUe0UOpz3JYa3MPYLDj9UbGp5/TAxOhCPEyKHfWko90=; h=References:In-Reply-To:From:Date:Subject:To:Cc:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=sx1N4aDur8ACtjBjb4Tf1Lmzq5+qgoh4GE6NvNvo1RMj9i5J7d5vssb5TZVssbpB8 xhfi9SWzly4isJol8zTeK8jyarbF9IaGM3QbscR7LIdc0GdXfSK1kwKgrMy5xXj6eL J1elUawnpdI4ZtvlflmDr+Lp3z1bFpgWVMCGYAxo= X-Virus-Scanned: Debian amavisd-new at turing.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 HyU3M5cKZw7n; Wed, 8 Aug 2018 14:35:30 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A2D452895F; Wed, 8 Aug 2018 14:35:11 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1533753324; bh=wUe0UOpz3JYa3MPYLDj9UbGp5/TAxOhCPEyKHfWko90=; h=References:In-Reply-To:From:Date:Subject:To:Cc:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=FBu+R5wXBNcQYfvjXFGARsP5jp2GX1827BffaG784fackImMS9hfWSJq07fUs8V0c RkrZA1OH2PO1ekC5ay5E7AUmVJ7tcPehRr89J+QriHn+UzidHG76kVYBgPcnYJAfph Kpv9YwQvRYbCJBgcKoI+54QYfKeyVhqVeAVMXlfc= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 08 Aug 2018 14:33:49 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5E175288CB for ; Wed, 8 Aug 2018 14:33:49 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=pass reason="1024-bit key; unprotected key" header.d=returnpath.com header.i=@returnpath.com header.b=XFA3Kc2Y; dkim-adsp=pass; 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 y_2DBG0VgPX5 for ; Wed, 8 Aug 2018 14:33:49 -0400 (EDT) Received: from mail-it0-f47.google.com (mail-it0-f47.google.com [209.85.214.47]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id E3ADC28796 for ; Wed, 8 Aug 2018 14:33:48 -0400 (EDT) Received: by mail-it0-f47.google.com with SMTP id 72-v6so4769344itw.3 for ; Wed, 08 Aug 2018 11:33:48 -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=j7mqj8fy0l3a62i0xXOGMtIO1lulWFE4gFoXhlE1P5w=; b=WU1rqDtQHelSxvvhIKgsgeNWdPipg2oBoXtH8vvW0jaaLkZd3+WCv9PpSDHH7eJ8L0 /z0nKj+pCLsj8WOqtjLAZQIs4fSRFk1cOH20QCJVxVBg8Vrg/jGWY99EknumFkMxptQ2 xm+N9K0SY4OCIiVVB9cbToRZ3mheADnFnw5InolJbQnHFlW8MBABxJe7eu293wRWhYdN /K+yTa5vp8i/om/2Bb2SbPULyfGcIKWdJnjePetZPcF+kfe/S1GWTPS46qOb9CmKPSnP 8w0BRL9jiUtjDilj8oKTAszA0KUGA6+OXaB3KIchii89m5MJIXJRDSY3oiW1H9acTqGv oYEQ== X-Gm-Message-State: AOUpUlHKCo7szqFzL+KDwQjPz6xuybfpRlxlt4VE5X8WhRuRGkjG5nuV eyNaozDTD8bjyvtfEZyrCvyJUAIF2soldM/K1LzvIA== X-Google-Smtp-Source: AA+uWPxRAGa0Z8YfqlD3rL8WpAiXTjVWg9mSTUH1sbNFU0l+hrk1x2EbV5ilB2nNKvkhY6i4/5IAG2m+3/ycst9ljUc= X-Received: by 2002:a24:715:: with SMTP id f21-v6mr180696itf.92.1533753228345; Wed, 08 Aug 2018 11:33:48 -0700 (PDT) MIME-Version: 1.0 References: <03e001d42f43$3ab9dac0$b02d9040$@rsiz.com> In-Reply-To: <03e001d42f43$3ab9dac0$b02d9040$@rsiz.com> From: "Daniel Fink" (Redacted sender "daniel.fink" for DMARC) Date: Wed, 8 Aug 2018 12:33:37 -0600 Message-ID: Subject: Re: Group by wrong results? To: mwf@rsiz.com Cc: oracle-l@freelists.org Content-Type: multipart/alternative; boundary="00000000000059664a0572f0c03d" X-archive-position: 72044 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: dmarc-noreply@freelists.org Precedence: normal Reply-To: dmarc-noreply@freelists.org 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 --00000000000059664a0572f0c03d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable The 2nd query I posted is returning the data I want (max number of sessions per minute, not a total count of sessions per minute). I pulled the queries apart and checked the data output. On Wed, Aug 8, 2018 at 12:12 PM Mark W. Farnham wrote: > Hmm. > > > > with session_count as > > ( > > select sample_time, count(1) sess_count, min(trunc(sample_time,=E2=80= =99MI=E2=80=99) > sample_minute > > from dba_hist_active_sess_history > > where sample_time > trunc(sysdate-(1/24)) > > group by sample_time > > ) > > select sample_minute, max(sess_count) max_sessions > > from session_count > > group by sample_minute > > order by sample_minute > > > > If I understand, you want at most a single time row per minute with that > row being the sample with the most sessions (the high sampled session_cou= nt > in each minute sampled). > > > > I don=E2=80=99t believe your code is WRONG, but I=E2=80=99m curious wheth= er calculating > the minute in the initial view gets you the correct answer. I just typed > this in, so it=E2=80=99s possible I fubar=E2=80=99d the syntax. > > > > mwf > > > > *From:* oracle-l-bounce@freelists.org [mailto: > oracle-l-bounce@freelists.org] *On Behalf Of *Daniel Fink (Redacted > sender "daniel.fink" for DMARC) > *Sent:* Tuesday, August 07, 2018 12:26 PM > *To:* oracle-l@freelists.org > *Subject:* Group by wrong results? > > > > A script to extract session count from ash data is not returning the righ= t > results using a TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') group by unless = I > use a factored subquery. > > > > Query 1 > > WITH session_count AS > > ( SELECT sample_time, count(1) sess_count > > FROM dba_hist_active_sess_history > > WHERE sample_time >=3D TRUNC(sysdate - (1/24)) > > GROUP BY sample_time > > ) > > SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute, > > MAX(sess_count) max_sessions > > FROM session_count > > GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') > > ORDER BY sample_minute > > / > > Wrong Results example > > SAMPLE_MINUTE SESS_COUNT > > -------------- ---------- > > 20180727 09:09 5 > > 20180727 09:09 1 > > 20180727 09:09 1 > > 20180727 09:09 5 > > 20180727 09:09 2 > > 20180727 09:09 4 > > 20180727 09:10 3 > > 20180727 09:10 5 > > 20180727 09:10 1 > > 20180727 09:10 4 > > 20180727 09:10 7 > > 20180727 09:10 9 > > > > If I add another factored subquery, I get the right aggregation > > WITH session_count AS > > ( SELECT sample_time, count(1) sess_count > > FROM dba_hist_active_sess_history > > WHERE sample_time >=3D sysdate - (1/24) > > GROUP BY sample_time > > ), > > session_minutes > > AS > > ( SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute, > > sess_count > > FROM session_count > > ) > > SELECT sample_minute, > > MAX(sess_count) > > FROM session_minutes > > GROUP BY sample_minute > > ORDER BY sample_minute > > > > Correct results > > SAMPLE_MINUTE MAX(SESS_COUNT) > > -------------- --------------- > > 20180727 09:01 22 > > 20180727 09:02 22 > > 20180727 09:03 15 > > 20180727 09:04 10 > > 20180727 09:05 11 > > 20180727 09:06 10 > > 20180727 09:07 20 > > 20180727 09:08 20 > > 20180727 09:09 5 > > 20180727 09:10 9 > > 20180727 09:11 12 > > 20180727 09:12 12 > > 20180727 09:13 9 > > 20180727 09:14 6 > > 20180727 09:15 3 > > > > Why is the first query (which looks correct to me) not properly > aggregating? > --00000000000059664a0572f0c03d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
The 2nd query I posted is returning the data I want (max n= umber of sessions per minute, not a total count of sessions per minute). I = pulled the queries apart and checked the data output.=C2=A0

On Wed, Aug 8, 2018 at 12:12 PM Mark = W. Farnham <mwf@rsiz.c= om> wrote:

Hmm.

=C2=A0

with session_count as

(

=C2=A0 select sample_time, count(1) s= ess_count, min(trunc(sample_time,=E2=80=99MI=E2=80=99) sample_minute=

=C2=A0= =C2=A0=C2=A0=C2=A0 from dba_hist_active_sess_history

=C2=A0=C2=A0=C2=A0 wher= e sample_time > trunc(sysdate-(1/24))

=C2=A0=C2=A0=C2=A0 group by sampl= e_time

)

select sample_minute, max(sess_count) max_sessions

=C2=A0=C2=A0 from = session_count

=C2=A0 group by sample_minute

=C2=A0 order by sample_minute<= /u>

<= /u>=C2=A0

=C2=A0

I don=E2=80=99t believe your code is WRONG, = but I=E2=80=99m curious whether calculating the minute in the initial view = gets you the correct answer. I just typed this in, so it=E2=80=99s possible= I fubar=E2=80=99d the syntax.

=C2=A0

mwf

=C2=A0

From: oracle-l-bounce@freel= ists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Daniel = Fink (Redacted sender "daniel.fink" for DMARC)
Sent: Tu= esday, August 07, 2018 12:26 PM
To: oracle-l@freelists.org
Subject: Group by wrong results?

=C2=A0

A script to extract sessi= on count from ash data is not returning the right results using a TO_CHAR(s= ample_time, 'YYYYMMDD hh24:mi') group by unless I use a factored su= bquery.

=C2=A0

Query 1

WITH session_count AS

=

( SELECT sample_time, count(1) sess_count<= /u>

=C2=A0 FROM dba_hist_active_sess_h= istory

=C2=A0 WHERE samp= le_time >=3D TRUNC(sysdate - (1/24))

=C2=A0 GROUP BY sample_time

)

SE= LECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sample_minute,=

=C2=A0 =C2=A0 =C2=A0 =C2=A0MAX= (sess_count) max_sessions

FROM=C2=A0 =C2=A0session_count

GROUP BY TO_CHAR(sample_time, 'YYYYMMDD hh24:mi')

ORDER BY sample_minute=

/

Wrong Results example

SAMPLE_MINUTE=C2=A0=C2=A0<= span class=3D"m_4328246588650761912m_6750371056882584385gmail-m-15700307678= 90357654gmail-s1">SESS_COUNT

-------------- ---= -------

20180727 09:09 5

20180727 09:09 1

20180727 0= 9:09 1

20180727 09:09<= span class=3D"m_4328246588650761912m_6750371056882584385gmail-m-15700307678= 90357654gmail-apple-tab-span"> 5

20180727 09:09 2

20180727 09:09 4

20180727 0= 9:10 3

20180727 09:10<= span class=3D"m_4328246588650761912m_6750371056882584385gmail-m-15700307678= 90357654gmail-apple-tab-span"> 5

20180727 09:10 1

20180727 09:10 4

20180727 0= 9:10 7

20180727 09:10<= span class=3D"m_4328246588650761912m_6750371056882584385gmail-m-15700307678= 90357654gmail-apple-tab-span"> 9

=C2=A0

If I add another= factored subquery, I get the right aggregation

WITH session_count AS

( SELECT sample_time, count(1) sess_count

=C2=A0=C2=A0FROM dba_hist_active_sess_history

= =C2=A0=C2=A0WHERE sample_time >= =3D sysdate - (1/24)

=C2=A0=C2=A0GROUP BY sample_time

),

session_minutes=

AS

( SELECT TO_CHAR(sample_time, 'YYYYMMDD hh24:mi') sampl= e_minute,

=C2=A0 =C2=A0 =C2=A0=C2=A0= sess_count

FRO= M=C2=A0=C2=A0= =C2=A0session_count<= span style=3D"font-size:10.5pt;font-family:"Courier New";color:#f= 4f4f4">

)

SELECT sample_minute,=

=C2=A0=C2=A0 =C2=A0 =C2=A0=C2=A0MAX(sess_count)

=

FROM session_minutes

GROUP BY sample_minute<= /span>

ORDER BY sample_= minute

=C2=A0

Correct results<= u>

= =C2=A0=C2=A0MAX(SESS_COUNT)=

-------------- ---------------=

20180727= 09:01 <= span class=3D"m_4328246588650761912m_6750371056882584385gmail-m-15700307678= 90357654gmail-apple-converted-space">=C2=A0 =C2=A0=C2=A022=

20180727 09:02<= /span> =C2=A0 =C2=A0=C2=A022

20180727 09:03 =C2=A0 =C2=A0=C2=A015

20180727 09:04 =C2=A0 =C2=A0=C2=A010=

20180727 09:05= =C2=A0= =C2=A0=C2=A011

=C2=A0 =C2=A0=C2=A0<= /span>10<= /u>

20180727 09:= 07 =C2=A0 =C2=A0=C2=A0<= span class=3D"m_4328246588650761912m_6750371056882584385gmail-m-15700307678= 90357654gmail-s1">20

=

20180727 09:08 =C2=A0 =C2=A0=C2=A020

20180727 09:09 =C2= =A0=C2=A0 =C2= =A0=C2=A05

201= 80727 09:10 =C2=A0=C2=A0 =C2=A0=C2=A0<= span style=3D"font-size:10.5pt;font-family:"Courier New";color:#f= 4f4f4">9

20180727 09:11 <= span style=3D"font-size:10.5pt;font-family:"Courier New";color:#f= 4f4f4">=C2=A0 =C2=A0=C2=A012

20180727 09:12= =C2=A0= =C2=A0=C2=A012

=C2=A0=C2=A0 =C2=A0=C2=A09

20180727 09:14 =C2= =A0=C2=A0 =C2= =A0=C2=A06

201= 80727 09:15 =C2=A0=C2=A0 =C2=A0=C2=A0<= span style=3D"font-size:10.5pt;font-family:"Courier New";color:#f= 4f4f4">3

=C2=A0

Why is the fir= st query (which looks correct to me) not properly aggregating?

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